asked on
IF OBJECT_ID('tempdb..#appointments_res') IS NOT NULL
DROP TABLE #appointments_res;
;WITH cte
AS ( SELECT a.IndividualID ,
a.AppointmentID ,
a.AppointmentDate ,
a.AppointmentStartTime ,
a.AppointmentTitle ,
t.ReservationID ,
CONVERT(VARCHAR(10), t.InDate, 126) CheckinDate ,
ROW_NUMBER() OVER ( PARTITION BY a.IndividualID ,
a.AppointmentTitle
ORDER BY a.IndividualID ,
AppointmentDate DESC ) rn
FROM ewAppointments a
JOIN Individuals i ON a.IndividualID = i.IndividualID
JOIN dbo.ewSalesSitesToIndividuals s ON i.IndividualID = s.IndividualID
JOIN dbo.everyware_t_reservation t ON t.ReservationID = REVERSE(LEFT(REVERSE(a.AppointmentTitle), CHARINDEX(' ', REVERSE(a.AppointmentTitle)) - 1))
WHERE a.AppointmentDate > GETDATE() - 1
AND ( a.AppointmentTitle LIKE 'Reservation' + '%' )
AND i.Dupe = 0
AND s.Active = 1
AND s.SalesSiteID IN ( 103405, 103648 ))
SELECT *
INTO #appointments_res
FROM cte
WHERE cte.AppointmentDate <> cte.CheckinDate;
UPDATE a
SET a.AppointmentDate = aa.CheckinDate
FROM ewAppointments a
JOIN #appointments_res aa ON a.AppointmentID = aa.AppointmentID
WHERE 1 = 1;