Grouping types of bugs by date and priority

Question

I currently have a table Bugs that looks like this:

    |ID      |Priority  |Created   |Updated   |Status    |Category
    |X123    |Major     |01/01     |01/03     |Open      |A
    |X145    |Normal    |01/01     |01/02     |Closed    |B
    |X678    |Minor     |01/03     |01/03     |Open      |C
    |X763    |Major     |01/02     |01/03     |Closed    |C

All columns are varchar(25) except Created and Updated, which are dates.

I need to create a view with the following format:

    |Date      |Major    |Normal    |Minor     |Category
    |01/01     |4        |3         |4         |A
    |01/01     |3        |5         |2         |B
    |01/01     |2        |4         |7         |C    
    |01/02     |7        |3         |4         |A
    |01/02     |3        |9         |5         |B
    |01/02     |1        |6         |3         |C

Where the numbers under Major, Normal, and Minor are the count of CURRENTLY OPEN bugs of that priority on a given date. By currently open, I mean this: open bugs are active on the interval Created-GETDATE(), closed bugs are active on the interval Created-Updated.

I have a list of all the dates I need through this query:

    WITH D AS
     (
      SELECT Dates AS DateValue
      FROM DatesTable
      WHERE Dates >= '2012-03-23'
        AND Dates <= GETDATE()
     ),

Any ideas of how I might do this? I've played with the idea of a pivot query and grouping, but I've not been able to cover everything I need. Your help is greatly appreciated!

---

EDIT: The Bugs table with example data

    CREATE TABLE [dbo].[Bugs](
            [ID] [varchar](25) NOT NULL,
            [Priority] [varchar](25) NOT NULL,
            [Updated] [date] NOT NULL,
            [Created] [date] NOT NULL,
            [Status] [varchar](25) NOT NULL,
            [Category] [varchar](25) NOT NULL,
            ) ON [PRIMARY]

    INSERT INTO Bugs VALUES (X123, Major, 01/01/12, 01/03/12, Open, A)
    INSERT INTO Bugs VALUES (X145, Normal, 01/01/12, 01/02/12, Closed, B)
    INSERT INTO Bugs VALUES (X678, Minor, 01/03/12, 01/03/12, Open, C)
    INSERT INTO Bugs VALUES (X763, Major, 01/02/12, 01/03/12, Closed, C )

Answer 1

Something like this might what you are looking for.

select b.Date
    , SUM(case when b.Priority = 'Major' then 1 end) as Major
    , SUM(case when b.Priority = 'Normal' then 1 end) as Normal
    , SUM(case when b.Priority = 'Minor' then 1 end) as Minor
    , b.Category
from Bugs b
group by b.Date, b.Category
order by b.Date

Answer 2

Rather than using a cte, I have used the query as the bases for what you need. by joining the datestable with your bugs table, we can compare the dates in the bugs table to see if the bug was open on the given date. We know that d.dates must be greater than the created date of the bug. We also need to check the bug status. If the bug is open, then we only want to include the bug up til the updated date. Otherwise, the bug needs to be included up to today since it is still open. The rest is essentially manually pivoting the data to get the values you need.

SELECT d.Dates, sum(case when b.priority = major then 1 else 0 end) as major,
        sum(case when b.priority = normal then 1 else 0 end) as normal,
        sum(case when b.priority = minor then 1 else 0 end) as minor,
        b.category
FROM DatesTable d
    join bugs b
    on d.dates >= b.Created 
    and case when b.status = 'closed' then b.updated else '12-31-9999' end <= d.dates
WHERE d.Dates >= '2012-03-23'
AND d.Dates <= GETDATE()
Group by d.dates,b.category

Answer 3

This is what I tested at last:

SELECT d.Dates as Date, 
    SUM(CASE Priority WHEN 'Major' THEN 1 ELSE 0 END) as Major, 
    SUM(CASE Priority WHEN 'Normal' THEN 1 ELSE 0 END) as Normal, 
    SUM(CASE Priority WHEN 'Minor' THEN 1 ELSE 0 END) as Minor,
    b.Category
    FROM Bugs b
INNER JOIN DatesTable d ON d.Dates >= b.Created 
WHERE (Status = 'Closed' AND d.Dates <= Updated OR Status = 'Open')
    AND d.Dates <= GETDATE() AND d.Dates >= '2012-01-01'
GROUP BY d.Dates, b.Category
ORDER BY d.Dates

Answer 4

You can use the following query

SELECT DatesTable.Dates
    , SUM(CASE WHEN bugs.Priority = 'Major' THEN 1 ELSE 0 END) AS Major
    , SUM(CASE WHEN bugs.Priority = 'Normal' THEN 1 ELSE 0 END) AS Normal
    , SUM(CASE WHEN bugs.Priority = 'Minor' THEN 1 ELSE 0 END) AS Minor
    , bugs.Category
FROM DatesTable 
LEFT JOIN Bugs ON DatesTable.Dates = Bugs.Date
    -- You may need to cast this, if the data type do not match
    AND Bugs.Status='Open'

WHERE Dates >= '2012-03-23'
      AND Dates <= GETDATE()   
GROUP BY DatesTable.Dates, bugs.Category
ORDER BY DatesTable.Dates

Answer 5

We can use a nested query to get the data we want to work on, and the parent query to display the end result:

 SELECT CreatedDate AS [Date],
        SUM(Major) AS Major,
        SUM(Normal) AS Normal,
        SUM(Minor) AS Minor,
        Category
   FROM (
         SELECT DATEADD(dd, DATEDIFF(dd, 0, Created), 0) AS CreatedDate,
                CASE WHEN Priority = 'Major' THEN 1 ELSE 0 END AS Major,
                CASE WHEN Priority = 'Normal' THEN 1 ELSE 0 END AS Normal,
                CASE WHEN Priority = 'Minor' THEN 1 ELSE 0 END AS Minor,
                Category
           FROM Bugs
          WHERE Status = 'Open'
        ) AS t
  GROUP BY CreatedDate, Category
  ORDER BY CreatedDate, Category

(Credit for removal of time from a DATETIME goes to this SO question)

The nested query makes it easier to see what data is being operated on, and filters said data as needed.

I've assumed the Date is a DATETIME, so I've removed the time part for the grouping.

See an example in this SQL Fiddle (I only added enough data for the first two entries to match the expected results).