Find how many ticket removed, added and existing with SQL query

Question

I have table with similar case number for different products. Hence I want to find the the case number has been added, removed and existing according to the date. I have a data something like below

So the code should take data from 2020-12-02 compare with data from 2020-12-11 and then it needs to compare data from 2020-12-11 with data from 2020-12-14
You may come up with entire new solution based on my desire output it will be very much helpful to me

Answer 1

Check a possibility:

declare @table table ([date] date, CapVersion varchar(max), CaseNumber int )

insert @table values
( '2020-12-02', 'CAP A', 1002 ),
( '2020-12-02', 'CAP A', 1003 ),
( '2020-12-02', 'CAP A', 1004 ),

( '2020-12-11', 'CAP B', 1002 ),
( '2020-12-11', 'CAP B', 1003 ),

( '2020-12-14', 'CAP C', 1002 ),
( '2020-12-14', 'CAP C', 1003 ),
( '2020-12-14', 'CAP C', 1005 )

select * 
from @table
order by [date], CaseNumber

---

;
with E as
(
    select [date], lag([date]) over (order by [date]) as pd
    from @table
    group by [date]
)
select [date], 
    (select top(1) CapVersion from @table where [date] = E.[date]) as CapVersion,
    (select count(*) from
        (
            select CaseNumber from @table where [date] = E.[date]
            except
            select CaseNumber from @table where [date] = E.pd or E.pd is null
        ) d 
    ) as CaseNumberAdded,
    (select count(*) from
        (
            select CaseNumber from @table where [date] = E.pd
            except
            select CaseNumber from @table where [date] = E.[date]
        ) d 
    ) as CaseNumberRemoved,
    (select count(*) from
        (
            select CaseNumber from @table where [date] = E.[date]
            intersect
            select CaseNumber from @table where [date] = E.pd or E.pd is null
        ) d 
    ) as CaseNumberExisting 
from E 
order by [date]