Special Date Filtering Options

Tony Coffman -

Special Date Filtering Options/Reserved Words

VDM has several reserved key words designed for Special Date Filtering:

@Date = Today’s Date

@XDaysAgo = Replace X with a number of days in the past; @30DaysAgo, @94DaysAgo

@XDaysOut = Replace X with a number of days in the future; @30DaysOut, @94DaysOut

@XWeeksAgo = Replace X with a number of weeks in the past; @4WeeksAgo, @8WeeksAgo

@XWeeksOut = Replace X with a number of weeks in the future; @4WeeksOut, @8WeeksOut

@ThisMonth = Creates a date filter that will return everything from the first day to the current day of the current month.

@LastMonth = Creates a date filter that will return everything from the first day to the last day of last month.

@XMonthsAgo = Replace X with a number of months in the past; @6MonthsAgo, @10MonthsAgo

@XMonthsOut = Replace X with a number of months in the future; @6MonthsOut, @10MonthsOut

@XYearsAgo = Replace X with a number of years in the past; @3YearsAgo, @4YearsAgo

@XYearsOut = Replace X with a number of years in the future; @3YearsOut, @4YearsOut

@XDayYMonthsAgo = Replace X with the Day of the month and Y with the number of Months in the past; @1Day12MonthsAgo

@XDayYMonthsOut = Replace X with the Day of the month and Y with the number of Months in the future; @1Day12MonthsOut

@LastDayofLastMonth = Gets the last day of the last month based on the date the report is executed

@TodayXYearsAgo = Gets the current date X years ago; @Today3YearsAgo, @Today1YearAgo

@FirstDayThisMonth = Gets the first date of the current month

@LastDayThisMonth = Gets the last date of the current month

@LastDayXMonthsAgo = Gets the last day of X months ago; @LastDay6MonthsAgo

@LastDayXMonthsOut = Gets the last day of X months in the future;  @LastDay6MonthsOut

 

Usage Examples:

Assigned_Date = @Date (will return everything assigned today)

Assgined_Date >= @30DaysAgo (will return everything assigned in the last 30 days)

Promise_Date >= @30DaysOut (will return all promises with a promise date over 30 days from the current date)

Assigned_Date = @1DaysAgo (will return everything assigned yesterday)

Assigned_Date >= @7DaysAgo and Assigned_Date < @Date (if run or scheduled to run on a Monday it’ll return everything assigned last week).

Transaction_Date >= @365DaysAgo (will return all transactions for the last year).

Assigned_Date = @1DaysAgo (will return everything assigned yesterday)

Assigned_Date >= @7DaysAgo and Assigned_Date < @Date (if run or scheduled to run on a Monday it’ll return everything assigned last week).

Transaction_Date >= @365DaysAgo (will return all transactions for the last year).

Assigned_Date = @4WeeksAgo (will return everything assigned today)

Transaction_Date = @ThisMonth (will return all transactions from the first of the current month through the current date)

Transaction_Date = @LastMonth (will return all transactions in the last full month)

ACCOUNT_SELFPAY.Assigned_Date >= 1Day12MonthsAgo will create the filter (ACCOUNT_SELFPAY.Assigned_Date >= '2014-01-01') AND (ACCOUNT_SELFPAY.Assigned_Date <= '2014-12-31') if the report was run in January 2015.

ACCOUNT_SELFPAY.Assigned_Date <= @LastDayofLastMonth will create the filter ACCOUNT_SELFPAY.Assigned_Date <= '12/31/2014' if the report was run in January 2015.

Transaction_Date = @Today3YearsAgo would return 10/10/2013 if Today was 10/10/2016.

Transaction_Date = @FirstDayThisMonth would return 2/1/2017 if today was 2/9/2017

Assigned_Date = @LastDayThisMonth would return 2/28/2017 if today was 2/9/2017

Transaction_Date = @LastDay3MonthsAgo would return 11/30/2017 if today was 2/9/2017

Assigned_Date = @LastDay3MonthsOut would return 5/31/2017 if today was 2/9/2017

Have more questions? Submit a request

Comments

Powered by Zendesk