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:

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 )

So the second part of the code after the UNION ALL I would love to be able to put that code to the right hand side into a column. Can anyone help me out with this?

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share