December 16, 2007 at 9:56 pm
Hi I am trying to create a cross tab query on dbl_FuelUsage and this is what I have
SELECT TOP 100 PERCENT LEFT( str_Sitename , 3) AS SiteName
, YEAR(dat_UsageDate) AS [YEAR],
Case MONTH(dat_UsageDate) When 1 THEN SUM(dbl_FuelUsage) END AS Jan,
Case MONTH(dat_UsageDate) When 2 THEN SUM(dbl_FuelUsage) END AS Feb,
Case MONTH(dat_UsageDate) When 3 THEN SUM(dbl_FuelUsage) END AS Mar,
Case MONTH(dat_UsageDate) When 4 THEN SUM(dbl_FuelUsage) END AS Apr
FROM Production.dbo.tbl_FuelUsage
GROUP BY MONTH(dat_UsageDate), YEAR(dat_UsageDate), str_Sitename
HAVING (MONTH(dat_UsageDate) IS NOT NULL) AND
(YEAR(dat_UsageDate) > 2005)
ORDER BY YEAR(dat_UsageDate), MONTH(dat_UsageDate),
str_Sitename
What I got from this is
SiteName YEARJanFebMarApr
51 2006331529NULLNULLNULL
602006100587NULLNULLNULL
512006NULL277086NULLNULL
602006NULL99944NULLNULL
512006NULLNULL308012NULL
612006NULLNULL0NULL
and so on
However, what i want is something like this
SiteName YEARJanFebMarApr
51 2006331529277086308012NULL
602006100587999440 NULL
If anyone can help, I would be very appreciated.
December 16, 2007 at 10:47 pm
Hi,
You need to do some sub-selects as you're still getting 1 row in your result set for each row in your table....
Perhaps you want
Select
left(str_sitename, 3) as SiteName,
year(dat_usageDate) as [year],
(select sum(dbl_FuelUsage from tbl_FuelUsage fu_in where fu.siteName = fu_in.siteName and year(fu.dat_usageDate) = year(fu_in.dat_usageDate)
and month(dat_usageDate) = 1) as Jan,
(select sum(dbl_FuelUsage from tbl_FuelUsage fu_in where fu.siteName = fu_in.siteName and year(fu.dat_usageDate) = year(fu_in.dat_usageDate)
and month(dat_usageDate) = 2) as Feb,
...
...
from tbl_FuelUsage fu
group by left(str_sitename, 3), year(dat_usageDate)
Should give you the trick... The ... should be for march->december. Note the subselect just changes the month(date_usageDate) and the column alias is jan, feb, etc.
December 17, 2007 at 1:57 am
No subqueries!
SELECT LEFT( str_Sitename , 3) AS SiteName
, YEAR(dat_UsageDate) AS [YEAR],
SUM(Case MONTH(dat_UsageDate) When 1 THEN dbl_FuelUsage ELSE NULL END) AS Jan,
SUM(Case MONTH(dat_UsageDate) When 2 THEN dbl_FuelUsage ELSE NULL END) AS Feb,
SUM(Case MONTH(dat_UsageDate) When 3 THEN dbl_FuelUsage ELSE NULL END) AS Mar,
SUM(Case MONTH(dat_UsageDate) When 4 THEN dbl_FuelUsage ELSE NULL END) AS Apr
FROM Production.dbo.tbl_FuelUsage
WHERE dat_UsageDate >= '20060101'
GROUP BY YEAR(dat_UsageDate), str_Sitename
ORDER BY YEAR(dat_UsageDate), str_Sitename
And don't build queries in Enterprise Manager.
Query Analyzer is much better for it.
_____________
Code for TallyGenerator
January 3, 2008 at 7:52 pm
Nice code sergiy - this would generate a better exec plan. Perhaps the case statements should say else 0 rather then else NULL only because you'll get annoying warnings depending on your SET settings since you're aggregating NULLs. A 0 here instead would not change the answer (since it's a sum not an average).
I was lazy when I initially answered this question - the laziness is on display for all forever. Shame 😀
January 3, 2008 at 9:35 pm
nice one sergiy
"Keep Trying"
January 4, 2008 at 12:43 am
It's fixed. Thanks Sergiy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply