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 aCASE
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;