Run the below query on WebCheckin database.
DECLARE
@FromDate VARCHAR(12)='20210706',
@ToDate VARCHAR(12) ='20210706'
CREATE TABLE #Dates
(
ID INT IDENTITY(1,1) PRIMARY KEY ,
[ReportDate] DATETIME
)
INSERT INTO #Dates (ReportDate)
SELECT CONVERT(DATETIME,[Date]) from Datedimension WHERE [Date] BETWEEN @FromDate AND @ToDate
SELECT
D.ReportDate ReportDate,RD.ReservationNumber,
COUNT(DISTINCT CASE WHEN ArrivalDate = D.ReportDate AND RD.StatusDescription NOT IN ('CANCELED','RESERVED') THEN RD.ReservationNameID END )Arrivals,
COUNT(DISTINCT CASE WHEN ArrivalDate = D.ReportDate AND ProcessType='Precheckinemail' THEN PT.ReservationNameID END) PrecheckinEmailSent ,
COUNT(DISTINCT CASE WHEN ArrivalDate = D.ReportDate AND ProcessType='PreCheckedInFetched' THEN PT.ReservationNameID END) PreCheckInSuccess,
COUNT(DISTINCT CASE WHEN DepartureDate = D.ReportDate THEN RD.ReservationNameID END )Departure,
COUNT(DISTINCT CASE WHEN DepartureDate = D.ReportDate AND ProcessType='Precheckoutemail' THEN PT.ReservationNameID END) PrecheckoutEmailsent,
COUNT(DISTINCT CASE WHEN DepartureDate = D.ReportDate AND ProcessType='PreCheckedOutFetched' THEN PT.ReservationNameID END) PrecheckoutSuccess,
COUNT(CASE WHEN RT.RoomUpgrade >0 THEN 1 END) RoomUpgrade,
COUNT(CASE WHEN RT.PackageUpgrade >0 THEN 1 END) PackageUpgrade
FROM tbReservationDetails RD
LEFT JOIN tbProcessTracking PT ON PT.ReservationNameID =RD.ReservationNameID
--FROM tbProcessTracking PT
--INNER JOIN (SELECT DISTINCT ReservationDetailID,ReservationNameID,ArrivalDate,DepartureDate FROM tbReservationDetails ) RD ON RD.ReservationNameID =PT.ReservationNameID
INNER JOIN #Dates D ON ( D.ReportDate = RD.ArrivalDate OR D.ReportDate = RD.DepartureDate)
LEFT JOIN
(
SELECT
RD.ReservationDetailID,
COUNT(CASE WHEN (ReqStatus = 1 AND IsApproved = 0) THEN 1 END) RoomUpgrade,
COUNT(CASE WHEN (ReqStatus = 0 AND IsApproved = 1) THEN 1 END) PackageUpgrade
FROM tbRequestDetails RD
INNER JOIN tbRequestTypeMaster RM ON RM.RequestTypeMasterID =RD.RequestTypeMasterID
WHERE RD.RequestType='Upsell Request'
GROUP BY RD.ReservationDetailID
)RT ON RT.ReservationDetailID =RD.ReservationDetailID
WHERE RD.StatusDescription <>'CANCELED' AND
CASE WHEN ArrivalDate = D.ReportDate AND RD.StatusDescription NOT IN ('CANCELED','RESERVED') THEN 1 END = 1
GROUP BY D.ReportDate,RD.ReservationNumber
DROP TABLE #Dates