Dynamic generation of columns in postgres subquery

Question

This below query gives name,id,subscribed,opening,closing,totalAmount details of a particular year i.e posted_date is the input to the query.

select la.name,la.id,la.parent_id,la.is_group,tb1.opening op_1,tb1.closing cl_1,
coalesce((select sum(j.amount) from journal j,voucher v , ledger_account le where j.voucher_id=v.id and le.id=j.ledger_account_id and la.id=le.id and
 v.posted_date::date>='2020-04-01' and v.posted_date::date<='2021-03-31'),0) balance_2020
from ledger_account la left join trialb tb1 on tb1.ledger_account_id=la.id and tb1.fy_id=1

The above query gives total data and balance of year 2020, For Ex:- If i need the balances from year 2005, I again need to paste the below logic multiple times

coalesce((select sum(j.amount) from journal j,voucher v , ledger_account le where j.voucher_id=v.id and le.id=j.ledger_account_id and la.id=le.id and  v.posted_date::date>='2020-04-01' and v.posted_date::date<='2021-03-31'),0) balance_2020

and change the v.posted date and column name as
v.posted_date::date>='2005-04-01' and v.posted_date::date<='2006-03-31'),0) balance_2005 and so on for almost 15 times to get total balances until year 2020 by which size of query keeps increasing every year and time taken process.

So is there any alternate or possible way using which the columns balance_2005,balance_2006.. so on can be generated dynamically if necessary based on the input given to the query?

Side notes: (1) comma-separated joins as you are using in the subquery where made redundant in 1992. You shouldn't use them anymore. (2) Date literals start with the keyword DATE. '2020-04-01' is just a string that must be converted to date, which can fail. Use DATE '2020-04-01' instead. — Thorsten Kettner
– Thorsten Kettner, Commented Apr 8, 2020 at 7:15
Yeah sure. I will look into those changes and Thanks for the tips.! — akhilesh kedarisetty
– akhilesh kedarisetty, Commented Apr 8, 2020 at 7:21

Thorsten Kettner · Accepted Answer · 2020-04-08 07:38:18Z

1

Move the subquery to the from clause and use conditional aggregation to get the various sums:

select
  la.name,
  la.id,
  la.parent_id,
  la.is_group,
  tb1.opening op_1,
  tb1.closing cl_1,
  sums.balance_2018,
  sums.balance_2019,
  sums.balance_2020
from ledger_account la
left join trialb tb1 on tb1.ledger_account_id = la.id and tb1.fy_id = 1
left join
(
  select
    j.ledger_account_id,
    sum(case when v.posted_date::date >= date '2018-04-01' and v.posted_date::date <= date '2019-03-31') then j.amount else 0 end) as balance_2018,
    sum(case when v.posted_date::date >= date '2019-04-01' and v.posted_date::date <= date '2020-03-31') then j.amount else 0 end) as balance_2019,
    sum(case when v.posted_date::date >= date '2020-04-01' and v.posted_date::date <= date '2021-03-31') then j.amount else 0 end) as balance_2020
  from journal j
  join voucher v on v.id = j.voucher_id
  group by j.ledger_account_id
) sums on sums.ledger_account_id = la.id
order by la.name;

If you don't want fixed years, then you cannnot use columns, but must use rows instead. You need a calculation to get from the date to the fiscal year, but this is merely subtracting three months from it.

select
  la.name,
  la.id,
  la.parent_id,
  la.is_group,
  tb1.opening op_1,
  tb1.closing cl_1,
  sums.fiscal_year
  sums.balance
from ledger_account la
left join trialb tb1 on tb1.ledger_account_id = la.id and tb1.fy_id = 1
left join
(
  select
    j.ledger_account_id,
    extract(year from v.posted_date - interval '3 months') as fiscal_year
    sum(j.amount) as balance
  from journal j
  join voucher v on v.id = j.voucher_id
  group by j.ledger_account_id, extract(year from v.posted_date - interval '3 months')
) sums on sums.ledger_account_id = la.id
order by la.name, sums.fiscal_year;

Have your app deal with an account's yearly data in a loop.

If you want to avoid to get a very large result set and limit it to certain years, you can add this criteria, e.g.

  ...
  where extract(year from v.posted_date - interval '3 months') between 2010 and 2020
  group by j.ledger_account_id, extract(year from v.posted_date - interval '3 months')
  ...

edited Apr 8, 2020 at 7:38

answered Apr 8, 2020 at 7:04

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

akhilesh kedarisetty Over a year ago

But even in this case we need to add the same logic again and again and mention the column names as well right?

Thorsten Kettner Over a year ago

Yes, you are right. If you want it completely dynamic, then get the data in rows, not columns. I'll edit my answer...

akhilesh kedarisetty Over a year ago

Yeah if the data is in rows, I think crosstab can be used. But in this case we cant guess what might be input to query, So we cant even add case statements for all the years and column names in before!

akhilesh kedarisetty Over a year ago

That really worked well, But little confused with the logic used and have to check whether getting the right data or not, As fiscal year starts from April of every year and ends with march of next year. So i think few more changes can bring this to end.

Collectives™ on Stack Overflow

Dynamic generation of columns in postgres subquery

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related