0

I am trying to get the due date of a project with the current date(today) by passing the ProjectId. I'm using SQL2008, this all seems complex for me as I need to validate it between WorkingDay and Holidays days.

Below are the tables.

Holidays
-------------------------------------------------------------
Id      ClientCode  Name        BusinessDays    
--------------------------------------------------------------
1   PA1000      holiday 1   2014-08-27  
3   PA1000      holiday 2   2014-08-29  
6   PA1001      holiday 1   2014-08-14  
7   PA1001      holiday 2   2014-08-29  
8   PA1001      holiday 3   2014-09-05  
9   PA1001      holiday 4   2014-09-12  
10  PA1002      holiday 1   2014-09-29  

Projects
----------------------------------------------------------------------------------------------------
ProjectId   ClientCode  ProjectName Description StartDate   TargetDate
----------------------------------------------------------------------------------------------------
P1      PA1000      Project 1   xyz Des     2014-07-29  2014-09-29 
P2      PA1001      Project 2   ABC Des     2014-08-29  2014-09-29 
P3      PA1002      Project 3   MNO Des     2014-08-29  2015-08-29 


WorkingDay
----------------------------------
Id  ClientCode  NoOfDay
----------------------------------
1   PA1001      5
2   PA1000      7
3   PA1002      6

Any help is much appreciated.

matt freake
  • 4,877
  • 4
  • 27
  • 56
user3913564
  • 1
  • 1
  • 1
    I'm a little unclear - are you asking us to compute/update `TargetDate` or is that column irrelevant? If it's irrelevant, what is your expected output given the above data? – Damien_The_Unbeliever Aug 06 '14 at 09:13
  • I've defined a project start date and target date/end date. I want to get the remaining days of the project that should exclude the holidays and include only the business day of the client, by passing the projectID. – user3913564 Aug 06 '14 at 10:30

1 Answers1

0

I assume, your targetDate is irrelevant.

To count work days, you can use this answer : https://stackoverflow.com/a/252533/911718

At this step you'll have the due date but you still have to exclude holidays.

The simpler way will be to create an holidays table and store all holidays (do not forget to keep the table up to date).

SELECT @holidaysFound=COUNT(*) FROM holidays WHERE date >= $startDate AND date <= @dueDate

Then, count how many holidays between your period (startDate to dueDate). Add this result to the dueDate and you'll have the real due date.

SET @dueDate = DATEADD(day,@holidaysFound,@dueDate)
Community
  • 1
  • 1
Kevin Labécot
  • 2,005
  • 13
  • 25
  • Thanks for the quick response. The start date may be irrelevant because I need to check the due date with respect to current date against the target/end date. while taking into consideration of the business and holidays days of the client. – user3913564 Aug 06 '14 at 10:37
  • If my answer is fine, can you accept the answer pls ? Thank you. – Kevin Labécot Aug 07 '14 at 07:09