mysql根据年度图表,查询MySQL数据库并按日期范围分组以创建图表

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.

CREATE PROCEDURE spStatesAtDate

@Date datetime,

@StateId int

AS

BEGIN

SET NOCOUNT ON;

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)

END

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