There is a TABLE storage data is as follows:


How to through the circular manner will output after each input data accumulated, the logic:
After each time the number of input processing QUANTITY, if it's the same ITEM_ID and CUSTOMER_ID, according to the result of TRANS_TYPE cumulative (plus or minus),
Will not allow the accumulative, and every day the same ITEM_ID and CUSTOMER_ID there may be multiple records, the result is as follows (the results for the last column) :

CodePudding user response:

ORACLE's analysis function to deal with this is very simple, no cycle, cycle efficiency low instead

The select statement as follows, the update yourself to think about how to write

The select org_id, lines_id, item_id, customer_id, trans_type, quantity, transaction_date,
The sum (quantity) over (partition by item_id, customer_id, trans_type order by lines_id) as inv_qty
From the table.

CodePudding user response:

reference 1st floor yaiger response:

ORACLE analysis function to deal with this is very simple, no cycle, cycle efficiency low instead

The select statement as follows, the update yourself to think about how to write

The select org_id, lines_id, item_id, customer_id, trans_type, quantity, transaction_date,
The sum (quantity) over (partition by item_id, customer_id, trans_type order by lines_id) as inv_qty
From the table.

This I know, but this is just the analysis data, to view the report way is presented to the others

CodePudding user response:

refer to the original poster liangbing88 response:

there is a TABLE to store the data as follows:


How to through the circular manner will output after each input data accumulated, the logic:
After each time the number of input processing QUANTITY, if it's the same ITEM_ID and CUSTOMER_ID, according to the result of TRANS_TYPE cumulative (plus or minus),
Will not allow the accumulative, and every day the same ITEM_ID and CUSTOMER_ID there may be multiple records, the result is as follows (the results for the last column) :

Logic added:
After each time the number of input processing QUANTITY, if it's the same ITEM_ID and CUSTOMER_ID, according to the result of TRANS_TYPE accumulative total, if it is Packing Issue,
In the last time the inventory addition on the basis of the calculation; If Packing identifiers, is on the basis of the last time the inventory subtraction calculation,
Every day the same ITEM_ID and CUSTOMER_ID there may be multiple records

CodePudding user response:

Can't report in writing analysis function of SQL? !

CodePudding user response:

The sum (decode (trans_type, 'Packing Issue', - quantity, 'Packing identifiers', quantity)) over (partition by item_id, customer order by transaction_date) aaa

CodePudding user response:

reference 4 floor yaiger response:

can't report it write analysis function of SQL? !

To insert into the Form

CodePudding user response:

The FORM of what you say is ORACLE DEVELOPER, insert, research

CodePudding user response:

To generate a cumulative effect, if is recommend to use table rownumber sorted, table since even before the statistics, should can a ladder effect,