Donate. I desperately need donations to survive due to my health

Get paid by answering surveys Click here

Click here to donate

Remote/Work from Home jobs

Get count of a variable for previous weeks based on if another variable is not null

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