June 25, 2011 at 5:49 pm
The relevant data is here :
http://www.sqlservercentral.com/Forums/Topic1131513-1292-1.aspx
When I run this query :
use HIS
SELECT
room_type,rate_start_date,rate_end_date,rate
, DATEDIFF(DAY,case when rate_end_date < '2011-04-20'
then '2011-04-14'
else rate_start_date
end,
case when rate_start_date > '2011-04-14'
then '2011-04-20'
else rate_end_date
end
) AS days FROM room_rates
WHERE room_type = 'DBLMS' AND rate_start_date <= '2011-04-20'
AND rate_end_date > '2011-04-14'
I get result:
room_type rate_start_date rate_end_date rate days
DBLMS 2011-01-03 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 4
So I was wondering if there is a way to modify this so the query would display :
room_type rate_start_date rate_end_date 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-20 00:00:00.000 122,00 4
The exact dates provided for search and not the price range.
June 26, 2011 at 2:48 am
First in the provided link you have got column name as RATE_END_RATE DATETIME instead of RATE_END_DATE DATETIME
Regarding your solution you need to use case statement in your select clause when you display rate start & end date.
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-20'
then '2011-04-20'
else RATE_END_DATE
end
,rate
, DATEDIFF(DAY,case when RATE_END_DATE < '2011-04-20'
then '2011-04-14'
else RATE_END_DATE
end,
case when rate_start_date > '2011-04-14'
then '2011-04-20'
else rate_start_date
end
) AS days FROM room_rates
WHERE room_type = 'DBLMS' AND rate_start_date <= '2011-04-20'
AND RATE_END_DATE > '2011-04-14'
June 26, 2011 at 9:02 am
I did a quickie and this is what I get :
room_type(No column name)(No column name)ratedays
DBLMS2011-04-14 00:00:00.0002011-04-16 00:00:00.000104,00-101
DBLMS2011-04-16 00:00:00.0002011-04-20 00:00:00.000122,00-5
????
June 26, 2011 at 10:37 am
Changed :
else RATE_START_DATE
end,
case when rate_start_date > '2011-04-14'
then '2011-04-20'
else RATE_END_DATE
end
but how do I get column names ???
June 26, 2011 at 11:58 am
No,this query does not work !
June 26, 2011 at 5:55 pm
To get column Name simply use As clause
case when rate_start_date < '2011-04-14'
then '2011-04-14'
else rate_start_date
end As "Rate Start Date"
Result matches with what you want. You need to change dates & "<" / ">" signs according to your requirements.
June 26, 2011 at 7:58 pm
"Result matches with what you want. You need to change dates & "<" / ">" signs according to your requirements."
this is the hard part ....i cant seem to make this query work.
gives me funny results...
And to think of it...all I ever want is the rates between 2 given dates.
I will check this once more...
Thank you very much for your reply...
June 26, 2011 at 10:01 pm
This will give you result you want
use HIS
SELECT
room_type,
case when rate_start_date < '2011-04-14'
then '2011-04-14'
else rate_start_date
end As "Rate Start Date"
,Case when RATE_END_DATE > '2011-04-20'
then '2011-04-20'
else RATE_END_DATE
end As "Rate End Date"
,rate
, DATEDIFF(DAY,
case when RATE_start_DATE < '2011-04-14'
then '2011-04-14'
else RATE_start_DATE
end
, case when RATE_End_DATE > '2011-04-20'
then '2011-04-20'
else RATE_End_DATE
end) AS days FROM room_rates
WHERE room_type = 'DBLMS' AND rate_start_date <= '2011-04-20'
AND RATE_END_DATE > '2011-04-14'
room_typeRate Start DateRate End Dateratedays
DBLMS2011-04-14 00:00:00.0002011-04-16 00:00:00.000104.002
DBLMS2011-04-16 00:00:00.0002011-04-20 00:00:00.000122.004
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply