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
