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