SQL & PL/SQL

1 error has occurred

Your session has timed out.

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Cumulative sum and Lag Scenario

Mr.JondeeAug 12 2019 — edited Aug 14 2019

Hello All
, I have a scenario , I am trying to derive SQL to enable us calculate running balance.

oracle version : PL/SQL Release 12.2.0.1.0 - Production

CREATE TABLE ADJUSTMENT3

(

R_ORDER_NO NUMBER(9),

CORE_TRANSDATE DATE,

INSTALL_BILLING_LINE_ID NUMBER,

TXN_DATE TIMESTAMP(6),

ADJ_ORDER_NO NUMBER,

ADJ_AMOUNT NUMBER

);

SET DEFINE OFF;

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706800, TO_DATE('7/31/2019 11:39:54 AM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,

95.35);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706800, TO_DATE('7/31/2019 11:39:54 AM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706801, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706801, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,

95.35);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706801, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706801, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706801, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706802, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706802,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706802, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706802, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,

95.35);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706802, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706802, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706802, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706803, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706803, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706802,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706803, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706803,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706803, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706803, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706803, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706803, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,

95.35);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,

95.35);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706804,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:01:12.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:00:47.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 3);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706802,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706803,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706804,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:01:12.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:00:47.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 3);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706803,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706802,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,

95.35);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706805,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706806,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706805,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706804,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,

95.35);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706803,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706802,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:00:47.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 3);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:01:12.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:00:47.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 3);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706805,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706804,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706806,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 2:39:55.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706807,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:01:12.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706803,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706802,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,

95.35);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:00:47.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 3);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:01:12.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706806,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706805,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706804,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 2:39:55.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706807,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,

95.35);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706802,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706803,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)

Values

(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,

-10.59);

Insert into ADJUSTMENT3

(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,

ADJ\_AMOUNT)

Values

(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 2:39:55.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706808,

-10.63);

COMMIT;

----------------------------------------------------------------------------

Expected output

Added on Aug 12 2019

#sql-pl-sql

28 comments

1,944 views

SQL & PL/SQL

Cumulative sum and Lag Scenario

Comments

Post Details