I have a table similar to this one:
| id | store | BOMdate | | 1 | A | 01/10/2018 | | 1 | B | 01/10/2018 | | 1 | C | 01/10/2018 | |... | ... | ... | | 1 | A | 01/11/2018 | | 1 | C | 01/11/2018 | | 1 | D | 01/11/2018 | |... | ... | ... | | 1 | B | 01/12/2018 | | 1 | C | 01/12/2018 | | 1 | E | 01/12/2018 |
It contains the stores that are active at BOM (beginning of month).
How do I query it to get the amount of stores that are new that month – those that where not active the previous month?
The output should be this:
| BOMdate | #newstores | | 01/10/2018 | 3 | * no stores on previous month | 01/11/2018 | 1 | * D is the only new active store | 01/12/2018 | 2 | * store B was not active on November, E is new
I now how to count the first time that each store is active (nested select, taking the MIN(BOMdate) and then counting). But I have no idea how to check each month vs its previous month.
I use SQL Server, but I am interested in the differences in other platforms if there are any.
Thanks
Advertisement
Answer
How do I query it to get the amount of stores that are new that month – those that where not active the previous month?
One option uses not exists
:
select bomdate, count(*) cnt_new_stores from mytable t where not exists ( select 1 from mytable t1 where t1.store = t.store and t1.bomdate = dateadd(month, -1, t.bomdate) ) group by bomdate
You can also use window functions:
select bomdate, count(*) cnt_new_stores from ( select t.*, lag(bomdate) over(partition by store order by bomdate) lag_bomdate from mytable t ) t where bomdate <> dateadd(month, 1, lag_bomdate) or lag_bomdate is null group by bomdate