Complex Select Statement

[oshlonger]

I'm trying come up with a select statement to help report on the history of a record.

I have a table like the following:

Record Start End
123 2/26/2006 3/10/2006
123 3/15/2006 3/16/2006
145 3/04/2006 3/07/2006
154 3/20/2006 3/25/2006
154 3/27/2006 3/30/2006
135 3/01/2006 3/26/2006
178 3/25/2006 4/05/2006

I'm trying to get results which show the number of records by day where the day falls between the start and end dates.

Day Feb-06 Mar-06 Apr-06
01[tab][tab] [tab][tab][tab]2[tab][tab][tab]1
02[tab][tab][tab] [tab][tab]2[tab][tab][tab]1
03[tab][tab] [tab][tab][tab]2[tab][tab][tab]1
04[tab][tab] [tab][tab][tab]3[tab][tab][tab]1
05[tab][tab] [tab][tab][tab]3
06[tab][tab] [tab][tab][tab]3
07[tab][tab] [tab][tab][tab]2
08[tab][tab] [tab][tab][tab]2
09[tab][tab] [tab][tab][tab]2
10[tab][tab] [tab][tab][tab]1
11[tab][tab] [tab][tab][tab]1
12[tab][tab] [tab][tab][tab]1
13[tab][tab] [tab][tab][tab]1
14[tab][tab] [tab][tab][tab]1
15[tab][tab] [tab][tab][tab]2
16[tab][tab] [tab][tab][tab]1
17[tab][tab] [tab][tab][tab]1
18[tab][tab] [tab][tab][tab]1
19[tab][tab] [tab][tab][tab]1
20[tab][tab] [tab][tab][tab]2
21[tab][tab] [tab][tab][tab]2
22[tab][tab] [tab][tab][tab]2
23[tab][tab] [tab][tab][tab]2
24[tab][tab] [tab][tab][tab]2
25[tab][tab] [tab][tab][tab]2
26[tab][tab]1[tab][tab][tab]1
27[tab][tab]1[tab][tab][tab]2
28[tab][tab]1[tab][tab][tab]2
29[tab][tab] [tab][tab][tab]2
30[tab][tab] [tab][tab][tab]1
31[tab][tab] [tab][tab][tab]1


I'm not really sure how to go about building this query. I searched the threads but this example is too specific, and I wasn't able to find anything. Does anyone have any suggestions?

 

[vongrunt]

Any calendar table handy?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[oshlonger]

There isn't a calendar table that I know of. If that is what is needed, I could ask for it to be implemented. Would it basically be a table listing all dates?

Could you let me know what the query would be like if I did have a calendar table?

 

[vongrunt]

With calendar table you can easily get individual dates and number of records for each date. Pivoting (separated columns for every month) requires dynamic SQL though - personally I would do that part client-side. Is that OK?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[oshlonger]

Sorry, but I'm not seeing how I could easily get individual dates and number of records for each date. Can you elaborate?

I'm not worried about the pivot part - I think I handle that as long as I can get counts for each date.

Thanks for your help.

 

[vongrunt]

Ock. Sample data:

create table blah (Record int, Start smalldatetime, [End] smalldatetime)
set dateformat mdy
insert into blah values (123, '2/26/2006', '3/10/2006')
insert into blah values (123, '3/15/2006', '3/16/2006')
insert into blah values (145, '3/04/2006', '3/07/2006')
insert into blah values (154, '3/20/2006', '3/25/2006')
insert into blah values (154, '3/27/2006', '3/30/2006')
insert into blah values (135, '3/01/2006', '3/26/2006')
insert into blah values (178, '3/25/2006', '4/05/2006')

This is calendar table. Create it once and forget. This one holds all dates from Y2000 til end of winter 2011

create table Calendar (calDate smalldatetime primary key)
declare @t table (N int)
insert into @t values (1)
while @@rowcount < 2048
	insert into @t select N + (select count(*) from @t) from @t

insert into Calendar select convert(datetime, '19991231')+N from @t

Code:

declare @mindate smalldatetime, @maxdate smalldatetime
select @mindate = min(Start), @maxdate = max([End]) from blah

-- wanna complete months in result set, uncomment two lines below
-- select @mindate = dateadd(mm, datediff(mm, 0, @mindate), 0)
-- select @maxdate = dateadd(mm, 1+datediff(mm, 0, @maxdate), -1)

set ansi_warnings off

select C.calDate, count(B.Record)
from Calendar C 
left outer join blah B on C.calDate between B.Start and B.[End]-1  -- **
where C.calDate between @mindate and @maxdate
group by C.calDate
order by C.calDate

** Based on sample data I assumed ends of intervals are not inclusive...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[oshlonger]

Wow, this is great! Thanks so much for the help. I just went through this and the results are what I'm looking for. I'll be sure to reply with any questions further down the road but this will get the job done.

Thanks again!