This example will return the Financial Year for today's date. This can then be set to a variable to be used in another query
me@jaykilleen.com wrote this over 6 years ago and it was last updated over 6 years ago.
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.