PIVOT CASE QUERY Help

Question

PIVOT CASE QUERY Help

sammm

SSC-Addicted

Points: 425
More actions
March 4, 2010 at 6:03 pm

#217488

HI ALL,
I have updated this one(mainly the code ) to get more clarity on this. Thanks in Advance.!
I am in process of creating a report. Below query gives me a report like this
YEARMONTHCOMPLETECANCELLED
20096 24348 20
Ask is i want to add one more where condition in the select statement( The highligted above result is only for one condition in the selevct query ex where codename=TRAIN. If i want to add one more condition to the query like " where codename=BUS how do i achive this. I would like to have a report like this
Any help would be greatly appreciated.
YEARMONTHCOMPLETE(TRAIN) CANCELLED(TRAIN) COMPLETE(BUS) CANCELLED(BUS)
2009624348 20 10 30
sAMPLE DATA:
INSERT INTO @TBL (yEAR,MONTH,COUNT,CODE)
VALUES ('2009','11','3400','DELIVERY')
INSERT INTO @TBL (yEAR,MONTH,COUNT,CODE)
VALUES ('2009','11','3000','CANCELLED')
INSERT INTO @TBL (yEAR,MONTH,COUNT,CODE)
VALUES ('2009','12','2300','DELIVERY')
INSERT INTO @TBL (yEAR,MONTH,COUNT,CODE)
VALUES ('2009','12','1300','CANCELLED')
DECLARE @tbl TABLE
([YEAR] INT,[MONTH] INT, [COUNT] INT, code VARCHAR(30)
)
INSERT INTO @tbl
select yEAR(a.datetime)AS YEAR,Month(a.datetime) as month,count(*) as count,b.code
from table a,
table b
where a.datetime between '2009-06-01 00:00:00.000' and '2009-12-30 23:59:59.999'
and a.codename like '%TRAIN%'
and a.statusid=b.ActivityID
group by yEAR(a.datetime),Month(a.datetime),a.statusid ,b.code
order by yEAR(a.datetime),Month(a.datetime) ,a.statusid,b.code
-- PIVOT
SELECT
[YEAR],
[MONTH],
SUM(CASE WHEN code ='COMPLETE' THEN [COUNT] ELSE 0 END) AS [COMPLETE],
SUM(CASE WHEN code ='CANCELLED' THEN [COUNT] ELSE 0 END) AS [CANCELLED],
100-CAST(SUM(CASE
WHEN code ='CANCELLED' THEN [COUNT]
ELSE 0
END)as decimal)*100
/
cast(SUM(CASE
WHEN code ='COMPLETE' THEN [COUNT]
ELSE 0 END) AS DECIMAL)As 'PERCENTAGE %'
FROM @tbl
GROUP BY [YEAR],[MONTH]
Thanks!

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Would you please modify your sample data to include some sample data for your new columns?

Also please clarify whether the data you'd like to PIVOT will be static (e.g. there will be no codename='CAR' added later on).

If you need to make your report dynamic then you might want to read the article "DynamicCrossTab" I rerferenced in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sammm SSC-Addicted Points: 425 More actions · Answer 2

Hi,I have updated the code to get more clarity on this request.

Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 3

Unfortunately, we don't have any sample data :unsure:

Please use the INSERT INTO @tbl and add some sample data rather thana join to tables we don't have access to.

Also, you didn't answer my question regarding any possible request to use that query with dynamic data.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sammm SSC-Addicted Points: 425 More actions · Answer 4

Hi ,Thanks fot your reply.i have addded some sample data. I need to research on dynamic crosstabs if this is not possible

I just want to add one more condition i.e bus(in addition to %TRAIN%') or something in the below where condition.

select yEAR(a.datetime)AS YEAR,Month(a.datetime) as month,count(*) as count,b.code

from table a,

table b

where a.datetime between '2009-06-01 00:00:00.000' and '2009-12-30 23:59:59.999'

and a.codename like

'%TRAIN%'

and a.statusid=b.ActivityID

group by yEAR(a.datetime),Month(a.datetime),a.statusid ,b.code

order by yEAR(a.datetime),Month(a.datetime) ,a.statusid,b.code

Thanks

sammm SSC-Addicted Points: 425 More actions · Answer 5

Never mind i framed the query myself. Thanks anyway

LutzM SSC Guru Points: 107049 More actions · Answer 6

There is still no sample data available that would make it possible to create your expected output: COMPLETE(TRAIN) CANCELLED(TRAIN) COMPLETE(BUS) CANCELLED(BUS)

Let's start from the very beginning:

Please show us the data you have (supported by ready to use sample data like you just added) and the result you need based on that. And we'll work from there. What you have posted so far is confusing (at least to me it is):

You posted that you'd like to build a temp table with a filter on '%TRAIN%' but at the same time you request an output like COMPLETE(BUS) CANCELLED(BUS).

Think about the result you need and the data you have. Don't try to change the requirement just because you think it might be easier to do (sometimes the desciption is getting more confusing by doing so). Post both, requested data based on the given sample in an easy consumable way (like you did with the INSERT statement).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

sammm (3/5/2010)
Never mind i framed the query myself. Thanks anyway

Two way street here. Please post the solution you came up with. Thanks.

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