Date Diff in Sql

Transact-SQL
1

I have a view in SQL using this DateDiff expression. HAVING (DATEDIFF(d, dbo.PVUNITALLOCMONTHDAY.DTTM, GETDATE()) = 2) I need it to make sure if it is Tuesday, Wednesday, Thursday or Friday it is Date - 2 If Monday it is Date -4 -3 and -2 when I run the view. So for example if I run it on Tuesday 2-25 it gives me the data in the table for 2-23. If I run it on a Monday 2-24 it gives me the Data from 2-20, 2-21 and 2-22. Does this make sense. I have to run this daily to update production so I have been manually changing the DateDiff on Mondays to say -4 then -3 then -2 and running them separately. I know there is a way to make it do it automatically but not sure how. Does anyone else know? Thanks in advance

2 
WHERE dbo.PVUNITALLOCMONTHDAY.DTTM >= DATEADD(DAY, CASE WHEN DATEDIFF(DAY, 0, GETDATE()) % 7 = 0 THEN -4 ELSE -2 END, CAST(GETDATE() AS date)) AND
    dbo.PVUNITALLOCMONTHDAY.DTTM < DATEADD(DAY, -1, CAST(GETDATE() AS date))
1 Like
3

Thank you so much that worked great

4

You're welcome. As a side benefit, it's potentially much more efficient, as SQL can use index seeks if available against that code but not the original code.

1 Like