I'm pretty new to SQL and using SQL Server 2012. I have a sample from the dataset I'm using below. It's an accounting dataset of bank transactions and invoice amounts with the following values customer ID, date of invoice, VADEKONTROL as indication whether the invoice maturity expired-not expired-not invoice, BORC is the invoice amount, ALACAK is the amount of payment received and BAKIYE is the total balance. Not invoice transactions are bank receipts.
What I want to do for each customer is to subtract from the last amount of BAKIYE the values in BORC column starting from the last entry until the difference is <= 0. Also it should stop subtracting if there's no more entry of the grouped customer found. So for M01516, 999,59 should be subtracted from 1982,40 and since the difference is not 0, keep on subtracting 982,81 and further results (if there was any) until difference is <= 0.
Also no need for the subtraction if the last value of BAKIYE equals 0 for a certain customer.
The basic logic is above and with that:
I want to select the entries which were subtracted from BAKIYE (until BAKIYE reaches 0 or the difference between sum of selected entries and bakiye reach 0, whichever way you look at it.
Count and sum the selected entries for each customer most likely with group by.
edit:
Keep in mind that if BORC value is 0 then transaction is a credit and if ALACAK value is 0 then transaction is a debt. So in order to find the current balance starting from the last, we might have to subtract both BORC and ALACAK values from BAKIYE.
Basically what I want is for each customer group starting from the last record: get last row of BAKIYE value, subtract BORC+ALACAK from BAKIYE for same row. Repeat this going up one row until BAKIYE is <= 0 for each customer. With this I can select the invoice amounts and number of invoices that will cover the remaining balance for a customer.
How can I go on solving this problem?
Any help would be greatly appreciated.
ACCID BDATE VADEKONTROL BORC ALACAK BAKIYE
------------------------------------------------------------
M01518 12.12.2018 expired 64,51 0,00 64,51
M01518 14.01.2019 expired 69,00 0,00 133,51
M01518 12.02.2019 not expired 69,00 0,00 202,51
M01518 18.02.2019 not invoice 0,00 203,00 -0,49
M01517 14.12.2018 expired 93,49 0,00 93,49
M01517 14.01.2019 expired 93,49 0,00 186,98
M01517 12.02.2019 not expired 93,49 0,00 280,47
M01516 25.12.2018 expired 982,81 0,00 982,81
M01516 21.01.2019 expired 999,59 0,00 1982,40
M01514 11.12.2018 expired 25,10 0,00 25,10
M01514 10.01.2019 not invoice 0,00 25,10 0,00
M01514 14.01.2019 expired 25,10 0,00 25,10
M01514 24.01.2019 not invoice 0,00 25,10 0,00
M01514 11.02.2019 not expired 25,10 0,00 25,10
edit2 output with Dwight's code:
ACCID BDATE VADEKONTROL BORC ALACAK BAKIYE BAKIYE_less_BORC
M01518 2018-12-12 Vadesi Geçmiş 64,51 0,00 64,51 0,00
M01518 2019-01-14 Vadesi Geçmiş 69,00 0,00 133,51 64,51
M01518 2019-02-12 Vadesi Gelmemiş 69,00 0,00 202,51 NULL
M01518 2019-02-18 FaturaDegil 0,00 203,00 -0,49 NULL
M01517 2018-12-14 Vadesi Geçmiş 93,49 0,00 93,49 0,00
M01517 2019-01-14 Vadesi Geçmiş 93,49 0,00 186,98 93,49
M01517 2019-02-12 Vadesi Gelmemiş 93,49 0,00 280,47 NULL
M01516 2018-12-25 Vadesi Geçmiş 982,81 0,00 982,81 0,00
M01516 2019-01-21 Vadesi Geçmiş 999,59 0,00 1982,40 982,81
M01514 2018-12-11 Vadesi Geçmiş 25,10 0,00 25,10 0,00
M01514 2019-01-10 FaturaDegil 0,00 25,10 0,00 NULL
M01514 2019-01-14 Vadesi Geçmiş 25,10 0,00 25,10 0,00
M01514 2019-01-24 FaturaDegil 0,00 25,10 0,00 NULL
M01514 2019-02-11 Vadesi Gelmemiş 25,10 0,00 25,10 NULL
I also used the following code (FNM00_ACC_CODE is ACCID) and got an almost correct result.
select FNM00_ACC_CODE,borc,alacak,BAKIYE,bdate,vadekontrol,
(cast(((SELECT TOP 1 BAKIYE FROM GRID_Temp_Current_Accounts_All_Can2 ac2
WHERE ac2.FNM00_ACC_CODE=c.FNM00_ACC_CODE ORDER BY ac2.ID desc)-
(select ISNULL(sum(convert(decimal(18,2),BORC)),'0.00') as money
from GRID_Temp_Current_Accounts_All_Can2 as ac
WHERE ac.FNM00_ACC_CODE=c.FNM00_ACC_CODE
and ac.id >= c.id)) as money)) BAKIYE2 from
GridTelekom_MetaData.dbo.GRID_Temp_Current_Accounts_All_Can2 c
FNM00_ACC_CODE borc alacak BAKIYE bdate vadekontrol BAKIYE2
M00385 2228,75 0,00 7689,75 2018-11-19 Vadesi Geçmiş -7039,00
M00385 2545,34 0,00 10235,09 2018-11-29 Vadesi Geçmiş -4810,25
M00385 2256,00 0,00 12491,09 2018-12-18 Vadesi Geçmiş -2264,91
M00385 0,00 3000,00 9491,09 2018-12-20 FaturaDegil -8,91
M00385 0,00 3500,00 5991,09 2018-12-28 FaturaDegil -8,91
M00385 2969,42 0,00 8960,51 2018-12-31 Vadesi Geçmiş -8,91
M00385 2244,15 0,00 11204,66 2019-01-18 Vadesi Geçmiş 2960,51
M00385 0,00 6000,00 5204,66 2019-01-24 FaturaDegil 5204,66
M00385 2237,34 0,00 7442,00 2019-01-29 Vadesi Geçmiş 5204,66
M00385 2217,11 0,00 9659,11 2019-02-18 Vadesi Gelmemiş 7442,00
Whenever BAKIYE2 reaches minus value, I want the transactions up and including to that point. So for the example above I would need the last 5 rows since sum of BORC for last 5 rows equals to 9668.02 which is 8.91 more than the last current BAKIYE which i 9659.11. I can then distinguish between expired maturities and not expired maturities.
With this method though I was thinking of selecting all entries where BAKIYE2 >= 0 , however this would skip the last row where it takes the BAKIYE into minuses (in this case it would not select 2969.42).
I have altered the query to provide 1 new column and an alteration to the original computed column previously supplied.
I have also translated some of the column names to make them easier for me to work with (my apologies if this is an inconvenience to you).
This query now shows when a balance hits 0 by taking the sum of the debt (borc) away from the balance at the time (bakiye). It also shows for any matured invoice, if there is outstanding balance to pay including any credits (alacak). This is done using the correlated inline subquery you have used, but using the bdate instead of the accid for establishing the latest balance and querying the records prior to hitting 0 or less rather than after hitting 0 or less which is what was happening in your version of the query.
I hope this is useful, please let me know if we are any closer or if there are further adjustments to be made.
declare @t table (
ACCID nvarchar(10),
BDATE date,
MaturityControl nvarchar(15),
Debt float,
Credit float,
Balance float
);
insert into @t (ACCID, BDATE, MaturityControl, Debt, Credit, Balance)
values
('M01518', '2018-12-12', 'expired', 64.51, 0.00, 64.51),
('M01518', '2019-01-14', 'expired', 69.00, 0.00, 133.51),
('M01518', '2019-02-12', 'not expired', 69.00, 0.00, 202.51),
('M01518', '2019-02-18', 'not invoice', 0.00, 203.00, -0.49),
('M01517', '2018-12-14', 'expired', 93.49, 0.00, 93.49),
('M01517', '2019-01-14', 'expired', 93.49, 0.00, 186.98),
('M01517', '2019-02-12', 'not expired', 93.49, 0.00, 280.47),
('M01516', '2018-12-25', 'expired', 982.81, 0.00, 982.81),
('M01516', '2019-01-21', 'expired', 999.59, 0.00, 1982.40),
('M01514', '2018-12-11', 'expired', 25.10, 0.00, 25.10),
('M01514', '2019-01-10', 'not invoice', 0.00, 25.10, 0.00),
('M01514', '2019-01-14', 'expired', 25.10, 0.00, 25.10),
('M01514', '2019-01-24', 'not invoice', 0.00, 25.10, 0.00),
('M01514', ' 2019-02-11', 'not expired', 25.10, 0.00, 25.10),
('M00385', '2018-11-19', 'expired', 2228.75, 0.00, 7689.75),
('M00385', '2018-11-29', 'expired', 2545.34, 0.00, 10235.09),
('M00385', '2018-12-18', 'expired', 2256.00, 0.00, 12491.09),
('M00385', ' 2018-12-20', 'not invoice', 0.00, 3000.00, 9491.09),
('M00385', '2018-12-28', 'not invoice', 0.00, 3500.00, 5991.09),
('M00385', '2018-12-31', 'expired', 2969.42, 0.00, 8960.51),
('M00385', '2019-01-18', 'expired', 2244.15, 0.00, 11204.66),
('M00385', '2019-01-24', 'not invoice', 0.00, 6000.00, 5204.66),
('M00385', '2019-01-29', 'expired', 2237.34, 0.00, 7442.00),
('M00385', '2019-02-18', 'not expired', 2217.11, 0.00, 9659.11);
select t.ACCID, t.BDATE, MaturityControl, Debt, Credit, Balance,
case when Balance_Less_Debt is null then balance-debt else Balance_Less_Debt end as computed_difference_in_transactions,
(select((select top 1 Balance from @t as t4 where t.accid=t4.ACCID order by t4.BDATE desc)-
(select sum(Debt) from @t as t3 where t.ACCID=t3.ACCID and t.BDATE>=t3.BDATE))-
(select sum(Credit) from @t as t5 where t.ACCID=t5.ACCID and t.BDATE>=t5.BDATE)) as current_balance_less_summed_debt
from @t as t
outer apply (select ACCID, BDATE, Balance-Debt as Balance_Less_Debt from @t t2 where
t.ACCID=t2.ACCID and t.BDATE=t2.BDATE and MaturityControl='expired'
group by ACCID, BDATE, Balance, Debt
having balance-Debt <=0
)
agg
where MaturityControl = 'expired'
order by t.ACCID desc, t.BDATE;