Number of cases per day in a date range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Number of cases per day in a date range

Author

Topic

samneedshelp
Starting Member

9 Posts

Posted - 2009-10-17 : 09:59:30

Hi All,

I have a table of Guests who have StartDate and EndDate for their stay. I want to find number of Guests *per day* in a specific date range (i.e. 2009-10-01 to 2009-10-10).

I am thinking about finding the number of days in that range and then looping through all days in that range. But I doubt that is an efficient way of doing so.

Any comments and help would be appreciated.

Thank you
Sam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-17 : 10:38:39

Are you using SQL 2000 or 2005 / 2008 ?

KH
[spoiler]Time is always against us[/spoiler]

samneedshelp
Starting Member

9 Posts

Posted - 2009-10-17 : 11:19:17

Hi Khatn,

Thank you for your response.

This will be run on SQL Server 2005.

Sam

samneedshelp
Starting Member

9 Posts

Posted - 2009-10-17 : 13:58:01

And this is a visual version of the idea.

I am trying to find how I can count the cases per 'o' (days) between Date A and B for each guest while we have Start (S) and End (E) dates of stay.

----------A-----------------------------------B----------
----------|-----------------------------------|-----------
----------|-------S|oooooooooooooooooo|E------|-----------
----------|-----------------------------------|-----------
----S|oooo|oooooooooooo|E---------------------|-----------
----------|-----------------------------------|-----------
----------|-----------------------------------|-----------
----------|---------------------S|oooooooooooo|oooooo|E---
----------|-----------------------------------|-----------
----------|-----------------------------------|-----------
----------|-----------------------------------|-----------
----------|-----------------------------------|-----------

.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-18 : 01:33:18

if you have a calendar table than join to that. If not cross apply to F_TABLE_DATE.

something like


select [DATE], count(*)
from   Guests 
       cross apply F_TABLE_DATE(start_date, end_date)
group by [DATE]

KH
[spoiler]Time is always against us[/spoiler]

samneedshelp
Starting Member

9 Posts

Posted - 2009-10-18 : 11:50:51

Thank you very much khtan. Your solution is awesome!

It works very well. Great job!

Cheers
Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources