I have a project where I'm doing some analysis on bugs for an R&D group. What they would like is to see how many bugs they have discovered over time and those that have been resolved as well as those that are still open.
The data has a number of dimensional items surrounding it such as priority, release version, etc.
A bug can have one of seven states: NEW, ASSIGNED, REOPENED, FIXED, VERIFIED, CLOSED or RESOLVED.
The first three states are considered bugs in the first three states are considered open whereas the other four are considered closed. As there is a REOPENED state, closed bugs can be reopened if the bug resurfaces.
They want to look at the data on a week-by-week basis. During a given week an individual bug may have more than one state. (i.e., New, then Assigned, then Fixed).
They would like to see a cumulative listing of total bugs, open and closed.
Imagine the following scenario
Week 1
bug 1 - Open
Bug 2 - Closed
Bug 3 - Closed
Bug 4 - Open
Week 2
Bug 5 - Open
Bug 4 - Closed (a bug from the prior week)
Bug 6 - Closed
Bug 7 - Open
Bug 8 - Open
They would like to see:
Week 1, 4 bugs, 2 open, 2 closed
Week 2, 8 bugs, 4 open, 4 closed
I have about two years worth of data and can do a count(distinct Total bug_id) to get the total number of bugs but how do I get a cumulative number of bugs based on the end-of-week date? It has to count all bugs prior to and including that date and determine how many are open vs. closed. I can also determine the number of total closed bugs by adding in the condition on my experession for the bug state but cannot determine how to do the cumulative calculation.
Can someone help me?
Using Personal Edition while waiting for licensing to come through so I can't open any example files. Sorry.
What I ended up doing (if I recall correctly) have a DBA create a table which was populated with an EndOfWeek and even if a bug had no action that week, the prior week data was reflected so that each bug's final status for a week was shown in the table. The table also had the various dimensions required and grouped the various status' into Open and Close fields so that if a bug was open the isOpen field showed 1 (else 0) and similar for the isClose field. Then simply pull the data in by the various dimensions and in the query, grouping the data by the end of week and dimensions and doing a Sum(isOpen) and a Sum(isClosed) so that provided me the numbers I needed.
