Flexible Fiscal Data Sorting
A few months ago, I faced the challenge of extracting data via SQL Queries (for our enterprise), whose fiscal year runs from July through June.
Further, the parameters fed through from SQL Server Reporting Services to our stored procedures, had to cater for multiple years, as the business folks would probably want to look back to prior years, for planning purposes. HOWEVER only one year at a time may be passed.
Reporting is ALWAYS viewed from July 1st through June 30th , either as year to date (for current year) OR in the case of past years, the whole year at one time. The important part is that when the monthly figures are shown, they must start with July and end with June.
In Short
Fiscal Year SSRS labelParameter passed to SP
(or chosen fiscal year)
@YearIncoming
2012-20132013
2013-20142014
First off, I created a piece of code that resides at the top of each stored procedure (SP). This script creates the start and end dates for the chosen fiscal year. The code to achieve this may be seen below:
Note that piece of code is based upon the input year (in the parameter @YearIncoming) passed to the stored procedure.
set @beginfiscal= (select Min(datee) from [ANC-DW].dbo.Dim_Date
where convert(int,datepart(year,datee)) + 1 = @yearincoming)
set @beginfiscal =
(case
When @yearIncoming<> '2013'** then dateadd(month,6,@beginFiscal) else @beginfiscal
end)
** The first date in my reference table is 2012-07-01
set @endfiscal= (select Min(datee) from [ANC-DW].dbo.Dim_Date
where convert(int,datepart(year,datee)) = @yearincoming)
set @endfiscal =dateadd(month,6,@endFiscal) -- 6 months past the first of
--January
The layout of table dim_Date is a shown below and runs from 2012-07-01 through 2016-06-30
Table [ANC-DW].dbo.Dim_Date
Datee DateKey WeekNumberMonthQuarter
2012-07-0120120701111
2012-07-0220120702111
2012-07-0320120703111
2012-07-0420120704111
2012-07-0520120705111
………………
Passing through ‘2013’ to the script will yield:
Start dateEnd date
2012-07-01 00:00:00.0002013-07-01 00:00:00.000 (further explanation below)
Now the plot thickens. We need some code to create month sort fields otherwise when sorted we will start with April and end with September.
Here is the code to achieve just this.
Note that for July through December that @beginFiscal is used to calculate the month. For January through June, I use the end date or @endFiscal.
set @month01 =
convert(varchar(4),datepart(Year,@beginFiscal)) +
case
when len(convert(varchar(2),datepart(Month,@beginFiscal))) = 1 then
convert(varchar(2),'0' + convert(varchar(2),datepart(Month,@beginFiscal))) else
convert(varchar(2),datepart(Month,@beginFiscal))
end
set @month02 =
convert(varchar(4),datepart(Year,@beginFiscal)) +
case
when len(convert(varchar(2),datepart(Month,dateadd(mm,1,@beginFiscal)))) = 1 then
convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,1,@beginFiscal)))) else
convert(varchar(2),datepart(Month,dateadd(mm,1,@beginFiscal)))
end
set @month03 =
convert(varchar(4),datepart(Year,@beginFiscal)) +
case
when len(convert(varchar(2),datepart(Month,dateadd(mm,2,@beginFiscal)))) = 1 then
convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,2,@beginFiscal)))) else
convert(varchar(2),datepart(Month,dateadd(mm,2,@beginFiscal)))
end
set @month04 =
convert(varchar(4),datepart(Year,@beginFiscal)) +
case
when len(convert(varchar(2),datepart(Month,dateadd(mm,3,@beginFiscal)))) = 1 then
convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,3,@beginFiscal)))) else
convert(varchar(2),datepart(Month,dateadd(mm,3,@beginFiscal)))
end
set @month05 =
convert(varchar(4),datepart(Year,@beginFiscal)) +
case
when len(convert(varchar(2),datepart(Month,dateadd(mm,4,@beginFiscal)))) = 1 then
convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,4,@beginFiscal)))) else
convert(varchar(2),datepart(Month,dateadd(mm,4,@beginFiscal)))
end
set @month06 =
convert(varchar(4),datepart(Year,@beginFiscal)) +
case
when len(convert(varchar(2),datepart(Month,dateadd(mm,5,@beginFiscal)))) = 1 then
convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,5,@beginFiscal)))) else
convert(varchar(2),datepart(Month,dateadd(mm,5,@beginFiscal)))
end
set @month07 =
convert(varchar(4),datepart(Year,@endFiscal)) +
case
when len(convert(varchar(2),datepart(Month,dateadd(mm,-6,@endFiscal)))) = 1 then
convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,-6,@endFiscal)))) else
convert(varchar(2),datepart(Month,dateadd(mm,-6,@endFiscal)))
end
set @month08 =
convert(varchar(4),datepart(Year,@endFiscal)) +
case
when len(convert(varchar(2),datepart(Month,dateadd(mm,-5,@endFiscal)))) = 1 then
convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,-5,@endFiscal)))) else
convert(varchar(2),datepart(Month,dateadd(mm,-5,@endFiscal)))
end
set @month09 =
convert(varchar(4),datepart(Year,@endFiscal)) +
case
when len(convert(varchar(2),datepart(Month,dateadd(mm,-4,@endFiscal)))) = 1 then
convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,-4,@endFiscal)))) else
convert(varchar(2),datepart(Month,dateadd(mm,-4,@endFiscal)))
end
set @month10 =
convert(varchar(4),datepart(Year,@endFiscal)) +
case
when len(convert(varchar(2),datepart(Month,dateadd(mm,-3,@endFiscal)))) = 1 then
convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,-3,@endFiscal)))) else
convert(varchar(2),datepart(Month,dateadd(mm,-3,@endFiscal)))
end
set @month11 =
convert(varchar(4),datepart(Year,@endFiscal)) +
case
when len(convert(varchar(2),datepart(Month,dateadd(mm,-2,@endFiscal)))) = 1 then
convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,-2,@endFiscal)))) else
convert(varchar(2),datepart(Month,dateadd(mm,-2,@endFiscal)))
end
set @month12 =
convert(varchar(4),datepart(Year,@endFiscal)) +
case
when len(convert(varchar(2),datepart(Month,dateadd(mm,-1,@endFiscal)))) = 1 then
convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,-1,@endFiscal)))) else
convert(varchar(2),datepart(Month,dateadd(mm,-1,@endFiscal)))
end
The end result may be seen in the table below:
yearmth
201207
201208
201209
201210
201211
201212
201301
201302
201303
201304
201305
201306
This year month combination is used as a ‘join key’ to join with records from the fact table. Each fact record has a date attached (as would any transaction).
The astute reader will note that the end date held in @endfiscal, at this point contains July 1 of the next fiscal year due to the calculations we did above. I now need to force that date to the previous day to ensure that the end date is June 30th 23hr 59 minutes and 59 seconds. This is achieved as follows:
--Take off 5 millisec to ensure that records with no times for July 1 are taken as a part of the year end
set @endFiscal = DATEADD(ms,-5,@endFiscal)
Finally
The trick is how to get the correct month names connected to each ‘year month’ combination.
This can be achieved using a simple case statement.
select yearmth,
Case
when substring(YearMth,5,6) = '01' then 'January'
when substring(YearMth,5,6) = '02' then 'February'
when substring(YearMth,5,6) = '03' then 'March'
when substring(YearMth,5,6) = '04' then 'April'
when substring(YearMth,5,6) = '05' then 'May'
when substring(YearMth,5,6) = '06' then 'June'
when substring(YearMth,5,6) = '07' then 'July'
when substring(YearMth,5,6) = '08' then 'August'
when substring(YearMth,5,6) = '09' then 'September'
when substring(YearMth,5,6) = '10' then 'October'
when substring(YearMth,5,6) = '11' then 'November'
when substring(YearMth,5,6) = '12' then 'December'
end as [Month] .. and fact data.
Thus when I create my report within SSRS I use the ‘year month’ as the sort field and the TRUE MONTH name as a secondary sort field. The ‘year month’ is hidden and I only show the true month.
As always should you have any questions or concerns, please feel free to contact me at
Steve.simon@sqlpass.org
