Data across multiple years.

Question

I need to show the following results in multiple years at once. So, rather than running this report for 2014, then 2015, and finally 2016 I would like to run it one time for all three years (@startDate & @endDate). Not certain if a rollup would work? Or grouping sets? The user would like to compare the data across multiple years. Thanks!

select ct.srv_ct_label as service, count (distinct cln.cln_urn) as total
 from cw_domain dmn
inner join cw_service srv on srv.srv_dmn_fk=dmn.dmn_pk
inner join cw_client cln on cln.cln_pk=srv.srv_cln_fk
inner join cw_subservice sbs on sbs.sbs_pk=srv.srv_sbs_fk
inner join cw_service_category ct on ct.srv_ct_rpk=sbs.sbs_srv_ct_rfk
where
srv_ct_rpk in (
'001',
'002',
'004',
'025',
'040',
'003',
'026',
'017',
'016',
'034',
'041',
'042',
'018',
'029'
)
and srv.srv_date >=@startDate and srv.srv_date <=endDate
group by ct.srv_ct_label, srv_ct_rpk, srv.srv_date
order by
case srv_ct_rpk
when '001' then 1
when'040' then 2
when '003' then 3
when '025' then 4
when '002' then 5
when '004' then 6
when '041' then 7
when '026' then 8
when '017' then 9
when '034' then 10
when '042' then 11
when '018' then 12
when '029' then 13
end

score 0 · Answer 1 · answered Sep 08 '16 at 15:26

Add a column for year(srv.srv_date) as srv_year in your select, group by and order by as required:

select year(srv.srv_date) as srv_year, ct.srv_ct_label as service, count (distinct cln.cln_urn) as total
 from cw_domain dmn
inner join cw_service srv on srv.srv_dmn_fk=dmn.dmn_pk
inner join cw_client cln on cln.cln_pk=srv.srv_cln_fk
inner join cw_subservice sbs on sbs.sbs_pk=srv.srv_sbs_fk
inner join cw_service_category ct on ct.srv_ct_rpk=sbs.sbs_srv_ct_rfk
where
srv_ct_rpk in (
'001',
'002',
'004',
'025',
'040',
'003',
'026',
'017',
'016',
'034',
'041',
'042',
'018',
'029'
)
and srv.srv_date >=@startDate and srv.srv_date <=endDate
group by year(srv.srv_date), ct.srv_ct_label, srv_ct_rpk, srv.srv_date
order by year(srv.srv_date),
case srv_ct_rpk
when '001' then 1
when'040' then 2
when '003' then 3
when '025' then 4
when '002' then 5
when '004' then 6
when '041' then 7
when '026' then 8
when '017' then 9
when '034' then 10
when '042' then 11
when '018' then 12
when '029' then 13
end

score 0 · Answer 2 · answered Sep 09 '16 at 00:06

It seems that you want to compare and show them in a horizontal way. Therefore, I recommend using selfjoin, like below.

select * from
(select ... 
from tableA join tableB join ...
on ...
where srv.srv_date = 2014) as a
JOIN
(select ... 
from tableA join tableB join ...
on ...
where srv.srv_date = 2015) as b ON ...
JOIN
(select ... 
from tableA join tableB join ...
on ...
where srv.srv_date = 2016) as c ON ...

Data across multiple years.

2 Answers2