Manager needs another report,

Right now I have this it displays a report that looks like this:

 

SELECT U.FULL_NAME AS SUBMITTER, COUNT(T.ID) AS TOTAL,

GROUP_CONCAT(CAST(T.ID AS CHAR) ORDER BY 1 SEPARATOR ', ') AS TICKETS

FROM HD_TICKET T

JOIN USER U ON (U.ID = T.OWNER_ID)

JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)

WHERE NOW() > DATE_ADD(T.CREATED,INTERVAL 3 DAY)

AND DAYOFWEEK(T.CREATED) NOT IN (1,7)

AND S.STATE = 'opened'

GROUP BY U.FULL_NAME

ORDER BY U.FULL_NAME

 

He wants same kind of data but in this format:

Techname0 had 4 of 10 total tickets in the past 7 days that violated an OLA limit.

Techname1 had 5 of 25 total tickets in the past 7 days that violated an OLA limit.

Techname2 had 0 of 10 total tickets in the past 7 days that violated an OLA limit.

Techname3 had 1 of 10 total tickets in the past 7 days that violated an OLA limit.


OLA limit is 3 days, but do not count Saturday and Sunday.


HELP PLEASE!

 

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

I think your WHERE statement is incorrect

you have

WHERE NOW() > DATE_ADD(T.CREATED,INTERVAL 3 DAY)

try

WHERE T.CREATED >DATE_ADD(NOW(), INTERVAL 3 DAY)

Answered 04/03/2013 by: nshah
Red Belt

  • Thank you for your reply. I'm looking for a way to display the data in the format example.
Please log in to comment
Answer this question or Comment on this question for clarity