SQL query to calculate running group counts on time-phased data
Posted
by spong
on Stack Overflow
See other posts from Stack Overflow
or by spong
Published on 2010-04-14T06:47:07Z
Indexed on
2010/04/14
6:53 UTC
Read the original article
Hit count: 226
sql
I have some data, like this:
BUG DATE STATUS
---- ---------------------- --------
9012 18/03/2008 9:08:44 AM OPEN
9012 18/03/2008 9:10:03 AM OPEN
9012 28/03/2008 4:55:03 PM RESOLVED
9012 28/03/2008 5:25:00 PM CLOSED
9013 18/03/2008 9:12:59 AM OPEN
9013 18/03/2008 9:15:06 AM RESOLVED
9013 18/03/2008 9:16:44 AM CLOSED
9014 18/03/2008 9:17:54 AM OPEN
9014 18/03/2008 9:18:31 AM RESOLVED
9014 18/03/2008 9:19:30 AM CLOSED
9015 18/03/2008 9:22:40 AM OPEN
9015 18/03/2008 9:23:03 AM RESOLVED
9015 19/03/2008 12:27:08 PM CLOSED
9016 18/03/2008 9:24:20 AM OPEN
9016 18/03/2008 9:24:35 AM RESOLVED
9016 19/03/2008 12:28:14 PM CLOSED
9017 18/03/2008 9:25:47 AM OPEN
9017 18/03/2008 9:26:02 AM RESOLVED
9017 19/03/2008 12:30:30 PM CLOSED
Which I would like to transform into something like this:
DATE OPEN RESOLVED CLOSED
---------------------- -------- -------- --------
18/03/2008 9:08:44 AM 1 0 0
18/03/2008 9:12:59 AM 2 0 0
18/03/2008 9:15:06 AM 1 1 0
18/03/2008 9:16:44 AM 1 0 1
18/03/2008 9:17:54 AM 2 0 1
18/03/2008 9:18:31 AM 1 1 0
18/03/2008 9:19:30 AM 1 0 2
18/03/2008 9:22:40 AM 2 0 2
18/03/2008 9:23:03 AM 1 1 2
18/03/2008 9:24:20 AM 2 1 2
18/03/2008 9:24:35 AM 1 2 2
18/03/2008 9:25:47 AM 2 2 2
18/03/2008 9:26:02 AM 1 3 2
19/03/2008 12:27:08 PM 1 2 3
19/03/2008 12:28:14 PM 1 1 4
19/03/2008 12:30:30 PM 1 0 5
28/03/2008 4:55:03 PM 0 1 5
28/03/2008 5:25:00 PM 0 0 6
i.e. keeping running counts of bugs with each status.
This is easy enough to code up using cursors, but I'm wondering if any of you SQL gurus out there can help with a query to achieve this?
Ideally for mysql, but I'm curious to see anything that will work.
© Stack Overflow or respective owner
