Remind the Reporting Database That it Does, in Fact, Know Holiday Dates

Once again, due to our constrained systems here, I needed to make the database tell us something that it knows, but the base of that knowledge is hiding somewhere. I had to get clever with the query to remind that sucker that it knew what it knows! It’s like a ginkgo biloba injection for the reporting database.

What I wanted was the list of holidays in our standard calendar to be listed in an excel workbook, so that I could use that data in another sheet. I wanted it to be pulled from the database and not allow for human error, so I used the fact that it knows days of the week and total capacity for each day. Any day that is not a weekend and has a 0 sum capacity must be a holiday, right? Well it works for me! Here are the two tricks in this one:

Tell it the days you want (or, I suppose you could tell it the days you don’t want)

To select the days, stick this in your WHERE statement:

(MSP_TimeByDay.TimeDayOfTheWeek = 2 OR <;– (Monday, and so forth)

MSP_TimeByDay.TimeDayOfTheWeek = 3 OR

MSP_TimeByDay.TimeDayOfTheWeek = 4 OR

MSP_TimeByDay.TimeDayOfTheWeek = 5 OR

MSP_TimeByDay.TimeDayOfTheWeek = 6)

The other thing you have to do is SUM the basecapacity. This means creating what I like to call an "x" table (hack, hack!) and then summing the column in your select statement. Then of course, the last thing you have to do is use the "HAVING" clause to restrict it to show days with zero capacity. The full query pretty much speaks for itself after that little bit of explanation:

SELECT

TimeByDay,

SUM (CAP) as [Cap]

FROM

(SELECT

MSP_TimeByDay.TimeByDay,

MSP_TimeByDay.TimeDayOfTheWeek,

MSP_EpmResourceByDay_UserView.BaseCapacity as [CAP],

MSP_EpmResource_UserView.ResourceBaseCalendar

FROM

MSP_EpmResourceByDay_UserView

INNER JOIN

MSP_TimeByDay ON

MSP_TimeByDay.TimeByDay = MSP_EpmResourceByDay_UserView.TimeByDay

INNER JOIN

MSP_EpmResource_UserView ON

MSP_EpmResource_UserView.ResourceUID = MSP_EpmREsourceByDay_UserView.ResourceUID

WHERE

(MSP_TimeByDay.TimeDayOfTheWeek = 2 OR

MSP_TimeByDay.TimeDayOfTheWeek = 3 OR

MSP_TimeByDay.TimeDayOfTheWeek = 4 OR

MSP_TimeByDay.TimeDayOfTheWeek = 5 OR

MSP_TimeByDay.TimeDayOfTheWeek = 6) AND

(MSP_EpmResource_UserView.ResourceBaseCalendar like ‘%standard%’) AND

(MSP_TimeByDay.TimeByDay >;Current_Timestamp)

) as x

GROUP BY

TimeByDay

HAVING

SUM (CAP) = 0

Alright then. Have fun storming the castle!

Advertisements

2 thoughts on “Remind the Reporting Database That it Does, in Fact, Know Holiday Dates

  1. Pingback: Adding Australian Holidays to Your Project Server/Project Online Calendars in Four Easy Steps - EPM

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