June 26, 2011 at 4:49 pm
I asked helped about this query a couple of times
but never got a finalizing answer.So please bear with me...
This is the required basic data :
CREATE DATABASE HIS;
GO
USE HIS
CREATE TABLE ROOM_TYPES
(
ROOMTYPE_ID INT IDENTITY (1,1)PRIMARY KEY CLUSTERED,
ROOM_TYPE VARCHAR(6) NOT NULL,
ROOM_DESCRIPTION VARCHAR(60)
);
GO
USE HIS
CREATE TABLE ROOM_RATES
(
ROOM_RATE_ID INT IDENTITY (1,1)PRIMARY KEY CLUSTERED,
ROOM_TYPE VARCHAR(6),
RATE_START_DATE DATETIME,
RATE_END_RATE DATETIME,
RATE MONEY
);
GO
USE HIS
INSERT INTO ROOM_TYPES (ROOM_TYPE,ROOM_DESCRIPTION)
VALUES
('DBLMS','DOUBLE ROOM SEA VIEW'),
GO
USE HIS
INSERT INTO ROOM_RATES (ROOM_TYPE,RATE_START_DATE,RATE_END_RATE,RATE)
VALUES
('DBLMS','3/1/2011','16/4/2011','104'),
('DBLMS','22/10/2011','28/12/2011','104'),
('DBLMS','25/4/2011','25/6/2011','118'),
('DBLMS','24/9/2011','22/10/2011','118'),
('DBLMS','16/4/2011','25/4/2011','122'),
('DBLMS','25/6/2011','30/7/2011','122'),
('DBLMS','20/8/2011','24/9/2011','122'),
('DBLMS','30/7/2011','20/8/2011','132'),
('DBLMS','28/12/2011','2/1/2012','132');
GO
Now,the query I would like to run against the table ROOM_RATES is this:
Find the RATE between supplied RATE_START_DATE and RATE_END_RATE WHERE
ROOM_TYPE = 'DBLMS'
I tried one query :
use HIS
SELECT room_type,rate_start_date,rate_end_date,rate,
DATEDIFF(DAY,case when rate_end_date < '2011-04-26'
then '2011-04-14'
else rate_start_date end,
case when rate_start_date > '2011-04-14'
then '2011-04-26'
else rate_end_date end ) AS days FROM room_rates
WHERE room_type = 'DBLMS' AND rate_start_date <= '2011-04-26'
AND rate_end_date > '2011-04-14'
I get nearly all results wrong :
room_type rate_start_date rate_end_date rate days
DBLMS 2011-01-03 2011-04-16 104,002
DBLMS 2011-04-25 2011-06-25 118,001
DBLMS 2011-04-16 2011-04-25 122,0012
The right results are displayed below
(However,I would like my query to display like this) :
room_type start_date end_date rate days
DBLMS 2011-04-14 2011-04-16 104,002
DBLMS 2011-04-16 2011-06-25 118,009
DBLMS 2011-04-25 2011-04-26 122,001
I am struggling with this query for a week now and I am next to
exaustion.
I tried this query :
use HIS
SELECT
room_type,
case when rate_start_date < '2011-04-14'
then '2011-04-14'
else rate_start_date
end
,Case when RATE_END_DATE > '2011-04-26'
then '2011-04-26'
else RATE_END_DATE
end
,rate
, DATEDIFF(DAY,case when RATE_END_DATE < '2011-04-26'
then '2011-04-14'
else RATE_START_DATE
end,
case when rate_start_date > '2011-04-14'
then '2011-04-26'
else RATE_END_DATE
end
) AS days FROM room_rates
WHERE room_type = 'DBLMS' AND rate_start_date <= '2011-04-26'
AND RATE_END_DATE > '2011-04-14'
and I get :
room_type(No column name)(No column name)ratedays
DBLMS2011-04-14 00:00:00.0002011-04-16 00:00:00.000104,002
DBLMS2011-04-25 00:00:00.0002011-04-26 00:00:00.000118,001
DBLMS2011-04-16 00:00:00.0002011-04-25 00:00:00.000122,0012
which is almost correct except my last line instead of displaying 9 days
displays 12,which is wrong of course as between 16.4 and 25.4 there are only
9 days.
Also there is this (no column name) issue.As I said,I would like to have the
columns start_date,end_date.
So can someone enlighten me and put me out of my misery.
Additional info would be great so I can understand something.
just learning ...
June 26, 2011 at 9:16 pm
skynet_si (6/26/2011)
So can someone enlighten me and put me out of my misery.
Absolutely.
Additional info would be great so I can understand something.
just learning ...
The additional info you seek is in the comments of the code.
I hope you don't mind but I changed the column you called "Rate_End_Rate" to "Rate_End_Date". 🙂
"Divide'n'Conquer" does it. Here's the code for the solution...
--===== Declare some obviously name variables
DECLARE @StartDate DATETIME,
@EndDate DATETIME,
@RoomType VARCHAR(6)
;
--===== Assign the desired "stay" start and end dates as well as the desired room type
SELECT @StartDate = '2011-04-14',
@EndDate = '2011-04-26',
@RoomType = 'DBLMS'
;
--===== Solve the problem
WITH
cteStayDates AS
( --=== Decide which dates to use on the endpoints of the date range
SELECT RoomType = Room_Type,
StartDate = CASE WHEN Rate_Start_Date < @StartDate THEN @StartDate ELSE Rate_Start_Date END,
EndDate = CASE WHEN Rate_End_Date > @EndDate THEN @EndDate ELSE Rate_End_Date END,
Rate
FROM dbo.Room_Rates
WHERE @RoomType = Room_Type
AND @StartDate < Rate_End_Date
AND @EndDate >= Rate_Start_Date
) --=== The hard part is calculating the number of days. Once we figure out that all the Rate_End_Dates
-- are relatively 1 larger than the days they include and the actual "Stay" EndDate is not, calculating
-- the number of days gets pretty easy.
SELECT RoomType, StartDate, EndDate, Rate,
Days = DATEDIFF(dd,StartDate,EndDate)
+ CASE WHEN EndDate = @EndDate THEN 1 ELSE 0 END --Compensate for the end point
FROM cteStayDates
ORDER BY StartDate
;
... and here's the output... just like you want.
RoomType StartDate EndDate Rate Days
-------- ----------------------- ----------------------- --------------------- -----------
DBLMS 2011-04-14 00:00:00.000 2011-04-16 00:00:00.000 104.00 2
DBLMS 2011-04-16 00:00:00.000 2011-04-25 00:00:00.000 122.00 9
DBLMS 2011-04-25 00:00:00.000 2011-04-26 00:00:00.000 118.00 2
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2011 at 9:20 pm
As a side bar, I believe using the actual enddates for each rate is confusing because they aren't actually the enddates. They're actually the start date of the next range. I believe that 1 day should be subtracted for display purposes so as to not confuse people when the read the output.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2011 at 10:06 pm
Additional info would be great so I can understand something.
just learning ...
Ok... in that light...
The key to easily figuring out this problem had absolutely nothing to do with any skill. Quite the contrary. It was terribly difficult for me to look at code with 10 date entries in it. I found myself asking "Ok, is that the start date or the end date I'm looking at?" I asked myself that question about twice and then decided to replace all the hardcoded dates with variables that were easy to understand the names of. It took an extra minute or two to do but the dates were going to have to be converted to variables later on, anyway.
The other thing (for me, anyway) was putting the code I was writing into a format that I'm more familiar with. It's not wrong but I find leading commas, inconsistent casing, keywords on the same line after something else, and "AS" aliasing very distracting. I normally end up refactoring the code I'm troubleshooting but I didn't do that this time because it was so different to what I normally use. In this case, I think it took less time to simply solve the problem than it would have taken me to refactor the code to something I'm used to seeing.
As yet another side bar, you did a very nice job of creating readily consumable data.
Apologies for not getting to you on the other two posts but it looked like things were going in the right direction before I stopped following them. In that vein, I'd normally bust your chops a bit for triple posting but, looking back at the other two threads, I can see why you decided to and needed to start afresh. Just don't make that a habit because it really ticks some of the folks on forums off. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2011 at 11:38 am
jeff,thanks a lot...
Did not have the time to check this but it seems the last output is wrong
DBLMS 2011-04-25 00:00:00.000 2011-04-26 00:00:00.000 118.00 2
That should be 1 day !
Ohhh ...now I tried it...
replaced: + CASE WHEN EndDate = @EndDate THEN 0 ELSE 0 END
Now the result is ok.
RoomTypeStartDateEndDateRateDays
DBLMS2011-04-14 00:00:00.0002011-04-16 00:00:00.000104,002
DBLMS2011-04-16 00:00:00.0002011-04-25 00:00:00.000122,009
DBLMS2011-04-25 00:00:00.0002011-04-26 00:00:00.000118,001
Thank you very,very much!
Now I need to study this all....
June 27, 2011 at 11:56 am
In hotel stay,the day of departure is the next day.
so if you enter 26.6 till 27.6 that is one day stay.
June 27, 2011 at 4:42 pm
skynet_si (6/27/2011)
jeff,thanks a lot...Did not have the time to check this but it seems the last output is wrong
DBLMS 2011-04-25 00:00:00.000 2011-04-26 00:00:00.000 118.00 2
That should be 1 day !
Ohhh ...now I tried it...
replaced: + CASE WHEN EndDate = @EndDate THEN 0 ELSE 0 END
Now the result is ok.
RoomTypeStartDateEndDateRateDays
DBLMS2011-04-14 00:00:00.0002011-04-16 00:00:00.000104,002
DBLMS2011-04-16 00:00:00.0002011-04-25 00:00:00.000122,009
DBLMS2011-04-25 00:00:00.0002011-04-26 00:00:00.000118,001
Thank you very,very much!
Now I need to study this all....
So just remove the case statement altogether.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply