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)
 Complex query - Cursor needed?!

Author  Topic 

glock
Starting Member

8 Posts

Posted - 2003-06-17 : 10:24:17
Dear SQL lovers,

I have a table which stores information about the prices of hotel rooms during different periods of the year.
Below I have created a sample copy of the table.

CREATE TABLE HOTELROOMPRICES (
PRICE_PER_NIGHT NUMERIC(9,2),
BEGIN_PERIOD DATETIME,
END_PERIOD DATETIME,
TYPE_OF_ROOM VARCHAR(20)
)

INSERT INTO HOTELROOMPRICES(PRICE_PER_NIGHT, BEGIN_PERIOD, END_PERIOD, TYPE_OF_ROOM)
SELECT 350,CONVERT(DATETIME, '01/05/2003', 103), CONVERT(DATETIME, '16/05/2003', 103), 'SINGLE'
UNION ALL
SELECT 400,CONVERT(DATETIME, '17/05/2003', 103), CONVERT(DATETIME, '01/06/2003', 103), 'SINGLE'
UNION ALL
SELECT 500,CONVERT(DATETIME, '02/06/2003', 103), CONVERT(DATETIME, '01/09/2003', 103), 'SINGLE'
UNION ALL
SELECT 250,CONVERT(DATETIME, '02/09/2003', 103), CONVERT(DATETIME, '30/10/2003', 103), 'SINGLE'
UNION ALL
SELECT 400,CONVERT(DATETIME, '31/10/2003', 103), CONVERT(DATETIME, '31/12/2003', 103), 'SINGLE'

If you run the above you should have a table containing 5 records. Now say that I booked a room starting on the 20th May 2003 and ends on the 23rd October 2003. How do I work out the exact cost of the the time spent at the hotel. This can vary depedning on the period of booking. Sometimes the period might span over 5,6 maybe 7 price groups.

Any suggestions?!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-17 : 11:22:46
Please no cursor!!

Use a 'Tally' table ... a single column (int) table containing all positive integers between 0 and N ... (N beging sufficiently large enough to satisfy your needs)


declare @start datetime,
@end datetime
select
@start = '20 May 2003',
@end = '23 October 2003'

select
dateadd(dd,n,'1/1/2003') as BookedDay,
price_per_night as price
from
hotelroomprices p
inner join tally t
on dateadd(dd,n,'1/1/2003') between p.begin_period and p.end_period
where
dateadd(dd,n,'1/1/2003') between @start and @end
order by
dateadd(dd,n,'1/1/2003')
compute
sum(price_per_night)

 


Jay White
{0}
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-17 : 11:26:56
something like this could work; i haven't fully tested it for all conditions but the idea should work fine:

declare @start datetime;
declare @end datetime;

set @start = '5/21/2003'
set @end = '10/23/2003'

EDIT: minor change so you can see the calculations and a small adjustment in the end date formula:

select price_per_night,
periodDays -
case when startDiff > 0 then startdiff else 0 end -
case when enddiff > 0 then enddiff-1 else 0 end as Nights
from
(
select price_per_night,
datediff(d,begin_period, end_period) as PeriodDays,
datediff(d,begin_period, @start) as StartDiff,
datediff(d,@end, end_period) as EndDiff
from
HOTELROOMPRICES
where @start between begin_period and end_period OR
@end between begin_period and end_period OR
(@start <= begin_period and @end >= end_period)
)
a

that returns: 62650.00 -- not sure if that's the right number or not.

- Jeff

Edited by - jsmith8858 on 06/17/2003 11:38:32
Go to Top of Page

glock
Starting Member

8 Posts

Posted - 2003-06-17 : 11:35:51
Jeff,

It works!! Thank you so much.

Just wondering. How old are you Jeff and what do you do for a living?

Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-17 : 11:41:36
glock -- note the change i made; i think it may have been off by a day or two.

i'm 30 ... i work for a medium-sized financial consulting firm, and I do database and application development for the firm's accounting/finance/hr/payroll departments.

- Jeff
Go to Top of Page

glock
Starting Member

8 Posts

Posted - 2003-06-17 : 11:54:55
Jeff,

Thanks for the reply. I realised your change and according to my calculations, it returns the correct amount. Why did you go for 21st anyway?!

