Flexible Fiscal Data Sorting

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s