CODEWITHSUNDEEP
sqlsql-servert-sqldatetimedatepart
Matt Walmsley
Matt Walmsley

Reputation: 23

How do I bring back the data for just weekends and evenings in SQL?

I'm creating two separate views. One for the day staff and one for the nights and weekend team. The first query works (or brings back data at least) between 08:00 - 21:00 Mon-Fri and 09:00 - 17:00 Sat.

The second query brings back the same data but I want it to bring back 00:00 - 08:00 & 21:00 23:59 Mon - Fri 00:00 - 09:00 & 17:00 - 23:59 Sat and all day Sunday.

Is it something to do with needing more parenthesis?

1st Query -

((DATEPART(hh,[Timestamp]) >= 08 AND DATEPART(hh,[Timestamp]) <= 21  
    AND  
DATEPART(dw,[Timestamp]) >= 2 AND DATEPART(dw,[Timestamp]) <= 6))  
OR  
((DATEPART(hh,[Timestamp]) >= 09 AND DATEPART(hh,[Timestamp]) <= 17  
    AND  
DATEPART(dw,[Timestamp]) != 7))

2nd Query -

((DATEPART(hh,[Timestamp]) < 08   
    AND  
DATEPART(dw,[Timestamp]) >= 2 AND DATEPART(dw,[Timestamp]) <= 6))  
or  
((DATEPART(hh,[Timestamp]) > 21  
    AND  
DATEPART(dw,[Timestamp]) >= 2 AND DATEPART(dw,[Timestamp]) <= 6))  
OR  
((DATEPART(hh,[Timestamp]) < 09   
    AND  
DATEPART(dw,[Timestamp]) != 7))  
OR  
((DATEPART(hh,[Timestamp]) > 17  
    AND  
DATEPART(dw,[Timestamp]) != 7))  
OR  
((DATEPART(dw,[Timestamp]) != 1))

Upvotes: 0

Views: 144

Answers (2)

t4r4z0k
t4r4z0k

Reputation: 26

It seems to me you'd probably be better off creating a configuration table like this. SQL Server data types of the columns would be: int,time,time,varchar. Values in DayOfWeek need to be aligned with however the DATEPART() function works on your particular SQL Server for days of week values.

DayOfWeek StartTime EndTime Staff
1 00:00:00 08:00:00 night
1 08:00:00 17:00:00 day
1 17:00:00 23:59:59 day
2 00:00:00 08:00:00 night
... ... ... ...

Then just join your original table/view to the new one on

DATEPART(dw,[yourdate])=newtable.DayOfWeek
AND CAST([yourdate] AS time)>=newtable.StartTime
AND CAST([yourdate] AS time)<newtable.EndTime

Finally you can filter on day or night staff by applying a WHERE clause like this:

WHERE newtable.Staff='day'

Upvotes: 1

JMabee
JMabee

Reputation: 2300

I think you almost had it, but you used != (not equal) instead of just =. So it should be something like this:

(DATEPART(hh,[Timestamp]) < 08 OR DATEPART(hh,[Timestamp]) >= 21) AND (DATEPART(dw,[Timestamp]) >= 2 AND DATEPART(dw,[Timestamp]) <= 6) -- MON-FRI
    OR
    (DATEPART(hh,[Timestamp]) < 09 OR DATEPART(hh,[Timestamp]) >= 17) AND (DATEPART(dw,[Timestamp]) = 7 ) --SAT
    OR
    (DATEPART(dw,[Timestamp]) = 1 ) -- SUN

Upvotes: 1

Related Questions