Report SQL
Hi there,
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
FROM HD_TICKET
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.
Thank you.
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
grayematter
8 years ago
This should give you what you need. It gives each user a row for each month in the last year. You can adjust the interval to other years or months as appropriate, such as "INTERVAL 6 MONTHS" to pull the last 6 months.
SELECT
FULL_NAME,
YEAR(HD_TICKET.TIME_CLOSED) AS Closed_Year,
MONTH(HD_TICKET.TIME_CLOSED) AS Closed_Month,
(SUM(ADJUSTMENT_HOURS * 60) + SUM(TIMESTAMPDIFF(MINUTE,
HD_WORK.START,
HD_WORK.STOP))) AS total
FROM
HD_TICKET
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 1 YEAR)
GROUP BY FULL_NAME , Closed_Year , Closed_Month
Comments:
-
Thank you very much gray ! This work perfectly. May i ask you how do i add a CASE in this SQL code so i can see january, february, march... like january is number 1, february is number 2... - Olendis 8 years ago
-
Replace
MONTH(HD_TICKET.TIME_CLOSED) AS Closed_Month,
with
MONTHNAME(HD_TICKET.TIME_CLOSED) AS Closed_Month, - grayematter 8 years ago-
Ok, didn't know this variable...
And last question if i may, if for some reason i would like to add a 0 before the month which have only one number ? This for the 9 first month. - Olendis 8 years ago -
LPAD(MONTH(HD_TICKET.TIME_CLOSED),2,'0') AS Closed_Month,
The caveat with padding a '0' here is that the number is converted to a string. So arithmetic and sorting may be off. - grayematter 8 years ago
Posted by:
Olendis
8 years ago
Top Answer
Thank you very much Gray !
Your answers are fast, clear and they work, great job !