Selecting time metadata off a calendar table

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

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.

StackExchange Code Review Q#113207, answer score: 2