How to return specific dates based on multiple conditions in SQL?
How about..
where
(reportDate between @YTD_Start_Dt and DATEADD(day, -8, @report_dt) and periodtype = 'mth') or
(reportDate between DATEADD(day, -6, @report_dt) and @report_dt and periodtype = 'wk') or
(DAY(@report_dt) < 8 AND periodtype = 'wk' and reportDate between DATEADD(d, -6, EOMONTH(@report_dt, -1)) AND EOMONTH(@n, -1))
.. where it's a
-
mth
type and the record is between year start and 8 days prior (removes the latest mth if it's the first week of the month after) or -
wk
type in the last week or - it's the first week of the month and is a
wk
report from the ultimate 7 days of last month