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

Answer 1

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;