0

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

2
Contributors
1
Reply
2
Views
8 Years
Discussion Span
Last Post by Teme64
0

Since you have two different Availability values, you'll get two rows no matter what you do. Drop the dbo.RoomRates.Overflow = 1 check.

Or use that query as a sub query:

SELECT DISTINCT dbo.RoomRates.RoomID, MIN(Availability) As Availability, SUM(RoomTotalPrice) AS RoomTotalPrice FROM (< your original query here >)
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.