Just wondering, what is your approach when you come across these kind of problems?
Page47 seems to dislike cursors and both of you managed to provide solutions that look simple yet effective. How do you do it?! (please don't say that you're just clever!)

Mr Cross Join?! LOL... I hope that's not your real surname!


Edited by - GLOCK on 06/17/2003 12:04:43
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-17 : 12:12:58
i hate cursors equally as well ... i also hate dynamic SQL but that's for another day ...

I thought about Jay's approach, I use tally tables as much as anyone, but in this case it seems like you don't need one. I always use the simpliest solution -- if a solution to a problem is overly-complex, then it is not a solution, it will just lead to more problems. that's what i always say.

in this case:

step 1: find out which rates are in the date range specified (the WHERE clause in the inner query does this). basically, take prices in which the start date is in the range or the end date is in that range, OR take price in which the entire price date range is within the start/end date specified.

step 2: from there, find out how many days are in each date range.

step 3: now, if the starting date is within a particular price's date range, SUBTRACT OUT the different between the starting date and the beginning of the price range.

step 4: if the ending date is within the a particular price's date range, SUBTRACT OUT the difference between the ending date and the ending date of the price range

step 5: for each price then, use the formula in steps 2-4 to calculate the # of days, and multiply the price by the # of days for that price.

step 6: add them all up. (note i didn' really do this in my revision solution, i just showed "my work")

note a good explaination, but that was my thought process. Jay's is equally as good, in a way a little cleaner, but it won't be as efficient due to the use of another table.


- Jeff

Edited by - jsmith8858 on 06/17/2003 12:15:04
Go to Top of Page

macka
Posting Yak Master

162 Posts

Posted - 2003-06-17 : 12:15:45
Glock (or should I say Crespo),

Are these really your questions ?

[url]http://dbforums.com/t819556.html[/url]

I'm sure you are just waiting to thank Jay and Jeff ???!!!



macka.


--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

Edited by - macka on 06/17/2003 12:18:27
Go to Top of Page

glock
Starting Member

8 Posts

Posted - 2003-06-17 : 12:43:01
quote:

Glock (or should I say Crespo),

Are these really your questions ?

[url]http://dbforums.com/t819556.html[/url]

I'm sure you are just waiting to thank Jay and Jeff ???!!!



macka.


--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

Edited by - macka on 06/17/2003 12:18:27



pffff... I hate people sometimes, I really do.

I spent 2 hours on the damn question. I did it in a cursor and it looked so bad it made me feel sick. I posted this question and Jeff answered and so I posted back to the other guy.

Just leave me alone please. Why do you want to stir up trouble anyway? If that is how you gain your daily satisfaction then I truly feel sorry for you.

Have a good day!

Edited by - GLOCK on 06/17/2003 12:47:04
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-17 : 14:35:21
quote:

select price_per_night,
periodDays -
case when startDiff > 0 then startdiff else 0 end -
case when enddiff > 0 then enddiff-1 else 0 end as Nights
from
(
select price_per_night,
datediff(d,begin_period, end_period)+1 as PeriodDays,
datediff(d,begin_period, @start) as StartDiff,
datediff(d,@end, end_period) as EndDiff
from
HOTELROOMPRICES
where @start between begin_period and end_period OR
@end between begin_period and end_period OR
(@start <= begin_period and @end >= end_period)
)
a



I guess we'd have to look closer at the business rules on this one, but remember, datediff counts the number of day boundaries crossed in a period, not the number of days.

datediff(dd,'05/20/2003','06/01/2003') = 12, but assuming the patron pays for every day they stay, it is 13 days total the way I count it.

Nice solution, though Jeff. I am too quick to pull the trigger on a tally table ...

Crespo, how 'bout you throw a link to this thread up over on dbforums ...

Jay White
{0}
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-17 : 15:04:44
good old crespo ....

Actually, Jay it depends, like you mentioned, about the business rules. if the start day is the day you check in and the end day is the day you check out, then datediff is the answer. but if the startday is the first NIGHT you stay, and endday is the last NIGHT you stay, then you would need to adjust.

or, did you already say that?

but usually, with hotel room-type problems and all that, i assume the day you checkout is NOT a day you are paying for ...

personally, usually I just pay by the hour but that's just me ...


- Jeff

Edited by - jsmith8858 on 06/17/2003 15:07:22
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-17 : 17:08:30
quote:

good old crespo ....



Belarus...wow macka, you're scaring me...that's amazing

quote:

personally, usually I just pay by the hour but that's just me ...




I thought that was Justin's gig....


Brett

8-)
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-06-17 : 19:44:10
A slightly different way for this which I think is a bit easier to understand....


