/build/static/layout/Breadcrumb_cap_w.png

K1000 SQL Report showing new hires, terminations, open and closed tickets

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

Answers (0)

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
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