I want to create a report for the # of tickets created each week for a date range. I have a query to get a count for one date range, but I'm wondering if I could have a date range broken down by week. So, the date range might be a month and I want it to give me the # for each week in that date range. This is a bit beyond my SQL skill level. Here's my query for simple count for a date range:

select COUNT(HD_TICKET.CREATED)
from HD_TICKET
Where HD_TICKET.CREATED between '2013-07-28' AND '2013-08-03'

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

This shows the week of the year and the number of tickets:

 select week(HD_TICKET.CREATED), COUNT(HD_TICKET.CREATED)
from HD_TICKET
Where HD_TICKET.CREATED between '2013-06-28' AND '2013-08-03'
GROUP BY week(HD_TICKET.CREATED)

This is a little nicer format:

 select DATE(DATE_ADD(MAKEDATE(YEAR(CREATED), 1), INTERVAL WEEK(CREATED) WEEK) -2) as 'Week beginning', COUNT(HD_TICKET.CREATED)
from HD_TICKET
Where HD_TICKET.CREATED between '2013-07-07' AND '2013-08-03'
GROUP BY week(HD_TICKET.CREATED)

 

Answered 08/26/2013 by: chucksteel
Red Belt

  • Awesome! That second one is purty! Thanks a lot!
  • I like the report and was wondering what modifications would I need to make something similar, but breaking down based on day for a month.?
    • So you want a report of the number of tickets opened per day in a particular month?
    • Here is the count of tickets opened per day in the previous month:
      SELECT DATE(HD_TICKET.CREATED) as "Day",
      COUNT(HD_TICKET.ID) as "Tickets Opened"

      FROM HD_TICKET
      JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
      LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
      WHERE (HD_TICKET.HD_QUEUE_ID = 2)
      AND MONTH(HD_TICKET.CREATED) = MONTH(DATE_SUB(NOW(),INTERVAL 1 MONTH))
      AND YEAR(HD_TICKET.CREATED) = YEAR(DATE_SUB(NOW(),INTERVAL 1 MONTH))
      GROUP BY DAY(HD_TICKET.CREATED)

      For a specific month:
      SELECT DATE(HD_TICKET.CREATED) as "Day",
      COUNT(HD_TICKET.ID) as "Tickets Opened"

      FROM HD_TICKET
      JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
      LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
      WHERE (HD_TICKET.HD_QUEUE_ID = 2)
      AND MONTH(HD_TICKET.CREATED) = 11
      AND YEAR(HD_TICKET.CREATED) = 2016
      GROUP BY DAY(HD_TICKET.CREATED)
      • Thank you for your help. This is exactly what we are looking to accomplish.
Please log in to comment
Answer this question or Comment on this question for clarity