· 8 years ago · Jan 23, 2018, 04:20 PM
1Account_ID DateOfEntry Balance
2---------- ----------- -------
31 1/1/2012 10.00
41 1/2/2012 -15.00
52 1/1/2012 -15.00
62 1/2/2012 10.00
73 1/1/2012 10.00
83 1/2/2012 1.00
93 1/3/2012 -5.00
104 1/1/2012 5.00
114 1/2/2012 5.00
124 1/3/2012 -7.00
135 1/1/2012 10.00
145 1/2/2012 -5.00
155 1/3/2012 -5.00
16
17Account_ID DateOfEntry Balance
18---------- ----------- -------
191 1/2/2012 -5.00
202 1/1/2012 -5.00
213 1/1/2012 5.00
223 1/2/2012 1.00
234 1/2/2012 2.00
24
25CREATE TABLE [dbo].[IDAT_AR_BALANCES](
26 [cvtGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
27 [CLIENT_ID] [varchar](11) NOT NULL,
28 [AGING_DATE] [datetime] NOT NULL,
29 [AMOUNT] [money] NOT NULL,
30 CONSTRAINT [PK_IDAT_ARBALANCES] PRIMARY KEY CLUSTERED ([cvtGUID] ASC)
31)
32
33--Remove AR that totals to 0.
34DELETE FROM IDAT_AR_BALANCES
35WHERE client_id IN (
36SELECT client_id
37FROM IDAT_AR_BALANCES
38GROUP BY client_id
39HAVING SUM(amount) = 0)
40
41--Spred the credits on to existing balances.
42select * into #balances from [IDAT_AR_BALANCES] where amount > 0
43select * into #credits from [IDAT_AR_BALANCES] where amount < 0
44
45declare credit_cursor cursor for select [CLIENT_ID], amount, cvtGUID from #credits
46
47open credit_cursor
48
49declare @client_id varchar(11)
50declare @credit money
51declare @balance money
52declare @cvtGuidBalance uniqueidentifier
53declare @cvtGuidCredit uniqueidentifier
54
55fetch next from credit_cursor into @client_id, @credit, @cvtGuidCredit
56while @@fetch_status = 0
57begin
58 --While balances exist for the current client_ID and there are still credits to be applied, loop.
59 while(@credit < 0 and (select count(*) from #balances where @client_id = CLIENT_ID and amount <> 0) > 0)
60 begin
61 --Find the oldest oustanding balance.
62 select top 1 @balance = amount, @cvtGuidBalance = cvtGuid
63 from #balances
64 where @client_id = CLIENT_ID and amount <> 0
65 order by AGING_DATE
66
67 -- merge the balance and the credit
68 set @credit = @balance + @credit
69
70 --If the credit is now postive save the leftover in the currently selected balance and set the credit to 0
71 if(@credit > 0)
72 begin
73 update #balances set amount = @credit where cvtGuid = @cvtGuidBalance
74 set @credit = 0
75 end
76 else -- Credit is larger than the balance, 0 out the balance and continue processesing
77 update #balances set amount = 0 where cvtGuid = @cvtGuidBalance
78
79 end -- end of while loop
80
81 --There are no more balances to apply the credit to, save it back to the list.
82 update #credits set amount = @credit where cvtGuid = @cvtGuidCredit
83
84 --Get the next credit.
85 fetch next from credit_cursor into @client_id, @credit, @cvtGuidCredit
86end
87close credit_cursor
88deallocate credit_cursor
89
90--Delete any balances and credits that where 0'ed out durning the spred negitive.
91delete #balances where AMOUNT = 0
92delete #credits where AMOUNT = 0
93
94truncate table [IDAT_AR_BALANCES]
95insert [IDAT_AR_BALANCES] select * from #balances
96insert [IDAT_AR_BALANCES] select * from #credits
97drop table #balances
98drop table #credits
99
100DECLARE ... CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
101
102--indexes to speed up first two queires
103IF not EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[IDAT_AR_BALANCES]') AND name = N'IX_IDATARBALANCES_CLIENTID_GUID')
104 CREATE NONCLUSTERED INDEX IX_IDATARBALANCES_CLIENTID_GUID
105 ON [dbo].[IDAT_AR_BALANCES] ([CLIENT_ID])
106 INCLUDE ([cvtGUID])
107
108IF not EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[IDAT_AR_BALANCES]') AND name = N'IX_IDATARBALANCES_AMOUNT_ID_DATE')
109 CREATE NONCLUSTERED INDEX [IX_IDATARBALANCES_AMOUNT_ID_DATE]
110 ON [dbo].[IDAT_AR_BALANCES] ([AMOUNT])
111 INCLUDE ([CLIENT_ID],[AGING_DATE])
112
113--Remove AR that totals to 0.
114DELETE FROM IDAT_AR_BALANCES
115WHERE client_id IN (
116SELECT client_id
117FROM IDAT_AR_BALANCES
118GROUP BY client_id
119HAVING SUM(amount) = 0)
120
121--find all instances that credit > balance
122SELECT newid() as cvtGUID, client_id, max(AGING_DATE) as AGING_DATE, sum(AMOUNT) as amount
123into #creditLarger
124FROM IDAT_AR_BALANCES
125GROUP BY client_id
126HAVING SUM(amount) < 0
127
128--remove all of the creditLargerEntries
129delete IDAT_AR_BALANCES where client_id in (select client_id from #creditLarger)
130
131--Build a list of remaining balances and summed credits
132select * into #balances from [IDAT_AR_BALANCES] where amount > 0
133
134SELECT newid() as cvtGUID, client_id, max(AGING_DATE) as AGING_DATE, sum(AMOUNT) as amount
135into #credits
136FROM [IDAT_AR_BALANCES]
137where amount < 0
138GROUP BY client_id
139
140--Index to make the update faster
141CREATE NONCLUSTERED INDEX BALANCE_INDEX ON #balances ([CLIENT_ID],[AMOUNT])
142
143--Begin loop of processing credits
144set nocount on
145declare credit_cursor cursor LOCAL STATIC READ_ONLY FORWARD_ONLY for select top 10 [CLIENT_ID], amount, cvtGUID from #credits
146open credit_cursor
147
148declare @client_id varchar(11)
149declare @credit money
150declare @balance money
151declare @cvtGuidBalance uniqueidentifier
152declare @cvtGuidCredit uniqueidentifier
153
154fetch next from credit_cursor into @client_id, @credit, @cvtGuidCredit
155while @@fetch_status = 0
156begin
157 --While balances exist for the current client_ID and there are still credits to be applied, loop.
158 while(@credit < 0 and exists(select * from #balances where @client_id = CLIENT_ID and amount > 0))
159 begin
160 --Find the oldest oustanding balance.
161 select top 1 @balance = amount, @cvtGuidBalance = cvtGuid
162 from #balances
163 where @client_id = CLIENT_ID and amount <> 0
164 order by AGING_DATE
165
166 -- merge the balance and the credit
167 set @credit = @balance + @credit
168
169 if(@credit > 0)
170 begin
171 --If the credit is now postive save the leftover in the currently selected balance and set the credit to 0
172 update #balances set amount = @credit where cvtGuid = @cvtGuidBalance
173 set @credit = 0
174 end
175 else
176 -- Credit is larger than the balance, 0 out the balance and continue processesing
177 update #balances set amount = 0 where cvtGuid = @cvtGuidBalance
178
179 end -- end of while loop
180
181 --There are no more balances to apply the credit to, save it back to the list.
182 update #credits set amount = @credit where cvtGuid = @cvtGuidCredit
183
184 --Get the next credit.
185 fetch next from credit_cursor into @client_id, @credit, @cvtGuidCredit
186end
187close credit_cursor
188deallocate credit_cursor
189set nocount off
190
191truncate table [IDAT_AR_BALANCES]
192
193insert into [IDAT_AR_BALANCES]
194 select * from #balances
195 union select * from #credits
196 union select * from #creditLarger
197
198--Delete any balances and credits that where 0'ed out durning the spred negitive.
199delete [IDAT_AR_BALANCES] where amount = 0
200
201drop table #balances
202drop table #credits
203drop table #creditLarger