Get individual day records from 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

SQL Server 2000 Forums

Transact-SQL (2000)

Get individual day records from a date range

Author

Topic

ashish908
Starting Member

18 Posts

Posted - 2006-01-24 : 00:26:30

I am having a table 'Booking' where i am storing a date range and no of rooms

e.g. Create Table Booking(
bookingid char(10),
TravelStartDate datetime,
TravelEndDate datetime,
NoofSeats int)

i want to find the max number of rooms booked for the given days of travel, say somebody wants to travel from 27th jan 06 to 30th Jan 06, i need to find the seats booked for each day between 27th and 30th and then find the maximum seat booked in that date range.

How can i do that?

Westley
Posting Yak Master

229 Posts

Posted - 2006-01-24 : 00:52:08

base on the startdate? if so, just break the start date into different date, and sum the Seats then group by the date?

ashish908
Starting Member

18 Posts

Posted - 2006-01-24 : 05:46:36

how do i break the dates, i am not that expert. do you mean i have to get records for each day between 27th and 30th , if yes, how do i do that

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-24 : 06:26:49

Can you post some sample data and expected result ?

----------------------------------
'KH'
I do work from home but I don't do homework

ashish908
Starting Member

18 Posts

Posted - 2006-01-24 : 08:13:50

insert into booking values('B000000001','27/JAN/2005','30/JAN/2005',2)
insert into booking values('B000000002','25/JAN/2005','27/JAN/2005',2)
insert into booking values('B000000002','20/JAN/2005','25/JAN/2005',5)

say these are the values in the table, now say i wish to find the no of seats occcupied between two dates then i should get the results as follows

if travel dates between 22nd jan 05 - 27th jan 05 then i should get results as 9
if travel dates between 20nd jan 05 - 25th jan 05 then i should get results as 7
if travel dates between 20nd jan 05 - 24th jan 05 then i should get results as 5

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-24 : 08:16:39

Try this

Select count(*) from yourTable where @date between TravelStartDate and TravelEndDate

Madhivanan

Failing to plan is Planning to fail

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2006-01-24 : 08:22:40

(Moved to the Transact-SQL forum)

-graz

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

ashish908
Starting Member

18 Posts

Posted - 2006-01-24 : 08:22:45

this will only give me the no of records , what i want is the maximum seats booked for any day in that date range

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-24 : 08:48:06

what is the PK of the booking table ? if it is bookingid, then the 3rd record bookingid should not be B000000002 ?

>> if travel dates between 22nd jan 05 - 27th jan 05 then i should get results as 9
How did you get the value 9 ?

----------------------------------
'KH'
I do work from home but I don't do homework

ashish908
Starting Member

18 Posts

Posted - 2006-01-24 : 08:54:58

Oops, my mistake, if the travel dates are betwen 22nd jan 05 - 27th jan 05, then the results should be 7, so the results should be

if travel dates between 22nd jan 05 - 27th jan 05 then i should get results as 7
if travel dates between 20nd jan 05 - 25th jan 05 then i should get results as 7
if travel dates between 20nd jan 05 - 24th jan 05 then i should get results as 5

ashish908
Starting Member

18 Posts

Posted - 2006-01-24 : 08:55:54

and yes, booking id is the primary key, so the 3rd record booking id will be B000000003

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-24 : 09:03:27

Try these code

declare
	@start_date	datetime,
	@end_date	datetime

select	@start_date	= '2005-01-20',
	@end_date	= '2005-01-24'

select	max(Total_Seats) as Max_Seats
from
(
	select	c.TravelDate, sum(NoofSeats) as Total_Seats
	from	
	(	-- individual TravelDate by BookingID
		select	BookingID, dateadd(day, n, TravelStartDate) as TravelDate
		from	#Booking b 
		cross join
		(	-- Number Table
			select	0 n union all
			select	1   union all select  2 union all select  3 union all select  4 union all
			select  5   union all select  6 union all select  7 union all select  8 union all
			select  9   union all select 10 union all select 11 union all select 12
		) as num
		where	datediff(day, TravelStartDate, TravelEndDate)	>= n
	) as c	
	inner join 
	#Booking a
		on	a.BookingID	= c.BookingID
	where	a.TravelStartDate	<= c.TravelDate
	and	a.TravelEndDate		>= c.TravelDate
	and	TravelDate		>= @start_date
	and	TravelDate		<= @end_date
	group by c.TravelDate
) as d

----------------------------------
'KH'
I do work from home but I don't do homework

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-24 : 09:09:32

Additonal Point to Note
Number Table
- I have only created 12 records. For actual codes, either you code the possible the max possible no of records or use a Number Table Function (Search under the script library forum for the code) or create a fix table for this purposes

----------------------------------
'KH'
I do work from home but I don't do homework

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources