November 1, 2008 at 1:25 am
Hi,
SELECT DISTINCT dbo.RoomRates.RoomID,
CASE
WHEN (dbo.RoomRates.Overflow = 1
AND MIN(dbo.RoomRates.Availability) <= 5)
THEN 5
ELSE MIN(dbo.RoomRates.Availability)
END AS Availability,
SUM( dbo.RoomRates.Price ) AS RoomTotalPrice
FROM dbo.RoomRates
WHERE dbo.RoomRates.[Date] BETWEEN @FromDate AND @ToDate
GROUP BY dbo.RoomRates.RoomID ,
dbo.RoomRates.Overflow
Input
roomid Date AvailableRooms RoomPrice FreeSale
100 01/12/08 FA 0 10 1
100 02/12/08 FA 8 10 0
100 03/12/08 FA 4 10 0
Output(needed output)
roomid Availability RoomTotalPrice
100 4 30
but what we are getting
roomid Availability RoomTotalPrice
100 5 10
100 4 20
Since Overflow data is different it groups according to Overflow data.
so we are receiving 2 records.
Thanks in advance
November 3, 2008 at 5:42 am
You don't want to group by Overflow, because in that case you get a result per overflow (1 result voor overflow=0 and 1 for overflow=1). But you can't simply leave the overflow away because you want to use the result. I don't know exactly what you want with the results but you could use an innerquery:
SELECT a.RoomID
, MIN(a.Availability)
, SUM(a.Price)
FROM (
SELECT r.RoomID
, CASE
WHEN r.Overflow = 1 AND RoomRates.Availability <= 5
THEN 5
ELSE r.Availability
END AS Availability
, r.Price
FROM RoomRates r
WHERE r.[Date] BETWEEN @FromDate AND @ToDate
) As Availabilities a
GROUP BY a.RoomID
(ps. I don't know for sure if this works in 1 time since i didn't test it. But i think you'll get the idea)
November 3, 2008 at 5:56 am
http://sql-server-performance.com/Community/forums/p/28631/152790.aspx
Failing to plan is Planning to fail
November 3, 2008 at 6:01 am
Madhivanan (11/3/2008)
http://sql-server-performance.com/Community/forums/p/28631/152790.aspx
Damn, more simple and better than mine.... i have to stop thinking the hard way 😛
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply