SQL - get summary of differences vs previous month

Question

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

score 2 · Accepted Answer · answered Sep 04 '20 at 08:06

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

Thanks for the answer, I tried the first one and worked like a charm. I will test the second one later. — MonoColorado, Oct 04 '20 at 14:53

score 1 · Answer 2 · answered Sep 04 '20 at 08:17

you can compare a date with previous month's date using DATEDIFF function of TSQL.

Using NOT EXIST you can count the stores which did not appear in last month as well you can get the names in a list using STRING_AGG function of TSQL introduced from SQL 2017.

select BOMDate, NewStoresCount=count(1),NewStores= STRING_AGG(store,',')  from 
yourtable
where not exists
(
    Select 1 from
    yourtable y where y.store=store and DATEDIFF(m,y.BOMDate,BOMDate)=1
)
group by BOMDate

SQL - get summary of differences vs previous month

2 Answers2

Linked