View a MySQL and Group By Date Range database to create a graph


I'm looking to create the following chart from a MySQL database. I know how to actually create the chart (using excel or similar program), my problem is how to get the data needed to create the chart. In this example, I can see that on January 1, 60 tickets were in the state illustrated by the green line.

I need to track the historical state of tickets of a project through a date range. The date range is determined by a project manager (in this case it's January 1st through January 9th).

For each ticket, I have the following set of historical data. Each time something changes in the ticket (state, description, assignee, customer update, and other attributes not shown in this problem), a "timestamp" entry is made in the database.

ticket_num      status_changed_date     from_state  to_state
123456          2011-01-01 18:03:44     --          1
123456          2011-01-01 18:10:26     1           2
123456          2011-01-01 14:37:10     2           2
123456          2011-01-02 07:55:44     2           3
123456          2011-01-03 06:12:18     3           2
123456          2011-01-04 19:03:43     3           3
123456          2011-01-05 02:05:24     3           4
123456          2011-01-06 18:13:28     4           4
123456          2011-01-07 13:14:48     4           5
123456          2011-01-09 01:35:39     5           5

How can I query the database for a given time (determined by my script) and find out what state each of the tickets are in?

For example: To produce the chart shown above, given the date 2011-01-02 12:00:00, how many tickets were in the state "2"?

I've tried querying the database with specific dates and ranges, but can't figure out the proper way to get the data to create the chart. Thanks in advance for any help.

Ok so if you are trying to get a count of records in a certain state at a certain time, I think a stored proc might be necessary.

    @Date datetime,
    @StateId int

   SELECT COUNT(*) as Count
   FROM ticket_table t1
   WHERE to_state = @StateId AND status_changed_date < @Date
        AND status_changed_date = (SELECT MAX(status_changed_date) FROM ticket_table t2 where t2.ticket_num=t1.ticket_num AND status_changed_date < @Date)

then to call this for the above example, you're query would look like

EXEC spStatesAtDate @Date='2011-01-02 12:00:00', @StateId=2