SQL Monthly Crosstab report - 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

Transact-SQL (2000)

SQL Monthly Crosstab report

Author

Topic

BigJohnson
Starting Member

9 Posts

Posted - 2006-07-11 : 05:40:52

Hi guys & gals....
I have a final SQL query to upsize from Access, and I can't figure this out, can anyone please assist.

Essentially, priority description down the left, months of the year across the top, and then a count of records as the data.

Here's my existing Access version - thanks all.

TRANSFORM Sum(OnTime)
SELECT tblDefStd.DefStdDesc FROM (tblDevCom INNER JOIN tblSites ON tblDevCom.DevComId = tblSites.DevComId) INNER JOIN (tblSiteAdd INNER JOIN (tblDefStd INNER JOIN (tblDefCatType INNER JOIN tblDefect ON tblDefCatType.DCTId = tblDefect.DCTId) ON tblDefStd.DefStdId = tblDefCatType.DefStdId) ON tblSiteAdd.AddID = tblDefect.AddID) ON tblSites.SiteId = tblSiteAdd.SiteID WHERE (DatePart('yyyy',[DateRaised])=::PickYear::) AND (tblDevCom.ComId = ::ComId::) AND (tblDevCom.Dev_Id LIKE '%::Dev_Id::%') GROUP BY tblDefStd.DefStd, tblDefStd.DefStdDesc ORDER BY tblDefStd.DefStd PIVOT DatePart('m',[DateRaised]) In (1,2,3,4,5,6,7,8,9,10,11,12);"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-11 : 08:42:53

http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

BigJohnson
Starting Member

9 Posts

Posted - 2006-07-28 : 09:21:42

Hi
Got the crosstab SP off of this site,and in itself works well, all apart from the multiple sum function.

EXECUTE crosstab 'SELECT DefStdDesc FROM tblDefStd
INNER JOIN tblDefCatType ON (tblDefStd.DefStdId=tblDefCatType.DefStdId)
INNER JOIN tblDefect ON (tblDefCatType.DCTId = tblDefect.DCTId)
INNER JOIN tblSiteAdd ON (tblDefect.AddID = tblSiteAdd.AddID)
INNER JOIN tblSites ON (tblSiteAdd.SiteID = tblSites.SiteID)
INNER JOIN tblDevCom ON (tblSites.DevComID = tblDevCom.DevComID)
WHERE DatePart(yyyy,DateRaised)=2005 AND tblDevCom.Dev_Id=1
GROUP BY DefStdDesc, DefStd ORDER BY DefStd ASC',
'100*sum(CASE WHEN DaysOD IS NOT NULL AND DaysOD <= tblDefStd.DefStd THEN 1 ELSE 0 END) / sum(CASE WHEN DateClosed IS NOT NULL THEN 1 ELSE 0 END)',
' Month(DateRaised) ', 'tblDefect'

If i use (100*sum(1)/sum(2)) it fails.
If I use (100*sum(1)) then it works.
If I use (100*sum(2)) then it works.

I need it to work for the first example.
Any ideas?

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-07-28 : 13:20:09

Change it to 100 * (Sum(1) / Sum(2))

Also, you're aware that you're setting yourself up for a divide by 0 error in your second SUM, aren't you?

Ken

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-29 : 09:27:35

"'100*sum(CASE WHEN DaysOD IS NOT NULL AND DaysOD <= tblDefStd.DefStd THEN 1 ELSE 0 END) / sum(CASE WHEN DateClosed IS NOT NULL THEN 1 ELSE 0 END)'"
You will have DIVIDE BY ZERO error when DateClosed is NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 03:38:16

[code]DECLARE @WantedYear INT,
@ComID VARCHAR(100),
@DevID VARCHAR(100)

SELECT @WantedYear = 2006,
@ComID = '4578',
@DevID = ''

SELECT tblDefStd.DefStd,
tblDefStd.DefStdDesc,
SUM(CASE WHEN Month(DateRaised) = 1 THEN OnTime ELSE 0 END) January,
SUM(CASE WHEN Month(DateRaised) = 2 THEN OnTime ELSE 0 END) February,
SUM(CASE WHEN Month(DateRaised) = 3 THEN OnTime ELSE 0 END) March,
SUM(CASE WHEN Month(DateRaised) = 4 THEN OnTime ELSE 0 END) April,
SUM(CASE WHEN Month(DateRaised) = 5 THEN OnTime ELSE 0 END) May,
SUM(CASE WHEN Month(DateRaised) = 6 THEN OnTime ELSE 0 END) June,
SUM(CASE WHEN Month(DateRaised) = 7 THEN OnTime ELSE 0 END) July,
SUM(CASE WHEN Month(DateRaised) = 8 THEN OnTime ELSE 0 END) August,
SUM(CASE WHEN Month(DateRaised) = 9 THEN OnTime ELSE 0 END) September,
SUM(CASE WHEN Month(DateRaised) = 10 THEN OnTime ELSE 0 END) October,
SUM(CASE WHEN Month(DateRaised) = 11 THEN OnTime ELSE 0 END) November,
SUM(CASE WHEN Month(DateRaised) = 12 THEN OnTime ELSE 0 END) December
FROM tblDevCom
INNER JOIN tblSites ON tblSites.DevComId = tblDevCom.DevComId
INNER JOIN tblSiteAdd ON tblSiteAdd.SiteID = tblSites.SiteId
INNER JOIN tblDefect ON tblDefect.AddID = tblSiteAdd.AddID
INNER JOIN tblDefCatType ON tblDefCatType.DCTId = tblDefect.DCTId
INNER JOIN tblDefStd ON tblDefStd.DefStdId = tblDefCatType.DefStdId
WHERE YEAR(DateRaised) = @WantedYear
AND tblDevCom.ComId = @ComId
AND tblDevCom.Dev_Id LIKE '%' + @Dev_Id + '%'
GROUP BY tblDefStd.DefStd,
tblDefStd.DefStdDesc
ORDER BY tblDefStd.DefStd,
tblDefStd.DefStdDesc[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources