Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Flexible Fiscal Data Sort

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 label Parameter passed to SP (or chosen fiscal year) @YearIncoming 2012-2013 2013 2013-2014 2014 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 WeekNumber Month Quarter 2012-07-01 20120701 1 1 1 2012-07-02 20120702 1 1 1 2012-07-03 20120703 1 1 1 2012-07-04 20120704 1 1 1 2012-07-05 20120705 1 1 1 ……………… Passing through ‘2013’ to the script will yield: Start date End date 2012-07-01 00:00:00.000 2013-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

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.