· 7 years ago · Sep 27, 2018, 08:30 PM
1USE DBAAdmin
2GO
3
4IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Unsubscribe_MessageGears_NotifyMe')
5BEGIN
6 PRINT 'Unsubscribe process was already run. Rollback is required before re-running.'
7END
8ELSE
9BEGIN
10
11
12 SELECT
13 EmailAddress,
14 S.SubscriberID,
15 NS.NotifyInformationID,
16 S.LocationID,
17 S.NotificationVehicleID,
18 MAX(SH.OperationDate) AS LastUnsubDate INTO #LastUnsubscriptions
19 FROM Pullapart_Email.dbo.SubscriberHistory SH
20 INNER JOIN Pullapart_Email.dbo.Subscriber S
21 ON SH.SubscriberID = S.SubscriberID
22 INNER JOIN Pullapart_Corp2.dbo.NotificationSubscribers NS
23 ON S.NotifyMeID = NS.NotifyInformationID
24 WHERE SH.SubscriberID = SH.SubscriberID
25 AND SH.SubscriberOperationID = 2
26 GROUP BY NS.EmailAddress,
27 NS.NotifyInformationID,
28 S.SubscriberID,
29 S.LocationID,
30 S.NotificationVehicleID
31
32
33
34 ---- VALIDATION TEST: Determine the rowcount that should be affected by your action by running something like this:
35 DECLARE @expectedRowCount int,
36 @actualRowCount int;
37
38 SELECT
39 @expectedRowCount = COUNT(*)
40 FROM (SELECT
41 NS2.EmailAddress,
42 S2.SubscriberID,
43 NS2.NotifyInformationID,
44 S2.LocationID,
45 S2.NotificationVehicleID,
46 MAX(SH2.OperationDate) AS 'LastSubDate'--,
47 --LU.LastUnsubDate
48 FROM Pullapart_Email.dbo.SubscriberHistory SH2
49 INNER JOIN Pullapart_Email.dbo.Subscriber S2
50 ON SH2.SubscriberID = S2.SubscriberID
51 INNER JOIN Pullapart_Corp2.dbo.NotificationSubscribers NS2
52 ON S2.NotifyMeID = NS2.NotifyInformationID
53 INNER JOIN #LastUnsubscriptions LU
54 ON S2.SubscriberID = LU.SubscriberID
55 WHERE SH2.SubscriberID = SH2.SubscriberID
56 AND SH2.SubscriberOperationID = 1
57 GROUP BY NS2.EmailAddress,
58 NS2.NotifyInformationID,
59 S2.SubscriberID,
60 LU.LastUnsubDate,
61 S2.LocationID,
62 S2.NotificationVehicleID
63 HAVING LU.LastUnsubDate > MAX(SH2.OperationDate)
64
65 UNION
66
67 SELECT
68 NS2.EmailAddress,
69 S2.SubscriberID,
70 NS2.NotifyInformationID,
71 S2.LocationID,
72 S2.NotificationVehicleID,
73 MAX(SH2.OperationDate) AS 'LastSubDate'--,
74 --LU.LastUnsubDate
75 FROM Pullapart_Email.dbo.SubscriberHistory SH2
76 INNER JOIN Pullapart_Email.dbo.Subscriber S2
77 ON SH2.SubscriberID = S2.SubscriberID
78 INNER JOIN Pullapart_Corp2.dbo.NotificationSubscribers NS2
79 ON S2.NotifyMeID = NS2.NotifyInformationID
80 INNER JOIN #LastUnsubscriptions LU
81 ON LOWER(NS2.EmailAddress) = LOWER(LU.EmailAddress)
82 WHERE SH2.SubscriberID = SH2.SubscriberID
83 AND SH2.SubscriberOperationID = 1
84 GROUP BY NS2.EmailAddress,
85 NS2.NotifyInformationID,
86 S2.SubscriberID,
87 LU.LastUnsubDate,
88 S2.LocationID,
89 S2.NotificationVehicleID
90 HAVING LU.LastUnsubDate > MAX(SH2.OperationDate)) Unsub
91
92
93 CREATE TABLE DBAAdmin.dbo.Unsubscribe_MessageGears_NotifyMe (
94 EmailAddress varchar(250) NOT NULL,
95 SubscriberID int NOT NULL,
96 NotifyInformationID int NOT NULL,
97 LocationID int NOT NULL,
98 NotificationVehicleID int NOT NULL,
99 LastSubDate datetime NOT NULL
100 );
101
102 INSERT INTO DBAAdmin.dbo.Unsubscribe_MessageGears_NotifyMe
103 SELECT
104 EmailAddress,
105 SubscriberID,
106 NotifyInformationID,
107 LocationID,
108 NotificationVehicleID,
109 LastSubDate
110 FROM (SELECT
111 NS2.EmailAddress,
112 S2.SubscriberID,
113 NS2.NotifyInformationID,
114 S2.LocationID,
115 S2.NotificationVehicleID,
116 MAX(SH2.OperationDate) AS 'LastSubDate'--,
117 --LU.LastUnsubDate
118 FROM Pullapart_Email.dbo.SubscriberHistory SH2
119 INNER JOIN Pullapart_Email.dbo.Subscriber S2
120 ON SH2.SubscriberID = S2.SubscriberID
121 INNER JOIN Pullapart_Corp2.dbo.NotificationSubscribers NS2
122 ON S2.NotifyMeID = NS2.NotifyInformationID
123 INNER JOIN #LastUnsubscriptions LU
124 ON S2.SubscriberID = LU.SubscriberID
125 WHERE SH2.SubscriberID = SH2.SubscriberID
126 AND SH2.SubscriberOperationID = 1
127 GROUP BY NS2.EmailAddress,
128 NS2.NotifyInformationID,
129 S2.SubscriberID,
130 LU.LastUnsubDate,
131 S2.LocationID,
132 S2.NotificationVehicleID
133 HAVING LU.LastUnsubDate > MAX(SH2.OperationDate)
134
135 UNION
136
137 SELECT
138 NS2.EmailAddress,
139 S2.SubscriberID,
140 NS2.NotifyInformationID,
141 S2.LocationID,
142 S2.NotificationVehicleID,
143 MAX(SH2.OperationDate) AS 'LastSubDate'--,
144 --LU.LastUnsubDate
145 FROM Pullapart_Email.dbo.SubscriberHistory SH2
146 INNER JOIN Pullapart_Email.dbo.Subscriber S2
147 ON SH2.SubscriberID = S2.SubscriberID
148 INNER JOIN Pullapart_Corp2.dbo.NotificationSubscribers NS2
149 ON S2.NotifyMeID = NS2.NotifyInformationID
150 INNER JOIN #LastUnsubscriptions LU
151 ON LOWER(NS2.EmailAddress) = LOWER(LU.EmailAddress)
152 WHERE SH2.SubscriberID = SH2.SubscriberID
153 AND SH2.SubscriberOperationID = 1
154 GROUP BY NS2.EmailAddress,
155 NS2.NotifyInformationID,
156 S2.SubscriberID,
157 LU.LastUnsubDate,
158 S2.LocationID,
159 S2.NotificationVehicleID
160 HAVING LU.LastUnsubDate > MAX(SH2.OperationDate)) Unsubs
161 GROUP BY Unsubs.EmailAddress,
162 Unsubs.NotifyInformationID,
163 Unsubs.SubscriberID,
164 Unsubs.LocationID,
165 Unsubs.NotificationVehicleID,
166 Unsubs.LastSubDate
167 ORDER BY Unsubs.EmailAddress
168
169 UPDATE nvl
170 SET nvl.Unsubscribe = 1
171 FROM Pullapart_Corp2.dbo.NotificationVehicleLocation nvl
172 , DBAAdmin.dbo.Unsubscribe_MessageGears_NotifyMe unsub
173 WHERE nvl.NotificationInformationID = unsub.NotifyInformationID
174 AND nvl.NotificationVehicleID = unsub.NotificationVehicleID
175 AND nvl.LocationID = unsub.LocationID
176
177 SET @actualRowCount = @@ROWCOUNT
178
179 ---- VALIDATION CONFIRMATION: If the actual number of rows affected does NOT match your Target, you ROLLBACK
180 IF @actualRowCount <> @expectedRowCount
181 BEGIN
182 ROLLBACK TRAN;
183 --Rollback OUTPUT message
184 PRINT N'Script failed. An erroneous amount of records were impacted. Expected: ' + CAST(@expectedRowCount AS varchar(30)) + N' Actual: ' + CAST(@actualRowCount AS varchar(30));
185 END;
186
187 --If the Actual Rowcount equals the Target rowcount, then you COMMIT
188 ELSE
189 BEGIN
190 COMMIT TRAN;
191 --Successful COMMIT message
192 PRINT 'Script successfully executed.';
193 END
194END