Number of days in a month excluding weekends 6

[drublic1019]

Well I have been searching the net for sometime and I was unable to find a nice way to find the number of days in a month excluding weekends. But after way tries and code collections I have finally wrote a script to do it. I figured to save people the same headache that I had I'd post it here. It looks big and bad but it always run under one second.

declare @startdate datetime,
@enddate datetime

set @startdate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))+1
set @enddate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0))

SELECT 
DATEDIFF(d, DATEADD(d, CASE WHEN DATEPART(dw, @startdate) = 7 THEN 1 ELSE 0 END, @startdate), DATEADD(d, CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END, @enddate)) 
- DATEDIFF(wk, DATEADD(d, CASE WHEN DATEPART(dw, @startdate) = 7 THEN 1 ELSE 0 END, @startdate), DATEADD(d, CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END, @enddate)) 
- DATEDIFF(wk, DATEADD(d, CASE WHEN DATEPART(dw, @startdate) = 6 THEN 1 ELSE 0 END, @startdate), DATEADD(d, CASE WHEN DATEPART(dw, @enddate) = 6 THEN 1 ELSE 0 END, @enddate))+1

 

[gmmastros]

Nice code. I would suggest that you make one small addition to your code.

Your code depends on the 'first day of the week'. You can see what your 'first day of the week' is by issuing this command.

Select @@DateFirst

My recommended addition to your code is...

[tt][blue]Set DateFirst 7[/blue][/tt]

[COLOR=blue]declare[/color] @startdate [COLOR=#FF00FF]datetime[/color],
@enddate [COLOR=#FF00FF]datetime[/color]

[!]Set DateFirst 7[/!]

[COLOR=blue]set[/color] @startdate = [COLOR=#FF00FF]DATEADD[/color](s,-1,[COLOR=#FF00FF]DATEADD[/color](mm, [COLOR=#FF00FF]DATEDIFF[/color](m,0,[COLOR=#FF00FF]getdate[/color]()),0))+1
[COLOR=blue]set[/color] @enddate = [COLOR=#FF00FF]DATEADD[/color](s,-1,[COLOR=#FF00FF]DATEADD[/color](mm, [COLOR=#FF00FF]DATEDIFF[/color](m,0,[COLOR=#FF00FF]getdate[/color]())+1,0))

[COLOR=blue]SELECT[/color]
[COLOR=#FF00FF]DATEDIFF[/color]([COLOR=blue]d[/color], [COLOR=#FF00FF]DATEADD[/color]([COLOR=blue]d[/color], [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]DATEPART[/color](dw, @startdate) = 7 [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color], @startdate), [COLOR=#FF00FF]DATEADD[/color]([COLOR=blue]d[/color], [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]DATEPART[/color](dw, @enddate) = 7 [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color], @enddate))
- [COLOR=#FF00FF]DATEDIFF[/color](wk, [COLOR=#FF00FF]DATEADD[/color]([COLOR=blue]d[/color], [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]DATEPART[/color](dw, @startdate) = 7 [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color], @startdate), [COLOR=#FF00FF]DATEADD[/color]([COLOR=blue]d[/color], [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]DATEPART[/color](dw, @enddate) = 7 [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color], @enddate))
- [COLOR=#FF00FF]DATEDIFF[/color](wk, [COLOR=#FF00FF]DATEADD[/color]([COLOR=blue]d[/color], [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]DATEPART[/color](dw, @startdate) = 6 [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color], @startdate), [COLOR=#FF00FF]DATEADD[/color]([COLOR=blue]d[/color], [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]DATEPART[/color](dw, @enddate) = 6 [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color], @enddate))+1

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh yeah... For another approach...

thread183-1246063

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

drublic1019,

Run your code with a start date of 3/1/2007. The correct answer is 22 but your code gives 23.

Here's my version:

DECLARE
   @startdate datetime,
   @enddate datetime

SET @startdate = DateAdd(mm, DateDiff(mm, 0, GetDate()), 0)
SET @enddate = DateAdd(mm, DateDiff(mm, 0, @startdate) + 1, 0)

SELECT
   WeekDays = DateDiff(dd, @startdate, @enddate) * 5 / 7
   - Sign(
      (DateDiff(dd, @startdate, @enddate) + Convert(int, @startdate + 1) % 7 - 35) - Calculate an offset
      * Sign(Convert(int, @startdate + 1) % 7) -- Months starting with Sunday are never wrong
      * CASE @startdate + 28 WHEN @enddate THEN 0 ELSE 1 END -- 28-day months are never wrong
   )

And here's the validation that it works correctly:

--Validation
SELECT * INTO #Tester FROM (
   SELECT 1, 28, 20
   UNION ALL SELECT 2, 28, 20
   UNION ALL SELECT 3, 28, 20
   UNION ALL SELECT 4, 28, 20
   UNION ALL SELECT 5, 28, 20
   UNION ALL SELECT 6, 28, 20
   UNION ALL SELECT 7, 28, 20
   UNION ALL SELECT 1, 29, 20
   UNION ALL SELECT 2, 29, 21
   UNION ALL SELECT 3, 29, 21
   UNION ALL SELECT 4, 29, 21
   UNION ALL SELECT 5, 29, 21
   UNION ALL SELECT 6, 29, 21
   UNION ALL SELECT 7, 29, 20
   UNION ALL SELECT 1, 30, 21
   UNION ALL SELECT 2, 30, 22
   UNION ALL SELECT 3, 30, 22
   UNION ALL SELECT 4, 30, 22
   UNION ALL SELECT 5, 30, 22
   UNION ALL SELECT 6, 30, 21
   UNION ALL SELECT 7, 30, 20
   UNION ALL SELECT 1, 31, 22
   UNION ALL SELECT 2, 31, 23
   UNION ALL SELECT 3, 31, 23
   UNION ALL SELECT 4, 31, 23
   UNION ALL SELECT 5, 31, 22
   UNION ALL SELECT 6, 31, 21
   UNION ALL SELECT 7, 31, 21
) X (DayNum, MonthNum, WeekDays)

ALTER TABLE #Tester ADD StartDate datetime
ALTER TABLE #Tester ADD EndDate datetime
GO
DECLARE @m datetime
SET @m = '1/1/1972'
WHILE EXISTS (SELECT * FROM #Tester WHERE StartDate IS NULL) BEGIN
   UPDATE #Tester
   SET
      StartDate = @m,
      EndDate = DateAdd(mm, 1, @m)
   WHERE
      StartDate IS NULL
      AND DatePart(dw, @m) = DayNum
      AND DateAdd(mm, 1, @m) - MonthNum = @m
   SET @m = DateAdd(mm, 1, @m)
END

SELECT *
FROM #Tester
WHERE
   WeekDays <> DateDiff(dd, StartDate, EndDate) * 5 / 7
      - Sign(
         (DateDiff(dd, StartDate, EndDate) + Convert(int, StartDate + 1) % 7 - 35)
         * Sign(Convert(int, StartDate + 1) % 7)
         * CASE StartDate + 28 WHEN EndDate THEN 0 ELSE 1 END
      )

-- 0 rows returned means expression is validated
IF @@RowCount <> 0 RAISERROR ('Function does not validate. See the returned rows for invalid results.', 16, 1)

DROP TABLE #Tester

I used Excel to analyze the grid of results from the input values (month starting day of week, days in month) and see if there were any regularities that could be reduced to a mathematical formula.

Multiplying by a fraction is an old trick I've used in the past, and something that works well in many situations. (Other fractions may have worked but I didn't go there because I found a solution relatively easily.) I tried rounding first, but it gave a complicated result pattern.

But truncating the number gave a regular pattern something like this. The number represents how many whole days error there is with the formula "MonthDays * 5 / 7", for the two inputs.

[tt]# D 28 29 30 31
1 N 0 0 0 0
2 M 0 1 1 1
3 T 0 1 1 1
4 W 0 1 1 1
5 R 0 1 1 0
6 F 0 1 0 -1
7 S 0 0 -1 -1[/tt]

And that was fairly easy. Sundays are never wrong (the top row of zeros). 28-day-months are never wrong (for obvious reasons, 28 is exactly four weeks). And there's a nice diagonal of zeroes with all 1s on one side and all -1s on the other side. shifting the numbers so Sunday is 0, 31 + 4 = 30 + 5 = 29 + 6 = 35, thus the 35 in the formula to get it back to 0 to do the sign operation.

If necessary to make the pattern work, I could have cycled the rows so any day of the week was the first day. Note that the day numbers here don't correspond to the day numbers in my calculation (I used 0-6 for the convenience of the sign of the day number telling me if it was a sunday).

My method of calculating the day of the week is datefirst independent: "Convert(int, @startdate + 1) % 7" gives Sundays as 0. Leaving out the + 1 makes Mondays 0. (This method doesn't work for dates before 12/31/1899, although if you needed that then you could add some multiple of seven or you could subtract the result from 8 if it comes out negative.)

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[vongrunt]

How about this:

declare @startdate datetime; set @startdate = dateadd(mm, datediff(mm, 0, getdate()), 0)
declare @days int; set @days = day(dateadd(mm, 1, @startdate)- 1)

select (@days/7) * 5
	+ len(replace(substring('111110011111', convert(int, @startdate+1) % 7, @days%7), '0', ''))

------
chemistry - the only natural science that can be broken down into the categories a) making drugs and b) blowing stuff up

 

[gmmastros]

Very nice, vongrunt. Star for you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[onpnt]

And another from me

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[SQLDenis]

but mine was the first

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[AlexCuse]

Brilliant!

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[sunila7]

And another from me

Sunil

 

[ESquared]

vongrunt,

Beautiful. That is a much better way to encode the adjustment than mine.

I am going to remember this "model" in the future. The one I chose here is one I've used before, and clearly a fully mathematical/numerical approach in this case was not the simplest. When one is dealing with a pattern, use a pattern!

When I was first starting out with SQL 3 1/2 years ago, I learned a ton from you and I miss your current participation in the forums. (I miss nigel rivett, too). Will you be around occasionally?

 

[vongrunt]

Don't forget donutman . I learned a lot too...

OK, will try to participate - at least in more interesting topics. And maybe finally start that blog-like website of mine, lol.

------
chemistry - the only natural science that can be broken down into the categories a) making drugs and b) blowing stuff up

 

[ESquared]

By the way I wanted to offer a slightly optimized version of my code. It looks a lot simpler when you take out comments and when you use @days instead of @enddate - @startdate in the code:

DECLARE
   @startdate datetime,
	@days int

SET @startdate = DateAdd(mm, DateDiff(mm, 0, GetDate()), 0)
SET @days = Day(DateAdd(mm, 1, @startdate) - 1)


SELECT
   WeekDays = @days * 5 / 7
   - Sign((@days + Convert(int, @startdate + 1) % 7 - 35) * Sign(Convert(int, @startdate + 1) % 7) * Sign(@days - 28))

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[ESquared]

vongrunt,

I developed a new method. It is still longer than yours, but I thought you'd like to see this.

declare @startdate datetime; set @startdate = '10/1/2007'
declare @days int; set @days = Day(DateAdd(mm, 1, @startdate) - 1)

SELECT @startdate, WorkDays = @days / 7 * 5 + Count(*)
FROM (SELECT n=8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) x
WHERE (n - Convert(int, @startdate + 1) % 7) % 7 < @days % 7

This is a development of your idea.

I modified both our versions to run against a table I have with 65000 days in it, calculating the number of workdays in the month for each one using the same formulas to derive startdate and days.

And your version kills mine on performance because your query has about one read per page and mine has about one read per row. I remedied that with... drumroll please... a correlated subquery. Now reads are the same as yours, but CPU is still 4x and duration is 3x. This is what my new query looks like.

SELECT
    E.dt,
   WorkDays = Day(DateAdd(mm, 1, DateAdd(mm, DateDiff(mm, 0, E.Dt), 0)) - 1) / 7 * 5
   + (
      SELECT Count(*)
      FROM (
         SELECT N = 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
      ) D WHERE (D.N - Convert(int, DateAdd(mm, DateDiff(mm, 0, E.Dt), 0) + 1) % 7) % 7
         < Day(DateAdd(mm, 1, DateAdd(mm, DateDiff(mm, 0, E.Dt), 0)) - 1) % 7
   )
FROM
   EveryDay E

The final revision of my method earlier in this thread was a better contender, using the same reads, 50% more cpu, and only a tiny bit more duration than vongrunt's. But it is flawed in that it only works for day durations in the list (28, 39, 30, 31).

Trying to precalculate the month beginning date and the number of days using a derived table didn't help, but instead increased the cpu and duration by about 30%.

I learned something interesting: correlated subqueries that don't have to hit a real table (it's all in a UNION list right there in the query) can perform surprisingly well.

vongrunt gets props for his substring calculation beating my row counting method.