I need to generate report that will show number of new / changed rows on per-day basis for SCD table.
Here is the SQL to create the table:
create table #scd(
code nvarchar not null
, startdate date not null
, enddate date
);
alter table #scd add constraint pk_scd primary key (code, startdate);
insert into #scd values
('A', '2012-06-01', '2012-06-02')
,('B', '2012-06-01', '2012-06-02')
,('A', '2012-06-02', '2012-06-03')
,('B', '2012-06-02', '2012-06-04')
,('A', '2012-06-03', '2012-06-04')
,('A', '2012-06-04', null)
,('B', '2012-06-04', null)
,('C', '2012-06-04', null)
select * from #scd
The result look like this:
code startdate enddate
A 2012-06-01 2012-06-02
B 2012-06-01 2012-06-02
A 2012-06-02 2012-06-03
B 2012-06-02 2012-06-04
A 2012-06-03 2012-06-04
A 2012-06-04 NULL
B 2012-06-04 NULL
C 2012-06-04 NULL
Now, I need to produce someting like this:
date new changed
2012-06-01 2 0
2012-06-02 0 2
2012-06-03 0 1
2012-06-04 1 2
Any help is much appreciated.
; with
q_00 as ( -- get new records
select
code
, startdate
from #scd as s
where s.startdate = (select MIN(xx.startdate) from #scd as xx where xx.code = s.code)
),
q_01 as ( -- get changed records, those that are not new
select
s.code
, s.startdate
from #scd as s
left join q_00 as b on b.code = s.code and b.startdate = s.startdate
where b.code is null
),
q_03 as ( -- get the list of all possible dates
select distinct
startdate
from #scd
),
q_04 as ( -- count new records per date
select
startdate
, COUNT(1) as new_rec
from q_00
group by startdate
),
q_05 as ( -- count changed records per date
select
startdate
, COUNT(1) as chn_rec
from q_01
group by startdate
)
select
a.startdate as OnDate
, coalesce(new_rec, 0) as new_records
, coalesce(chn_rec, 0) as changed_records
from q_03 as a
left join q_04 as b on b.startdate = a.startdate
left join q_05 as c on c.startdate = a.startdate
order by a.startdate
;