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.