query for counting bugs

dani shamir 81 Reputation points
2022-06-30T07:04:21.363+00:00

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

thanks

No comments
{count} votes

Accepted answer
  1. LiHong-MSFT 9,986 Reputation points
    2022-06-30T07:54:58.837+00:00

    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)  
    --WHERE D.TheDate BETWEEN @STARTDATE AND @ENDDATE  
    GROUP BY TheDate  
    

    Best regards,
    LiHong


    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.

    No comments

1 additional answer

Sort by: Most helpful
  1. Viorel 82,906 Reputation points
    2022-06-30T07:48:50.273+00:00

    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  
    
    No comments