pivot by year using datetime column

Question

pivot by year using datetime column

polkadot

SSChampion

Points: 13664
More actions
April 22, 2012 at 12:06 pm

#259873

i would like a pivot query that aggregates counts by years 2012, 2011, 2012 for all workitems having a severity =1.
---what I have
select
application
, severity
, [2010]
, [2011]
, [2012]
from workitems
where severity = 1
pivot (count(severity) for year(date_opened) in [2010], [2011], [2012]) pvt1;
---DDL
create table WorkItems
(workitem_id int
, application varchar (20)
, resolution_reason varchar(20)
, severity int
, date_opened datetime
)
insert into 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);
I am trying to get pivotting down this weekend and keep butting up against problems. If you show me, using my query as foundation, where I have gone wrong?
--Quote me

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I'd say your problem is that your code is syntacticly incorrect. You may want to review the PIVOT operator in Books Online.

Below is what I was able to come up with following the example in Books Online'

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);

select

app

, [2010]

, [2011]

, [2012]

from

(select app, severity, YEAR(date_opened) as RptYear from dbo.WorkItems where severity = 1) as wi

pivot(count(severity) for RptYear in ([2010], [2011], [2012])) as pvt

;

drop table dbo.WorkItems;

polkadot SSChampion Points: 13664 More actions · Answer 2

There was only one problem. I was trying to use YEAR function within the PIVOT operator. I can get the same results with count(workitem_id) as in:

select

application

, [2010]

, [2011]

, [2012]

from

(select application, workitem_id, YEAR(date_opened) as RptYear from WorkItems where severity = 1) as wi

pivot(count(workitem_id) for RptYear in ([2010], [2011], [2012])) as pvt

Thanks.

--Quote me

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 3

polkadot (4/22/2012)
There was only one problem. I was trying to use YEAR function within the PIVOT operator. I can get the same results with count(workitem_id) as in:
select
application
, [2010]
, [2011]
, [2012]
from
(select application, workitem_id, YEAR(date_opened) as RptYear from WorkItems where severity = 1) as wi
pivot(count(workitem_id) for RptYear in ([2010], [2011], [2012])) as pvt
Thanks.

You'll need to remember to change that code every year. Consider something a bit more dynamic. A relatively easy and performant method can be found in the following article.

http://www.sqlservercentral.com/articles/Crosstab/65048/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

polkadot SSChampion Points: 13664 More actions · Answer 4

polkadot

SSChampion

Points: 13664

April 22, 2012 at 5:31 pm

#1477225

good point. thanks again.

--Quote me

polkadot SSChampion Points: 13664 More actions · Answer 5

Jeff, can I use cross tabs (or pivot) to aggregate by both Year and Quarter horizontally and by application vertically?

I would say that it would create such a wide report, that it would be absurd, but please tell me if it can be done.

--Quote me

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

polkadot (4/22/2012)
Jeff, can I use cross tabs (or pivot) to aggregate by both Year and Quarter horizontally and by application vertically?
I would say that it would create such a wide report, that it would be absurd, but please tell me if it can be done.

I am much better at problem solving if I have something visual to see. Could you post an example of what you are asking, including some sample data?

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 7

polkadot (4/22/2012)
Jeff, can I use cross tabs (or pivot) to aggregate by both Year and Quarter horizontally and by application vertically?
I would say that it would create such a wide report, that it would be absurd, but please tell me if it can be done.

Absolutely. IMHO, Cross Tabs would make an easy go of it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)