[SOLVED] SQL Get Financial Year from Date

Sometimes I write small queries to simply return a value direct from our databases. This might be a way to manually test against another report (ie PowerBI or Excel) or just sanity check a result. Alot of times I need to use the current financial year.

Lately I have been bypassing Excel by going straight to the databases using SQL. This is really easy if you know what you are doing and I have written up my workflow over at Using VS Code Extension MSSQL Instead of Excel Power Query for Easy Database Querying. Wow what a long title, lol XD

Anyway, so we can use this to set a variable to the current Financial Year.

DECLARE @Today date = GETDATE();
DECLARE @ThisFinancialYear varchar(4)

IF DatePart(Month, @Today) <= 6
  SET @ThisFinancialYear = DatePart(Year, @Today)
ELSE 
  SET @ThisFinancialYear = DatePart(Year, @Today) +1 

SELECT @ThisFinancialYear

We can then use the query in other querys. Note that you don't need to SELECT @ThisFinancialYear. In the last query it was only done to output the result to the screen so you can check it works. Below we pass this variable straight into our WHERE clause to filter on the FinancialYearKey column for Date.

DECLARE @Today date = GETDATE();
DECLARE @ThisFinancialYear varchar(4)

IF DatePart(Month, @Today) <= 6
  SET @ThisFinancialYear = DatePart(Year, @Today)
ELSE 
  SET @ThisFinancialYear = DatePart(Year, @Today) +1

SELECT
  CONVERT(varchar, CAST(
    SUM([_].[Amount])
  AS money), 1) AS [Revenue]
FROM
  ((dbo.FactSales AS [_]
INNER JOIN
  dbo.dimCustomer AS [Customer] ON [_].[CustomerKey] = [Customer].[CustomerKey])
INNER JOIN
  dbo.dimDate AS [Date] ON [_].[DerivedDocOrXferDateKey] = [Date].[DateKey])
WHERE
  [Customer].[Type] IN ('New','Existing') AND
  [Date].[FinancialYearKey] = @ThisFinancialYear

I know I am going to be doing this a lot which is great. Alternatively, I know that the Date model in SQL Server actually has a column to flag for the current financial year so this is probably something you (or your sysadmin) can add to your date model so that you don't even need a variable. You can just

SELECT
  CONVERT(varchar, CAST(
    SUM([_].[Amount])
  AS money), 1) AS [Revenue]
FROM
  ((dbo.FactSales AS [_]
INNER JOIN
  dbo.dimCustomer AS [Customer] ON [_].[CustomerKey] = [Customer].[CustomerKey])
INNER JOIN
  dbo.dimDate AS [Date] ON [_].[DerivedDocOrXferDateKey] = [Date].[DateKey])
WHERE
  [Customer].[Type] IN ('New','Existing') AND
  [Date].[ThisFinancialYearFlag] = true

But we don't always have the privilege of being able to modify tables in our data warehouses.

Hope this helps.

Similar