• Status: Solved
• Priority: Medium
• Security: Public
• Views: 380

# Cost exists within a table

I have a table that holds a roomrate, periodstart, periodend. i need to query that table with a booking date and noofnights and test to ensure that there is a rate for every night of the stay bearing in mind that there may be a number of entries in the table for each period.

If there is a cost for every night of the stay it would be useful to return an average rate for the booking.
0
AnthonyHanson
• 5
• 4
• 3
• +1
1 Solution

Commented:
You can query for the rates of any period that overlaps with the booking date and the number of nights:

select *  from rates_table where
(periodstart > bookdate and periodstart < date_add(bookdate, interval @noofnights day))
or (periodend > bookdate and periodend < date_add(bookdate, interval @noofnights day))
or (periodstart < bookdate and periodend > date_add(bookdate, interval @noofnights day))

To get the non-weighted average rate:

select avg(roomrate)  from rates_table where
(periodstart > bookdate and periodstart < date_add(bookdate, interval @noofnights day))
or (periodend > bookdate and periodend < date_add(bookdate, interval @noofnights day))
or (periodstart < bookdate and periodend > date_add(bookdate, interval @noofnights day))

However, if you want to test for gaps in rates, I would think you need to write a stored proc that iterates across every day in the book period and tests for a rate for that day. Testing for a negative across a variable number of discrete conditions... nothing springs to mind for how to accomplish that in standard SQL.
0

Commented:
I take that last part back... You can test for rates that overlap your booking period for which there are no other rates that overlap or pick up where that period left off. Note, the DATE_ADD function is MySQL, the SQL Server equivalent for

... is:

``````select *  from rates_table rt where
(
(periodstart > bookdate and periodstart < date_add(bookdate, interval @noofnights day))
and not exists (
select 1 from rates_table
where periodend >= date_add(rt.periodstart, interval -1 day) and periodstart < rt.periodstart
)
)
or
(
(periodend > bookdate and periodend < date_add(bookdate, interval @noofnights day))
and not exists (
select 1 from rates_table
where periodstart <= date_add(rt.periodend, interval 1 day) and periodend > rt.periodstart
)
)
``````
0

Database Architect - Dba - Data ScientistCommented:
Please post you data structure.  We can not guess it to recommend a solution.
0

Author Commented:
Hi Angry Binary.
Thanks for the response, i was hoping to avoid a stored procedure so your second post appeals. The periods for the rates do not overlap because i have prevented overlapping validity periods for the room rate. It is possible that there is a gap between date periods which is what i am testing for.

Its been a long day so off to bed. will test your suggestion tomorrow.
0

Author Commented:
Hi Racimo
Table structure very simple
Rates table: RateId(int), PeriodStart(datetime), PeriodEnd(datetime), RoomRate(currency)

I need to query the table knowing a BookingDate and NoOfNights of stay and to test to see 1) if there is a rate for every night of the stay and 2) what the average nights rate is.

Regards
0

Database Architect - Dba - Data ScientistCommented:
I am sorry but you are not providing enough information to solve your problem.  For instance, what rates applies when two periods overlap ? Should average take into consideration gaps ?
0

Author Commented:
Hi Racimo
Thats my problem.
Dates will never overlap as the data entry has alredy been validated but but there could be a gap between dates, in whch case the routine would have to return False ie no costs to warn users of missing costs for the supplied booking date and duration.
I know i can do it with a stored procedure but a select statement would run much faster which is important as i could be testing hundreds of dates at a time.

Regards
0

Database Architect - Dba - Data ScientistCommented:
<<in whch case the routine would have to return False ie no costs to warn users of missing costs for the supplied booking date and duration.>>
I see.  You have not answered the questions I asked: should the average rate be calculated by taking into consideration only dates where they were bookings or not ?

<<I know i can do it with a stored procedure but a select statement would run much faster which is important as i could be testing hundreds of dates at a time.>>
For the moment, forget about how you you want to achieve it and focus on stating clearly what you want to achieve.  An average and a boolean value are not the same objective, SQL wise.
0

Author Commented:
Hi Racimo
Quick answer NO If there is a rate missing for any day within the period the routine must return False indicating to the user that there is incomplete cost information.

I appreciate an average and a boolean are not the same. But the routine could return  0 (cur) for missing costs or a value (cur) for a valid result.

Regards

0

Database Architect - Dba - Data ScientistCommented:
I am sorry but you lost me.  Please post a sample input/output of what you expect.
0

Senior DBACommented:
Do you have a tally / sequential numbers table?  Using it is one easy way to get what you need.

I've returned the avg room rate *and* the number of nights that had no rate.  Naturally if the # of nights missing is > 0, you could ignore the room rate if you wanted.

Note that the inner query will give you the (avg) rate separately for each night (in case there is more than one rate for the same night).
``````-- strip time off @bookingDate, just to be sure
SET @bookingDate = DATEADD(DAY, DATEDIFF(DAY, 0, @bookingDate), 0)

SELECT
SUM(ISNULL(AvgRoomRate, 0)) / ISNULL(NULLIF(COUNT(AvgRoomRate), 0), 1) AS AvgRoomRate,
@NoOfNights - COUNT(AvgRoomRate) AS NoOfNightsMissingRate
FROM (
SELECT DATEADD(DAY, tt.tally, @bookingDate) AS Night,
AVG(rt.roomrate) AS AvgRoomRate
FROM dbo.tally tt WITH (NOLOCK)
LEFT OUTER JOIN dbo.rateTable rt ON
DATEADD(DAY, tt.tally, @bookingDate) BETWEEN periodstart AND periodend
WHERE tt.tally BETWEEN 0 AND (@noOfNights - 1)
) AS derived
``````
0

Senior DBACommented:
CORRECTION:
Pls change "FROM dbo.tally" to "FROM dbo.tallyTable".

Here's the code to create the tally table w/ 10000 rows (0-9999).
``````DROP TABLE tallyTable
GO
CREATE TABLE tallyTable ( tally int )
CREATE UNIQUE CLUSTERED INDEX tallyTable_CL ON tallyTable (tally) WITH (FILLFACTOR = 100);
INSERT INTO tallyTable ( tally )
SELECT digits + tens + hundreds + thousands
FROM (
SELECT 0 AS digits 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
) AS digits
CROSS JOIN (
SELECT 00 AS tens UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL
SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL
SELECT 80 UNION ALL SELECT 90
) AS tens
CROSS JOIN (
SELECT 000 AS hundreds UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300 UNION ALL
SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL SELECT 700 UNION ALL
SELECT 800 UNION ALL SELECT 900
) AS hundreds
CROSS JOIN (
SELECT 0000 AS thousands UNION ALL SELECT 1000 UNION ALL SELECT 2000 UNION ALL SELECT 3000 UNION ALL
SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000 UNION ALL SELECT 7000 UNION ALL
SELECT 8000 UNION ALL SELECT 9000
) AS thousands
ORDER BY digits + tens + hundreds + thousands

ALTER INDEX tallytable_CL ON tallyTable REBUILD --just to make sure
``````
0

Author Commented:
Wow!!!
That seems to do it. I am pleased that the solution was complex because it had me scratching my head.

Thanks
0

Senior DBACommented:
It could be simplified somewhat but I wanted to leave in the capability to get a quote for each separate night.

The tally table is very useful for certain types of queries.  You may to create a "utility" db so it's in a known common location and can  be used by other queries.  Or put it in the master db.

I'm sure you know this, but just to verify, you can add the db name before the table name to use a table from a different db in your query.

For example:

USE bookingDatabase

SELECT *
FROM master.dbo.tally
INNER JOIN dbo.rate ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.