Tickets by owner by day/month/year
Hi
I've been using this great script which outputs tickets opened and closed per owner by month in the format below i.e.
# OWNER MONTH YEAR OPENED CLOSED
1 bob 4 2011 10 20
2 sarah 4 2011 22 3
3 bob 5 2011 1 4
4 sarah 5 2011 8 6
I've tried to include the Day in addition to Month and Year, so I can see the number of tickets opened/closed by engineer by DAY as well but it doesnt show every day of the month for each engineer.
SELECT OPEN.OWNER,
OPEN.MONTH,
OPEN.YEAR,
Coalesce(OPEN.OPEN, 0) AS OPENED,
Coalesce(CLOSED.CLOSED, 0) AS CLOSED
FROM (SELECT Coalesce(U.FULL_NAME, 'NO OWNER ASSIGNED') AS OWNER,
MONTH(T.CREATED) AS MONTH,
YEAR (T.CREATED) AS YEAR,
COUNT(*) AS OPEN
FROM HD_TICKET T
LEFT JOIN USER U
ON T.OWNER_ID = U.ID
GROUP BY OWNER_ID,
MONTH,
YEAR
ORDER BY YEAR,
MONTH) OPEN
LEFT JOIN (SELECT Coalesce(U.FULL_NAME, 'NO OWNER ASSIGNED') AS OWNER,
MONTH(T.TIME_CLOSED) AS MONTH,
YEAR (T.TIME_CLOSED) AS YEAR,
COUNT(*) AS CLOSED
FROM HD_TICKET T
LEFT JOIN USER U
ON T.OWNER_ID = U.ID
GROUP BY OWNER_ID,
MONTH,
YEAR
ORDER BY YEAR,
MONTH) CLOSED
ON( OPEN.MONTH = CLOSED.MONTH
AND OPEN.YEAR = CLOSED.YEAR
AND OPEN.OWNER = CLOSED.OWNER )
ORDER BY YEAR,
MONTH,
OWNER
I've been using this great script which outputs tickets opened and closed per owner by month in the format below i.e.
1 bob 4 2011 10 20
2 sarah 4 2011 22 3
3 bob 5 2011 1 4
4 sarah 5 2011 8 6
I've tried to include the Day in addition to Month and Year, so I can see the number of tickets opened/closed by engineer by DAY as well but it doesnt show every day of the month for each engineer.
OPEN.MONTH,
OPEN.YEAR,
Coalesce(OPEN.OPEN, 0) AS OPENED,
Coalesce(CLOSED.CLOSED, 0) AS CLOSED
FROM (SELECT Coalesce(U.FULL_NAME, 'NO OWNER ASSIGNED') AS OWNER,
MONTH(T.CREATED) AS MONTH,
YEAR (T.CREATED) AS YEAR,
COUNT(*) AS OPEN
FROM HD_TICKET T
LEFT JOIN USER U
ON T.OWNER_ID = U.ID
GROUP BY OWNER_ID,
MONTH,
YEAR
ORDER BY YEAR,
MONTH) OPEN
LEFT JOIN (SELECT Coalesce(U.FULL_NAME, 'NO OWNER ASSIGNED') AS OWNER,
MONTH(T.TIME_CLOSED) AS MONTH,
YEAR (T.TIME_CLOSED) AS YEAR,
COUNT(*) AS CLOSED
FROM HD_TICKET T
LEFT JOIN USER U
ON T.OWNER_ID = U.ID
GROUP BY OWNER_ID,
MONTH,
YEAR
ORDER BY YEAR,
MONTH) CLOSED
ON( OPEN.MONTH = CLOSED.MONTH
AND OPEN.YEAR = CLOSED.YEAR
AND OPEN.OWNER = CLOSED.OWNER )
ORDER BY YEAR,
MONTH,
OWNER
0 Comments
[ + ] Show comments
Answers (0)
Please log in to answer
Be the first to answer this question
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
