query for counting bugs

Question

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

Answer 1

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.

Answer 2

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