Help with pivoting and cross tabbing query

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 2000 Forums

SQL Server Development (2000)

Help with pivoting and cross tabbing query

Author

Topic

PWilson
Starting Member

1 Post

Posted - 2002-06-10 : 18:03:25

Hi I was wondering if anyone can help me with this query. I am getting an invalid column name error message on "Qtr1Cnt" and "Qtr1PLCTot" (etc. through Qtr4) columns. Also, I realize that pivoting and cross tabbing are expensive operations but unfortunately I'm not sure how to write this in a stored procedure...

Here's the code:

select Name, BusinessPartner, UserName, Email, OptVal, BPName,
Quarter1CountTotal as Q1Cnt,
sum(cast(Quarter1PLCTotal as money)) as Q1PLCTot,
Quarter2CountTotal as Q2Cnt,
sum(cast(Quarter2PLCTotal as money)) as Q2PLCTot,
Quarter3CountTotal as Q3Cnt,
sum(cast(Quarter3PLCTotal as money)) as Q3PLCTot,
Quarter4CountTotal as Q4Cnt,
sum(cast(Quarter4PLCTotal as money)) as Q4PLCTot
from (
select a.Name "Name", a.BP "BusinessPartner", a.UserName "UserName", a.Email "Email", b.OptVal "OptVal",
b.BPName "BPName", count(c.DateTimeKey) "Quarter1CountTotal",c.PLCEst "Quarter1PLCTotal",
0 "Quarter2CountTotal", 0 "Quarter2PLCTotal", 0 "Quarter3CountTotal", 0 "Quarter3PLCTotal", 0
"Quarter4CountTotal", 0 "Quarter4PLCTotal"
from dbo.tUsers a, dbo.BP b, dbo.FallSpiff C
where
a.BP = b.OptVal and
c.Owner = a.UserName and
c.FinalStatus = 1 and
((c.DE <> 1) and (c.CCD <> 1)) and
(cast(c.CloseDate as DateTime)) > '2002-01-01 00:00:00.000' and
(cast(c.CloseDate as DateTime)) < '2002-04-01 00:00:00.000'
union
select a.Name "Name", a.BP "BusinessPartner", a.UserName "UserName", a.Email "Email", b.OptVal "OptVal",
b.BPName "BPName", 0, 0, count(c.DateTimeKey), c.PLCEst, 0, 0, 0, 0
from dbo.tUsers a, dbo.BP b, dbo.FallSpiff C
where
a.BP = b.OptVal and
c.Owner = a.UserName and
c.FinalStatus = 1 and
((c.DE <> 1) and (c.CCD <> 1)) and
(cast(c.CloseDate as DateTime)) > '2002-04-01 00:00:00.000' and
(cast(c.CloseDate as DateTime)) < '2002-07-01 00:00:00.000'
union
select a.Name "Name", a.BP "BusinessPartner", a.UserName "UserName", a.Email "Email", b.OptVal "OptVal",
b.BPName "BPName", 0, 0, 0, 0, count(c.DateTimeKey), c.PLCEst, 0, 0
from dbo.tUsers a, dbo.BP b, dbo.FallSpiff C
where
a.BP = b.OptVal and
c.Owner = a.UserName and
c.FinalStatus = 1 and
((c.DE <> 1) and (c.CCD <> 1)) and
(cast(c.CloseDate as DateTime)) > '2002-07-01 00:00:00.000' and
(cast(c.CloseDate as DateTime)) < '2002-10-01 00:00:00.000'
union
select a.Name "Name", a.BP "BusinessPartner", a.UserName "UserName", a.Email "Email", b.OptVal "OptVal",
b.BPName "BPName", 0, 0, 0, 0, 0, 0, count(c.DateTimeKey), c.PLCEst
from dbo.tUsers a, dbo.BP b, dbo.FallSpiff C
where
a.BP = b.OptVal and
c.Owner = a.UserName and
c.FinalStatus = 1 and
((c.DE <> 1) and (c.CCD <> 1)) and
(cast(c.CloseDate as DateTime)) > '2002-10-01 00:00:00.000' and
(cast(c.CloseDate as DateTime)) < '2002-01-02 00:00:00.000'
) QuarterSpiff
group by Name, BusinessPartner, UserName, Email, OptVal, BPName, Q1Cnt, Q1PLCTot,
Q2Cnt, Q2PLCTot, Q3Cnt, Q3PLCTot, Q4Cnt, Q4PLCTot

JamesH
Posting Yak Master

149 Posts

Posted - 2002-06-10 : 22:23:27

I think you may have thought about this too hard. Try removing your aliases from your group by and having it actually group by your base column.

BTW, Your stored procedure can utilize the creation of tables 'on-the-fly' which might help your performance if you're having a hit. Proper indexing of the base tables will probably help you the most though.

HTH,

JamesH.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources