4
\$\begingroup\$

I have a report on SSRS (that's SQL Server Reporting Services) that I need to be able to parameterize so that it can run for..

  • Yesterday (scheduled every morning)
  • The week that just elapsed (scheduled on Sunday mornings)
  • The month that just finished (scheduled on the first Sunday of the month)

Now, in order to be able to schedule the 3 subscriptions with parameters I wouldn't need to update every time the report needs to run, I needed a way to set up the default parameter values, notably because "month" in this context isn't referring to "calendar month", but to "fiscal month", which doesn't necessarily line up with calendar months.

So I wrote this stored procedure (in a ssrs schema dedicated to SSRS-specific stuff) so that I could set up a shared dataset that contains time metadata that I could use for default parameter values.

The procedure is designed to return a single record combining all the information I might need to set the default parameter values for that report, and pretty much any other report I'm going to be designing in the next couple of months.

It runs in 00:00:00 - but is it as good as it gets?

create procedure ssrs.TimeMetadata
as

    with tToday as (        
        select * from dwd.FiscalCalendars where CalendarDate = cast(getdate() as date)
    )
    ,tCurrentWeek as (  
        select
             t.FiscalYear TW_FiscalYear
            ,t.FiscalQuarterOfYear TW_FiscalQuarterOfYear
            ,t.FiscalMonthOfYear TW_FiscalMonthOfYear
            ,t.FiscalWeekOfYear TW_FiscalWeekOfYear
            ,t.CalendarDate TW_WeekEndingDate
        from dwd.FiscalCalendars t
            inner join tToday on t.FiscalWeekOfYear = tToday.FiscalWeekOfYear
                            and t.FiscalYear = tToday.FiscalYear
        where 
             t.FiscalDayOfWeek = 7 -- week ending dates
    )
    ,tCurrentMonth as (
        select
             t.FiscalYear TM_FiscalYear
            ,t.FiscalQuarterOfYear TM_FiscalQuarterOfYear
            ,t.FiscalMonthOfYear TM_FiscalMonthOfYear
            ,m.NameEN TM_FiscalMonthNameEN
            ,m.NameFR TM_FiscalMonthNameFR
            ,max(t.CalendarDate) TM_MonthEndingDate
        from dwd.FiscalCalendars t
            inner join dbo.MonthNames m on t.FiscalMonthOfYear = m.FiscalMonthOfYear
            inner join tToday on t.FiscalMonthOfYear = tToday.FiscalMonthOfYear
                            and t.FiscalYear = tToday.FiscalYear
        where 
             t.FiscalDayOfWeek = 7 -- week ending dates
        group by
             t.FiscalYear
            ,t.FiscalQuarterOfYear
            ,t.FiscalMonthOfYear
            ,m.NameEN
            ,m.NameFR
    )
    ,tCurrentQuarter as (
        select
             t.FiscalYear TQ_FiscalYear
            ,t.FiscalQuarterOfYear TQ_FiscalQuarterOfYear
            ,max(t.CalendarDate) TQ_QuarterEndingDate
        from dwd.FiscalCalendars t
            inner join tToday on t.FiscalQuarterOfYear = tToday.FiscalQuarterOfYear
                            and t.FiscalYear = tToday.FiscalYear
        where 
             t.FiscalDayOfWeek = 7 -- week ending dates
        group by
             t.FiscalYear
            ,t.FiscalQuarterOfYear
    )
    ,tLastYearSameWeek as (
        select
             t.FiscalYear LYTW_FiscalYear
            ,t.FiscalQuarterOfYear LYTW_FiscalQuarterOfYear
            ,t.FiscalMonthOfYear LYTW_FiscalMonthOfYear
            ,t.FiscalWeekOfYear LYTW_FiscalWeekOfYear
            ,t.CalendarDate LYTW_WeekEndingDate
        from dwd.FiscalCalendars t
            inner join tToday on t.FiscalWeekOfYear = tToday.FiscalWeekOfYear
                            and t.FiscalYear = tToday.FiscalYear - 1
        where 
             t.FiscalDayOfWeek = 7 -- week ending dates 
    )
    ,tLastYearSameMonth as (
        select
             t.FiscalYear LYTM_FiscalYear
            ,t.FiscalQuarterOfYear LYTM_FiscalQuarterOfYear
            ,t.FiscalMonthOfYear LYTM_FiscalMonthOfYear
            ,m.NameEN LYTM_FiscalMonthNameEN
            ,m.NameFR LYTM_FiscalMonthNameFR
            ,max(t.CalendarDate) LYTM_MonthEndingDate
        from dwd.FiscalCalendars t
            inner join dbo.MonthNames m on t.FiscalMonthOfYear = m.FiscalMonthOfYear
            inner join tLastYearSameWeek lw on t.FiscalMonthOfYear = lw.LYTW_FiscalMonthOfYear
                            and t.FiscalYear = lw.LYTW_FiscalYear
        where 
             t.FiscalDayOfWeek = 7 -- week ending dates
        group by
             t.FiscalYear
            ,t.FiscalQuarterOfYear
            ,t.FiscalMonthOfYear
            ,m.NameEN
            ,m.NameFR
    )
    ,tLastYearSameQuarter as (
        select
             t.FiscalYear LYTQ_FiscalYear
            ,t.FiscalQuarterOfYear LYTQ_FiscalQuarterOfYear
            ,max(t.CalendarDate) LYTQ_QuarterEndingDate
        from dwd.FiscalCalendars t
            inner join tLastYearSameMonth tm on t.FiscalQuarterOfYear = tm.LYTM_FiscalQuarterOfYear
                            and t.FiscalYear = tm.LYTM_FiscalYear
        where 
             t.FiscalDayOfWeek = 7 -- week ending dates
        group by
             t.FiscalYear
            ,t.FiscalQuarterOfYear
    )
    ,tLastQuarter as (
        select
             t.FiscalYear LQ_FiscalYear
            ,t.FiscalQuarterOfYear LQ_FiscalQuarterOfYear
            ,max(t.CalendarDate) LQ_QuarterEndingDate
        from dwd.FiscalCalendars t
            inner join tToday 
            on t.FiscalQuarterOfYear = 
                case when tToday.FiscalQuarterOfYear = 1 then 4
                     else tToday.FiscalQuarterOfYear - 1 end
            and t.FiscalYear = 
                case when tToday.FiscalQuarterOfYear = 1 then tToday.FiscalYear - 1
                     else tToday.FiscalYear end
        where 
             t.FiscalDayOfWeek = 7 -- week ending dates
        group by
             t.FiscalYear
            ,t.FiscalQuarterOfYear
    )
    ,tLastMonth as (
        select
             t.FiscalYear LM_FiscalYear
            ,t.FiscalQuarterOfYear LM_FiscalQuarterOfYear
            ,t.FiscalMonthOfYear LM_FiscalMonthOfYear
            ,m.NameEN LM_FiscalMonthNameEN
            ,m.NameFR LM_FiscalMonthNameFR
            ,max(t.CalendarDate) LM_MonthEndingDate
        from dwd.FiscalCalendars t
            inner join dbo.MonthNames m on t.FiscalMonthOfYear = m.FiscalMonthOfYear
            inner join tToday 
            on t.FiscalMonthOfYear = 
                case when tToday.FiscalMonthOfYear = 1 then 12
                     else tToday.FiscalMonthOfYear - 1 end
            and t.FiscalYear = 
                case when tToday.FiscalMonthOfYear = 1 then tToday.FiscalYear - 1
                     else tToday.FiscalYear end
        where 
             t.FiscalDayOfWeek = 7 -- week ending dates
        group by
             t.FiscalYear
            ,t.FiscalQuarterOfYear
            ,t.FiscalMonthOfYear
            ,m.NameEN
            ,m.NameFR
    )
    ,tLastWeek as (
        select
             t.FiscalYear LW_FiscalYear
            ,t.FiscalQuarterOfYear LW_FiscalQuarterOfYear
            ,t.FiscalMonthOfYear LW_FiscalMonthOfYear
            ,t.FiscalWeekOfYear LW_FiscalWeekOfYear
            ,t.CalendarDate LW_WeekEndingDate
        from dwd.FiscalCalendars t
        where 
             t.FiscalDayOfWeek = 7 -- week ending dates
             and t.CalendarDate >= dateadd(week,-1,getdate())
             and t.CalendarDate < getdate()
    )

    select * 
    from tToday, tCurrentWeek, tCurrentMonth, tCurrentQuarter, tLastWeek, tLastMonth, tLastQuarter, tLastYearSameWeek, tLastYearSameMonth, tLastYearSameQuarter
\$\endgroup\$
2
\$\begingroup\$

Day of week

This is repeated multiple times over your script:

t.FiscalDayOfWeek = 7 -- week ending dates

Assuming your fiscal week ends on Sunday, which appears to be the case, why not just do:

declare @Sunday int = 7;

Or in case it can change (unlikely, but possible):

declare @EndOfWeekDay int = 7

Then you can replace all those calls to 7 to a meaningful variable name!

Better yet!

I think you should document the heck out of this, and specify the SET DATEFIRST value, whether it be default or not. Since this code is definitely time-sensitive, take a minute and document it.

create procedure ssrs.TimeMetadata
as
    /* Fiscal weeks normally begin on Sundays, 
       therefore setting datefirst to 7. */
    declare @Sunday int = 7;
    set datefirst @Sunday;

That way, it's clear to anyone who bothers to take a moment and look at your procedure. And if they don't, it's no longer your problem.


t prefix

I saw all your CTEs are prefixed with t, like tToday, tCurrentWeek, etc. Is there a particular reason for that? I would venture a guess that t means "time", but I could be wrong. Why not just spell it out?


Other than that...

I have to admit that your code reads good. Maybe take fewer shortcuts in naming, but other than that, it reads really well. I tried to nitpick for other things, but really, I'd be proud to have code like this in my code base.

|improve this answer|||||
\$\endgroup\$
  • \$\begingroup\$ Eh, fiscal weeks start on Sunday.. nice review! \$\endgroup\$ – Mathieu Guindon Dec 9 '15 at 2:25
  • \$\begingroup\$ You're right, the t stands for time - when I join some OrderDateCalendarId to my time table I call it tOrderDate, this way I can also have a tInvoiceDate or whatever - and when I'm only joining on one time field I just alias it t - that's where the t comes from :-) \$\endgroup\$ – Mathieu Guindon Dec 9 '15 at 2:31
  • 2
    \$\begingroup\$ I t you should alias as time so it's clear you mean t and not t \$\endgroup\$ – Phrancis Dec 9 '15 at 2:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.