Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

SQL Server Update Query Streamline

The code below pulls data from a HUGE detail table and updates a summary table
Does the code itself look "streamlined" enough?
Should I also have
Where campaign_id = @campaign_id in the sub query?
BEGIN

		UPDATE	main
		SET	main.bounce = sub.bounce ,
			main.click = sub.click ,
			main.deferred = sub.deferred ,
			main.delivered = sub.delivered ,
			main.dropped = sub.dropped ,
			main.group_unsubscribe = sub.group_unsubscribe ,
			main.[open] = sub.[open] ,
			main.processed = sub.processed ,
			main.unsubscribe = sub.unsubscribe
		FROM	dbo.SendGridSummary main
		JOIN	(
				SELECT	Campaign_ID ,
					Customer_ID ,
					email ,
					MAX(CASE WHEN _event = 'bounce' THEN 1
								ELSE 0
						END) [bounce] ,
					MAX(CASE WHEN _event = 'click' THEN 1
								ELSE 0
						END) [click] ,
					MAX(CASE WHEN _event = 'deferred' THEN 1
								ELSE 0
						END) [deferred] ,
					MAX(CASE WHEN _event = 'delivered' THEN 1
								ELSE 0
						END) [delivered] ,
					MAX(CASE WHEN _event = 'dropped' THEN 1
								ELSE 0
						END) [dropped] ,
					MAX(CASE WHEN _event = 'group_unsubscribe' THEN 1
								ELSE 0
						END) [group_unsubscribe] ,
					MAX(CASE WHEN _event = 'open' THEN 1
								ELSE 0
						END) [open] ,
					MAX(CASE WHEN _event = 'processed' THEN 1
								ELSE 0
						END) [processed] ,
					MAX(CASE WHEN _event = 'unsubscribe' THEN 1
								ELSE 0
						END) [unsubscribe]
				FROM	SendGridStats WITH (NOLOCK)
				WHERE	summarysent = 0
				GROUP BY Campaign_ID ,
					Customer_ID ,
					email
			) sub
			ON sub.Customer_ID = main.Customer_ID
				AND sub.email = main.email
				AND sub.campaign_id = main.campaign_id
		WHERE	main.campaign_id = @campaign_id;
END;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Larry Brister

ASKER

Vitor Montalvão
That is correct. I want to know if it occurred at least once and just flag it as having occurred
Hi Irbrister,

The code looks fine to me. There are other ways also. I think you should be fine with this. Please let me know if your takes time, then will try to improve its performance.

Hope it helps!

/Pawan
Thanks