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.
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
Thank you very much Gray ! 

Your answers are fast, clear and they work, great job ! 
Answered 07/12/2016 by: Olendis
Orange Belt

Please log in to comment

Answers

1

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


Answered 07/12/2016 by: grayematter
Fourth Degree Black Belt

  • 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...
    • Replace

      MONTH(HD_TICKET.TIME_CLOSED) AS Closed_Month,

      with

      MONTHNAME(HD_TICKET.TIME_CLOSED) AS Closed_Month,
      • 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.
      • 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.
Please log in to comment
Answer this question or Comment on this question for clarity