Whit ONE select

[bborissov]

've been asked it is possible to do this with ONE SELECT. First I answered NO, but here is about 2 PM and my mind doesn't works properly, so ask you
Here the conditions:

The database is a bug tracking database.
Table has several columns: bugID, open_date, close_date
A bug is considered open on a given day if it's open date is on or before that day, and it's close date is on or after that day or it is NULL.
So the goal is to generate a record set to show the number of open bugs for a range of dates.
The ideal result set would have two columns: date and open bug count on that date.
Some data:

(all dates here are in German Format - dd.mm.yyyy)

BugId   Open_Date       Close_Date
 1      01.01.2006      02.01.2006	
 3      21.01.2006      NULL
 4      05.01.2006      10.02.2006	
 5      01.12.2005      NULL
 7      13.12.2005      13.02.2006
 8      12.02.2006      NULL
12      10.02.2006      10.02.2006
14      07.04.2006      NULL
20      18.11.2005      NULL
21      10.05.2006      NULL
22      15.02.2006      NULL
23      01.10.2004      03.02.2006	
24      31.12.2005      31.12.2005	
27      09.01.2005      09.01.2006	
32      01.01.2006      NULL
45      13.02.2006      NULL
55      12.12.2005      12.12.2005
61      31.12.2005      NULL
88      09.01.2006      09.01.2006
99      13.03.2006      NULL

So if the period is 01.04.2006 - 15.04.2006 desired result is:

Date                          BugsCount 
2006-04-01 00:00:00.000	        9
2006-04-02 00:00:00.000	        9
2006-04-03 00:00:00.000	        9
2006-04-04 00:00:00.000	        9
2006-04-05 00:00:00.000	        9
2006-04-06 00:00:00.000	        9
2006-04-07 00:00:00.000	       10
2006-04-08 00:00:00.000	       10
2006-04-09 00:00:00.000	       10
2006-04-10 00:00:00.000	       10
2006-04-11 00:00:00.000	       10
2006-04-12 00:00:00.000	       10
2006-04-13 00:00:00.000	       10
2006-04-14 00:00:00.000	       10
2006-04-15 00:00:00.000	       10

Thanks in advance

Borislav Borissov

 

[Jamfool]

thought id have a stab at it...tis quite late in the day tho

drop table BugTrackingTable
drop table #date_range
go
set dateformat dmy


CREATE TABLE BugTrackingTable
(
BugId   int,
Open_Date datetime,       
Close_Date datetime,
)
go

insert into BugTrackingTable values (1,'01/01/2006','02/01/2006')
insert into BugTrackingTable values (3,'21/01/2006','')
insert into BugTrackingTable values (4,'05/01/2006','10/02/2006')
insert into BugTrackingTable values (5,'01/12/2005',NULL)
insert into BugTrackingTable values (7,'13/12/2005','13/02/2006')
insert into BugTrackingTable values (8,'12/02/2006',NULL)
insert into BugTrackingTable values (12,'10/02/2006','10/02/2006')
insert into BugTrackingTable values (14,'07/04/2006',NULL)
insert into BugTrackingTable values (20,'18/11/2005',NULL)
insert into BugTrackingTable values (21,'10/05/2006',NULL)
insert into BugTrackingTable values (22,'15/02/2006',NULL)
insert into BugTrackingTable values (23,'01/10/2004','03/02/2006')
insert into BugTrackingTable values (24,'31/12/2005','31/12/2005')
insert into BugTrackingTable values (27,'09/01/2005','09/01/2006')
insert into BugTrackingTable values (32,'01/01/2006',NULL)
insert into BugTrackingTable values (45,'13/02/2006',NULL)
insert into BugTrackingTable values (55,'12/12/2005','12/12/2005')
insert into BugTrackingTable values (61,'31/12/2005',NULL)
insert into BugTrackingTable values (88,'09/01/2006','09/01/2006')
insert into BugTrackingTable values (99,'13/03/2006',NULL)
go
set dateformat dmy
DECLARE @BeginDate datetime,@EndDate datetime,@startdate datetime

SET @BeginDate = '04/01/2006'
SET @EndDate = '15/1/2006'


create table #date_range
( 
    mydates datetime
)
	SET @startdate = cast(@BeginDate as datetime)
	while @startdate <= cast(@EndDate as datetime)
	begin
	  insert into #date_range values (@startdate)	
	  set @startdate = @startdate + 1 
	end

SELECT     
#date_range.mydates, 
SUM(CASE WHEN Open_Date <= @EndDate AND Close_Date >= @BeginDate THEN 1 ELSE 0 END) AS BugCount
FROM         #date_range LEFT OUTER JOIN
BugTrackingTable ON #date_range.mydates = BugTrackingTable.Open_Date
group by #date_range.mydates,Open_Date
order by #date_range.mydates

 

[bborissov]

Thank Jam,
but creating anything is not allowed
Here it is the answer I receive from member of another forum:

DECLARE @dBegin datetime, @dEnd datetime
SET @dBegin = '20050401'
SET @dEnd   = '20070414'

-- Single select statement here
select r.range_date, 
       (SELECT COUNT(*) FROM Bugs WHERE Open_Date <= r.Range_Date AND
               (Close_Date >= r.Range_Date OR Close_Date IS NULL))
from  bugs b
right outer join (
select @dBegin + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 range_date
from	   (select 0 b1  union select 1    b1)  t1
cross join (select 0 b2  union select 2    b2)  t2
cross join (select 0 b3  union select 4    b3)  t3
cross join (select 0 b4  union select 8    b4)  t4
cross join (select 0 b5  union select 16   b5)  t5
cross join (select 0 b6  union select 32   b6)  t6
cross join (select 0 b7  union select 64   b7)  t7
cross join (select 0 b8  union select 128  b8)  t8
cross join (select 0 b9  union select 256  b9)  t9
cross join (select 0 b10 union select 512  b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd) r
on b.open_date = r.range_date
order by r.range_date

Borislav Borissov