/build/static/layout/Breadcrumb_cap_w.png

Best Practices Question


SQL Report Coding Help - Show new hires, terminations, open and closed tickets in a column view

03/01/2013 2920 views

I've been working on a code trying to get it to display into columns view and not the row type. So I've added two codes that I've joined together using the UNION ALL but I would love to get the second part of the code into a column view instead of a row view. Code is attached below. Any help would be much appreciated:

SELECT OPEN.MONTH, 
        OPEN.YEAR,
        Coalesce(OPEN.OPEN, 0) AS OPENED,
        Coalesce(CLOSED.CLOSED, 0) AS CLOSED
FROM  (SELECT MONTH(T.CREATED) AS MONTH, YEAR (T.CREATED) AS YEAR, COUNT(*) AS OPEN
        FROM  HD_TICKET T
        WHERE (T.HD_QUEUE_ID = 23) and T.HD_CATEGORY_ID not in (1169,695,1172,1171,1179,1174,1175,1178,1205,1170,1176,1177,1181,1207,1173,1183,1186,1184,1189,1187,1188,1182,1190,1185,1196,1191,1194,1192,1198,1195,1256,1197,1193) and (T.CUSTOM_FIELD_VALUE5 like '%tech%') and T.CREATED not like '%2011%'
        GROUP  BY MONTH, YEAR
        ORDER  BY YEAR, MONTH) OPEN
LEFT JOIN (SELECT MONTH(T.TIME_CLOSED) AS MONTH, YEAR (T.TIME_CLOSED) AS YEAR, COUNT(*) AS CLOSED
        FROM  HD_TICKET T
        WHERE (T.HD_QUEUE_ID = 23) and T.HD_CATEGORY_ID not in (1169,695,1172,1171,1179,1174,1175,1178,1205,1170,1176,1177,1181,1207,1173,1183,1186,1184,1189,1187,1188,1182,1190,1185,1196,1191,1194,1192,1198,1195,1256,1197,1193) and (T.CUSTOM_FIELD_VALUE5 like '%tech%') and T.CREATED not like '%2011%'
        GROUP  BY MONTH, YEAR
        ORDER  BY YEAR, MONTH) CLOSED
ON( OPEN.MONTH = CLOSED.MONTH AND OPEN.YEAR = CLOSED.YEAR )

UNION ALL

SELECT  NH.MONTH,
        NH.YEAR,
        Coalesce(NH.NH, 0) AS 'New Hires',
        Coalesce(Term.Term, 0) AS 'Terminations'
FROM  (SELECT MONTH(T.TIME_CLOSED) AS MONTH, YEAR (T.TIME_CLOSED) AS YEAR, COUNT(*) AS NH
        FROM  HD_TICKET T
        WHERE (T.HD_QUEUE_ID = 23) and T.HD_CATEGORY_ID in (919,167,918,920) and (T.CUSTOM_FIELD_VALUE5 like '%tech%') and T.CREATED not like '%2011%'
        GROUP  BY MONTH, YEAR
        ORDER  BY YEAR, MONTH) NH
LEFT JOIN (SELECT MONTH(T.TIME_CLOSED) AS MONTH, YEAR (T.TIME_CLOSED) AS YEAR, COUNT(*) AS Term
        FROM  HD_TICKET T
        WHERE (T.HD_QUEUE_ID = 23) and T.HD_CATEGORY_ID in (168) and (T.CUSTOM_FIELD_VALUE5 like '%tech%') and T.CREATED not like '%2011%'
        GROUP  BY MONTH, YEAR
        ORDER  BY YEAR, MONTH) Term
ON( NH.MONTH = Term.MONTH AND NH.YEAR = Term.YEAR )

After the UNION ALL I would love to get the second part of the code to show up as a seperate column but I've been having really hard time getting this figured out. Any help is greatly appreciated!

Hello,

I've been working on a code trying to get it to display into columns view and not the row type. So I've added two codes that I've joined together using the UNION ALL but I would love to get the second part of the code into a column view instead of a row view. Code is attached below. Any help would be much appreciated:

- See more at: http://www.itninja.com/question/sql-report-showing-new-hires-terminations-open-and-closed-tickets#sthash.YPwKwygF.dpuf

Hello,

I've been working on a code trying to get it to display into columns view and not the row type. So I've added two codes that I've joined together using the UNION ALL but I would love to get the second part of the code into a column view instead of a row view. Code is attached below. Any help would be much appreciated:

- See more at: http://www.itninja.com/question/sql-report-showing-new-hires-terminations-open-and-closed-tickets#sthash.YPwKwygF.dpuf

Hello,

I've been working on a code trying to get it to display into columns view and not the row type. So I've added two codes that I've joined together using the UNION ALL but I would love to get the second part of the code into a column view instead of a row view. Code is attached below. Any help would be much appreciated:

- See more at: http://www.itninja.com/question/sql-report-showing-new-hires-terminations-open-and-closed-tickets#sthash.YPwKwygF.dpuf

Hello,

I've been working on a code trying to get it to display into columns view and not the row type. So I've added two codes that I've joined together using the UNION ALL but I would love to get the second part of the code into a column view instead of a row view. Code is attached below. Any help would be much appreciated:

- See more at: http://www.itninja.com/question/sql-report-showing-new-hires-terminations-open-and-closed-tickets#sthash.YPwKwygF.dpuf
 
 

Hello,

I've been working on a code trying to get it to display into columns view and not the row type. So I've added two codes that I've joined together using the UNION ALL but I would love to get the second part of the code into a column view instead of a row view. Code is attached below. Any help would be much appreciated:

- See more at: http://www.itninja.com/question/sql-report-showing-new-hires-terminations-open-and-closed-tickets#sthash.YPwKwygF.dpuf
0 Comments   [ + ] Show comments

Comments


Be the first to answer this question

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