Find how many ticket removed, added and exisiting

SQL Server Administration
,
1

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

image
image707×464 96.1 KB

, 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
but what my current code is doing it compare data from 2020-12-02 with data from 2020-12-11 and then again it compare 2020-12-02 with 2020-12-14.

I will provide a fiddle and a code below.
Open the fiddle link you will get full access to my code
https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=426aaaf1b851462d975909e56a08d4be

2

hi

i played around with this ..

its not exactly what you want .. but please have a look ..

please click arrow to the left for DROP CREATE data 
drop table DataTable

CREATE TABLE DataTable (
        Date Date,
        CapVersion varchar(100),
        CaseNumber int
    );
INSERT INTO DataTable (Date, CapVersion, CaseNumber)
VALUES 
('2020-12-02', 'CAP A', 1002),
('2020-12-02', 'CAP A', 1003),
('2020-12-02', 'CAP A', 1004),
--compare CAP A with CAP B
('2020-12-11', 'CAP B', 1002),
('2020-12-11', 'CAP B', 1003),
--compare CAP B with CAP C
('2020-12-14', 'CAP C', 1003),
('2020-12-14', 'CAP C', 1004),
('2020-12-14', 'CAP C', 1005),
--compare CAP C with CAP D
('2020-12-15', 'CAP D', 1005),
('2020-12-15', 'CAP D', 1007);

--select * from DataTable

-- case number removed 
select 0 as CaseNumber_Removed
union all 
select count(*) from 
(
select CaseNumber from DataTable where CapVersion= 'CAP A' 
except 
select CaseNumber from DataTable where CapVersion= 'CAP B' 
) a 
union all
select count(*) from 
(
select CaseNumber from DataTable where CapVersion= 'CAP B' 
except 
select CaseNumber from DataTable where CapVersion= 'CAP C' 
)a 
union all
select count(*) from 
(
select CaseNumber from DataTable where CapVersion= 'CAP C' 
except 
select CaseNumber from DataTable where CapVersion= 'CAP D' 
) a 

---- case number added  
select 0 as CaseNumber_Added 
union all 
select count(*) from 
(
select CaseNumber from DataTable where CapVersion= 'CAP B' 
except 
select CaseNumber from DataTable where CapVersion= 'CAP A' 
) a 
union all
select count(*) from 
(
select CaseNumber from DataTable where CapVersion= 'CAP C' 
except 
select CaseNumber from DataTable where CapVersion= 'CAP B' 
)a 
union all
select count(*) from 
(
select CaseNumber from DataTable where CapVersion= 'CAP D' 
except 
select CaseNumber from DataTable where CapVersion= 'CAP C' 
) a

image

3 
-- ---- case number existing 
select count(*) as CaseNumber_Existing  from DataTable where CapVersion= 'CAP A' 
union all 
select count(*) from 
(
select CaseNumber from DataTable where CapVersion= 'CAP A' 
intersect  
select CaseNumber from DataTable where CapVersion= 'CAP B' 
) a 
union all
select count(*) from 
(
select CaseNumber from DataTable where CapVersion= 'CAP B' 
intersect 
select CaseNumber from DataTable where CapVersion= 'CAP C' 
)a 
union all
select count(*) from 
(
select CaseNumber from DataTable where CapVersion= 'CAP C' 
intersect 
select CaseNumber from DataTable where CapVersion= 'CAP D' 
) a

image