Pivot Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pivot Table

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-19 : 14:20:56

How do I set up a pivot table with this stored procedure?

I want the Distinct MonthDt to be the header

The Reg to be on the left hand side
The iCnt and tCnt to match what's in the database for Reg and for that month. Does that make sense?

Want it to look like this?

Oct 08 Nov 08 Dec 08 Jan 09
Nat 15.85% 22.22% 75.22% 12.34%
NYC 12.50% 54.44% 33.55% 33.45%

ALTER PROCEDURE [dbo].[iClaimsNationM]
@Rpt char(1),
@Period char(1)
As
SET NOCOUNT ON
select ZipCd = Case when RSDNC_ZIP5 is Null then MAILG_ADDR_ZIP5
else RSDNC_ZIP5 end, MonthDt, INET_IND, COSSN
into #tempzip
from iClaims
If @Rpt = '1' and @Period = 'M'
begin
Select convert(char,a.MonthDt,1) as DowrDt, Sort='1', Reg='NAT', iCnt, tCnt, [PctInt] = Case
when tCnt = 0 then 0
when iCnt = 0 then 0
Else (convert(decimal(5,2),(((convert(decimal,iCnt))/(tCnt)) * 100)))
end
From
(select MonthDt, Count(COSSN) as iCnt
from #tempzip
where INET_IND <> 'N'
Group by MonthDt
)a
inner join
(select MonthDt, Count(COSSN) as tCnt
from #tempzip
Group by MonthDt
)b
on a. MonthDt = b. MonthDt

UNION
Select convert(char,c.MonthDt,1) as DowrDt, Sort =c.Region, Reg=c.Reg, iCnt, tCnt, [PctInt] = Case
when tCnt = 0 then 0
when iCnt = 0 then 0
Else (convert(decimal(5,2),(((convert(decimal,iCnt))/(tCnt)) * 100)))
end
From
(select Region, Reg, MonthDt, Count(COSSN) as iCnt
from #tempzip inner join ZipCodes on zipcd = zip
where INET_IND <> 'N'
Group by MonthDt ,Region, Reg
)c
inner join
(select Region, Reg, MonthDt, Count(COSSN) as tCnt
from #tempzip inner join ZipCodes on zipcd = zip
Group by Region, Reg, MonthDt
)d
on c. MonthDt = d. MonthDt and c.Region = d.Region
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 02:07:23

something like


SELECT Reg,
SUM(CASE WHEN MONTH(MonthDt)=1  AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Jan 08],
SUM(CASE WHEN MONTH(MonthDt)=2  AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08]
...

SUM(CASE WHEN MONTH(MonthDt)=1  AND YEAR(MonthDt)=2009 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2009 THEN tCnt ELSE 0 END) AS [Jan 09]

and if you want to generate year columns dynamicaly, you need dynamic sql.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-20 : 10:05:23

Thanks so much Visakh16! Added what you sent to the bottom of the stored procedure but now I'm getting

Invalid column name 'Reg'
Invalid column name 'MonthDt'
Invalid column name 'iCnt'
Invalid column name 'tCnt'

Which alias should I use for them?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 10:13:58

quote:
Originally posted by JJ297

Thanks so much Visakh16! Added what you sent to the bottom of the stored procedure but now I'm getting

Invalid column name 'Reg'
Invalid column name 'MonthDt'
Invalid column name 'iCnt'
Invalid column name 'tCnt'

Which alias should I use for them?

arent the above fields in your table?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-20 : 10:26:38

Yes they are in there but I'm joining tables and dropping a table so I was wondering if I need to use an alias to get the info?

Should I have a from in the statement to grab the info from iclaims table or from #tempzip table?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-20 : 10:36:06

This is my stored procedure do I need everything or just what you wrote?

