CREATE PROCEDURE dbo.GetRoomDiscountRate @AReferenceNumber INT, @AGuestNumber INT, @ASplitBillNumber INT, @ARoomDiscountRate FLOAT OUTPUT, @AAddressType INT OUTPUT AS
/*
  Created by Kent Johansen march 2015
  Changes:

*/

SET NOCOUNT ON

DECLARE @RoomWareGroupNumber INT
DECLARE @GuestType INT

SELECT TOP 1 @RoomWareGroupNumber = WareGroups.WareGroupNumber 
FROM Ware WITH (NOLOCK) 
INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
  AND WareGroups.Payment = 2
  AND ISNULL(WareGroups.MarkAsDeleted, '0') = '0'
WHERE Ware.MarkAsDeleted IS NULL 

SELECT @GuestType = GuestType 
FROM SplitBillWareGroupXTab WITH (NOLOCK) 
WHERE SplitBillNumber = @ASplitBillNumber 
AND WareGroupNumber = @RoomWareGroupNumber
IF ISNULL(@GuestType, 1) = 1 
  SET @GuestType = 1

IF (@GuestType in (2,4,6,8))
BEGIN
  SET @AGuestNumber=0
  SET @AAddressType = (@GuestType/2)
END
ELSE
BEGIN
  IF @GuestType=1
    SET @AAddressType = 1
  ELSE IF @GuestType=3
    SET @AAddressType = 2
  ELSE IF @GuestType=5
    SET @AAddressType = 3
  ELSE IF @GuestType=7
    SET @AAddressType = 4
END

SELECT @ARoomDiscountRate = RoomDiscountRate
FROM ReservationAddress WITH (NOLOCK)
INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.AddressNumber = ReservationAddress.AddressNumber
  AND ReservationAddressXtab.ReferenceNumber = @AReferenceNumber
  AND ReservationAddressXtab.GuestNumber = @AGuestNumber
  AND RESERVATIONADDRESS.AddressType = @AAddressType