i do want to get record set as per below format, sql query to get date(in column 1) and account_analytic_acccount_name in column 2,3..n many as has value of transaction in date range. i need opening balance in first column say date filter from '01-JUL-2024 to 31-JUL-2024' should be accumulated sum of dr/cr as opening balance ‘O/B’ and in second…n column with all of my account_analytic_account.name sum values from transaction table from account_move and accont_move_line with filter of ‘E’ in account_type will be populated accordingly. pl assist, i am testing this in oracle10, following are my ddl/dml, i have tried below query but unable to get required rec set
WITH opening_balance AS (
SELECT
aml.analytic_account_id AS analytic_account_id,
SUM(aml.debit - aml.credit) AS opening_balance
FROM
account_move_line aml
JOIN account_move am ON aml.move_id = am.id
JOIN account_account aa ON aml.account_id = aa.id
WHERE
aa.user_type_id IN (
SELECT id
FROM account_account_type
WHERE name = 'E'
)
AND am.date1 <= '30-JUN-2024'
GROUP BY aml.analytic_account_id
),
transactions AS (
SELECT
am.date1,
aml.analytic_account_id AS analytic_account_id,
SUM(aml.debit - aml.credit) AS transaction_sum
FROM
account_move_line aml
JOIN account_move am ON aml.move_id = am.id
JOIN account_account aa ON aml.account_id = aa.id
WHERE
aa.user_type_id IN (
SELECT id
FROM account_account_type
WHERE name = 'E'
)
AND am.date1 BETWEEN '01-JUL-2024' AND '31-JUL-2024'
GROUP BY am.date1, aml.analytic_account_id
)
SELECT
t.date1 AS "date1",
COALESCE(ana1.name, 'No Analytic Account') AS "Analytic Account 1",
COALESCE(SUM(CASE WHEN t.analytic_account_id = ana1.id THEN t.transaction_sum END), 0) AS "Account 1 Total",
-- Add more analytic accounts dynamically based on the number of accounts
(ob.opening_balance + COALESCE(SUM(t.transaction_sum), 0)) AS "Opening Balance"
FROM
transactions t
LEFT JOIN opening_balance ob ON t.analytic_account_id = ob.analytic_account_id
LEFT JOIN account_analytic_account ana1 ON t.analytic_account_id = ana1.id
-- Add more LEFT JOINs for as many analytic accounts as needed
--where ana1.id in(1,2,3,4,5,6,50,446)
GROUP BY t.date1, ana1.name , ob.opening_balance
ORDER BY t.date1;
DML
CREATE TABLE account_account (
id number primary key,
name varchar2(100),
user_type_id number);
CREATE TABLE account_account_type (
id number primary key,
name varchar2(100));
CREATE TABLE account_move (
id number primary key,
date1 date);
CREATE TABLE account_move_line (
id number primary key,
debit number,
credit number,
date1 date ,
analytic_account_id number,
move_id number,
account_id number);
ALTER TABLE account_move_line ADD CONSTRAINT aamla_id_fkey FOREIGN KEY (account_id) REFERENCES account_account(id) ON DELETE CASCADE;
ALTER TABLE account_move_line ADD CONSTRAINT aaml_move_id_fkey FOREIGN KEY (move_id) REFERENCES account_move(id) ON DELETE CASCADE;
CREATE TABLE account_analytic_account (
id number primary key,
name varchar2(100) NOT NULL
);
insert into account_analytic_account values(41, 'H2O');
insert into account_analytic_account values(42, 'H3O');
insert into account_analytic_account values(396,'ENG');
insert into account_account_type values(16,'E');
insert into account_account_type values(17,'R');
insert into account_account(id,name,user_type_id) values (34, 'ABNK',16);
insert into account_account(id,name,user_type_id) values (584, 'S.Exp',16);
insert into account_move values (12200, '30-JUN-2024');
insert into account_move_line(id,debit,credit,date1,analytic_account_id,move_id,account_id) values (66703, 6500,0, '30-JUN-2024', 396,12200,34);
insert into account_move_line(id,debit,credit,date1,analytic_account_id,move_id,account_id) values (66704, 8500,0, '30-JUN-2024', 396,12200,584);
insert into account_move_line(id,debit,credit,date1,analytic_account_id,move_id,account_id) values (66705, 0,15000, '30-JUN-2024', 42,12200,584);
insert into account_move values (12201, '10-JUN-2024');
insert into account_move_line(id,debit,credit,date1,analytic_account_id,move_id,account_id) values (66706, 6000,0, '10-JUN-2024', 396,12201,34);
insert into account_move_line(id,debit,credit,date1,analytic_account_id,move_id,account_id) values (66707, 8000,0, '10-JUN-2024', 396,12201,584);
insert into account_move_line(id,debit,credit,date1,analytic_account_id,move_id,account_id) values (66708, 0,14000, '10-JUN-2024', 41,12201,584);
insert into account_move values (12278, '17-JUL-2024');
insert into account_move_line(id,debit,credit,date1,analytic_account_id,move_id,account_id) values (66780, 3500,0, '17-JUL-2024', 41,12278,34);
insert into account_move_line(id,debit,credit,date1,analytic_account_id,move_id,account_id) values (66781, 0,3500, '17-JUL-2024', 41,12278,584);