CREATE PROCEDURE dbo.PMIPosHistory @AHotelNumber INT, @AStartDate CHAR(8), @AEndDate CHAR(8) AS
SET NOCOUNT ON
/*
  Created by Kent Johansen February 2013
  Returns the Pos info for PMI/d2o interface
  Changes:
    KJ 20160428: Optimized sql

*/

SELECT CONVERT(CHAR(8), Transactions.DateStamp, 112) AS Date,  Accounts.AccountNumber, Accounts.AccountText, 
dbo.GetVATPercent(Accounts.VATValueNumber) AS VATValue,
Transactions.FK_DepartmentNumber,
SUM(ISNULL(Transactions.PAXadult, 0) + ISNULL(Transactions.PAXchild1, 0) + ISNULL(Transactions.PAXchild2, 0)) AS Pieces,
ROUND(SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END))*-1, 4) AS Price
FROM Transactions WITH (NOLOCK)
INNER JOIN Accounts WITH (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
  AND Accounts.Balance = 1
  AND NOT Accounts.AccountType  IN (1,2)
INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = Transactions.HotelCustomerNumber
  AND Customer.HotelNumber = @AHotelNumber
/*
WHERE CONVERT(CHAR(8), Transactions.DateStamp, 112) >= @AStartDate 
AND CONVERT(CHAR(8), Transactions.DateStamp, 112) <= @AEndDate
*/
WHERE Transactions.DateStamp BETWEEN CONVERT(DATETIME, @AStartDate) AND CONVERT(DATETIME, @AEndDate + ' 23:59:59.997')
GROUP BY CONVERT(CHAR(8), Transactions.DateStamp, 112), Accounts.AccountNumber, Accounts.AccountText, Accounts.VATValueNumber, Transactions.FK_DepartmentNumber
HAVING ROUND(SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*
CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END)*-1, 3) <> 0
ORDER BY CONVERT(CHAR(8), Transactions.DateStamp, 112), Accounts.AccountNumber