sql - Intelligent Debt Ageing Code -

i have data in format of;

client amt   date abc co £250  20/09/16 abc co £250  20/10/16 cde co £200  20/11/16 cde co £200  20/10/16 cde co £-200 20/09/16 fgh co £600  01/01/16 fgh co £-500 20/09/16 fgh co £-50  20/10/16 fgh co £100  20/11/16 

i can pivot easily;

client balance 0-29days 30-59days 60-89days 90days+ abc co £500    £0       £250      £250      £0 cde co £200    £200     £200      £-200     £0 fgh co £100    £100     £-50      £-500     £600 ijk co £-100   £100     £0        £0        £-200 

but need like;

client balance 0-29days 30-59days 60-89days 90days+ abc co £500    £0       £250      £250      £0 cde co £200    £200     £0        £0        £0 fgh co £100    £100     £0        £0        £50 ijk co £-100   £0       £0        £0        £-100 

the columns or "aging buckets" represent age of debit/credit. single transaction not occur in more 1 bucket. if there credits , debits should applied eachother (starting oldest). elaborate on few of records...

cde co; earliest transaction £-200 credit on 20/09 balanced next transaction £200 debit on 20/10. leaves £200 debit on 20/11 (hence £200 debit in 0-29days bucket).

fgh co; earliest transaction £600 debit on 01/01 part paid 2 payments of £-500 (20/09) , £-50 (20/10) leaving £50 of debit in 90days+ bucket , more recent debit of £100 on 20/11 in 0-29days bucket.

is there query/formula can use evaluate this? or going have use cursor?

thanks

here solution seems match expected output. note, it's bit messy , might able simplify logic bit, @ least seems work.

link working example: http://rextester.com/owh97326

note answer adapted solution similar problem on dba.stackexchange.com. impressed solution.

create table debt (     client char(6),     amount money,     [date] date);  insert debt  values  ('abc co', 250,  convert(date, '20/09/2016', 103)), ('abc co', 250,  convert(date, '20/10/2016', 103)), ('cde co', 200,  convert(date, '20/11/2016', 103)), ('cde co', 200,  convert(date, '20/10/2016', 103)), ('cde co', -200, convert(date, '20/09/2016', 103)), ('fgh co', 600,  convert(date, '01/01/2016', 103)), ('fgh co', -500, convert(date, '20/09/2016', 103)), ('fgh co', -50,  convert(date, '20/10/2016', 103)), ('fgh co', 100,  convert(date, '20/11/2016', 103));  grouping_cte ( select client, sum(abs(amount)) amount,      case when datediff(day, getdate(), [date]) > -30 '0-29 days'          when datediff(day, getdate(), [date]) > -60 '30-59 days'          when datediff(day, getdate(), [date]) > -90 '60-89 days'          else '90+ days' end [date],     case when amount < 0 'in' else 'out' end [type]   debt   group client,     case when datediff(day, getdate(), [date]) > -30 '0-29 days'          when datediff(day, getdate(), [date]) > -60 '30-59 days'          when datediff(day, getdate(), [date]) > -90 '60-89 days'          else '90+ days' end,     case when amount < 0 'in' else 'out' end), runningtotals_cte ( select client, amount, [date], [type],     sum(amount) on (partition client, [type] order [date] desc) - amount runningtotalfrom,     sum(amount) on (partition client, [type] order [date] desc) runningtotalto   grouping_cte), allocated_cte ( select outs.client, outs.date, outs.amount + isnull(sum(x.borrowed_qty),0) adjustedamount   (select * runningtotals_cte [type] = 'out') outs   left join (select * runningtotals_cte [type] = 'in') ins     on ins.runningtotalfrom < outs.runningtotalto     , outs.runningtotalfrom < ins.runningtotalto     , ins.client = outs.client   cross apply (       select case when ins.runningtotalto < outs.runningtotalto case when ins.runningtotalfrom > outs.runningtotalfrom  -1 * ins.amount                                                                           else -1 * (ins.runningtotalto - outs.runningtotalfrom) end                   else case when outs.runningtotalfrom > ins.runningtotalfrom outs.amount                             else -1 * (outs.runningtotalto - ins.runningtotalfrom) end end) x (borrowed_qty)   group outs.client, outs.date, outs.amount) --select * allocated_cte;  select client,     sum(adjustedamount) balance,     sum(iif([date] = '0-29 days', adjustedamount, null)) [0-29 days],     sum(iif([date] = '30-59 days', adjustedamount, null)) [30-59 days],     sum(iif([date] = '60-89 days', adjustedamount, null)) [60-89 days],     sum(iif([date] = '90+ days', adjustedamount, null)) [90+ days]   allocated_cte   group client;