SQL SELECT Date based on multiple conditions

Question

I have a set of date in a Table which contains weekly date.

I want to select the following:

If the date is less than 2 months old then i want to select all the date (weekly).

If the date is more than 2 months old then i only want to select the last date of each month (monthly).

I tried the following code:

SELECT DISTINCT(Date) FROM [Table] WHERE Date IN 
(CASE 
WHEN Date> DATEADD(month, -2, GETDATE()) 
THEN Date 
ELSE MAX(Date) GROUP BY Month(Date),Year(Date)
);

But without success:

Incorrect syntax near the keyword 'GROUP'.

If for instance the current Date is 13/09/2022,

13/09/2022 - 2 months = 13/07/2022

If i have the following Date in my Table:

06/05/2022

13/05/2022

20/05/2022

31/05/2022

07/06/2022

10/06/2022

17/06/2022

24/06/2022

30/06/2022

08/07/2022 (<13/07/2022)

15/07/2022 (>13/07/2022)

22/07/2022

29/07/2022

05/08/2022

12/08/2022

19/08/2022

26/08/2022

Then the final output should be:

31/05/2022

30/06/2022 (<13/07/2022)

15/07/2022 (>13/07/2022)

22/07/2022

29/07/2022

05/08/2022

12/08/2022

19/08/2022

26/08/2022

Charlieface · Answer

Your syntax is completely invalid, I'm not going to bother fixing it.

DISTINCT is not a function, it works over the whole set of columns.
You can't use aggregates inside a WHERE, even if they would be window functions (which they're not).
The GROUP BY is inside a CASE which makes no sense.

Instead I'm just going off your requirements

If the date is less than 2 months old then I want to select all the date (weekly).
If the date is more than 2 months old then I only want to select the last date of each month (monthly).

You can use a ROW_NUMBER strategy for this.

SELECT
  t.Date
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY EOMONTH(t.Date) ORDER BY t.Date DESC)
    FROM [Table] t
) t
WHERE (
   t.Date > DATEADD(month, -2, GETDATE())
   OR rn = 1
)
ORDER BY
  Date;

SQL SELECT Date based on multiple conditions

1 Answers