Crosstab Query using Convert

[jlig]

I'm trying to convert the MYSQL query below to work with my current CF Report. It is stopping at the CONVERT statement..?

--------------------------------------------------------------------------

SELECT CONVERT(varchar(8),cfmDate,1) AS 'Month',
       SUM(CASE WHEN DATEPART(month,cfmDate) = 1 THEN 1 ELSE 0 END) AS 'Jan',
       SUM(CASE WHEN DATEPART(month,cfmDate) = 2 THEN 1 ELSE 0 END) AS 'Feb',
       SUM(CASE WHEN DATEPART(month,cfmDate) = 3 THEN 1 ELSE 0 END) AS 'Mar',
       SUM(CASE WHEN DATEPART(month,cfmDate) = 4 THEN 1 ELSE 0 END) AS 'Apr',
       SUM(CASE WHEN DATEPART(month,cfmDate) = 5 THEN 1 ELSE 0 END) AS 'May',
       SUM(CASE WHEN DATEPART(month,cfmDate) = 6 THEN 1 ELSE 0 END) AS 'Jun',
       SUM(CASE WHEN DATEPART(month,cfmDate) = 7 THEN 1 ELSE 0 END) AS 'Jul',
       SUM(CASE WHEN DATEPART(month,cfmDate) = 8THEN 1 ELSE 0 END) AS 'Aug',
       SUM(CASE WHEN DATEPART(month,cfmDate) = 9 THEN 1 ELSE 0 END) AS 'Sep',
       SUM(CASE WHEN DATEPART(month,cfmDate) = 10 THEN 1 ELSE 0 END) AS 'Oct',
       SUM(CASE WHEN DATEPART(month,cfmDate) = 11 THEN 1 ELSE 0 END) AS 'Nov'

       SUM(CASE WHEN DATEPART(month,cfmDate) = 12 THEN 1 ELSE 0 END) AS 'Dec'

FROM      tblStatus RIGHT OUTER JOIN tblCalixFilterMaint ON tblStatus.statusID =

tblCalixFilterMaint.cfmStatus LEFT OUTER JOIN tblColloNode ON tblColloNode.NodeID

= tblCalixFilterMaint.cfmCollo
GROUP BY CONVERT(varchar(8),cfmDate,1)
ORDER BY CONVERT(varchar(8),cfmDate,1)

------------------------------------------------------------------------------

Here is the link to the original author of this code:http://www.sqlteam.com/article/counting-transactions-per-hour-using-a-pivot-table

Thanks,

jlig

[Dan_Bracuk]

Don't quote your alias.

[-__cfSearching__-]

It is stopping at the CONVERT statement..?

That article was written for MS SQL, which has a different syntax for CONVERT(..) than MySQL. The example seems to be grouping and ordering the results by day, in U.S. date format (mm/dd/yy). Just do the same, only using MySQL's functions instead. Though I personally would use a 4 digit year.

[jlig]

I have fixed the query, and it now runs with no errors (see image & code below), but it is not listing any records for April, only the May records show?

SELECT DATE_FORMAT(cfmDate, '%M') AS 'Month', tblColloNode.NodeDesc,
       SUM(CASE WHEN DATE_FORMAT(cfmDate, '%d') = 1 THEN 1 ELSE 0 END) AS 'Jan',
       SUM(CASE WHEN DATE_FORMAT(cfmDate, '%d') = 2 THEN 1 ELSE 0 END) AS 'Feb',
       SUM(CASE WHEN DATE_FORMAT(cfmDate, '%d') = 3 THEN 1 ELSE 0 END) AS 'Mar',
       SUM(CASE WHEN DATE_FORMAT(cfmDate, '%d') = 4 THEN 1 ELSE 0 END) AS 'Apr',
       SUM(CASE WHEN DATE_FORMAT(cfmDate, '%d') = 5 THEN 1 ELSE 0 END) AS 'May',
       SUM(CASE WHEN DATE_FORMAT(cfmDate, '%d') = 6 THEN 1 ELSE 0 END) AS 'Jun',
       SUM(CASE WHEN DATE_FORMAT(cfmDate, '%d') = 7 THEN 1 ELSE 0 END) AS 'Jul',
       SUM(CASE WHEN DATE_FORMAT(cfmDate, '%d') = 8 THEN 1 ELSE 0 END) AS 'Aug',
       SUM(CASE WHEN DATE_FORMAT(cfmDate, '%d') = 9 THEN 1 ELSE 0 END) AS 'Sep',
       SUM(CASE WHEN DATE_FORMAT(cfmDate, '%d') = 10 THEN 1 ELSE 0 END) AS 'Oct',
       SUM(CASE WHEN DATE_FORMAT(cfmDate, '%d') = 11 THEN 1 ELSE 0 END) AS 'Nov',
       SUM(CASE WHEN DATE_FORMAT(cfmDate, '%d') = 12 THEN 1 ELSE 0 END) AS 'Dec'

FROM      tblStatus RIGHT OUTER JOIN tblCalixFilterMaint ON tblStatus.statusID =
tblCalixFilterMaint.cfmStatus LEFT OUTER JOIN tblColloNode ON tblColloNode.NodeID
= tblCalixFilterMaint.cfmCollo

GROUP BY tblColloNode.NodeDesc, DATE_FORMAT(cfmDate, '%M')
ORDER BY tblColloNode.NodeID, DATE_FORMAT(cfmDate, '%M')

[-__cfSearching__-]

DATE_FORMAT(cfmDate, '%M')

SUM(CASE WHEN DATE_FORMAT(cfmDate, '%d') = 1

It is probably because you are using different date formats. The article groups/orders by "mm/dd/yy", and the CASE statement filters by month number. Whereas you are using month ('%M') and day '%d' only. Is that intentional?

[jlig]

That was it.. I mistakenly used %d instead of %m.. now all of the records are showing up.

Thanks to all..

jlig