· 7 years ago · Dec 24, 2018, 10:38 AM
1-- Duplicate AR batch number(s):
2select * from zzrbatch
3 where Batch_Num in (
4select Batch_Num from zzrbatch group by Batch_Num having count(*) > 1)
5order by 1
6
7select 'zzrbatcd' as Tbl, Batch_Num, Check_Trn, Customer, * from zzrbatcd
8 where Batch_Num in (
9select Batch_Num from zzrbatch group by Batch_Num having count(*) > 1)
10 order by 1, 2, 3
11
12select 'zzrpaidd' as Tbl, Batch_Num, Doc_num, Doc_Type, Division, Check_Trn, Customer, Doc_Amt, Paid_Amt, *
13 from zzrpaidd
14 where Batch_Num in (
15select Batch_Num from zzrbatch group by Batch_Num having count(*) > 1)
16 order by 2,3,4,5, 6
17
18select 'zzrchrgd' as Tbl, Batch_Num, Doc_num, Doc_Type, Division, Check_Trn, Customer, Doc_Amt, Paid_Amt, *
19 from zzrchrgd
20 where Batch_Num in (
21select Batch_Num from zzrbatch group by Batch_Num having count(*) > 1)
22 order by 2,3,4,5, 6
23
24select 'zzrwoffd' as Tbl, Batch_Num, Doc_num, Doc_Type, Division, Check_Trn, Customer, Doc_Amt, Paid_Amt, *
25 from zzrwoffd
26 where Batch_Num in (
27select Batch_Num from zzrbatch group by Batch_Num having count(*) > 1)
28 order by 2,3,4,5, 6
29
30select 'zzrchckd' as Tbl, Batch_Num, Doc_num, Doc_Type, Division, Check_Trn, Customer, Doc_Amt, Paid_Amt, *
31 from zzrchckd
32 where Batch_Num in (
33select Batch_Num from zzrbatch group by Batch_Num having count(*) > 1)
34 order by 2, 3,4,5, 6
35
36====================
37---1 backups:
38Print '
39zzrbatch:'
40select * into zzrbatch_20180823_Dup
41 from zzrbatch
42 where Batch_Num in (
43select Batch_Num from zzrbatch group by Batch_Num having count(*) > 1)
44order by 1
45
46if exists ( select null
47 from zzrbatcd
48 where Batch_Num in (select Batch_Num from zzrbatch_20180823_Dup))
49begin
50Print '
51zzrbatcd:'
52select * into zzrbatcd_20180823_Dup
53 from zzrbatcd
54 where Batch_Num in (
55select Batch_Num from zzrbatch group by Batch_Num having count(*) > 1)
56end
57
58
59if exists ( select null
60 from zzrpaidd
61 where Batch_Num in (
62select Batch_Num from zzrbatch_20180823_Dup))
63begin
64Print '
65zzrpaidd:'
66select * into zzrpaidd_20180823_Dup
67 from zzrpaidd
68 where Batch_Num in (
69select Batch_Num from zzrbatch group by Batch_Num having count(*) > 1)
70 order by 2,3,4,5, 6
71end
72
73if exists ( select null
74 from zzrchrgd
75 where Batch_Num in (
76select Batch_Num from zzrbatch_20180823_Dup))
77begin
78Print '
79zzrchrgd:'
80select * into zzrchrgd_20180823_Dup
81 from zzrchrgd
82 where Batch_Num in (
83select Batch_Num from zzrbatch group by Batch_Num having count(*) > 1)
84end
85
86if exists ( select null
87 from zzrwoffd
88 where Batch_Num in (
89select Batch_Num from zzrbatch_20180823_Dup))
90begin
91Print '
92zzrwoffd:'
93select * into zzrwoffd_20180823_Dup
94 from zzrwoffd
95 where Batch_Num in (
96select Batch_Num from zzrbatch group by Batch_Num having count(*) > 1)
97end
98
99if exists ( select null
100 from zzrchckd
101 where Batch_Num in (
102select Batch_Num from zzrbatch_20180823_Dup))
103begin
104Print '
105zzrchckd:'
106select * into zzrchckd_20180823_Dup
107 from zzrchckd
108 where Batch_Num in (
109select Batch_Num from zzrbatch group by Batch_Num having count(*) > 1)
110end
111
112
113
114zzrbatch:
115(9 row(s) affected)
116
117zzrbatcd:
118(9 row(s) affected)
119
120zzrpaidd:
121(5365 row(s) affected)
122
123zzrwoffd:
124(21 row(s) affected)
125
126select * from zzrbatch_20180823_Dup order by Batch_num
127select check_trn, * from zzrbatcd_20180823_Dup order by Batch_num, 1
128
129select check_trn as ChkTr, h.PKey as PK, * from zzrbatch_20180823_Dup h
130 left join zzrbatcd_20180823_Dup d
131 on d.Fkey = h.PKey
132 order by d.Batch_num, 1
133
134select * from zzrpaidd_20180823_Dup
135select * from zzrwoffd_20180823_Dup
136
137-- find a hole:
138select top 1 Hole = Batch_Num + 1
139 , MaxBatch = (select Max(Batch_num) from zzrbatch)
140 , CurBatch = (select batch_num from zzxcompr)
141 from zzrbatch b
142 where Batch_Num >= 9674 -- (select Min(Batch_Num) from zzrbatch_20180823_Dup)
143 and not exists (
144select null from zzrbatch n where n.Batch_Num = b.Batch_Num + 1)
145 order by 1
146
147============================
148== Duplicates Delete
149select Pkey, * from zzrbatch_20181116_Dup
150select FKey, check_trn, * from zzrbatcd_20181116_Dup
151select check_trn, * from ZZRWOFFD_20181116_DUP
152
153
154begin tran
155delete from zzrbatch where batch_num =10435 and Pkey = 14664
156delete from zzrbatcd where batch_num =10435 and Fkey = 14664 and check_Trn = 685358
157delete from ZZRWOFFD where batch_num =10435 and check_Trn = 685358
158(1 row(s) affected)
159
160(1 row(s) affected)
161
162(2 row(s) affected)
163commit
164
165== Assign new Batch Num:
166============================
167
168declare @MaxBatch int, @i int = 0
169select @MaxBatch = Max(Batch_num) from zzrbatch
170
171create table batch_Dup_20180823 (old_Batch int not null, ChkTr int, new_Batch int, PK int)
172insert into batch_Dup_20180823
173select d.Batch_num, d.check_trn
174 , n = (
175 select top 1 Hole = Batch_Num + 1
176 from zzrbatch b
177 where b.Batch_Num >= h.Batch_num
178 and b.BAtch_num < @MaxBatch
179 and not exists ( select null from zzrbatch n where n.Batch_Num = b.Batch_Num + 1))
180 , h.PKey
181 from zzrbatch_20180823_Dup h
182 join zzrbatcd_20180823_Dup d
183 on d.Fkey = h.PKey
184 where exists (
185select null from zzrbatch_20180823_Dup o
186 where o.Batch_num = h.Batch_num
187 and o.PKey < h.PKey)
188 order by d.Batch_num, 2
189
190while @i < 100 and exists (
191select null from batch_Dup_20180823 d
192 where exists (
193select null from batch_Dup_20180823 p
194 where p.new_Batch = d.new_Batch
195 and p.PK < d.PK))
196begin
197update d
198 set new_Batch = (
199 select top 1 Hole = Batch_Num + 1
200 from zzrbatch b
201 where b.Batch_Num >= d.new_Batch
202 and b.Batch_num < @MaxBatch
203 and not exists ( select null from zzrbatch n where n.Batch_Num = b.Batch_Num + 1))
204 from batch_Dup_20180823 d
205 where exists (
206select null from batch_Dup_20180823 p
207 where p.new_Batch = d.new_Batch
208 and p.PK < d.PK)
209
210set @i = @i + 1
211end
212
213select * from batch_Dup_20180823
214
215===================
216-- Fix Data:
217begin tran
218select @@TranCount
219
220Print '
221zzrbatch:'
222update b
223 set Batch_num = new_Batch
224 from zzrbatch b
225 join batch_Dup_20180823 f
226 on f.PK = b.PKey
227Print '
228zzrbatcd:'
229update b
230 set Batch_num = new_Batch
231 from zzrbatcd b
232 join batch_Dup_20180823 f
233 on f.PK = b.FKey
234
235Print '
236zzrpaidd:'
237update b
238 set Batch_num = new_Batch
239 from zzrpaidd b
240 join batch_Dup_20180823 f
241 on f.old_Batch = b.Batch_num
242 and f.ChkTr = b.Check_Trn
243
244Print '
245zzrchrgd:'
246update b
247 set Batch_num = new_Batch
248 from zzrchrgd b
249 join batch_Dup_20180823 f
250 on f.old_Batch = b.Batch_num
251 and f.ChkTr = b.Check_Trn
252
253Print '
254zzrwoffd:'
255update b
256 set Batch_num = new_Batch
257 from zzrwoffd b
258 join batch_Dup_20180823 f
259 on f.old_Batch = b.Batch_num
260 and f.ChkTr = b.Check_Trn
261
262Print '
263zzrchckd:'
264update b
265 set Batch_num = new_Batch
266 from zzrchckd b
267 join batch_Dup_20180823 f
268 on f.old_Batch = b.Batch_num
269 and f.ChkTr = b.Check_Trn
270
271Dups:
272zzrbatch:
273(9 row(s) affected)
274
275zzrbatcd:
276(9 row(s) affected)
277
278zzrpaidd:
279(5365 row(s) affected)
280
281zzrwoffd:
282(21 row(s) affected)
283
284===========
285Updates:
286zzrbatch:
287(5 row(s) affected)
288
289zzrbatcd:
290(5 row(s) affected)
291
292zzrpaidd:
293(2690 row(s) affected)
294
295zzrchrgd:
296(0 row(s) affected)
297
298zzrwoffd:
299(11 row(s) affected)
300
301zzrchckd:
302(0 row(s) affected)
303
304
305
306ROLLBACK COMMIT
307
308
309=====================
310
311sp_helpIndex zzrbatch
312sp_help zzrbatch
313GO
314====================
315-- Make BC Index unique:
316begin tran
317select * from zzzddkyh where TABNAME = 'zzrbatch'
318select * from zzzddkyd where TABNAME = 'zzrbatch'
319update h set KeyName = 'uq_zzrbatch', keyType = 'ukey', FlgUnique = 'Y' from zzzddkyh h where TABNAME = 'zzrbatch' and KeyName = 'norm_zzrbatch_batch_num'
320update d set KeyName = 'uq_zzrbatch', keyType = 'ukey', FlgUnique = 'Y', keySeq = 'uq_zzrbatch.1' from zzzddkyd d where TABNAME = 'zzrbatch' and KeyName = 'norm_zzrbatch_batch_num'
321select * from zzzddkyh where TABNAME = 'zzrbatch'
322select * from zzzddkyd where TABNAME = 'zzrbatch'
323drop index NORM_ZZRBATCH_BATCH_NUM on [zzrbatch]
324create unique nonclustered index [uq_zzrbatch] on [zzrbatch] ([batch_num])
325
326rollback
327commit