· 6 years ago · Aug 02, 2019, 03:42 PM
1DROP TABLE IF EXISTS #LedgerTransactionTypeReportingCategoryMatching
2CREATE TABLE #LedgerTransactionTypeReportingCategoryMatching
3(
4 LedgerTransactionTypeId INT PRIMARY KEY,
5 ReportingCategoryId INT,
6)
7
8declare @Deposit INT = (select rc.ReportingCategoryId from dbo.ReportingCategory rc where rc.ReportingCategoryDesc = 'Deposit')
9declare @Withdraw INT = (select rc.ReportingCategoryId from dbo.ReportingCategory rc where rc.ReportingCategoryDesc = 'Withdraw')
10
11INSERT INTO #LedgerTransactionTypeReportingCategoryMatching
12VALUES --@Deposit
13 (11, @Deposit),
14 (12, @Deposit),
15 (25, @Deposit),
16 (26, @Deposit),
17 (27, @Deposit),
18 (28, @Deposit),
19 (29, @Deposit),
20 (30, @Deposit),
21 (31, @Deposit),
22 (32, @Deposit),
23 (33, @Deposit),
24 (34, @Deposit),
25 (35, @Deposit),
26 (36, @Deposit),
27 (37, @Deposit),
28 (38, @Deposit),
29 (39, @Deposit),
30 (40, @Deposit),
31 (41, @Deposit),
32 (42, @Deposit),
33 (43, @Deposit),
34 (44, @Deposit),
35 (45, @Deposit),
36 (46, @Deposit),
37 (50, @Deposit),
38 (51, @Deposit),
39 (52, @Deposit),
40 (53, @Deposit),
41 (79, @Deposit),
42 (82, @Deposit),
43 (83, @Deposit),
44 (84, @Deposit),
45 (86, @Deposit),
46 (87, @Deposit),
47 (88, @Deposit),
48 (89, @Deposit),
49 (90, @Deposit),
50 (91, @Deposit),
51 (92, @Deposit),
52 (93, @Deposit),
53 (228, @Deposit),
54 (230, @Deposit),
55 (232, @Deposit),
56 (233, @Deposit),
57 (234, @Deposit),
58 (235, @Deposit),
59 (236, @Deposit),
60 (237, @Deposit),
61 (238, @Deposit),
62 (239, @Deposit),
63 (240, @Deposit),
64 (242, @Deposit),
65 (243, @Deposit),
66 (244, @Deposit),
67 (245, @Deposit),
68 (246, @Deposit),
69 (251, @Deposit),
70 (268, @Deposit),
71 (269, @Deposit),
72 (271, @Deposit),
73 (272, @Deposit),
74 (273, @Deposit),
75 (274, @Deposit),
76 (275, @Deposit),
77 (276, @Deposit),
78 (337, @Deposit),
79 (338, @Deposit),
80 (343, @Deposit),
81 (344, @Deposit),
82 --@Withdraw
83 (62, @Withdraw),
84 (63, @Withdraw),
85 (64, @Withdraw),
86 (65, @Withdraw),
87 (66, @Withdraw),
88 (67, @Withdraw),
89 (68, @Withdraw),
90 (69, @Withdraw),
91 (70, @Withdraw),
92 (71, @Withdraw),
93 (72, @Withdraw),
94 (73, @Withdraw),
95 (74, @Withdraw),
96 (75, @Withdraw),
97 (76, @Withdraw),
98 (77, @Withdraw),
99 (102, @Withdraw),
100 (103, @Withdraw),
101 (104, @Withdraw),
102 (105, @Withdraw),
103 (107, @Withdraw),
104 (108, @Withdraw),
105 (109, @Withdraw),
106 (110, @Withdraw),
107 (111, @Withdraw),
108 (112, @Withdraw),
109 (113, @Withdraw),
110 (114, @Withdraw),
111 (115, @Withdraw),
112 (116, @Withdraw),
113 (117, @Withdraw),
114 (213, @Withdraw),
115 (215, @Withdraw),
116 (229, @Withdraw),
117 (231, @Withdraw),
118 (252, @Withdraw),
119 (270, @Withdraw),
120 (339, @Withdraw),
121 (340, @Withdraw)
122GO
123
124 MERGE dbo.LedgerTransactionTypeReportingCategoryTest AS lttrct
125 USING #LedgerTransactionTypeReportingCategoryMatching AS matchingSource
126 ON (lttrct.LedgerTransactionTypeId = matchingSource.LedgerTransactionTypeId)
127
128 WHEN MATCHED AND lttrct.ReportingCategoryId = 2 THEN
129 UPDATE SET NewReportingCategoryId = matchingSource.ReportingCategoryId
130 WHEN NOT MATCHED THEN
131 INSERT (LedgerTransactionTypeId, ReportingCategoryId, NewReportingCategoryId)
132 VALUES (matchingSource.LedgerTransactionTypeId, matchingSource.ReportingCategoryId, '!!')
133
134 OUTPUT $action AS [Action], Inserted.LedgerTransactionTypeId,
135 Inserted.ReportingCategoryId,
136 Inserted.NewReportingCategoryId,
137 Deleted.LedgerTransactionTypeId,
138 Deleted.NewReportingCategoryId;
139
140
141
142 select * from dbo.LedgerTransactionTypeReportingCategoryTest
143
144 select * from dbo.LedgerTransaction lt
145 where lt.LedgerTransactionTypeId = 50