1

I need to create a custom report for my PMO (Project Management Office) where they want allocation to be calculated by multiplying working days for each month * total hours.

For example, January 2014 should have 21 working days (2 holidays minus Saturday and Sunday) * 8 = 168 hours.

How would I go about creating this logic for the calendar? Should I focus on creating a function that will calculate working days for each month? Also we have various calendars but the server that I'm using doesn't have any connection to them.

1

I'd start with a table that contains the date and some convenience columns plus a workday indicator. (date full db date column, day, month, year, day_of_week, work_day Y/N field, isvacation, isweekday).

Populate that with values in the time range you're going to use. Set appropriate flags (e.g. update date_table set isweekday where day_of_week between 1 and 5) and update all the work_day and isvacation flags by vacation that your company observes. This is going to be a manual process, but not daunting. Some holidays are the same day each year, some are not. A vacation day could fall on the weekend, but be observed the previous Friday, etc.

Then you can calculate work hours pretty easily (e.g. select count(1)*8 from date_table where month = 1 and year = 2014 and work_day = 'Y'

Once the table is created, you can use it for lots of stuff. Add a quarter field, first work day of the month field, day names, month names, etc.. Pretty handy, especially for reporting, just join on the truncated date column in your source data to the date field in the table.

0

I would create a table (holidays in the example below) to store all your company holidays, then write the calendar logic as a function, which calculates all weekdays minus the holidays in your holiday table between specified date range. This I believe is the easiest way to get the total workdays. You can then multiply with hrs/day to get total hours.

My solution as per your example:

create table holidays
(holiday  date);
go

insert into holidays
values ('20140101'), ('20140120');
go

declare @startdate as date = '20140101';
declare @enddate as date = '20140131';

with cte as(
select (DATEDIFF(dd, @startdate, @enddate) + 1)
    -(DATEDIFF(wk, @startdate, @enddate) * 2
    -(case when DATEPART(dw, @startdate) in (7,1) then 1
      else 0 end)) as totalweekdays
union
select COUNT(holiday)*(-1) as totalholidays from holidays
where holiday >= @startdate and holiday <= @enddate)
select sum(totalweekdays)*8 as TotalWorkHours from cte;

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.