I have a table with Id, dates, a separate variable (V2) that is a date or null, and a variable (V3) that has a value of 1 for each row. I need to count the number of rows that occur the week before the date in V2. If V2 is null, then I need to count the number of rows that occur the week before the current week.
All I can think of to use is a case when statement, but I haven't gotten this to work.
SELECT *
,CASE WHEN V2 IS NULL THEN V3 OVER (PARTITION BY Id ORDER BY dates ROWS 1 PRECEDING) AS count1
FROM table
I get an "incorrect syntax near keyword 'OVER'" error.
Edit:
Sample Data
Id dates V2 V3
1 2018-01-05 201802 1
1 2018-01-06 201802 1
2 2018-12-13 NULL 1
Desired output:
Id count1
1 2
2 1
Comments
Post a Comment