troubleshooting Question

MS SQL Server Better way to do update?

Avatar of Larry Brister
Larry BristerFlag for United States of America asked on
Microsoft SQL ServerRemote Access
3 Comments1 Solution132 ViewsLast Modified:
My code below works... but it seems like I should be able to get by (Better???) without the separate update on the # temp table.

Would that be better?

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;

Open in new window

ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros