query for counting bugs

dani shamir 81 Reputation points

A bug tracking system has a table of all opened bugs:
if a bug is not closed yet - the closed date is null

bug_id opened_date (dd/mm/yyyy) closed_date (dd/mm/yyyy)

1 01/01/2015 04/01/2015
2 02/01/2015 null
3 03/01/2015 04/01/2015

what sql can I write that will create the following report:

date (dd/mm/yyyy) number of opened bugs

01/01/2015 1
02/01/2015 2
03/01/2015 3
04/01/2015 1


A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,517 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHong-MSFT 10,036 Reputation points

    Hi @dani shamir
    Suppose you have a calendar table say #Date, then you can try the query below:

    CREATE TABLE #Bug(bug_id int,opened_date DATE,closed_date DATE)  
    INSERT INTO #Bug VALUES(1,'01/01/2015','1/04/2015'),(2 ,'01/02/2015', null),(3 ,'01/03/2015', '1/4/2015')  
    SELECT D.TheDate,COUNT(B.bug_id) AS Num_of_OpenedBugs  
    FROM #Date D JOIN #Bug B ON D.TheDate>=B.opened_date AND D.TheDate<(CASE WHEN B.closed_date IS NULL THEN GETDATE() ELSE B.closed_date END)  
    GROUP BY TheDate  

    Best regards,

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Viorel 109.9K Reputation points

    Check a similar problem. I think that it can be adjusted for your case.

    set dateformat dmy  
    declare @homeworks table (id int, start_date date, end_date date)  
    insert @homeworks values  
    ( 1, '01/01/2015', '04/01/2015' ),  
    ( 2, '02/01/2015', null         ),  
    ( 3, '03/01/2015', '04/01/2015' )  
    select * from @homeworks  
    with C as  
        select min(start_date) as start_date, max(end_date) as end_date  
        from @homeworks  
    L as  
        select start_date as [date], end_date  
        from C  
        union all  
        select dateadd(day, 1, [date]), end_date  
        from L  
        where [date] < end_date  
    select [date],   
      (select count(*) from @homeworks where [start_date] <= L.[date]  and (end_date is null or end_date > L.[date])) as [number of unfinished works]  
    from L  
    0 comments No comments