I have a table like below in SQL server
| Date | Store ID | Sales Amount |
|---|---|---|
| 01-01-2021 | 1001 | 1000$ |
| 01-01-2021 | 1002 | 1000$ |
| 01-01-2021 | 1003 | 0$ |
| 02-01-2021 | 1001 | 1000$ |
| 02-01-2021 | 1002 | 1000$ |
| 02-01-2021 | 1003 | 1000$ |
| 03-01-2021 | 1001 | 1000$ |
| 03-01-2021 | 1002 | 1000$ |
| 03-01-2021 | 1003 | 1000$ |
how to calculate count of previous days if Sales Amount > 0, using TSQL? (same as below column)
| Date | Store ID | Sales Amount | Active Days |
|---|---|---|---|
| 01-01-2021 | 1001 | 1000$ | 1 |
| 01-01-2021 | 1002 | 1000$ | 1 |
| 01-01-2021 | 1003 | 0$ | 0 |
| 02-01-2021 | 1001 | 1000$ | 2 |
| 02-01-2021 | 1002 | 1000$ | 2 |
| 02-01-2021 | 1003 | 1000$ | 1 |
| 03-01-2021 | 1001 | 1000$ | 3 |
| 03-01-2021 | 1002 | 1000$ | 3 |
| 03-01-2021 | 1003 | 1000$ | 2 |
Thanks in advance.
Advertisement
Answer
… conditional window aggregate …
(as you’ve mentioned you are using sql server)
sum(case when SalesAmount > 0 then 1 else 0 end) over(partition by StoreId order by Date) as ActiveDays