July 3, 2012 at 6:15 am
I haad written a query using dense_rank() it returns the following table
HotelID RoomID AgencyID Date Rate DenseRank
1 1 1 2012-07-03 1000 1
1 1 2 2012-07-04 2000 2
1 1 3 2012-07-04 1000 2
1 1 1 2012-07-05 5000 3
2 20 1 2012-07-03 1000 1
10 2 1 2012-07-03 500 1
10 2 1 2012-07-04 2500 2
10 2 1 2012-07-05 1500 3
3 30 2 2012-07-04 5500 1
3 30 1 2012-07-05 4500 2
After creating the rank i want to filter the data based on DENSE_RANK() which has Rank 1,2,3
i want the following results
HotelID RoomID AgencyID Date Rate DenseRank
1 1 1 2012-07-03 1000 1
1 1 2 2012-07-04 2000 2
1 1 3 2012-07-04 1000 2
1 1 1 2012-07-05 5000 3
10 2 1 2012-07-03 500 1
10 2 1 2012-07-04 2500 2
10 2 1 2012-07-05 1500 3
here HotelID = 2 &3 are excluded as i am looking for Date = 03,04,05.
If there is Data for this dates it will return only those data.
so i am using Dense _Rank based on this i am ranking
Any help?
July 3, 2012 at 6:21 am
wrap your initial query up into a CTE, then select from the CTE where rank in (1,2,3)
July 3, 2012 at 6:25 am
it will not always be(1,2,3)
July 3, 2012 at 6:27 am
then just change the query to match the rank id's you are after.
July 3, 2012 at 7:29 am
dilipd006 (7/3/2012)
I haad written a query using dense_rank() it returns the following table
HotelID RoomID AgencyID Date Rate DenseRank
1 1 1 2012-07-03 1000 1
1 1 2 2012-07-04 2000 2
1 1 3 2012-07-04 1000 2
1 1 1 2012-07-05 5000 3
2 20 1 2012-07-03 1000 1
10 2 1 2012-07-03 500 1
10 2 1 2012-07-04 2500 2
10 2 1 2012-07-05 1500 3
3 30 2 2012-07-04 5500 1
3 30 1 2012-07-05 4500 2
After creating the rank i want to filter the data based on DENSE_RANK() which has Rank 1,2,3
i want the following results
HotelID RoomID AgencyID Date Rate DenseRank
1 1 1 2012-07-03 1000 1
1 1 2 2012-07-04 2000 2
1 1 3 2012-07-04 1000 2
1 1 1 2012-07-05 5000 3
10 2 1 2012-07-03 500 1
10 2 1 2012-07-04 2500 2
10 2 1 2012-07-05 1500 3
here HotelID = 2 &3 are excluded as i am looking for Date = 03,04,05.
If there is Data for this dates it will return only those data.
so i am using Dense _Rank based on this i am ranking
Any help?
Your requirement is not very clear
From whatever I could understand from your question and the previous responses, here is what I think you want
SELECT*
FROMYourCTE CTE
WHERE EXISTS ( SELECT * FROM YourCTE CTEInn WHERE CTEInn.HotelId = yourCTE.HotelId AND CTEInn.DenseRank = 3 )
If this is not what you want, please post the DDL and the sample data in a readily usable format so that people can come up with tested solutions
Please check the link given in my signature if you are not aware of how to do this.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 3, 2012 at 8:12 am
Her is the sample data
I am searching hotel room for the specified checkin and check out date.
If we r searchinhg for room between 2012-07-03 and 2012-07-06,
then it will bring those rooms which has availabilty and rate in the following dates
2012-07-03,2012-07-04,2012-07-05
we have look the dat in these date only not in checkout date
DECLARE @Hotel TABLE(HotelID int,HotelName varchar(150))
INSERT INTO @Hotel (HotelID,HotelName)
SELECT 1,'Kingston hotel'
UNION ALL
SELECT 2,'Sydney hotel'
UNION ALL
SELECT 3,'London hotel'
SELECT * FROM @Hotel
DECLARE @Agency TABLE (AgencyID int,AgencyName varchar(150))
INSERT INTO @Agency (AgencyID,AgencyName)
SELECT 1,'ABC'
UNION ALL
SELECT 2,'XYZ'
SELECT * FROM @Agency
DECLARE @Room Table(RoomID int,HotelID int,RoomName varchar(150))
INSERT INTO @Room (RoomID,HotelID,RoomName)
SELECT 1,1,'Kingston Single'
UNION ALL
SELECT 2,1,'Kingston Double'
UNION ALL
SELECT 3,2,'Sydney Double'
UNION ALL
SELECT 4,3,'London Single'
SELECT * FROM @Room
DECLARE @RoomAvailability TABLE (RoomAvailabilityID int,RoomID int,AgencyID int,AvailabilityDate date, NoOfRooms int)
INSERT INTO @RoomAvailability (RoomAvailabilityID,RoomID,AgencyID,AvailabilityDate,NoOfRooms)
SELECT 1,1,1,'2012-07-03',10--AgencyID=1,RoomID=1,Date=03
UNION ALL
SELECT 2,1,2,'2012-07-03',10--AgencyID=2,RoomID=1,Date=03
UNION ALL
SELECT 3,1,1,'2012-07-04',10--AgencyID=1,RoomID=1,Date=04
UNION ALL
SELECT 4,1,2,'2012-07-04',10--AgencyID=2,RoomID=1,Date=04
UNION ALL
SELECT 5,1,1,'2012-07-05',10--AgencyID=1,RoomID=1,Date=05
UNION ALL
SELECT 6,1,2,'2012-07-05',10--AgencyID=2,RoomID=1,Date=05
UNION ALL
SELECT 7,4,1,'2012-07-03',10--AgencyID=1,RoomID=4,Date=03
UNION ALL
SELECT 8,4,1,'2012-07-04',10--AgencyID=1,RoomID=4,Date=04
UNION ALL
SELECT 9,4,1,'2012-07-05',10--AgencyID=1,RoomID=4,Date=04
UNION ALL
SELECT 10,3,2,'2012-07-03',10--AgencyID=2,RoomID=3,Date=03
UNION ALL
SELECT 11,3,2,'2012-07-04',10--AgencyID=2,RoomID=3,Date=04
SELECT * FROM @RoomAvailability
DECLARE @RoomRate TABLE (RoomRateID int,RoomID int,AgencyID int,RateDate date, Rate numeric(9,3))
INSERT INTO @RoomRate(RoomRateID,RoomID,AgencyID,RateDate,Rate)
SELECT 1,1,1,'2012-07-03',1000--AgencyID=1,RoomID=1,Date=03
UNION ALL
SELECT 2,1,2,'2012-07-03',2000--AgencyID=2,RoomID=1,Date=03
UNION ALL
SELECT 3,1,1,'2012-07-04',2000--AgencyID=1,RoomID=1,Date=04
UNION ALL
SELECT 4,1,2,'2012-07-04',1000--AgencyID=2,RoomID=1,Date=04
UNION ALL
SELECT 5,1,1,'2012-07-05',1000--AgencyID=1,RoomID=1,Date=05
UNION ALL
SELECT 6,1,2,'2012-07-05',1000--AgencyID=2,RoomID=1,Date=05
UNION ALL
SELECT 7,4,1,'2012-07-03',2000--AgencyID=1,RoomID=4,Date=03
UNION ALL--RoomId =4 data is not inserted for date =04
SELECT 8,4,1,'2012-07-05',2000--AgencyID=1,RoomID=4,Date=05
UNION ALL
SELECT 9,3,1,'2012-07-03',3000--AgencyID=1,RoomID=3,Date=03
UNION ALL
SELECT 10,3,2,'2012-07-03',2000--AgencyID=2,RoomID=3,Date=03
UNION ALL
SELECT 11,3,1,'2012-07-04',1000--AgencyID=1,RoomID=3,Date=04
UNION ALL
SELECT 12,3,2,'2012-07-04',1000--AgencyID=2,RoomID=3,Date=04
SELECT * FROM @RoomRate
DECLARE @Checkin date = '20120703'
DECLARE @Checkout date = '20120705'
DECLARE @diff tinyint
SELECT @diff = DATEDIFF(DAY,@Checkin,@Checkout)--+ 1
SELECT @diff As datedifference
SELECT
DENSE_RANK() OVER(PARTITION BY H.HotelID,R.RoomID ORDER BY A.AvailabilityDate) AS DenseRank,
H.HotelID,
HotelName,
R.RoomID,
R.RoomName,
A.AgencyID,
A.AvailabilityDate,
A.NoOfRooms,
Ra.Rate
FROM @Hotel AS H
JOIN @Room AS R
ON H.HotelID = R.HotelID
JOIN @RoomAvailability AS A
ON R.RoomID = A.RoomID
JOIN @RoomRate AS Ra
ON A.RoomID = Ra.RoomID AND A.AvailabilityDate = Ra.RateDate AND A.AgencyID = Ra.AgencyID
WHERE(A.AvailabilityDate >= @Checkin AND A.AvailabilityDate < @Checkout)
this is the result coming
DenseRankHotelIDHotelNameRoomIDRoomNameAgencyIDAvailabilityDateNoOfRoomsRate
11Kingston hotel1Kingston Single12012-07-03101000.000
11Kingston hotel1Kingston Single22012-07-03102000.000
21Kingston hotel1Kingston Single12012-07-04102000.000
21Kingston hotel1Kingston Single22012-07-04101000.000
12Sydney hotel3Sydney Double22012-07-03102000.000
22Sydney hotel3Sydney Double22012-07-04101000.000
13London hotel4London Single12012-07-
Expected result
remove the last row as there is only data from 2012-07-03
As i searched the data between 03 and 05
i am taking the date difference as for this result it is 2
so i am trying to compare this with dense_Rank to get all the data which has dense_rank 1 and 2
any info need pls let me know
July 3, 2012 at 8:45 am
Good work with the test data
I hope the below query helps
DECLARE @Checkin date = '20120703'
DECLARE @Checkout date = '20120705'
DECLARE @diff tinyint
SELECT @diff = DATEDIFF(DAY,@Checkin,@Checkout)--+ 1
SELECT @diff As datedifference
; WITH cte_Rooms AS
(
SELECT
DENSE_RANK() OVER(PARTITION BY H.HotelID,R.RoomID ORDER BY A.AvailabilityDate) AS DenseRank,
H.HotelID,
HotelName,
R.RoomID,
R.RoomName,
A.AgencyID,
A.AvailabilityDate,
A.NoOfRooms,
Ra.Rate
FROM @Hotel AS H
JOIN @Room AS R
ON H.HotelID = R.HotelID
JOIN @RoomAvailability AS A
ON R.RoomID = A.RoomID
JOIN @RoomRate AS Ra
ON A.RoomID = Ra.RoomID AND A.AvailabilityDate = Ra.RateDate AND A.AgencyID = Ra.AgencyID
WHERE(A.AvailabilityDate >= @Checkin AND A.AvailabilityDate < @Checkout)
)
SELECT*
FROMcte_Rooms R
WHEREEXISTS ( SELECT * FROM cte_Rooms RInn WHERE R.HotelID = RInn.HotelID AND RInn.DenseRank = @diff )
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 6, 2012 at 8:29 am
thanks Kingston for the solution
July 17, 2012 at 5:54 am
For this set of data the results failed
I didn't set the rate for single room on O2/07/2012 but availability is there
so it shouldnot come to the result
And i set the rate and availability for only one agency
DECLARE @Hotel TABLE(HotelID int,HotelName varchar(150))
INSERT INTO @Hotel (HotelID,HotelName)
SELECT 1,'Kingston hotel'
--UNION ALL
--SELECT 2,'Sydney hotel'
--UNION ALL
--SELECT 3,'London hotel'
--SELECT * FROM @Hotel
DECLARE @Agency TABLE (AgencyID int,AgencyName varchar(150))
INSERT INTO @Agency (AgencyID,AgencyName)
SELECT 1,'ABC'
--UNION ALL
--SELECT 2,'XYZ'
--SELECT * FROM @Agency
DECLARE @Room Table(RoomID int,HotelID int,RoomName varchar(150))
INSERT INTO @Room (RoomID,HotelID,RoomName)
SELECT 1,1,'Kingston Single'
UNION ALL
SELECT 2,1,'Kingston Double'
--UNION ALL
--SELECT 3,2,'Sydney Double'
--UNION ALL
--SELECT 4,3,'London Single'
--SELECT * FROM @Room
DECLARE @RoomAvailability TABLE (RoomAvailabilityID int,RoomID int,AgencyID int,AvailabilityDate date, NoOfRooms int)
INSERT INTO @RoomAvailability (RoomAvailabilityID,RoomID,AgencyID,AvailabilityDate,NoOfRooms)
SELECT 1,1,1,'2012-07-01',10--AgencyID=1,RoomID=1,Date=03
UNION ALL
SELECT 2,1,1,'2012-07-02',10--AgencyID=2,RoomID=1,Date=03
UNION ALL
SELECT 3,1,1,'2012-07-03',10--AgencyID=1,RoomID=1,Date=04
UNION ALL
SELECT 4,2,1,'2012-07-01',10--AgencyID=2,RoomID=1,Date=04
UNION ALL
SELECT 5,2,1,'2012-07-02',10--AgencyID=1,RoomID=1,Date=05
UNION ALL
SELECT 6,2,1,'2012-07-03',10--AgencyID=2,RoomID=1,Date=05
--UNION ALL
--SELECT 7,4,1,'2012-07-03',10--AgencyID=1,RoomID=4,Date=03
--UNION ALL
--SELECT 8,4,1,'2012-07-04',10--AgencyID=1,RoomID=4,Date=04
--UNION ALL
--SELECT 9,4,1,'2012-07-05',10--AgencyID=1,RoomID=4,Date=04
--UNION ALL
--SELECT 10,3,2,'2012-07-03',10--AgencyID=2,RoomID=3,Date=03
--UNION ALL
--SELECT 11,3,2,'2012-07-04',10--AgencyID=2,RoomID=3,Date=04
--SELECT * FROM @RoomAvailability
DECLARE @RoomRate TABLE (RoomRateID int,RoomID int,AgencyID int,RateDate date, Rate numeric(9,3))
INSERT INTO @RoomRate(RoomRateID,RoomID,AgencyID,RateDate,Rate)
SELECT 1,1,1,'2012-07-01',1000--AgencyID=1,RoomID=1,Date=03
UNION ALL
SELECT 2,1,1,'2012-07-03',2000--AgencyID=2,RoomID=1,Date=03
UNION ALL
SELECT 3,2,1,'2012-07-01',2000--AgencyID=1,RoomID=1,Date=04
UNION ALL
SELECT 4,2,1,'2012-07-02',1000--AgencyID=2,RoomID=1,Date=04
UNION ALL
SELECT 5,2,1,'2012-07-03',1000--AgencyID=1,RoomID=1,Date=05
--UNION ALL
--SELECT 6,1,2,'2012-07-05',1000--AgencyID=2,RoomID=1,Date=05
--UNION ALL
--SELECT 7,4,1,'2012-07-03',2000--AgencyID=1,RoomID=4,Date=03
--UNION ALL--RoomId =4 data is not inserted for date =04
--SELECT 8,4,1,'2012-07-05',2000--AgencyID=1,RoomID=4,Date=05
--UNION ALL
--SELECT 9,3,1,'2012-07-03',3000--AgencyID=1,RoomID=3,Date=03
--UNION ALL
--SELECT 10,3,2,'2012-07-03',2000--AgencyID=2,RoomID=3,Date=03
--UNION ALL
--SELECT 11,3,1,'2012-07-04',1000--AgencyID=1,RoomID=3,Date=04
--UNION ALL
--SELECT 12,3,2,'2012-07-04',1000--AgencyID=2,RoomID=3,Date=04
--SELECT * FROM @RoomRate
DECLARE @Checkin date = '20120701'
DECLARE @Checkout date = '20120703'
DECLARE @diff tinyint
SELECT @diff = DATEDIFF(DAY,@Checkin,@Checkout)--+ 1
SELECT @diff As datedifference
; WITH cte_Rooms AS
(
SELECT
DENSE_RANK() OVER(PARTITION BY H.HotelID,R.RoomID ORDER BY A.AvailabilityDate) AS DenseRank,
H.HotelID,
HotelName,
R.RoomID,
R.RoomName,
A.AgencyID,
A.AvailabilityDate,
A.NoOfRooms,
Ra.Rate
FROM @Hotel AS H
JOIN @Room AS R
ON H.HotelID = R.HotelID
JOIN @RoomAvailability AS A
ON R.RoomID = A.RoomID
JOIN @RoomRate AS Ra
ON A.RoomID = Ra.RoomID AND A.AvailabilityDate = Ra.RateDate AND A.AgencyID = Ra.AgencyID
WHERE(A.AvailabilityDate >= @Checkin AND A.AvailabilityDate < @Checkout)
)
SELECT*
FROMcte_Rooms R
WHEREEXISTS ( SELECT * FROM cte_Rooms RInn WHERE R.HotelID = RInn.HotelID AND RInn.DenseRank = @diff )
This is the result
DenseRankHotelIDHotelNameRoomIDRoomNameAgencyIDAvailabilityDateNoOfRoomsRate
11Kingston hotel1Kingston Single12012-07-01101000.000
11Kingston hotel2Kingston Double12012-07-01102000.000
21Kingston hotel2Kingston Double12012-07-02101000.000
expected results
I don't want the first row as there is no rate for that room on 02
AS i am searching room on this range 01 to 03 ..so i want room which has availabilty and rate on 01,02.
July 17, 2012 at 6:19 am
You can change the query as below
SELECT*
FROMcte_Rooms R
WHEREEXISTS ( SELECT * FROM cte_Rooms RInn WHERE R.HotelID = RInn.HotelID AND R.RoomID = RInn.RoomID AND RInn.DenseRank = @diff )
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 17, 2012 at 6:23 am
Thanks kingston for the solution
Let me check with samples data
Thanks oncce again
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply