Solved

Cost exists within a table

Posted on 2010-11-11
14
368 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 13

Expert Comment

by:AngryBinary
ID: 34114646
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
ID: 34114756
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
ID: 34114841
Please post you data structure.  We can not guess it to recommend a solution.
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 1

Author Comment

by:AnthonyHanson
ID: 34115475
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
ID: 34115504
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
ID: 34116296
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
ID: 34119256
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 34119354
<<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
ID: 34119594
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
ID: 34119677
I am sorry but you lost me.  Please post a sample input/output of what you expect.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 34122398
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:Scott Pletcher
ID: 34122429
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
ID: 34122641
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:Scott Pletcher
ID: 34122683
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

717 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