LAG or Lead function
select order_no, installment_id, installment_id2, due_date , due_date2,number_of_days
from
(
select t.order_no,
installment_id,
LEAD (installment_id,1) OVER (PARTITION BY t.order_no ORDER BY installment_id) AS installment_id2,
due_date,
LEAD (due_date,1) OVER (PARTITION BY t.order_no ORDER BY due_date) AS due_date2,
(LEAD (due_date,1) OVER (PARTITION BY t.order_no ORDER BY due_date)-due_date) number_of_days
from C_CREDIT_PAYMENT_PLAN p , PAYMENT_PLAN c, CUSTOMER_ORDER_CFV t
where p.invoice_id=c.invoice_id
and p.order_no=t.order_no
and c_early_settle <>'TRUE'
and PARTY_TYPE_DB='CUSTOMER'
--and t.order_no='L500592'
and CUSTOMER_ORDER_API.GET_C_LEASING_ACCOUNT_STATUS(t.ORDER_NO)<>'Closed'
and installment_id>1
and CUSTOMER_ORDER_API.GET_ORDER_ID(t.ORDER_NO) <>'MIG'
)
where number_of_days>31
//////
select order_no, installment_id, installment_id2, due_date , due_date2,number_of_days
from
(
select t.order_no,
installment_id,
LAG (installment_id,1) OVER (PARTITION BY t.order_no ORDER BY installment_id) AS installment_id2,
due_date,
LAG (due_date,1) OVER (PARTITION BY t.order_no ORDER BY due_date) AS due_date2,
(LAG (due_date,1) OVER (PARTITION BY t.order_no ORDER BY due_date)-due_date) number_of_days
from C_CREDIT_PAYMENT_PLAN p , PAYMENT_PLAN c, CUSTOMER_ORDER_CFV t
where p.invoice_id=c.invoice_id
and p.order_no=t.order_no
and c_early_settle <>'TRUE'
and PARTY_TYPE_DB='CUSTOMER'
and t.order_no='L500592'
and CUSTOMER_ORDER_API.GET_C_LEASING_ACCOUNT_STATUS(t.ORDER_NO)<>'Closed'
and installment_id>1
and CUSTOMER_ORDER_API.GET_ORDER_ID(t.ORDER_NO) <>'MIG'
)
--where number_of_days>31
