Home > OS >  SQL SELECT Date based on multiple conditions
SQL SELECT Date based on multiple conditions

Time:09-14

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

CodePudding user response:

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;
  • Related