Custom calendar for calculating workdays per month

database-designdate

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.

Best Answer

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.

Related Solutions

Mysql – Normalize repeated indefinite ongoing dates for an event

To focus on ongoing and future dates...

A table with suitable columns for simple cases of repeated actions. One row per repeating event. No specific dates except start and end.
A table for exceptions (extra dates, skipped dates, etc). This has a column with a specific date. This table could also handle simple, one-time, events.
Application code that reads those tables, and computes whether or not one of those events occurs on a particular day.

One important point here is: Do not try to do all the work in SQL; leave the messy stuff for the 'real' programming language in your application. The database is just a "repository of information" and a "source of truth". That is, store the raw data there (and only there).

Once you have made the split between what can and should be done in the database versus what can and should be done in the app, you may find the need to make minor tweaks in the boundary between them.

I don't think we can make all the decisions before starting. Instead, I like to plan to have a mid-course-correction.

Sql-server – Writing and reading date ranges for a million items

It looks like you're on the right path. Your datebit pattern idea is an interesting option, I'd like to hear if you make any more progress towards that design.

My suggestion is a pretty simple one. Why not split the ItemOfType out to their own tables and reference them with a surrogate id?. I know it isn't always a popular idea if you have good natural keys available, but in terms of space saving it could reduce your storage requirement a bit.

This was too much for a comment, please don't crucify me.

create table dbo.ItemOfType1 (
    ItemOfType1Id int identity(1,1) not null
  , ItemOfType1Code char(12) not null
  , ReferenceType bit not null
  , constraint pkc_ItemOfType1 primary key clustered (ItemOfType1Id)
  , constraint uq_ItemOfType1_ItemOfType1Code_ReferenceType unique nonclustered (ItemOfType1Code, ReferenceType)
);
create table dbo.ItemOfType2 (
    ItemOfType2Id int identity(1,1) not null
  , ItemOfType2Code char(7) not null
  , ReferenceType bit not null
  , constraint pkc_ItemOfType2 primary key clustered (ItemOfType2Id)
  , constraint uq_ItemOfType2_ItemOfType2Code_ReferenceType unique nonclustered (ItemOfType2Code, ReferenceType)
);
create table dbo.ItemOfType1Dates (
    ItemOfType1Id int  not null
  , ReferenceDate date not null
  , constraint pkc_ItemOfType1Dates primary key clustered (ReferenceDate,ItemOfType1Id)
  , constraint fk_ItemOfType1Dates_ItemOfType1_ItemOfType1Id foreign key (ItemOfType1Id) references dbo.ItemOfType1 (ItemOfType1Id)
);
create table dbo.ItemOfType2Dates (
    ItemOfType2Id int  not null
  , ReferenceDate date not null
  , constraint pkc_ItemOfType2Dates primary key clustered (ReferenceDate,ItemOfType2Id)
  , constraint fk_ItemOfType2Dates_ItemOfType2_ItemOfType2Id foreign key (ItemOfType2Id) references dbo.ItemOfType2 (ItemOfType2Id)
);
/*  -- date ranges alternate
create table dbo.ItemOfType1DateRanges (
    ItemOfType1Id int  not null
  , FromBusinessDate date not null
  , ToBusinessDate date not null     
  , constraint pkc_ItemOfType1DateRanges primary key clustered (FromBusinessDate,ItemOfType1Id)
  , constraint fk_ItemOfType1DateRanges_ItemOfType1_ItemOfType1Id foreign key (ItemOfType1Id) references dbo.ItemOfType1 (ItemOfType1Id)
);
create table dbo.ItemOfType2DateRanges (
    ItemOfType2Id int  not null
  , FromBusinessDate date not null
  , ToBusinessDate date not null     
  , constraint pkc_ItemOfType2DateRanges primary key clustered (FromBusinessDate,ItemOfType2Id)
  , constraint fk_ItemOfType2DateRanges_ItemOfType2_ItemOfType2Id foreign key (ItemOfType2Id) references dbo.ItemOfType2 (ItemOfType2Id)
);    
*/