declare @start datetime;
declare @end datetime;

set @start = '5/21/2003'
set @end = '10/23/2003'

Select PRICE_PER_NIGHT,
CASE WHEN @start BETWEEN BEGIN_PERIOD AND END_PERIOD
THEN DATEDIFF(d, @start, END_PERIOD)
WHEN @end BETWEEN BEGIN_PERIOD AND END_PERIOD
THEN DATEDIFF(d, BEGIN_PERIOD, END_PERIOD) - DATEDIFF(d, @end, END_PERIOD) +1
ELSE DATEDIFF(d, BEGIN_PERIOD, END_PERIOD) END AS TotalNights
from HOTELROOMPRICES
WHERE BEGIN_PERIOD BETWEEN @start AND @end
OR END_PERIOD BETWEEN @start AND @end


DavidM

"SQL-3 is an abomination.."
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-17 : 19:46:24
David -- not bad but doesn't handle all cases.

what if start date and end date are in the same price period? I don't think your example handles that ...

- Jeff
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-17 : 21:24:57
Well....


Since this original post was a post by someone else, this "poster" is using this site to get an answer to be delivered to a different third party. The original "poster" (on a different web site) is not reapping the benefit of this thread... I must say at that other site the poster here mentions that he was not the author of the solution.

We are pawns relegated to the curse of sql additicion...

oops sorry

my bad...



Brett

8-)

Edited by - x002548 on 06/17/2003 21:27:24
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-06-17 : 21:37:08
quote:

David -- not bad but doesn't handle all cases.

what if start date and end date are in the same price period? I don't think your example handles that ...




Spot on Jeff! Just add an extra condition to fix that....

I get damn lazy (don't test) when it is not my problem!


OR @start BETWEEN BEGIN_PERIOD AND END_PERIOD


DavidM

"SQL-3 is an abomination.."
Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-18 : 02:59:49
Actually when looking for a datespan within a datespan, where you could have any type of overlap, you dont really need to have three conditions.
quote:
where @start between begin_period and end_period OR
@end between begin_period and end_period OR
(@start <= begin_period and @end >= end_period)



You could do the same thing with only one condition

WHERE @StartDate <= End_Period AND @EndDate >= Start_Period

We've had this discussion before http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23593

Owais

Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-06-18 : 03:14:35
Owais,

I remember reading that thread and thinking "Very elegant, would use indexes well (seek instead of scan) MUST remember that"...

Less than 6 months later, didn't even cross my mind

[Must..resist..urge..to..blame..beer..]

DavidM

"SQL-3 is an abomination.."

Edited by - byrmol on 06/18/2003 04:01:30

Edited by - byrmol on 06/18/2003 04:08:40
Go to Top of Page

Djiff
Starting Member

3 Posts

Posted - 2003-06-18 : 08:12:16
Hi guys,
I post the thread on dbforum ( [url]http://dbforums.com/t819556.html[/url]).
It seems I'm not the only one working on this kind of project.
I'm discovering this forum and it seems to be very animated !

just one question: how bad is it to use temporary tables in a sp ?
Go to Top of Page

glock
Starting Member

8 Posts

Posted - 2003-06-18 : 09:01:53
quote:

Hi guys,
I post the thread on dbforum ( [url]http://dbforums.com/t819556.html[/url]).
It seems I'm not the only one working on this kind of project.
I'm discovering this forum and it seems to be very animated !

just one question: how bad is it to use temporary tables in a sp ?



Djiff, I tried to solve your problem yesterday and the solution that I came up with did not look pretty. I used a cursor to do it and it looked more complicated than it should be. I posted your question here with some sample data and Jeff (the clever developer I mentioned on the dbforums) kindly solved the problem without a cursor.

I am sorry if I made it sound like my own problem but it did take the best part of two hours of my time!



Have a good day!
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-18 : 10:17:26
quote:

WHERE @StartDate <= End_Period AND @EndDate >= Start_Period



funny ... i had thought of that but for some reason I thought it didn't work for certain cases ....

but, it seems to work fine. nice one.

- Jeff
Go to Top of Page
    Next Page

- Advertisement -