CODEWITHSUNDEEP
sqlpostgresql
Shh
Shh

Reputation: 1006

view with incremental dates

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions