SQL Query Between two dates and two times for only weekdays

user_12244016 0
Viewed 118

I am trying to write a query that will give me the number of procedures done between two specific dates during two specific hours, but only on working days. I have the script working for the dates and times, but I can't figure out how to get it so that it only includes workdays. I only want results from Monday thru Friday, excluding Saturday and Sunday from the results.

My query:

SELECT MONTH(r.LastModifiedDate) AS MONTHpreCOVID, PlacerFld2 AS MODALITY, COUNT(*) AS CountOfReportsDayTimePreCOVID
FROM [order] o

LEFT JOIN report r
ON o.reportID = r.reportID

WHERE r.LastModifiedDate >= '2019-07-01' AND r.lastmodifieddate <= '2020-06-01'
AND CAST(r.lastmodifieddate as TIME) >= '08:00:00' AND CAST(r.lastmodifieddate as TIME) <='16:59:59'
AND reportstatusID = '7'
AND r.creatorAcctID = '139'

GROUP BY MONTH(r.LastModifiedDate), PlacerFld2
ORDER BY MONTH(r.LastModifiedDate) ASC

I've tried adding something like WEEKDAY(r.lastmodifieddate) IN ('0','1','2','3','4') but that doesn't work.

sql-server-2016 datetime sql
2 Answers

use this : 

AND (((DATEPART(DW, r.lastmodifieddate) - 1 ) + @@DATEFIRST ) % 7) in ('1','2','3','4','5')

(((DATEPART(DW, r.lastmodifieddate) - 1 ) + @@DATEFIRST ) % 7) will always return a number between 0 and 6 where every number is : 

0 -> Sunday
1 -> Monday
2 -> Tuesday
3 -> Wednesday
4 -> Thursday
5 -> Friday
6 -> Saturday

you can check it with a simple query like that 

SELECT (((DATEPART(DW, @DATE_VAR) - 1 ) + @@DATEFIRST ) % 7)

replace @DATE_VAR with a valid date. i.g 1900-01-01

user_71498350

You can determine the day of the week as DATEPART(WEEKDAY, dt).

For you to see it

    Select Dt, DATEPART(WEEKDAY, dt) as WeekDayNumber, DATEName(WEEKDAY, dt) as WeekDayName
 from
(
Select Getdate() as Dt Union
Select Getdate() + 1 Union
Select Getdate() + 2 Union
Select Getdate() + 3 Union
Select Getdate() + 4 Union
Select Getdate() + 5 Union
Select Getdate() + 6 Union
Select Getdate() + 7 
) Q
Where DATEPART(WEEKDAY, dt) Not In ( 1,7)

So in your case, it should be

....
WHERE r.LastModifiedDate >= '2019-07-01' AND r.lastmodifieddate <= '2020-06-01'
AND CAST(r.lastmodifieddate as TIME) >= '08:00:00' AND CAST(r.lastmodifieddate as TIME) <='16:59:59'
AND reportstatusID = '7'
AND r.creatorAcctID = '139'
AND DATEPART(WEEKDAY, r.LastModifiedDate) Not In ( 1,7)
user_62014040
Related