How To: Finance Date Functions in EDW

Do you ever get tired of building complicated date logic in your EDW queries?

The EDW now supports several finance date functions to help you simplify your queries! These functions help enterprise data users to easily convert calendar dates, months, and years to UW fiscal calendar objects. These functions are most helpful to SQL developers working on reports and visualizations that use fiscal calendar parameters/variables.

Context: 

In SQL, a function is a program that takes some input and generates a value. SQL has many built-in functions that you get out-of-the-box.  For example, one of the built-in system functions is GETDATE().  This function returns the current date and does not require an input or argument. Another example of a system function is REPLACE(), which expects to have 3 inputs, 1. The expression you want to search (i.e. column or field), 2. what you are searching for, and 3. what you want to replace it with.

The UW-IT EDW team recently added 5 new functions to the EDW. The following functions are now available to use:

How you can use these functions today: 

If you ever do your own calculations in SQL to determine things like Fiscal Year or Biennium start or end dates, or create a rolling window, then you might benefit from using these functions.

These functions reside in EDWPresentaton database.  To call a function simply follow examples below:

SELECT 
EDWPresentation.dbo.FiscalYear('4/4/2018') as FiscalYear,
EDWPresentation.dbo.FiscalMonth('4/4/2018') as FiscalMonth,
EDWPresentation.dbo.BienniumYear('4/4/2018') as BienniumYear,
EDWPresentation.dbo.BienniumMonth('4/4/2018') as BienniumMonth,
EDWPresentation.dbo.LastMonthEndDate('4/4/2018') as LastMonthEndDate

Please Note:

• When functions are used as parameters in a report or visualization, they should be set as variables.
• Also, the BienniumMonth function returns values ranging from 01-24; however, UW accounting months are 01-25 or in previous years may even have fallen between 01-27. This happens because some transactions are entered in a new biennium, but should be attributed to the prior one. Examples below show how to use functions in variables and how to handle accounting months

Examples

SQL – if parameters are start and end dates

--Report parameters DECLARE @MyDate date = '4/4/2018' 
DECLARE @FiscalYear varchar(4) = EDWPresentation.dbo.FiscalYear(@MyDate)  DECLARE @AcctngMonth int = 
case 
   when EDWPresentation.dbo.BienniumMonth(@MyDate) = '24' 
      then '27'  
   else EDWPresentation.dbo.BienniumMonth(@MyDate) 
end DECLARE @BienniumYear varchar(4) = EDWPresentation.dbo.BienniumYear(@MyDate) SELECT @FiscalYear FiscalYear, @BienniumYear BienniumYear, @AcctngMonth AcctngMonth

SQL – if parameters are month and year

--Report parameters DECLARE @calmonth int = 9 DECLARE @calyear int = 2017 

--Converting calendar month and year to a date that can be used by functions
DECLARE @MyDate 
   date = convert(date, convert(varchar(4),@calyear) + '-' + convert(varchar(2),
       @calmonth) + '-01') 
DECLARE @FiscalYear varchar(4) = EDWPresentation.dbo.FiscalYear(@MyDate)  DECLARE @AcctngMonth int = 
case 
   when EDWPresentation.dbo.BienniumMonth(@MyDate) = '24' then '27'  
      else EDWPresentation.dbo.BienniumMonth(@MyDate) 
end
DECLARE @BienniumYear varchar(4) = EDWPresentation.dbo.BienniumYear(@MyDate)
SELECT @FiscalYear FiscalYear, @BienniumYear BienniumYear, @AcctngMonth AcctngMonth

SQL – rolling window (2 years back from end date)

--Report parameters DECLARE @EndDate date = '4/4/2018'
 --Converting end date into a 2-year rolling window  DECLARE @LastMonthEndDate date = EDWPresentation.dbo.LastMonthEndDate(@EndDate)
DECLARE @BienniumYear varchar(4) = EDWPresentation.dbo.BienniumYear(@LastMonthEndDate)
DECLARE @AcctngMonth varchar(2) = 
case 
   when EDWPresentation.dbo.BienniumMonth(@LastMonthEndDate) = '24' then '27'  
      else EDWPresentation.dbo.BienniumMonth(@LastMonthEndDate) 
end 
DECLARE @EndRollingPeriod varchar(6) = @BienniumYear + @AcctngMonth DECLARE @StartRollingPeriod varchar(6) = CONVERT(varchar(4), CONVERT(int, @BienniumYear)-2) + @AcctngMonth SELECT @StartRollingPeriod, @EndRollingPeriod

Questions:

If you have any questions about functions or how to use them in your solution, please write help@uw.edu and put “EDW Feature: Finance Date Functions” in the subject line.