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

SQL Server Development (2000)

Chart Data

Author

Topic

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2009-02-18 : 17:11:57

I need to put this data in a cross tab format for graphing. Here's what I got below and it's just not working. It's doing this . .

WorkGroupName|Jan|Feb
Tapping Automated|57|Null
Tapping Automated|Null|57

And of course, I need this . . .

WorkGroupName|Jan|Feb
Tapping Automated|57|57

Any help would be soooo appreciated!

Declare @WorkGroupName varchar(25)
Set @WorkGroupName = 'Tapping Automated'

Select wc.WorkGroupName,

Case when DatePart(mm, r.RunDate) = 1 then
((Sum([Labor]) - Sum([DownTime])) / Sum([Labor])) * --as Av,
(SUM(SPM) / (sum(Labor) - sum(DownTime))) * --as PF,
(Sum([PcsProduced]) / (Sum([PcsProduced]) - Sum([Scrap])) * 100)
Else Null end as Jan,

Case when DatePart(mm, r.RunDate) = 2 then
((Sum([Labor]) - Sum([DownTime])) / Sum([Labor])) * --as Av,
(SUM(SPM) / (sum(Labor) - sum(DownTime))) * --as PF,
(Sum([PcsProduced]) / (Sum([PcsProduced]) - Sum([Scrap])) * 100)
Else Null end as Feb

FROM dbo.TAM_DP_QAD_Reporting AS r INNER JOIN
dbo.QAD_WC AS wc ON r.WC = wc.WCID
where
year(r.RunDate) = Year(GetDate())
and wc.WorkGroupName=@WorkGroupName
Group by wc.WorkGroupName, DatePart(mm, r.RunDate)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-18 : 18:43:56

Maybe:

Select WorkGroupName,
MAX(Jan)as JAN,
MAX(Feb)as FEB....
from
 (Select wc.WorkGroupName,
Case when DatePart(mm, r.RunDate) = 1 then
((Sum([Labor]) - Sum([DownTime])) / Sum([Labor])) * --as Av,
(SUM(SPM) / (sum(Labor) - sum(DownTime))) * --as PF,
(Sum([PcsProduced]) / (Sum([PcsProduced]) - Sum([Scrap])) * 100)
Else Null end as Jan,

Case when DatePart(mm, r.RunDate) = 2 then
((Sum([Labor]) - Sum([DownTime])) / Sum([Labor])) * --as Av,
(SUM(SPM) / (sum(Labor) - sum(DownTime))) * --as PF,
(Sum([PcsProduced]) / (Sum([PcsProduced]) - Sum([Scrap])) * 100)
Else Null end as Feb

FROM dbo.TAM_DP_QAD_Reporting AS r INNER JOIN
dbo.QAD_WC AS wc ON r.WC = wc.WCID
where 
year(r.RunDate) = Year(GetDate())
and wc.WorkGroupName=@WorkGroupName
Group by wc.WorkGroupName, DatePart(mm, r.RunDate))Z
Group by WorkGroupName

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2009-02-19 : 08:48:33

That totally worked! You're the best sodeep!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 10:13:06

Good Girl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources