SQLTeam.com | Weblogs | Forums

Select statement


#1

I have a table that contains the day name of the week.

I would like to write a select statement that returns records with an inv_dt of yesterday. But on Monday's I would like it to return orders with an invoice date of Friday or Saturday.

Would like to turn this select statement into a SQL view.

I know I can do this to get one day back.

select *
from DayINV
where inv_dt = dateadd(dd, datediff(dd,0, getdate()),0)-1

Not sure how I can add the 2nd condition that if the day is Monday grab Friday and Saturday dates.

Table is DayINV with

ord_no Inv_no Day Inv DT
1234 5443 Tuesday 2017-05-09


#2
FROM table_name
CROSS APPLY (
    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS today
) AS assign_alias_names
WHERE inv_dt IN (DATEADD(DAY, CASE WHEN DATEDIFF(DAY, 0, GETDATE()) % 7 = 0 THEN -2 ELSE -1 END, today), 
    DATEADD(DAY, CASE WHEN DATEDIFF(DAY, 0, GETDATE()) % 7 = 0 THEN -3 ELSE -1 END), today)