db2 luw - DB2 - Determine a future date based on two fields -

db2 luw - DB2 - Determine a future date based on two fields -

i need calculate date in db2 unix.

i have date field: contract_dt (examples: 2/7/2006, 8/25/2006, 11/16/2007, 2/25/2008, 12/29/2005)

and type field prime (examples: c, i, e, z, v, k)

i need calculate next date loan reviewed (review_dt).

if prime = z every year contract_dt if prime = v every 3 years contract_dt if prime = k every 5 years contract_dt if prime = null or other letter, null

an illustration loan 01 has contract_dt of 3/1/2004, , has prime of v. need count by/add 3 years 3/1/2004, until date greater mthly_close_dt. (options 2007, 2010, 2013, 2016, 2019,2022). right reply 3/1/2016.

i realize construction case statement, have no thought how pick date based on year multiples , find 1 greater mnthly_close_dt.

here's have far:

create procedure "finance"."al_loop_test"(out r_rvdt date) begin atomic declare v_tmgi date; declare v_ctdt date; declare v_rvdt date; set v_tmgi = '2014-09-01'; --close month set v_ctdt = '2012-06-02'; -- contract_dt set v_rvdt = v_ctdt; -- starting value v_rvdt while (v_rvdt < v_tmgi) -- while review dt less close month set v_rvdt = (v_rvdt + 5 year); -- add together 5 years date end while; set r_rvdt = v_rvdt; end

thanks!

jimmy, help. created:

create procedure "x"."al_loop_test" ( out "r_rvdt" date ) language sql not deterministic external action modifies sql info old savepoint level begin atomic declare v_tmgi date; declare v_ctdt date; declare v_rvdt date; set v_tmgi = '2014-09-01'; --close month set v_ctdt = '2002-06-02'; -- contract_dt set v_rvdt = v_ctdt; -- starting value v_rvdt while (v_rvdt < v_tmgi) -- while review dt less close month set v_rvdt = (v_rvdt + 5 year); -- add together 5 years date end while; set r_rvdt = v_rvdt; end;

db2 db2-luw