Finiding Working days

arnabghosh · July 10th, 2006, 08:40 AM

Hi,
I want to find the count of all the Saturdays & Sundays within a date range stored in 2 columns startdate & enddate of a table called semesterMaster.

Can the same be done for the days of a month. Here no table would be used.

Can this be done with the help of SQL query.???

Pls help

rstelma · July 10th, 2006, 11:19 AM

Hi,

There's at least two ways that I know of to do this. One is to generate a calendar table which creates dates in a table. Then you can select the datepart and count for the range that you want.

Another way to do this is to use Peso's Date Sequence Generator function:

http://p2p.wrox.com/topic.asp?TOPIC_...archTerms=peso

And then use the query:

SELECT count(DS.Value) FROM dbo.fnSeqDates('6/30/2006', '7/30/2006') DS WHERE DATEPART(dw, DS.Value) = 6 OR DATEPART(dw, DS.Value) = 7

The only problem I've seen with the sequence generator is that if you want to count all of the days between certain dates it seems that you have to subtract one day from the first date or it won't include the start day. As in this query I used the last day of June to start counting Saturday's and Sunday's so that it would include the Saturday on '7/1/2006'.

I'm sure they are many other ways to do this.

Richard

Jeff Mason · July 10th, 2006, 11:30 AM

If it's at all possible, I recommend that you construct a calendar table. These are very easy to make, you can generate them years in advance so you don't have to worry about them much, and if you include an additional indicator or two in the calendar table you can indicate which days are business days, holidays, etc.

Having such a calendar makes calculations such as what the OP needs really quite simple. It also makes the next step - a business day calculation, also quite simple and fast.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Peso · July 10th, 2006, 01:51 PM

rstelma and arnabghosh, the DATEPART(dw, value) depends on the SET DATEFIRST setting in SQL Server.

If I run this code on my computer

Code:

SELECT value, DATEPART(dw, value) FROM fnSeqDates('7/1/2006', '7/31/2006')

I get this result

Code:

2006-07-01 00:00:00.000    7
2006-07-02 00:00:00.000    1
2006-07-03 00:00:00.000    2
2006-07-04 00:00:00.000    3
2006-07-05 00:00:00.000    4
2006-07-06 00:00:00.000    5
2006-07-07 00:00:00.000    6
2006-07-08 00:00:00.000    7
2006-07-09 00:00:00.000    1
2006-07-10 00:00:00.000    2
2006-07-11 00:00:00.000    3
2006-07-12 00:00:00.000    4
2006-07-13 00:00:00.000    5
2006-07-14 00:00:00.000    6
2006-07-15 00:00:00.000    7
2006-07-16 00:00:00.000    1
2006-07-17 00:00:00.000    2
2006-07-18 00:00:00.000    3
2006-07-19 00:00:00.000    4
2006-07-20 00:00:00.000    5
2006-07-21 00:00:00.000    6
2006-07-22 00:00:00.000    7
2006-07-23 00:00:00.000    1
2006-07-24 00:00:00.000    2
2006-07-25 00:00:00.000    3
2006-07-26 00:00:00.000    4
2006-07-27 00:00:00.000    5
2006-07-28 00:00:00.000    6
2006-07-29 00:00:00.000    7
2006-07-30 00:00:00.000    1
2006-07-31 00:00:00.000    2

So for me Saturday is 7 and Sunday is 1.

So if I write

Code:

SELECT count(*) FROM fnSeqDates('7/1/2006', '7/31/2006') where DATEPART(dw, value) in (1, 7)

I get 10 as the expected result.

rstelma · July 10th, 2006, 04:00 PM

Peso,

I'm a ding dong. You're absolutely right. I get the same results as you do when I select the correct days, 1 and 7.

Thank you!

Richard

arnabghosh · July 11th, 2006, 02:18 AM

Thanks Peso for your great help!!!!