07/12/2016 1016 views
I'm really really bad in SQL... I would like to add to the following report a way to store every month the time worked on ticket by owners:
SELECT FULL_NAME,(SUM(ADJUSTMENT_HOURS*60) + SUM(TIMESTAMPDIFF(MINUTE,HD_WORK.START,HD_WORK.STOP))) AS total
LEFT JOIN HD_CATEGORY ON HD_CATEGORY_ID = HD_CATEGORY.ID
LEFT JOIN HD_WORK ON HD_TICKET.ID = HD_WORK.HD_TICKET_ID
LEFT JOIN USER ON USER.ID = HD_TICKET.OWNER_ID
LEFT JOIN HD_STATUS ON HD_STATUS_ID = HD_STATUS.ID
WHERE HD_STATUS.STATE = 'closed' AND HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
GROUP BY FULL_NAME
Actually, this report work fine, i have for all closed ticket the time worked by owner for 31 days.
But i would like to store the data for each month or each week in the same report. In this way, i will be able to compare each month without saving each report every 31 days.
Besides, i actually need to execute the report only when i 'm at the end of the month. For exemple, if i am the 28 of june, i have to change the SQL code to put 28 and no 31 days to get the report of june.
If i'm not clear please don't hesitate to question me.
Answer Chosen by the Author
Please log in to answer
Thank you very much Gray !
Your answers are fast, clear and they work, great job !
Answered 07/12/2016 by: Olendis