/build/static/layout/Breadcrumb_cap_w.png

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:
Posted by: grayematter 7 years ago
5th Degree Black Belt
2

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 7 years ago
    • Replace

      MONTH(HD_TICKET.TIME_CLOSED) AS Closed_Month,

      with

      MONTHNAME(HD_TICKET.TIME_CLOSED) AS Closed_Month, - grayematter 7 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 7 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 7 years ago
Posted by: Olendis 7 years ago
Orange Belt
0

Top Answer

Thank you very much Gray ! 

Your answers are fast, clear and they work, great job ! 
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