· 7 years ago · Jan 04, 2019, 08:58 PM
137-529-1 1
213793-1 1
342086-1 1
413793-1 1
5
6update Parts
7set Parts.Quantity = cast(cast(Parts.quantity as int) -
8cast(SAMS_Updates.quantity as int) as varchar(100))
9from SAMS_Updates
10where Parts.SAMS_Custom_Label = SAMS_Updates.Custom_Label
11
12update p
13 set p.Quantity = p.Quantity - su.Quantity
14from parts p
15 inner join (
16 select Custom_Label, sum(cast(Quantity as int)) as Quantity
17 from SAMS_Updates
18 group by Custom_Label
19 ) su
20 on p.SAMS_Custom_Label = su.Custom_Label
21
22+-------------------+----------+
23| SAMS_Custom_Label | Quantity |
24+-------------------+----------+
25| 37-529-1 | 9 |
26| 13793-1 | 8 |
27| 42086-1 | 9 |
28+-------------------+----------+
29
30/*Create Base Tables*/
31 /*Parts Table*/
32 /*Create Table*/
33 drop table if exists #parts
34 create table #parts
35 (idParts bigint identity(1, 1) not null,
36 SAMS_Custom_Label varchar(25) primary key not null,
37 Quantity int not null default 0 check(Quantity >= 0)) --check assumes backorder logic is separate
38
39 /*Insert Values*/
40 insert into #parts
41 (SAMS_Custom_Label,
42 Quantity)
43 values ('37-529-1', 5),
44 ('13793-1', 5),
45 ('42086-1', 5)
46
47 /*Review Current Parts Quantities*/
48 select P.SAMS_Custom_Label,
49 P.Quantity
50 from #parts P
51
52 /*SAM_Updates Table*/
53 /*Create Table*/
54 drop table if exists #SAM_Updates
55 create table #SAM_Updates
56 (idSAM_Updates bigint identity(1, 1) primary key not null,
57 Custom_Label varchar(25) not null, --the #parts table should be reference for referential integrity
58 Quantity int not null check(Quantity <> 0),
59 partsQuantityUpdated bit not null default 'false') --Indicates Quantity Update was applied to #parts table
60
61 /*Insert Values*/
62 insert into #SAM_Updates
63 (Custom_Label,
64 Quantity)
65 values ('37-529-1', 1),
66 ('13793-1', 1),
67 ('42086-1', 1),
68 ('13793-1', 1)
69
70
71/*Parts Inventory Update Process*/
72 /*Setup Cursor*/
73 /*Operational parms*/
74 declare @idSAM_Updates bigint,
75 @Custom_Label varchar(25),
76 @Quantity int
77
78 /*Create & Open Cursor record set*/
79 DECLARE C CURSOR
80 FOR ( select SU.idSAM_Updates,
81 SU.Custom_Label,
82 SU.Quantity
83 from #SAM_Updates SU
84 where SU.partsQuantityUpdated = 'false') --Apply only updates that haven't been performed
85 OPEN C
86
87 /*Initiate first fetch record*/
88 FETCH NEXT
89 FROM C
90 INTO @idSAM_Updates,
91 @Custom_Label,
92 @Quantity
93
94
95 /*Perform Loop*/
96 WHILE @@FETCH_STATUS = 0
97 BEGIN
98 /*Apply difference to Parts Inventory Quantity*/
99 update P
100 set P.Quantity = P.Quantity - @Quantity
101 from #parts P
102 where P.SAMS_Custom_Label = @Custom_Label
103
104 /*Mark current SAM_Updates record as applied to #parts table*/
105 update SU
106 set SU.partsQuantityUpdated = 'true'
107 from #SAM_Updates SU
108 where SU.idSAM_Updates = @idSAM_Updates
109
110 /*Initiate next fetch record*/
111 FETCH NEXT
112 FROM C
113 INTO @idSAM_Updates,
114 @Custom_Label,
115 @Quantity
116 END
117
118 /*Close Cursor*/
119 CLOSE C
120 DEALLOCATE C
121
122
123/*Examine Results*/
124 /*Updated Parts Quantities*/
125 select P.SAMS_Custom_Label,
126 P.Quantity
127 from #parts P
128
129
130/*new records added to the #SAM_Updates table will be applied to the parts inventory each time the Parts Inventory Update Process is reran*/