my sql query fetch records with accurate "CLOSING" column (as shown in below record set) when using single selection of accountid.
i am facing issue in this query (below) with run with IN clause multiple accountid, it gives inaccurate CLOSING value, require assistance to fix this issue;
|
Header 1
|
|
CREATE TABLE AML
(
ID NUMBER,
ACCOUNT_ID NUMBER,
AAID NUMBER,
DEBIT NUMBER,
CREDIT NUMBER,
TRDATE DATE
)
INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE) VALUES ( 18,2292,41,44000,0,'03-Aug-2019');
INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE) VALUES ( 19,2292,41,1299000,0,'03-Aug-2019');
INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE) VALUES ( 1,2440,40,0,177555,'31-Mar-2018');
INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE) VALUES ( 2,2440,41,69180,0,'12-Apr-2018');
INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE) VALUES ( 17,2440,40,69180,0,'12-Apr-2018');
INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE) VALUES ( 13,2441,42,0,10000,'31-Mar-2018');
INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE) VALUES ( 14,2441,42,69180,0,'12-Apr-2018');
INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE) VALUES ( 16,2441,42,0,1000,'16-Apr-2018');
|
|
|
| |
|
WITH ledger
AS (SELECT account\_id,
aaid,
TrDate,
Debit,
Credit,
ROW\_NUMBER () OVER (ORDER BY trdate) AS row1
FROM aml
WHERE account\_id IN (2292,2440) AND aaid IN (40, 41))
SELECT L1.account_id,
L1.aaid,
L1.TrDate,
L1.Debit AS Debit,
L1.Credit AS Credit,
(CASE
WHEN SIGN (
( NVL (L1.Credit, 0)
+ NVL (SUM (L2.Credit), 0)
- NVL (SUM (L2.Debit), 0)
- NVL ( (L1.Debit), 0))) = -1
THEN
ABS (
( NVL (L1.Credit, 0)
+ NVL (SUM (L2.Credit), 0)
- NVL (SUM (L2.Debit), 0)
- NVL ( (L1.Debit), 0)))
ELSE
-1
\* ( NVL (L1.Credit, 0)
+ NVL (SUM (L2.Credit), 0)
- NVL (SUM (L2.Debit), 0)
- NVL ( (L1.Debit), 0))
END)
AS closing
FROM ledger L1
LEFT JOIN ledger L2
ON L1.row1 > L2.row1
AND L1.aaid IN (40, 41)
AND L1.account\_id IN (2292,2440)
AND L1.trDate >= TO\_DATE ('01-Apr-2018', 'dd-Mon-yyyy')
AND L1.trDate \<= TO\_DATE ('03-Aug-2019','dd-Mon-yyyy')
GROUP BY L1.account_id,
L1.aaid,
L1.TrDate,
L1.Credit,
L1.Debit
ORDER BY L1.ACCOUNT_ID,
L1.aaid,
L1.TRDATE,
closing
|