USE [iClaims]
GO
/****** Object: StoredProcedure [dbo].[iClaimsNationM] Script Date: 11/20/2008 09:55:36 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[iClaimsNationM]
@Rpt char(1),
@Period char(1)
As
SET NOCOUNT ON
select ZipCd = Case when RSDNC_ZIP5 is Null then MAILG_ADDR_ZIP5
else RSDNC_ZIP5 end, MonthDt, INET_IND, COSSN
into #tempzip
from iClaims
If @Rpt = '1' and @Period = 'M'
begin
Select convert(char,a.MonthDt,1) as DowrDt, Sort='1', Reg='NAT', iCnt, tCnt, [PctInt] = Case
when tCnt = 0 then 0
when iCnt = 0 then 0
Else (convert(decimal(5,2),(((convert(decimal,iCnt))/(tCnt)) * 100)))
end
From
(select MonthDt, Count(COSSN) as iCnt
from #tempzip
where INET_IND <> 'N'
Group by MonthDt
)a
inner join
(select MonthDt, Count(COSSN) as tCnt
from #tempzip
Group by MonthDt
)b
on a. MonthDt = b. MonthDt

UNION
Select convert(char,c.MonthDt,1) as DowrDt, Sort =c.Region, Reg=c.Reg, iCnt, tCnt, [PctInt] = Case
when tCnt = 0 then 0
when iCnt = 0 then 0
Else (convert(decimal(5,2),(((convert(decimal,iCnt))/(tCnt)) * 100)))
end
From
(select Region, Reg, MonthDt, Count(COSSN) as iCnt
from #tempzip inner join ZipCodes on zipcd = zip
where INET_IND <> 'N'
Group by MonthDt ,Region, Reg
)c
inner join
(select Region, Reg, MonthDt, Count(COSSN) as tCnt
from #tempzip inner join ZipCodes on zipcd = zip
Group by Region, Reg, MonthDt
)d
on c. MonthDt = d. MonthDt and c.Region = d.Region
end

SELECT Reg,
SUM(CASE WHEN MONTH(DowrDt)=1 AND YEAR(DowrDt)=2008 THEN c.iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(DowrDt)=2 AND YEAR(DowrDt)=2008 THEN tCnt ELSE 0 END) AS [Jan 08],
SUM(CASE WHEN MONTH(DowrDt)=2 AND YEAR(DowrDt)=2008 THEN c.iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(DowrDt)=2 AND YEAR(DowrDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(DowrDt)=1 AND YEAR(DowrDt)=2009 THEN c.iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(DowrDt)=1 AND YEAR(DowrDt)=2009 THEN tCnt ELSE 0 END) AS [Jan 09]

drop table #tempzip

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 10:45:02

where is from clause? it should be likw


SELECT Reg,
SUM(CASE WHEN MONTH(MonthDt)=1  AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Jan 08],
SUM(CASE WHEN MONTH(MonthDt)=2  AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08]
...

SUM(CASE WHEN MONTH(MonthDt)=1  AND YEAR(MonthDt)=2009 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2009 THEN tCnt ELSE 0 END) AS [Jan 09]
FROM YourTable
GROUP BY Reg

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-20 : 11:00:19

Okay I'm confused...

I added this and I'm now getting incorrect syntax near the keyword 'case'

SELECT Reg
SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Jan 08],
SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08]
FROM iclaims
GROUP BY Reg

SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2009 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2009 THEN tCnt ELSE 0 END) AS [Jan 09]
FROM iclaims
GROUP BY Reg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 11:06:36

quote:
Originally posted by JJ297

Okay I'm confused...

I added this and I'm now getting incorrect syntax near the keyword 'case'

SELECT Reg
SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Jan 08],
SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=3 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=3 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Mar 08],
SUM(CASE WHEN MONTH(MonthDt)=4 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=4 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Apr 08],
SUM(CASE WHEN MONTH(MonthDt)=5 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=5 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=6 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=6 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=7 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=7 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=8 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=8 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=9 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=9 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=10 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=10 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=11 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=11 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=12 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=12 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2009 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2009 THEN tCnt ELSE 0 END) AS [Jan 09]
FROM iclaims
GROUP BY Reg

please use query as i posted without any alterations

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-20 : 11:22:40

Okay added this and now getting incorrect syntax near the keyword 'Case'

SELECT Reg
SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Jan 08],
SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=3 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=3 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Mar 08],
SUM(CASE WHEN MONTH(MonthDt)=4 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=4 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Apr 08],
SUM(CASE WHEN MONTH(MonthDt)=5 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=5 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=6 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=6 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=7 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=7 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=8 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=8 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=9 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=9 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=10 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=10 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=11 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=11 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=12 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=12 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2009 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2009 THEN tCnt ELSE 0 END) AS [Jan 09]
FROM iclaims
GROUP BY Reg

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-11-20 : 11:26:57

You need a comma after Reg.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-20 : 11:28:48

Did that and still getting:

Msg 207, Level 16, State 1, Line 16
Invalid column name 'Reg'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Reg'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'iCnt'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'tCnt'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'iCnt'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'tCnt'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'iCnt'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'tCnt'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'iCnt'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'tCnt'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'iCnt'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'tCnt'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'iCnt'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'tCnt'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'iCnt'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'tCnt'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'iCnt'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'tCnt'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'iCnt'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'tCnt'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'iCnt'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'tCnt'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'iCnt'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'tCnt'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'iCnt'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'tCnt'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'iCnt'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'tCnt'.

ugh...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 11:29:17

quote:

Originally posted by JJ297

Okay added this and now getting incorrect syntax near the keyword 'Case'


SELECT Reg,
SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Jan 08],
SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=3 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=3 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Mar 08],
SUM(CASE WHEN MONTH(MonthDt)=4 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=4 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Apr 08],
SUM(CASE WHEN MONTH(MonthDt)=5 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=5 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [May 08],
SUM(CASE WHEN MONTH(MonthDt)=6 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=6 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Jun 08],
SUM(CASE WHEN MONTH(MonthDt)=7 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=7 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Jul 08],
SUM(CASE WHEN MONTH(MonthDt)=8 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=8 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Aug 08],
SUM(CASE WHEN MONTH(MonthDt)=9 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=9 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Sep 08],
SUM(CASE WHEN MONTH(MonthDt)=10 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=10 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Oct 08],
SUM(CASE WHEN MONTH(MonthDt)=11 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=11 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Nov 08],
SUM(CASE WHEN MONTH(MonthDt)=12 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=12 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Dec 08],
SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2009 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2009 THEN tCnt ELSE 0 END) AS [Jan 09]
FROM iclaims
GROUP BY Reg

missed a comma

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-11-20 : 11:35:52

Those columns must not be in your iclaims table. I show these in your #tmpzip. That's my guess.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-20 : 11:47:03

Yes I was getting ready to respond back only MonthDt is in the iclaims table.

The other's are in the #tempzip table. I have to join some tables to make sure I have the correct mailing zipcode and then I'm counting the internet claims and getting their totals by %. All of that is going in the #tempzip table.

When I try to put the code in front of the #tempzip table

I added this and still getting the same invalid column name for those fields:

USE [iClaims]
GO
/****** Object: StoredProcedure [dbo].[iClaimsNationM] Script Date: 11/20/2008 11:26:28 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[iClaimsNationM]
@Rpt char(1),
@Period char(1)
As
SET NOCOUNT ON
select ZipCd = Case when RSDNC_ZIP5 is Null then MAILG_ADDR_ZIP5
else RSDNC_ZIP5 end, MonthDt, INET_IND, COSSN
into #tempzip
from iClaims
If @Rpt = '1' and @Period = 'M'
begin
Select convert(char,a.MonthDt,1) as DowrDt, Sort='1', Reg='NAT', iCnt, tCnt, [PctInt] = Case
when tCnt = 0 then 0
when iCnt = 0 then 0
Else (convert(decimal(5,2),(((convert(decimal,iCnt))/(tCnt)) * 100)))
end
From
(select MonthDt, Count(COSSN) as iCnt
from #tempzip
where INET_IND <> 'N'
Group by MonthDt
)a
inner join
(select MonthDt, Count(COSSN) as tCnt
from #tempzip
Group by MonthDt
)b
on a. MonthDt = b. MonthDt

UNION
Select convert(char,c.MonthDt,1) as DowrDt, Sort =c.Region, Reg=c.Reg, iCnt, tCnt, [PctInt] = Case
when tCnt = 0 then 0
when iCnt = 0 then 0
Else (convert(decimal(5,2),(((convert(decimal,iCnt))/(tCnt)) * 100)))
end
From
(select Region, Reg, MonthDt, Count(COSSN) as iCnt
from #tempzip inner join ZipCodes on zipcd = zip
where INET_IND <> 'N'
Group by MonthDt ,Region, Reg
)c
inner join
(select Region, Reg, MonthDt, Count(COSSN) as tCnt
from #tempzip inner join ZipCodes on zipcd = zip
Group by Region, Reg, MonthDt
)d
on c. MonthDt = d. MonthDt and c.Region = d.Region
end

SELECT Reg,
SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Jan 08],
SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=3 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=3 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Mar 08],
SUM(CASE WHEN MONTH(MonthDt)=4 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=4 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Apr 08],
SUM(CASE WHEN MONTH(MonthDt)=5 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=5 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=6 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=6 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=7 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=7 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=8 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=8 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=9 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=9 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=10 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=10 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=11 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=11 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=12 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=12 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2009 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2009 THEN tCnt ELSE 0 END) AS [Jan 09]
FROM iclaims
GROUP BY Reg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 11:48:39

very sorry we're not able to help you further if you dont know which table you've columns belonging to and also if you cant show us some sample data to indicate which table and which column you've data.please understand we can neither see your system not read your mind

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-20 : 11:58:29

Okay thanks for your help. The person who wrote the stored procedure is out today I will ask her tomorrow about it. Meanwhile I will play around with it.

One question is that a pivot table you wrote or a CTE? Can you explain what this line is doing?

SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Jan 08]

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 12:07:09

quote:
Originally posted by JJ297

Okay thanks for your help. The person who wrote the stored procedure is out today I will ask her tomorrow about it. Meanwhile I will play around with it.

One question is that a pivot table you wrote or a CTE? Can you explain what this line is doing?

SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Jan 08]

Thanks!

its neither pivot table nor CTE. its just a cross tabbing statements which aggregates all the iCnt values & tCnt values belonging to Jan 2008 and finds percentage of them into the new column

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-20 : 12:27:06

Okay thanks for the explaination. I've was able to get some data together to show you here it goes...

Here's some data from iclaims

	BICNUM	DOWR_RCPDT      Claim  INET	MAILG	RSDNC	APP_RCPDT       MonthDt
	102	9/26/2008	016375	N	89014	89014	9/24/2008	9/26/2008
	002	9/26/2008	024347	N	89014	89074	9/25/2008	9/26/2008
	002	9/26/2008	034206	N	89104	89104	9/25/2008	9/26/2008
	002	9/26/2008	056197	N	89044	89044	9/23/2008	9/26/2008
	002	9/26/2008	075684	N	89044	89044	9/24/2008	9/26/2008
	003	9/26/2008	681859	N	89044	89044	9/24/2008	9/26/2008

USE [iClaims]

This is the stored procedure that's putting the info into temp table #tempzip

GO
/****** Object:  StoredProcedure [dbo].[iClaimsNationM]    Script Date: 11/20/2008 11:26:28 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[iClaimsNationM]
@Rpt  char(1),
@Period  char(1) 
As
SET NOCOUNT ON
select ZipCd = Case when RSDNC is Null then MAILG
else RSDNC end, MonthDt, INET, Claim
into #tempzip
from iClaims 
If @Rpt = '1' and @Period = 'M' 
begin
Select  convert(char,a.MonthDt,1) as DowrDt, Sort='1',  Reg='NAT', iCnt, tCnt, [PctInt] = Case
when tCnt = 0 then 0
when iCnt = 0 then 0
Else (convert(decimal(5,2),(((convert(decimal,iCnt))/(tCnt)) * 100))) 
end  
From
 (select  MonthDt, Count(Claim) as iCnt
from  #tempzip
where INET <> 'N'
Group by  MonthDt 
)a 
inner join
 (select MonthDt, Count(Claim) as tCnt
from  #tempzip
Group by  MonthDt 
)b
on a. MonthDt = b. MonthDt

UNION
Select  convert(char,c.MonthDt,1) as DowrDt, Sort =c.Region,  Reg=c.Reg, iCnt, tCnt, [PctInt] = Case
when tCnt = 0 then 0
when iCnt = 0 then 0
Else (convert(decimal(5,2),(((convert(decimal,iCnt))/(tCnt)) * 100))) 
end
From
(select Region, Reg,  MonthDt, Count(Claim) as iCnt 
from  #tempzip inner join ZipCodes on zipcd = zip
where INET <> 'N'
Group by  MonthDt ,Region, Reg 
 )c 
inner join
(select Region, Reg,  MonthDt, Count(Claim) as tCnt 
from  #tempzip inner join ZipCodes on zipcd = zip
Group by Region, Reg, MonthDt 
 )d 
on c. MonthDt = d. MonthDt and c.Region = d.Region
end

SELECT Reg,
SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Jan 08],
SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=2 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=3 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=3 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Mar 08],
SUM(CASE WHEN MONTH(MonthDt)=4 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=4 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Apr 08],
SUM(CASE WHEN MONTH(MonthDt)=5 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=5 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=6 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=6 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=7 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=7 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=8 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=8 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=9 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=9 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=10 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=10 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=11 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=11 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=12 AND YEAR(MonthDt)=2008 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=12 AND YEAR(MonthDt)=2008 THEN tCnt ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2009 THEN iCnt ELSE 0 END)*1.0/SUM(CASE WHEN MONTH(MonthDt)=1 AND YEAR(MonthDt)=2009 THEN tCnt ELSE 0 END) AS [Jan 09]
FROM #tempzip
GROUP BY Reg

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-20 : 15:54:12

I got the cross tab to work but it's not giving me the right data (I need to change the fields). I had to keep the temp table and join a view in order to get my correct info.

Thanks so much for your help I now know about cross tabs!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources