How to return specific dates based on multiple conditions in SQL?

Question

I have the table below (its test dates, not all dates are included) and I need to return specific dates based on quarterly and fiscal year periods.

reportdate  periodtype
2021-11-12  wk
2021-11-19  wk
2021-11-30  mth
2021-12-03  wk
2021-12-10  wk
2021-12-31  mth
2022-01-07  wk
2022-01-14  wk
2022-01-28  wk
2022-01-31  mth
2022-02-04  wk

I have a condition which I am having difficulty writing in SQL.

For the fiscal year I need to return the all the month end report dates plus the latest weekly reportdate. This I am able to to do using this code:

reportDate between @YTD_Start_Dt and @report_dt and (periodtype = 'mth' or reportDate =@report_dt)

There is a case which occurs the first week of every month (in this case 2022-02-04) where the last monthly report is sometimes not available until the second week. Therefore I need to return all available monthly dates plus the latest weekly plus the last weeks report in the month prior.

So for 2022-02-04 I would need to return:

--if current day is less than 8 then return:
2021-11-30
2021-12-31 
2022-01-28 
2022-02-04 

--if current day is greater or equal to 8 then return    
2021-11-30
2021-12-31
2022-01-31
2022-02-04

I don't know how to write the logic so that it returns the correct combo of reportdates depending on what is available. Any help is appreciated.

You say if the last monthly report is not available - should this actually say "if the current day is less than 8" ? — Caius Jard
– Caius Jard, Commented Jan 21, 2022 at 17:40
Isn't it the same as the last (mth/wk) report available for every month? — Serg
– Serg, Commented Jan 21, 2022 at 17:44
i think you should change the input dataset as well based on two scenarios. will you have same dataset in both the cases ? — NNM
– NNM, Commented Jan 21, 2022 at 17:59

Caius Jard · Accepted Answer · 2022-01-21 18:14:09Z

1

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

answered Jan 21, 2022 at 18:14

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to return specific dates based on multiple conditions in SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related