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
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

There are no answers at this time
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

Answer this question or Comment on this question for clarity