Solved

Cost exists within a table

Posted on 2010-11-11
14
326 Views
Last Modified: 2012-05-10
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
Comment
Question by:AnthonyHanson
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 13

Expert Comment

by:AngryBinary
Comment Utility
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
 
LVL 13

Expert Comment

by:AngryBinary
Comment Utility
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

    DATE_ADD(bookdate, intervale @noofnights day)

... is:

    DATEADD (day, @noofnights, bookdate)


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
	)
)

Open in new window

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
Please post you data structure.  We can not guess it to recommend a solution.
0
 
LVL 1

Author Comment

by:AnthonyHanson
Comment Utility
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
 
LVL 1

Author Comment

by:AnthonyHanson
Comment Utility
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
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
 
LVL 1

Author Comment

by:AnthonyHanson
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
<<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
 
LVL 1

Author Comment

by:AnthonyHanson
Comment Utility
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
I am sorry but you lost me.  Please post a sample input/output of what you expect.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
Comment Utility
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)

    GROUP BY DATEADD(DAY, tt.tally, @bookingDate)

) AS derived

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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

Open in new window

0
 
LVL 1

Author Comment

by:AnthonyHanson
Comment Utility
Wow!!!
That seems to do it. I am pleased that the solution was complex because it had me scratching my head.

Thanks
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Numeric sequence in SQL 14 36
SQL Server memory Issue 7 73
Copy Database Wizard Error 3 19
Pivot not using aggregate yield error 3 13
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now