SQLTeam.com | Weblogs | Forums

SQL - Filter Weekend Data, but include half day monday (up till noon)

I'm currently running data to pull weekend data. Below is the logic I'm using for both in my 'WHERE' statement....

Where (dw, dd.date) NOT IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday')

I was recently asked to include half of Monday on my weekend data (up till 12pm Monday). I need help updating my where clause to include half day monday on my SQL statement. To be clear, weekends should include all day Saturday, Sunday and 12am to 12pm Monday.

Thanks!

Create Sample Data Script

-- Create Tables
create table SQLTeam.TempTable1(dw_date datetime )


-- Insert data Tables

insert into SQLTeam.TempTable1 select getdate()
insert into SQLTeam.TempTable1 select '2022-08-21 17:20:57.417'
insert into SQLTeam.TempTable1 select '2022-08-20 17:20:57.417'
insert into SQLTeam.TempTable1 select '2022-08-22 10:20:57.417'
insert into SQLTeam.TempTable1 select '2022-08-22 13:20:57.417'

; with cte as 
( select 
      *
	  , case when datepart(weekday,dw_date) =2 and datepart(hour,dw_date) <= 12 then 1 else 0 end  as ok 
  from 
     SQLTeam.TempTable1 
	 ) 
  select  'SQL'
         , * 
  from 
     cte 
  where  
    datepart(weekday,dw_date) in (1,7) OR (datepart(weekday,dw_date) in (2) and ok =1)

image

For best performance, do not use functions against a column name in a WHERE (or JOIN). In technical terms, it's non-sargable if a function is used.

If you want the most recent Saturday thru Monday, and assuming that dd.date is a datetime, you can do this:

WHERE dd.date >= DATEADD(DAY, -DATEDIFF(DAY, 5, GETDATE()) % 7, CAST(GETDATE() AS date)) AND
dd.date <= DATEADD(HOUR, 60, DATEADD(DAY, -DATEDIFF(DAY, 5, GETDATE()) % 7, CAST(CAST(GETDATE() AS date) AS datetime)))