April 22, 2012 at 9:15 pm
I need to add another aggregation to this query to make the distinction between applications that have workitems with severity 1 status versus severity 2 status and are in any of the four statuses.
--current my pivot displays how many applications currently have workitems in any of 4 statuses which are listed horizontally.
select
application, [deferred], [fixed], [by design], [total]
from
(
select application, resolution_reason, workitem_id, COUNT(*) over (partition by application) as total
from workitems) wi
pivot (count(workitem_id) for resolution_reason in ([deferred], [fixed], [by design])) as pvt
order by application;
---DDL
create table dbo.WorkItems
(workitem_id int
, app varchar (20)
, resolution_reason varchar(20)
, severity int
, date_opened datetime
)
insert into dbo.WorkItems
values
(20, 'hearts', 'deferred', 1, getdate()-30),
(21, 'spades', 'deferred', 1, getdate()-60),
(22, 'diamonds', 'fixed', 1, getdate()-390),
(23, 'diamonds', 'by design', 1, getdate()-450),
(24, 'hearts', 'fixed', 1, getdate()-660),
(25, 'hearts', 'fixed', 1, getdate()-700),
(26, 'spades', 'fixed', 1, getdate()-45),
(27, 'joker', 'fixed', 1, getdate()-500),
(28, 'hearts', 'deferred', 1, getdate()-469),
(29, 'spades', 'deferred', 1, getdate()-523),
(30, 'spades', 'fixed', 1, getdate()-360)
(31, 'spades', 'deferred', 2, getdate()-60),
(32, 'diamonds', 'fixed', 2, getdate()-390),
(33, 'diamonds', 'by design', 2, getdate()-450),
(34, 'hearts', 'fixed', 2, getdate()-660),
(35, 'hearts', 'fixed', 2, getdate()-700),
(36, 'spades', 'fixed', 2, getdate()-45),
(37, 'joker', 'fixed', 2, getdate()-500),
(38, 'hearts', 'deferred', 2, getdate()-469),
(39, 'spades', 'deferred', 2, getdate()-523),
(40, 'spades', 'fixed', 2, getdate()-360);
select
app
, [2010]
, [2011]
, [2012]
from
(select app, severity, YEAR(date_opened) as RptYear from dbo.WorkItems where severity = 1) as wi
,YEAR(date_opened) as RptYear from dbo.WorkItems where severity = 2) as wi
pivot(count(severity) for RptYear in ([2010], [2011], [2012])) as pvt
;
drop table dbo.WorkItems;
demo of desired outcome
create table #Result
(
Application varchar(50),
Severity varchar(50),
Deferred varchar(50),
Fixed varchar(50),
[By Design] varchar(50)
)
insert #Result
select 'hearts','', '2', '2', '' union all
select 'Sev1', '2', '2', '', '' union all
select 'Sev2', '2', '2', '', '' union all
select 'spades', '','2', '2', '' union all
select 'Sev1', '2','2','', '' union all
select 'Sev2', '2','2', '', ''
select * from #Result;
drop table #Result;
What am I trying to do in SQL terms? What tool do I reach for?
--Quote me
April 22, 2012 at 10:13 pm
Instead of what you posted, how about this:
create table dbo.WorkItems
(workitem_id int
, app varchar (20)
, resolution_reason varchar(20)
, severity int
, date_opened datetime
)
insert into dbo.WorkItems
values
(20, 'hearts', 'deferred', 1, getdate()-30),
(21, 'spades', 'deferred', 1, getdate()-60),
(22, 'diamonds', 'fixed', 1, getdate()-390),
(23, 'diamonds', 'by design', 1, getdate()-450),
(24, 'hearts', 'fixed', 1, getdate()-660),
(25, 'hearts', 'fixed', 1, getdate()-700),
(26, 'spades', 'fixed', 1, getdate()-45),
(27, 'joker', 'fixed', 1, getdate()-500),
(28, 'hearts', 'deferred', 1, getdate()-469),
(29, 'spades', 'deferred', 1, getdate()-523),
(30, 'spades', 'fixed', 1, getdate()-360),
(31, 'spades', 'deferred', 2, getdate()-60),
(32, 'diamonds', 'fixed', 2, getdate()-390),
(33, 'diamonds', 'by design', 2, getdate()-450),
(34, 'hearts', 'fixed', 2, getdate()-660),
(35, 'hearts', 'fixed', 2, getdate()-700),
(36, 'spades', 'fixed', 2, getdate()-45),
(37, 'joker', 'fixed', 2, getdate()-500),
(38, 'hearts', 'deferred', 2, getdate()-469),
(39, 'spades', 'deferred', 2, getdate()-523),
(40, 'spades', 'fixed', 2, getdate()-360);
go
select * from dbo.WorkItems order by app, severity;
select
app, severity, [deferred], [fixed], [by design], [total]
from
(
select app, severity, resolution_reason, workitem_id, COUNT(*) over (partition by app, severity) as total
from workitems) wi
pivot (count(workitem_id) for resolution_reason in ([deferred], [fixed], [by design])) as pvt
order by app;
GO
drop table dbo.WorkItems;
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply