Pivot table

Question

Pivot table

sthenmozhi14

Valued Member

Points: 61
More actions
January 19, 2010 at 6:21 am

#215806

Hi All,
I have display monthly claim of count for yearwise using pivot table.
select Policy,[April], [May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March]
from
(
select policy.new_policytype as policy,insur.new_claimvouchernumber as vocher,datename(mm,insur.NEW_EFFECTIVEDATE) as name from new_nsurance insur,new_insurancepolicy policy where ((Year(insur.new_effectivedate)='2008') and (datename(mm,insur.new_effectivedate) between 'april' and 'december')) or ((Year(insur.new_effectivedate)='2008'+1) and (datename(mm,insur.new_effectivedate) between 'january' and 'march')) and insur.new_claimvouchernumber is not null )as new_nsurance
PIVOT
(
count(vocher)
FOR name IN ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])
) AS PivotTable;
ex:
i have applied where condition year like '2008'.It should display 2008 financial year of policy and count of voucher.
policy april may june july aug sep oct nov dec jan feb march
sdsd 34 444 33 33 33 444 55 55 55 55 55 5555
In my question is
But i want see sametime two finanicial year comparision of vocher.
I have passed 2009 in my query.
it should show 2009 year data as well as previous year 2008.
policy april 08 april 09 may 08 may 09 jun 08 jun 09 july 08 jul 09
sdsd 34 67 444 5454 56 66 4545 5454
can u help me

Viewing 4 posts - 1 through 3 (of 3 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

You would either have to add a column identifying year + month instead of month only and alter your PIVOT clause to reflect that or you might want to have a look into DynamicCrossTabs (see the related link in my signature for more details).

If you need further assistance please provide table definition, sample data and expected result as described in the first link in my sig.

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 2

The Dynamic Crosstab article that Lutz mentions above very specifically has a "year long" example that should do the trick for you. All you have to do is change the order to reflect the non-calendar year order of the months.

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

sthenmozhi14 Valued Member Points: 61 More actions · Answer 3

Hi ,

Thank you for your reply Lutz and Jeff Moden.

I have using cross tab..I got it.

Thank you..