How do I get count of bookings per day? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

How do I get count of bookings per day?

Author

Topic

mobydickins
Starting Member

3 Posts

Posted - 2007-06-20 : 04:44:58

I have a table (aBookings) with
BookingID int
SiteID int
FromDate DateTime
ToDate DateTime

I need an sql statement that will give me all bookings for every day between two periods (Say a year)
The follownig statements give me the correct result but all as seperate data lists.
Is there a way of getting the desired result so I can fill a datagrid from one statement

Declare @MinDate DateTime
Declare @MaxDate DateTime
Declare @DateLoop DateTime
Declare @DayCount int
Select @MinDate = Min(FromDate) From aBookings
Select @MaxDate = Max(ToDate) From aBookings
Set @DateLoop = @MinDate
While @DateLoop < @MaxDate
Begin
SELECT Count(BookingID), @DateLoop AS Expr1
FROM aBookings Where SiteID = 26 And @DateLoop Between FromDate And ToDate
Having Count(BookingID) <> 0
Set @DateLoop = DateAdd(day,1,@DateLoop)
End

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-20 : 05:09:40

Try this.


Declare @MinDate DateTime
Declare @MaxDate DateTime
Select @MinDate = Min(FromDate) From aBookings
Select @MaxDate = Max(ToDate)   From aBookings

SELECT d.[DATE], Count(b.BookingID)
-- F_TABLE_DATE is from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE
FROM   F_TABLE_DATE_RANGE(@MinDate, @MaxDate) d 
INNER JOIN aBookings b
ON    d.[DATE] BETWEEN b.FromDate AND b.ToDate
WHERE SiteID = 26 
GROUP BY d.[DATE]
Having Count(b.BookingID) <> 0

KH

mobydickins
Starting Member

3 Posts

Posted - 2007-06-20 : 06:20:49

Thanks for the swift reply.

I get this...

Invalid object name 'F_TABLE_DATE_RANGE'.

Any ideas?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-20 : 06:25:37

Possibly you thought below line as comment:

-- F_TABLE_DATE is from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE[/code]

Click on the link in the above comment to get code for the F_Table_Date() function.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mobydickins
Starting Member

3 Posts

Posted - 2007-06-20 : 06:26:32

Sorted.
F_TABLE_DATE
That is brilliant thanks for your help :-)
Phil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources