SQL - How to exclude weekends from date range using 'datediff'

Question

I am having some trouble getting my desired results, below is my logic statement.

-- logic: count the # of days between xx to yy for every GRM renewal completed in previous month (February) and then take the average (exclude holidays & weekends).

My query is below, I have been playing around with the count for weekdays. It works but I cannot apply it in the way I want it.

The result I am looking for is to identify the previous month to the current month (feb for this example) and from the range XX to YY count the # of days using (datediff) but only count weekdays.

--query 1 counts the avg cycle time (days in range)

select  AVG(1.00 * DATEDIFF(DAY, xx, yy)) AS Avg_DayDiff

FROM Database1.dbo.table1

where month(datecompleted) = month(dateadd(month,-1,current_timestamp))
       and year(datecompleted) = year(dateadd(month,-1,current_timestamp))
       and ApprovalRequiredFrom = 'GRM'

join(

--Table Query to return # of days in previous month, including name of the day.

select CALENDAR_DATE,
        DAY_NAME,
        YEAR(CALENDAR_DATE) AS cal_year,
        MONTH(CALENDAR_DATE) AS cal_month

from Database1.dbo.table2

where month(CALENDAR_DATE) = month(dateadd(month,-1,current_timestamp))
      and year(CALENDAR_DATE) = year(dateadd(month,-1,current_timestamp))

Any Tips?

Thank you!!

Possible duplicate of [get DATEDIFF excluding weekends using sql server](https://stackoverflow.com/questions/7388420/get-datediff-excluding-weekends-using-sql-server) — Thom A, Mar 28 '18 at 12:55

Zorkolot · Accepted Answer · 2018-03-29T18:14:29.350

3

A possible solution is to use a CTE to populate a range of dates, then select the dates where the DATEPART(dw, [thedate]) is not in 1 or 7 (Sunday and Saturday respectively).

For example, if you just want a list of dates without weekends:

DECLARE @startdate date = --Startdate that you specify. In this case I'll select the first day of the previous month
                          (SELECT DATEADD(MONTH, -1, CAST(DATEADD(DAY, -DAY(GETDATE()) + 1, GETDATE()) as date)))
      , @enddate date   = --Enddate that you specify.  In this case I'll select the last day of the previous month
                          (SELECT DATEADD(DAY, -1, CAST(DATEADD(DAY, -DAY(GETDATE()) + 1, GETDATE()) as date)))
DECLARE @temp TABLE(thedate date)
;

WITH dates_CTE (thedate) as 
(
  SELECT @startdate
  UNION ALL
  SELECT DATEADD(day, 1, thedate)
    FROM dates_CTE
   WHERE thedate < @enddate

)     
INSERT INTO @temp
SELECT thedate 
FROM dates_CTE
WHERE DATEPART(dw, [thedate]) NOT IN (1,7) --not a weekend

--continue the main query here
SELECT * FROM @temp

In your query for GRM's, you could subquery the count of the rows in @temp where thedate is greater than 'xx' and less than 'yy' for a given GRM.

A caveat here is that this doesn't take into account holidays. How these are calculated is different for each organization. It's not impossible to do it in pure SQL but a calendar table is easiest IMO.

edited Mar 29 '18 at 18:14

answered Mar 28 '18 at 20:11

Zorkolot

1,899
1
11
8

Hi thanks for this, It runs without any flaws, I am just having trouble understanding the bottom portion of the query where it takes dt.number_of_grm / numberofdays as [avg_daydiff]. for example your 'where' statement is from datacompleted to datecompleted but my range is xx to yy – Pat Mar 29 '18 at 13:15
For example find all Rows where column name 'ApprovalRequiredFrom' = GRM, and it must be between feb1 to feb 28 (beg to end of previous month) and for each row that is returned meeting these two conditions identify the DATEDIFF from columns XX to YY and tell me the # of working days (not including weekends) then get a average of the whole set – Pat Mar 29 '18 at 13:20
It's possible I misunderstood the question... Apologies, I've edited the answer. What the answer is providing is the range of dates that don't include weekends. How to count the dates between a date xx and yy? I would probably do a count where the `thedate` variable in @temp is greater than your `xx` and less than your `yy`. – Zorkolot Mar 29 '18 at 18:18
thanks so much for all your help, I think I will go with using a calendar because eventually I will have to exclude holidays. I have created a calendar so i will try to work it out using it. – Pat Mar 29 '18 at 19:08

score 1 · Answer 2 · answered Mar 28 '18 at 12:56

1

What you probably really want to do is to generate a calendar table, which makes all sorts of custom date manipulation nearly trivial. As luck would have it, this comes up all the time and some of our friends on SO have been kind enough to gather up a ton of pre-rolled scripts for us here: How to create a Calendar table for 100 years in Sql

answered Mar 28 '18 at 12:56

Eric Brandt

7,886
3
18
35

Hi @Eric Brandt, I was looking to avoid having to source a table. From previously answered question It seems it is doable without a calendar. I am not that experienced with SQL to figure out the proper syntax for this problem yet. Is using a calendar my only option? – Pat Mar 28 '18 at 12:59
I am using this question for reference; https://stackoverflow.com/questions/252519/count-work-days-between-two-dates#comment2679712_252533 – Pat Mar 28 '18 at 13:00
There’s almost never an “only option”. :) There are several good ways to get just weekdays, and you are starting with one of them. Getting holidays is much more work, though. If you’re going to invest that work, saving it to a table will save you having to do it again later. But you’ll certainly get more folks offering other opinions shortly. – Eric Brandt Mar 28 '18 at 13:06
1

It probably is possible with some effort, but as you can see, when you start to get more creative with your queries, not only does the solution become harder to develop, it will be harder to maintain in future, and is also likely to impact performance the more complicated it gets. By creating a calendar table, you have pre-configured any calculations that you may need, and then all you need to do is join the dates. Not only is this easier, it's also likely to be better for query performance. – DimUser Mar 28 '18 at 13:07
I think creating an advanced calendar in Excel and importing it, is probably the easiest way to go. Literally a matter of minutes. – SQL_M Mar 28 '18 at 13:38
Yeah I'm going to work with a calendar and see what I can come up with, my calendar has 'calendar_date' column and a 'day_name' column so I can work with that. Any date that is Saturday and Sunday to exclude from the datediff count – Pat Mar 28 '18 at 14:06
Okay I updated my above query, I am not the best with sql so how would I use my sourced calendar so that when the datediff runs for range xx to yy it excludes days 'Saturday & Sunday'? thanks everyone – Pat Mar 28 '18 at 14:24
cant figure it out, if anyone could help me out. – Pat Mar 28 '18 at 18:58

SQL - How to exclude weekends from date range using 'datediff'

2 Answers2