SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Incorrect syntax errors
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/12/2007 :  19:51:29  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.

Go to Top of Page

scottbak
Starting Member

USA
16 Posts

Posted - 11/12/2007 :  19:58:38  Show Profile  Reply with Quote
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
Go to Top of Page

scottbak
Starting Member

USA
16 Posts

Posted - 11/12/2007 :  20:00:37  Show Profile  Reply with Quote
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
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/12/2007 :  20:02:18  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.

Go to Top of Page

scottbak
Starting Member

USA
16 Posts

Posted - 11/12/2007 :  20:38:45  Show Profile  Reply with Quote
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
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/12/2007 :  20:45:33  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.

Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/12/2007 :  21:13:56  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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
Go to Top of Page

scottbak
Starting Member

USA
16 Posts

Posted - 11/12/2007 :  22:12:42  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36639 Posts

Posted - 11/13/2007 :  01:32:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000