469,138 Members | 1,298 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,138 developers. It's quick & easy.

Crosstab Query

hi, im am newbie in sql 2000 server and appreciate any help on my problem.

i have this data :

COC_DATE POLICY NO PREMIUM
01/02/2007 CEN001 750.00
01/02/2007 CEN002 750.00
01/02/2007 NCR001 750.00
01/03/2007 NCR002 750.00

and i need to produce the following result having each day of the a given month as column header.

POLICY_NO 01/02/2007 01/03/2007
CEN 1500.00 0.00
NCR 750.00 750.00

thanks in advance for any help.

jude
Jan 17 '08 #1
2 1010
debasisdas
8,127 Expert 4TB
Kindly post what you have tried so far.
Jan 17 '08 #2
Delerna
1,134 Expert 1GB
Hey jude (sorry couldn't resist)
This is actually very well posed, a diagram of your table data and the query result you want helps to write any query, I encourage you to always do that.

I think this is what you are after. do it in a stored procedure or a user defined function so you can have variable parameters

Here I provide the basics of it as a stored procedure for you

CREATE PROC NameOfYourChoosing @Yr int,@Mth
AS
SELECT PolicyNo,
Sum(D1) as D1,
Sum(D2) as D2
--I will let you fill out the rest of the sum statements, 1 for each day
FROM
(SELECT left(policyNo,3) as PolicyNo,
case when day(coc_date)=1 then NoPremium else 0 end as D1,
case when day(coc_date)=2 then NoPremium else 0 end as D2,
--repeat the case statements 1 for each day of the month,
FROM YourTableName) a
WHERE year(coc_date)=@yr and Month(coc_date)=@Mth
GROUP BY PolicyNo

how it works
this bit is a subquery or derived table
SELECT left(policyNo,3) as PolicyNo,
case when day(coc_date)=1 then NoPremium else 0 end as D1,
case when day(coc_date)=2 then NoPremium else 0 end as D2,
--repeat the case statements 1 for each day of the month,
FROM YourTableName

it organises your data as a crosstab query, ie 1 column for each day of the month. If there is data for that month then it is returned in the corresponding column, otherwise a 0 is returned.

The rest of the query uses the results of the subquery as though it was a table called "a" (thats why its called a derived table) and sums the data grouped by your Policy No

You may need to play around with it a bit but I think it will get you started in the right direction, I hope it helps
Jan 17 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Nathan Bloomfield | last post: by
2 posts views Thread by Sherman H. | last post: by
4 posts views Thread by Judy | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.