Reputation: 1006
I have a postgres table Prop
with columns
date date,
ins1 integer,
insamt1 numeric,
ins2 integer,
insamt2 numeric,
ins3 integer,
insamt3 numeric
ins4 integer,
insamt4 numeric
ins1, ins2.... represent number of months and insamt1, insamt2...represent amount. This table is a legacy data table imported from dbf. So I have to work with this structure.
One sample row from the table:
'2017-03-15', 2, 12000.00, 3, 10000.00, 1, 14000.00, 4, 8000
Can a view be made like:
date amount
'2017-04-15' 12000
'2017-05-15' 12000
'2017-06-15' 10000
'2017-07-15' 10000
'2017-08-15' 10000
'2017-09-15' 14000
....
or should I construct a new table and insert data via script. Using Postgres 9.4
EDIT: The amounts get due from the clients of the company where the clients agree to pay every month.
Upvotes: 1
Views: 84
Reputation: 1271241
I think you can use a lateral join for this I am thinking:
select date + ins * interval '1 month', insamt
from t cross join lateral
(values (ins1, insamt1),
(ins2, insamt2),
(ins3, insamt3),
(ins4, insamt4)
) v(ins, insamt);
EDIT:
Oh, I think I get it:
select date + gs.i * interval '1 month',
v.insamt
from t cross join lateral
generate_series(1, ins1 + ins2 + ins3 + ins4) gs(i) cross join lateral
(values (1, ins1, insamt1),
(ins1 + 1, ins1 + ins2, insamt2),
(ins1 + ins2 + 1, ins1 + ins2 + ins3, insamt3),
(ins1 + ins2 + ins3 + 1, ins1 + ins2 + ins3 + ins4, insamt4)
) v(s, e, insamt)
where gs.i between v.s and v.e;
Upvotes: 1