Hallo
Even een achtergrond verhaal, ik ben aan het wisselen van software pakket dat wordt gebruikt om gegevens van huurders bij te houden.
Ik ben nu bezig om de transacties om te zetten tussen de systemen, daarbij moet ik rekening houden met welke kosten wel en niet zijn betaald.
Voorbeeld:
Jan heeft in februari als kosten:
Jan heeft in maart de volgende kosten
Het totaal aan kosten in deze periode is 1000 euro. het totaal betaald bedrag door jan is 870, verdeeld over 3 betalingen (1x 450, 1x 370 1x 50)
Ik moet nu deze transacties overzetten in het nieuwe systeem, maar ik moet dus rekening houden met welke rekeningen wel en niet zijn betaald. Ik kan dus niet alle rekeningen en betalingen op een hoop gooien (dat zou bijvoorbeeld betekenen dat de incidentele kosten worden betaald terwijl dit niet het geval is)
Dit is het punt waar ik een beetje moeite mee heb om dit goed SET gebaseerd te doen.
Ik heb de onderstaande query die werkt, helaas wel met een cursor.
De tabel #Transactie en #Betaling zijn onderdeel van het nieuwe systeem. #AlBetaald heb ik zelf afgeleid uit het oude systeem (dat heel onduidelijk is in welke betalingen nu bij welke rekeningen horen, maar dat terzijde)
Kan iemand helpen om deze cusor weg te werken ?
Even een achtergrond verhaal, ik ben aan het wisselen van software pakket dat wordt gebruikt om gegevens van huurders bij te houden.
Ik ben nu bezig om de transacties om te zetten tussen de systemen, daarbij moet ik rekening houden met welke kosten wel en niet zijn betaald.
Voorbeeld:
Jan heeft in februari als kosten:
- 400 euro huur
- 50 euro service kosten
- 100 euro incidentele kosten (bijvoorbeeld omdat Jan rommel in de portiek heeft achtergelaten)
Jan heeft in maart de volgende kosten
- 400 euro huur
- 50 euro service kosten
Het totaal aan kosten in deze periode is 1000 euro. het totaal betaald bedrag door jan is 870, verdeeld over 3 betalingen (1x 450, 1x 370 1x 50)
Ik moet nu deze transacties overzetten in het nieuwe systeem, maar ik moet dus rekening houden met welke rekeningen wel en niet zijn betaald. Ik kan dus niet alle rekeningen en betalingen op een hoop gooien (dat zou bijvoorbeeld betekenen dat de incidentele kosten worden betaald terwijl dit niet het geval is)
Dit is het punt waar ik een beetje moeite mee heb om dit goed SET gebaseerd te doen.
Ik heb de onderstaande query die werkt, helaas wel met een cursor.
De tabel #Transactie en #Betaling zijn onderdeel van het nieuwe systeem. #AlBetaald heb ik zelf afgeleid uit het oude systeem (dat heel onduidelijk is in welke betalingen nu bij welke rekeningen horen, maar dat terzijde)
Kan iemand helpen om deze cusor weg te werken ?
SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
| CREATE TABLE #Transactie (TransactieID INT IDENTITY(1,1), BatchID INT, TransactieNo INT, EigenaarID INT, ContractID INT, TransactieType VARCHAR(3), -- CHG = Kosten, PMT = Betaling TransactieDatum DATE, ToepassenID INT, BetalingID INT, Bedrag DECIMAL(16,2) ) CREATE TABLE #Betaling (BetalingID INT IDENTITY(1,1), Bedrag DECIMAL(16,2), ContractID INT ) CREATE TABLE #AlBetaald ( TransactieID INT, BedragBetaald DECIMAL(16,2), ContractID INT ) -- Deze tabel bevat kosten en welke kosten zijn betaald. INSERT INTO #Transactie (BatchID, TransactieNo, EigenaarID, ContractID, TransactieType, TransactieDatum, ToepassenID, BetalingID, Bedrag) VALUES (1,1,344557,854123,'CHG','2017-01-01',1,NULL,400), -- 400 kosten voor contract 854123 (1,2,344557,854123,'CHG','2017-01-02',2,NULL,50), -- 50 kosten voor contract 854123 (1,3,344557,854123,'CHG','2017-01-02',3,NULL,100), -- 100 kosten voor contract 854123 (1,4,344557,854123,'CHG','2017-01-01',4,NULL,400), -- 400 kosten voor contract 854123 (1,5,344557,854123,'CHG','2017-01-02',5,NULL,50) -- 50 kosten voor contract 854123 INSERT INTO #Betaling (Bedrag, ContractID) VALUES (-450,854123), (-370,854123), (-50,854123) -- totaal aantal betalingen voor contract 854123 = 870 -- deze tabel houdt bij INSERT INTO #AlBetaald (TransactieID,BedragBetaald,ContractID) VALUES (1,400,854123), -- rekening van 400 volledig betaald (2,50,854123), -- rekening van 50 volledig betaald (4,370,854123), -- rekening van 400 niet volledig betaald, slechts 370 betaald (5,50,854123) -- rekening van 50 volledig betaald -- Dit is de cursor waar ik vanaf wil DECLARE @CursorTransactieID INT DECLARE @CursorBedragTeBetalen DECIMAL(16,2) DECLARE @CursorContractID INT DECLARE @CursorBalansOver DECIMAL(16,2) DECLARE AlBetaaldeTransactiesToepassenCursor CURSOR FOR SELECT TransactieID, Bedragbetaald, ContractID FROM #AlBetaald OPEN AlBetaaldeTransactiesToepassenCursor FETCH NEXT FROM AlBetaaldeTransactiesToepassenCursor INTO @CursorTransactieID, @CursorBedragTeBetalen, @CursorContractID WHILE @@FETCH_STATUS = 0 BEGIN --Cusor logic SET @CursorBalansOver = @CursorBedragTeBetalen WHILE -- Zolang als de rekening nog niet volledig betaald is (volledig = het bedrag dat voorheen betaald is) (SELECT ISNULL(SUM(ABS(Bedrag)),0) FROM #Transactie WHERE ToepassenID = @CursorTransactieID AND TransactieType = 'PMT') < ABS(@CursorBedragTeBetalen) AND -- En het totaal aan betalingen gedaan groter is / gelijk aan dan de rekening. (SELECT SUM(ABS(Bedrag)) FROM #Betaling WHERE ContractID = @CursorContractID) >= (SELECT ISNULL(SUM(ABS(Bedrag)),0) FROM #Transactie WHERE ContractID = @CursorContractID AND TransactieType = 'PMT') BEGIN -- Voer het betalings record in INSERT INTO #Transactie (BatchID, TransactieNo, EigenaarID, ContractID, TransactieType, TransactieDatum, ToepassenID, BetalingID, Bedrag) SELECT 1, (SELECT ISNULL(MAX(TransactieNo),0)+1 FROM #Transactie), -- +1, dit is helaas geen identity in de tabel. (SELECT TOP 1 EigenaarID FROM #Transactie WHERE ContractID = @CursorContractID), @CursorContractID, 'PMT', -- betaling GETDATE(), @CursorTransactieID, -- Pas deze betaling toe aan de rekening met id '@CursorTransactieID' BetalingGeldNemen.BetalingID, -- We gebruiken het geld van deze betaling om de rekening te betalen. CASE WHEN ABS(BetalingGeldNemen.GeldOver) > ABS(@CursorBalansOver) THEN ABS(@CursorBalansOver) * -1 ELSE BetalingGeldNemen.GeldOver * -1 END AS PayBedrag FROM -- Zoek uit van welke betaling we nog geld kunnen pakken. ( SELECT TOP 1 * FROM (SELECT TBetaling.BetalingID, (SELECT ABS(TBetaling.Bedrag) -- het totaalbedrag dat is betaald - -- minus (SELECT ISNULL(SUM(ABS(Bedrag)),0) FROM #Transactie WHERE BetalingID = TBetaling.BetalingID)) AS GeldOver -- het bedrag dat al is toegepast FROM #Betaling TBetaling WHERE TBetaling.ContractID = @CursorContractID ) Beschikbarebetaling WHERE Beschikbarebetaling.GeldOver > 0 ) AS BetalingGeldNemen -- Herbereken hoeveel uitstaande balans er nog is voor deze transactie SET @CursorBalansOver = ABS(@CursorBedragTeBetalen) - (SELECT ISNULL(SUM(ABS(Bedrag)),0) FROM #Transactie WHERE ToepassenID = @CursorTransactieID AND TransactieType = 'PMT') END FETCH NEXT FROM AlBetaaldeTransactiesToepassenCursor INTO @CursorTransactieID, @CursorBedragTeBetalen, @CursorContractID END CLOSE AlBetaaldeTransactiesToepassenCursor DEALLOCATE AlBetaaldeTransactiesToepassenCursor SELECT * FROM #Transactie SELECT * FROM #Betaling SELECT * FROM #AlBetaald DROP TABLE #Transactie DROP TABLE #Betaling DROP TABLE #AlBetaald |
[ Voor 0% gewijzigd door RobIII op 24-10-2017 22:11 . Reden: quote naar code tags aangepast ]
:strip_icc():strip_exif()/u/43400/avatar.jpeg?f=community){kind=avatar}
/u/147751/avatar455992_1.gif.png?f=community){kind=avatar}
/u/56000/neuken.png?f=community)
