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
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share