SQL Statement to Track the Number of Defects Opened Each Day By Application

I use Quality Center to track defects, I believe it uses MSSQL language. I have a table that has the following fields: defectID, summary, application, severity, 开发者_开发技巧status, createDate, closedDate. Will just cover the relevant fields.

defectID application severity createDate closedDate
-------- ----------- -------- ---------- ---------
1        app1        sev1     3/10/2010  3/23/2010
2        app1        sev1     3/15/2010  3/23/2010
3        app2        sev1     3/14/2010  3/25/2010
4        app1        sev2     3/18/2010  3/24/2010
5        app1        sev1     3/15/2010  3/19/2010
6        app1        sev1     3/25/2010

I need the SQL to output the number of sev1 defects that were open on a given date. I would like to supply the date range. Here is the output sample? Some where in the SQL I will identify the start date of 3/22 and an end date of 3/26

application 3/22 3/23 3/24 3/25 3/26
---------------------------------------
app1        3    1    0    1    1
app2        1    1    1    0    0

If the app as to be horizontal (list going across) and date has to be vertical (list going down) that is fine. Any help is appreciated.

Use:

  SELECT t.application,
         SUM(CASE WHEN '3/22/2010' BETWEEN t.createdate AND t.closedate THEN 1 ELSE 0 END) AS "3/22",
         SUM(CASE WHEN '3/23/2010' BETWEEN t.createdate AND t.closedate THEN 1 ELSE 0 END) AS "3/23",
         SUM(CASE WHEN '3/24/2010' BETWEEN t.createdate AND t.closedate THEN 1 ELSE 0 END) AS "3/24"
    FROM TABLE t
GROUP BY t.application

I didn't do them all, but there's enough of an example. Dynamic date ranges require the use of dynamic SQL.

A lazy afternoon produced chaos that you can possibly use... Its a little dirty, but it seems to work :D

This is mostly a modified version of the answer above with case statements, just using the pivot command. Note this will only work in Sql Server 2005 and above. If you have 2000, then you will have to use the case method above in a dynamic way.

declare @startDate datetime
declare @EndDate datetime
declare @Dates varchar(max)
declare @nums varchar(max)
select @startdate = '3/22/10', @endDate = '3/26/10', @Dates = '', @nums = ''

;with nums as (
    select row_number() over (order by object_id)-1 as num from sys.objects        
)
select @dates = '' + '[' + convert(varchar(8),num) + '] as ' + '''' + cast(Dateadd(day,num,@StartDate) as varchar(11)) + ''',' + @dates
,@nums = '[' + convert(varchar(8),num) + '],' + @nums
    from nums
    where num between 1 and DateDiff(day,@StartDatE,@EndDate)
    order by num desc

select @dates = substring(@dates,0,len(@dates)),  @nums = substring(@nums,0,len(@nums))

declare @qry varchar(max)
set @qry = ';with nums as (
    select row_number() over (order by object_id)-1 as num from sys.objects        
)
select AppName, ' + @dates + ' from (select [Application],[Application] as AppName, num as cnt from ##temp
    cross join nums 
        where num between 1 and ' + convert(varchar(8),DateDiff(day,@StartDatE,@EndDate)) + '+1
        and Dateadd(day,num,''' + cast(@StartDate as varchar(11)) +''') between CreateDate and closeddate
        ) as p
    pivot (count([Application]) for [cnt] in (' +@nums + ')) as pvt'

exec (@qry)

You'll have to make it a table yourself, but

SELECT createDate, application, COUNT(*) FROM Defects GROUP BY createDate, application

should give you the data. If you want the data to come directly from your RDBMS exactly as you've drawn it you'll have to dynamically create the query like the other answer suggests.