Calculating Working days - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculating Working days

Author

Topic

willnewbie
Starting Member

5 Posts

Posted - 2002-11-14 : 08:43:37

I am looking to calculate the number of working days between a transaction.

The working week being Monday to Friday. I appreciate to get a truly accurate figure you should in-coroprate public holidays within this calcualtion, however as this does not effect me I am looking for a simple way should that be possible.

Simply a date where something was sent out and then the date received back.

Thanks in advance.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-11-14 : 08:51:00

Determining if a day is a working day is pretty straightforward using the datepart function. However, public holidays differ in each country. The only program I know of which contains a list of public holidays for different countries is Outlook. You could export a file from outlook with the dates for public holidays. However, it will probably not be entirely accurate.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-14 : 12:57:30

OK .... here's something you can paste into the QueryAnalyzer and give it a try. I think it works. I didn't try it for every possible case, but it looks pretty solid. The key is that datediff(wk,..) is pretty smart and isn't just the (# of days) / 7 -- it takes into accounting the difference between saturdays and sundays.

declare @startdate datetime;
declare @enddate datetime;

set @startdate = '11/6/2002';
set @enddate = '11/12/2002';

Select RawDays
- NumWeeks * 2
+ CASE WHEN StartWD = 1 THEN 1 ELSE 0 END
- CASE WHEN EndWD = 7 THEN 1 ELSE 0 END
AS Result
FROM
(Select Datepart(dw,@StartDate) as StartWD,
Datepart(dw,@EndDate) as EndWD,
DateDiff(d,@StartDate,@EndDAte) as RawDays,
DateDiff(wk,@StartDAte,@EndDate) as NumWeeks
) A

Note: if you create a table of holidays, with a key of "HolidayDate" in a table called "Holidays", you could do this:

Select RawDays
- NumWeeks * 2
+ CASE WHEN StartWD = 1 THEN 1 ELSE 0 END
- CASE WHEN EndWD = 7 THEN 1 ELSE 0 END
- (SELECT Count(*) FROM HOLIDAYS WHERE HolidayDate BETWEEN @StartDAte and @EndDate)
AS Result
FROM
(Select Datepart(dw,@StartDate) as StartWD,
Datepart(dw,@EndDate) as EndWD,
DateDiff(d,@StartDate,@EndDAte) as RawDays,
DateDiff(wk,@StartDAte,@EndDate) as NumWeeks
) A

Not sure if that will work 100% of the time, again. But you get the idea. I used a subquery to make it more readable.

Let me know how it works!
- Jeff

willnewbie
Starting Member

5 Posts

Posted - 2002-11-15 : 06:22:55

As usual I fail to give everyone the total picture.

The startdate and enddate are table values (datetime), my aim is to produce a report with the turnaround times on certain stages.

Thanks again for the input to date.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-15 : 08:12:46

Will, I think I understand 100% what your needs are, I too have needed this type of calculation to generate Accrual basis payroll entries (don't ask!). I guess I was hoping you would understand how to plug what I gave you into a SELECT query if I just gave your the algorithm. Seemed easier to understand that way.

Anyway, if you have a table called "Dates" with values of "StartDate" and "EndDate", here's how you would translate what I wrote:

Select StartDate, EndDate, (RawDays - NumWeeks * 2 + (CASE WHEN StartWD = 1 THEN 1 ELSE 0 END) - (CASE WHEN EndWD = 7 THEN 1 ELSE 0 END)) AS NumberOfWorkingDays
FROM
(Select StartDate, EndDate, Datepart(dw,StartDate) as StartWD,
Datepart(dw,EndDate) as EndWD,
DateDiff(d,StartDate,@EndDAte) as RawDays,
DateDiff(wk,StartDAte,@EndDate) as NumWeeks
FROM Dates
) A

And, again, if you wish to create a table called "Holidays" with a key of "HolidayDate" (which of course you would have to populate), you could skip the counting of holidays like so:

Select StartDate, EndDate, (RawDays - NumWeeks * 2 + (CASE WHEN StartWD = 1 THEN 1 ELSE 0 END) - (CASE WHEN EndWD = 7 THEN 1 ELSE 0 END) - (SELECT Count(*) FROM Holidays WHERE HolidayDate BETWEEN StartDate AND EndDate)) AS NumberOfWorkingDays
FROM
(Select StartDate, EndDate, Datepart(dw,StartDate) as StartWD,
Datepart(dw,EndDate) as EndWD,
DateDiff(d,StartDate,@EndDAte) as RawDays,
DateDiff(wk,StartDAte,@EndDate) as NumWeeks
FROM Dates
) A

DateDiff(w,...) basically returns the # of times you are crossing from Saturday to Sunday in a date range. We take this value and multiply it by 2 to count all weekends in the range, and subtract it from the total days. Then, if the starting day is a sunday, we take out 1 more day, and if the ending date is a saturday, we take out 1 more as well. The result is the # of working days in the range. By creating the holiday table, you could even take out holidays if you wish by counting the # of holidays in that date range. Of course, don't list holidays that occur on weekends in this table because you are already taking those days out !!

Let me know if you need more help. Good luck. I personally think this is a clever bit of code.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-15 : 08:16:34

ooops -- take away all of the @'s in my last post before StartDAte and EndDate, I was in a hurry and didn't remove them all.

That is, everywhere you see @StartDate and @EndDate, replace with StartDate and EndDate respectively.

willnewbie
Starting Member

5 Posts

Posted - 2002-11-15 : 08:34:38

Huge thanks that's hit the nail on the head, yeah I saw that and figured it out.

Many, many thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources