CREATE PROCEDURE dbo.MonaStatistic @AHotelCustomerNumberCSV VARCHAR(100), @AFromDate DATETIME, @AToDate DATETIME AS
/*
  Created by Kent Johansen, november 2021

*/

SELECT COUNT(*) AS cnt, Personel.UserTypeNumber, Followup.SubType2, ReservationMain.FkCustomerHotel AS HotelCustomerNumber
FROM FollowUp WITH (NOLOCK)
INNER JOIN Personel WITH (NOLOCK) ON Personel.UserIdNumber = Followup.CreatedBy
INNER JOIN ReservationFollowupXTab WITH (NOLOCK) ON ReservationFollowupXTab.FollowUpNumber = Followup.FollowupNumber
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = ReservationFollowupXTab.ReferenceNumber
  AND (ISNULL(@AHotelCustomerNumberCSV, '') = '' OR ReservationMain.FkCustomerHotel IN (SELECT Number FROM dbo.iter_intlist_to_table(@AHotelCustomerNumberCSV)))
WHERE ISNULL(Followup.SubType2, 0) > 0
AND Followup.CreatedDate BETWEEN @AFromDate AND @AToDate
GROUP BY Personel.UserTypeNumber, Followup.SubType2, ReservationMain.FkCustomerHotel

UNION

SELECT COUNT(*), Personel.UserTypeNumber, 1, Transactions.HotelCustomerNumber
FROM Transactions WITH (NOLOCK)
INNER JOIN Personel WITH (NOLOCK) ON Personel.UserIdNumber = Transactions.UserIdNumber
  AND Personel.UserTypeNumber IN (5, 8, 9, 10, 11)
INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = Transactions.WareNumber
INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
  AND Waregroups.Payment = 1 -- Payment
WHERE Transactions.ShowOnBillFlag = 1
AND Transactions.MovedDeleted = 0
AND Transactions.DateStamp BETWEEN @AFromDate AND @AToDate
AND (ISNULL(@AHotelCustomerNumberCSV, '') = '' OR Transactions.HotelCustomerNumber IN (SELECT Number FROM dbo.iter_intlist_to_table(@AHotelCustomerNumberCSV)))
GROUP BY Personel.UserTypeNumber, Transactions.HotelCustomerNumber
ORDER BY SubType2, HotelCustomerNumber