CREATE PROCEDURE dbo.ArrangementLinkWares @AStartDateTime DATETIME, @AEndDateTime DATETIME, @AHotelNumber INT AS
/*
  Created by Kent Johansen June 2015
  Return a list of linked wares grouped by recurring pattern
  Changes:
    KJ 20160114: Optimized sql.
    KJ 20150609: Fix for "Wrong number of Pieces"

*/
DECLARE @IncludeTentativeInArrReports BIT
SELECT @IncludeTentativeInArrReports = ISNULL(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'IncludeTentativeInArrReports'

SELECT * INTO TmpArr FROM (
SELECT 
ISNULL((
  SELECT SUM(ISNULL(AL.Pieces, 0))
  FROM ArrangementReservationsXtab ARXtab WITH (NOLOCK)
  INNER JOIN Reservations R WITH (NOLOCK) ON ARXtab.ReferenceNumber = R.ReferenceNumber
    AND ARXtab.GuestNumber = R.GuestNumber
    AND R.Status NOT IN (7,8)
    AND (@IncludeTentativeInArrReports = 1 OR (@IncludeTentativeInArrReports = 0 AND R.Status <> 4))
  INNER JOIN Arrangementlines AL WITH (NOLOCK) ON ARXtab.ArrangementLineNumber = AL.ArrangementLineNumber
    AND AL.WareNumber = Ware.WareNumber
    AND AL.[Date] >= @AStartDateTime
    AND AL.[Date] <= @AEndDateTime
  INNER JOIN ReservationMain RM WITH (NOLOCK) ON R.ReferenceNumber = RM.ReferenceNumber
    AND (@AHotelNumber = -1 OR (@AHotelNumber > -1 AND RM.HotelNumber = @AHotelNumber))
  WHERE 1=1
  AND (CONVERT(DATETIME, AL.Date, 112) + CONVERT(DATETIME, AL.Time, 108) BETWEEN RecurringPatternSpecified.StartDateTime AND DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime) OR
  CONVERT(DATETIME, AL.Date, 112) + CONVERT(DATETIME, AL.DepartureTime, 108) BETWEEN RecurringPatternSpecified.StartDateTime AND DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime))
), 0) AS Pieces, 
RecurringPatternSpecified.StartDateTime, DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime) AS EndDateTime, Ware.WareLinkNumber, Ware.WareNumber, Ware.WareName, -1 AS ArrangementLineNumber
FROM RecurringPatternSpecified WITH (NOLOCK)
INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.FK_RecurringPatternId = RecurringPatternSpecified.FK_RecurringPatternId
INNER JOIN RecurringRule WITH (NOLOCK) ON RecurringPatternSpecified.FK_RecurringPatternID = RecurringRule.FK_RecurringPatternID
  AND RecurringRule.RuleType = 4 -- Number for sale
INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPattern.Id = RecurringPatternSpecified.FK_RecurringPatternID
INNER JOIN Ware WITH (NOLOCK) ON Ware.WareLinkNumber = WareLinkGroup.Id
INNER JOIN WareGroups WITH (NOLOCK) ON Ware.WareGroupNumber = WareGroups.WareGroupNumber
  AND WareGroups.WareGroupNumber IN (8)
WHERE 1=1
AND RecurringPatternSpecified.StartDateTime BETWEEN @AStartDateTime AND @AStartDateTime+1
AND ISNULL(RecurringPatternSpecified.Deleted, 0) = 0

UNION

SELECT DISTINCT
ArrangementLines.Pieces, 
CONVERT(datetime, ArrangementLines.Date, 112), CONVERT(datetime, ArrangementLines.Date, 112), Ware.WareLinkNumber, Ware.WareNumber, Ware.WareName, ArrangementLines.ArrangementLineNumber
FROM ArrangementReservationsXtab WITH (nolock) 
INNER JOIN RESERVATIONS WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = RESERVATIONS.ReferenceNumber
  AND ArrangementReservationsXtab.GuestNumber = RESERVATIONS.GuestNumber
INNER JOIN ArrangementLines WITH (nolock) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber 
INNER JOIN Ware WITH (nolock) ON ArrangementLines.WareNumber = Ware.WareNumber 
  AND ISNULL(Ware.WareLinkNumber, 0) > 0 
INNER JOIN ReservationMain WITH (nolock) ON ReservationMain.ReferenceNumber = Reservations.Referencenumber
INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.Id = Ware.WareLinkNumber
  AND ISNULL(WareLinkGroup.FK_RecurringPatternId, 0) = 0
  AND Status NOT IN (7,8)
  AND (@IncludeTentativeInArrReports = 1 OR (@IncludeTentativeInArrReports = 0 AND Status <> 4) )
WHERE ArrangementLines.Date>=@AStartDateTime  AND Arrangementlines.Date<=@AEndDateTime
  AND (@AHotelNumber = -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))
--GROUP BY CONVERT(datetime, ArrangementLines.Date, 112), CONVERT(datetime, ArrangementLines.Date, 112), Ware.WareLinkNumber, Ware.WareNumber, Ware.WareName, ArrangementLines.ArrangementLineNumber


) AS tmp
SELECT SUM(ISNULL(Pieces, 0)) AS Pieces, StartDateTime, DATEADD(millisecond, -3, EndDateTime) AS EndDateTime, WareLinkNumber, WareNumber, WareName
FROM TmpArr
GROUP BY StartDateTime, DATEADD(millisecond, -3, EndDateTime), WareLinkNumber, WareNumber, WareName
DROP TABLE TmpArr