Workdays per week from our Enterprise calendar RRS feed

  • Question

  • I need to pull the number of workdays in a week from our Enterprise calendar into a SQL results set (Thanksgiving week only has 3 work days, etc).  Can anyone point me in the direction of a table where I can pull that number or to one where I can calculate that number via SQL?


    DJ Johnson

    Friday, November 9, 2012 9:54 PM


All replies

  • Hey DJ!

    The data isn't available in the RDB. However, you might be able to derive it from the Resource By Day (MSP_EPMResourceByDay_UserView) table. The capacity of a resource is stored by day so if the day is non-working, the capacity will be zero. You could set up a generic resource assigned to a corporate calendar for this purpose.

    Hope this helps!

    Treb Gatte | @tgatte |

    Saturday, November 10, 2012 12:00 AM
  • Thanks Treb.  Yes, I was working along those lines when I made the post, but was hoping there was an easier way.  Finally got what I needed to work right before walking out the door this evening.

    Created a function that added up the hours and pulled it into the PowerPivot results set with the SQL:

    MAX(ProjectServer_Reporting.dbo.fn_getworkdaysinweek(CONVERT(varchar, dateadd(day, - DATEDIFF(dd, @@DATEFIRST - 1, CONVERT(Date, ProjectServer_Reporting.dbo.MSP_EpmResourceByDay_UserView.TimeByDay)) % 7 + 7, ProjectServer_Reporting.dbo.MSP_EpmResourceByDay_UserView.TimeByDay),111))) AS DaysInWeek

    Created a calculated field that multiplied the the DaysInWeek number by the resource max percentage to calculate the number of hours/heads per week.  Whew!

    Thanks for letting me know I was pointed in the right direction!


    DJ Johnson

    Saturday, November 10, 2012 5:44 AM