| Author |
 Topic  |
|
dataguru1971
Flowing Fount of Yak Knowledge
USA
1464 Posts |
 Posted - 11/12/2007 : 19:51:29
|
I think I get it, might disagree.See if this gives you any ideas (copy and paste into your QA to run on its own).
What you describe, requires a cursor...at least I am not savvy enough to figure out another way (at least with SQL 2000)
It seems like a check for the availability would be better handled by a web-side query, rather than posting every day like you describe.
Create Table #Reservations (ResID int not null,
Checkin datetime not null,
checkout datetime not null,
rm int not null)
Insert INto #reservations (ResID,Checkin,Checkout,rm)
Select 1, '12/1/2007','12/8/2007',1 UNION ALL
Select 2, '12/4/2007','12/20/2007',1 UNION ALL
Select 3, '12/10/2007','12/18/2007',1
Create Table #myTable ( thedate smalldatetime,
ag_year int,
ag_month smallint,
ag_day varchar(10),
ag_message varchar(10),
ag_bgcolor varchar(10),
ag_action varchar(10),
ag_boxit varchar(10),
ag_fgcolor varchar(10),
ag_bgimg varchar(10),
ag_html varchar(10),
resid int )
Declare @Day int,@ID int ,@Start datetime ,@END datetime
DECLARE mydates CURSOR
READ_ONLY
FOR
Select ResID,Checkin,Checkout
FROM #reservations
OPEN mydates
FETCH NEXT FROM mydates INTO @ID, @start,@end
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
Select @Day = 0
While dateadd(dd,@day,@start) <= @end
Begin
insert #myTable (thedate, ag_year, ag_month, ag_day, ag_message, ag_bgcolor,
ag_action, ag_boxit, ag_fgcolor, ag_bgimg, ag_html,resid)
Select dateadd(dd, @day,@start), year(dateadd(dd, @day, @start)),
month(dateadd(dd, @day,@start)), day(dateadd(dd, @day, @start)),
'Reserved', 'red', '', 'TRUE', '', '', '',@ID
Select @day = @day + 1
END
END
FETCH NEXT FROM mydates INTO @ID, @Start,@END
END
CLOSE mydates
DEALLOCATE mydates
select *
from #myTable
drop table #reservations
drop table #mytable
Poor planning on your part does not constitute an emergency on my part.
|
 |
|
|
scottbak
Starting Member
USA
16 Posts |
Posted - 11/12/2007 : 19:58:38
|
YES, that works! That you sooooooooooo much! You just saved me days of trying to figure this out and hours trying to explain to Tara. :)
Scott |
|
|
|
scottbak
Starting Member
USA
16 Posts |
Posted - 11/12/2007 : 20:00:37
|
ok so:
Insert INto #reservations (ResID,Checkin,Checkout,rm)
Select 1, '12/1/2007','12/8/2007',1 UNION ALL
Select 2, '12/4/2007','12/20/2007',1 UNION ALL
Select 3, '12/10/2007','12/18/2007',1
how can I pull the checkin and checkout columns for each rows to put in place of the hard coded dates?
Scott |
|
|
|
dataguru1971
Flowing Fount of Yak Knowledge
USA
1464 Posts |
Posted - 11/12/2007 : 20:02:18
|
I am certain Tara understood more than you might think.
If you need any more help on this, feel free to continue the thread. If I don't respond fairly quickly, just shoot me an email.
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
scottbak
Starting Member
USA
16 Posts |
Posted - 11/12/2007 : 20:38:45
|
I think I got it:
changed:
Insert INto #reservations (ResID,Checkin,Checkout,rm)
Select 1, '12/1/2007','12/8/2007',1 UNION ALL
Select 2, '12/4/2007','12/20/2007',1 UNION ALL
Select 3, '12/10/2007','12/18/2007',1
to:
Insert Into #reservations (ResID,Checkin,Checkout,rm)
select distinct '1',checkin,checkout,'1' from bookings
thanks again for all your help!
Scott |
|
|
|
dataguru1971
Flowing Fount of Yak Knowledge
USA
1464 Posts |
Posted - 11/12/2007 : 20:45:33
|
That would probably do it. I had reservation ID in there, and rm in cast you needed to account for uniqueness or multiple/overlapping reservations.
Without distinct resid or rm, you will end up with duplicates in your table (which does you no good I don't think)
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
dataguru1971
Flowing Fount of Yak Knowledge
USA
1464 Posts |
Posted - 11/12/2007 : 21:13:56
|
YOu got me thinking..since you don't need to pass anything but the two dates..your original procedure below would work with one minor change...(the one Tara pointed out, btw), although it would populate for EVERY day between the lowest checkin and the highest checkout dates (so that wouldn't work I don't think)
set @StartDate = (select min(checkin) from bookings)
set @EndDate = (select max(checkout) from bookings)
THat way you don't need the cursor and you will populate a day for every booked day. If you still need to do it the way I created, you would still likely want unique records (no duplicates) with this change
select DISTINCT *
from #myTable
which would return only unique rows from the resulting table
Poor planning on your part does not constitute an emergency on my part.
|
Edited by - dataguru1971 on 11/12/2007 21:15:36 |
|
|
|
scottbak
Starting Member
USA
16 Posts |
Posted - 11/12/2007 : 22:12:42
|
yours works, as there cannot be a duplicate date (or overlapping) since once it gets booked, it blocks it out so you can no longer select it or any within the range.
again thanks for your help, I got it working.
Scott |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 11/13/2007 : 01:32:56
|
quote:
Originally posted by scottbak
You just saved me days of trying to figure this out and hours trying to explain to Tara. :)
Well that's just rude.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/ |
|
|
|
Topic |
|
|
|
Incorrect syntax errors
