Home » SQL & PL/SQL » SQL & PL/SQL » Running settlement of inward vs outward quantity (Oracle 11g)
Running settlement of inward vs outward quantity [message #671249] Mon, 20 August 2018 01:23 Go to next message
chintan.patel
Messages: 159
Registered: July 2008
Location: Ahmedabad
Senior Member
Hello friends,

I have some inward and outward data which i would like compare and print side by side. I do not have any relation between inward and outward, i need to distribute on the basis of quantity. Is there any function or formula to get below output.

-------------------------------------------------------------------------------------
                 Inward                  |                       Outward
-------------------------------------------------------------------------------------
INW_No	        INW_Date	Qty	 |	OUT_No	        OUT_Date	Qty
-------------------------------------------------------------------------------------
INW/1	      01/07/2018	1000	 |	OUT/1	      01/07/2018	500
				         |      OUT/2	      01/07/2018	500
-------------------------------------------------------------------------------------
INW/2	      01/07/2018	500	 |	OUT/2	      01/07/2018	250
				         |      OUT/3	      01/07/2018	250
-------------------------------------------------------------------------------------

Thank you,
Chintan
Re: Running settlement of inward vs outward quantity [message #671250 is a reply to message #671249] Mon, 20 August 2018 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 66684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You KNOW what is missing.

Re: Running settlement of inward vs outward quantity [message #671253 is a reply to message #671250] Mon, 20 August 2018 05:25 Go to previous messageGo to next message
Bill B
Messages: 1907
Registered: December 2004
Senior Member
It's easy to do, but we need the table layout in a create script and some sample data in insert statements
Re: Running settlement of inward vs outward quantity [message #671260 is a reply to message #671253] Mon, 20 August 2018 06:38 Go to previous messageGo to next message
chintan.patel
Messages: 159
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks Bill B
Please find below scrip

CREATE TABLE INW_DATA
(
  INW_NO    VARCHAR2(5 BYTE),
  INW_DATE  DATE,
  QTY       NUMBER(5)
);

Insert into INW_DATA   (INW_NO, INW_DATE, QTY) Values ('INW/1', TO_DATE('07/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1000);
Insert into INW_DATA   (INW_NO, INW_DATE, QTY) Values ('INW/2', TO_DATE('07/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 500);

CREATE TABLE OUT_DATA
(
  OUT_NO    VARCHAR2(5 BYTE),
  OUT_DATE  DATE,
  QTY       NUMBER(5)
);

Insert into OUT_DATA   (OUT_NO, OUT_DATE, QTY) Values   ('OUT/1', TO_DATE('07/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 500);
Insert into OUT_DATA   (OUT_NO, OUT_DATE, QTY) Values   ('OUT/2', TO_DATE('07/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 750);
Insert into OUT_DATA   (OUT_NO, OUT_DATE, QTY) Values   ('OUT/3', TO_DATE('07/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 250);
Re: Running settlement of inward vs outward quantity [message #671262 is a reply to message #671260] Mon, 20 August 2018 07:18 Go to previous messageGo to next message
Bill B
Messages: 1907
Registered: December 2004
Senior Member
The following code will do what you want
SELECT Inw.Inw_no,
       Inw.Inw_date,
       Inw.Qty,
       Out.Out_no,
       Out.Out_date,
       Out.Qty
FROM (SELECT A.Inw_no,
             A.Inw_date,
             A.Qty,
             ROW_NUMBER()
                 OVER(PARTITION BY A.Inw_no, A.Inw_date ORDER BY A.Qty DESC)
                 Rn
      FROM Inw_data A) Inw
     FULL OUTER JOIN
     (SELECT A.Out_no,
             A.Out_date,
             A.Qty,
             ROW_NUMBER()
                 OVER(PARTITION BY Out_no, Out_date ORDER BY A.Qty DESC)
                 Rn
      FROM Out_data A) Out
         ON SUBSTR(Inw.Inw_no, INSTR(Inw.Inw_no, '/') + 1) =
            SUBSTR(Out.Out_no, INSTR(Out.Out_no, '/') + 1)
        AND Inw.Inw_date = Out.Out_date
        AND Inw.Rn = Out.Rn
ORDER BY NVL(Inw.Inw_no, Out.Out_no),
         NVL(Inw.Inw_date, Out.Out_date),
         NVL(Inw.Rn, Out.Rn)
Re: Running settlement of inward vs outward quantity [message #671263 is a reply to message #671262] Mon, 20 August 2018 07:32 Go to previous messageGo to next message
chintan.patel
Messages: 159
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks Bill B but do not get desired result as shared

Re: Running settlement of inward vs outward quantity [message #671264 is a reply to message #671263] Mon, 20 August 2018 07:33 Go to previous messageGo to next message
Bill B
Messages: 1907
Registered: December 2004
Senior Member
Oh, I see. You wanted to join on like quantity. Sorry I will rewrite
Re: Running settlement of inward vs outward quantity [message #671265 is a reply to message #671264] Mon, 20 August 2018 07:38 Go to previous messageGo to next message
Bill B
Messages: 1907
Registered: December 2004
Senior Member
SELECT Inw.Inw_no,
       Inw.Inw_date,
       Inw.Qty,
       Out.Out_no,
       Out.Out_date,
       Out.Qty
FROM (SELECT A.Inw_no,
             A.Inw_date,
             A.Qty,
             ROW_NUMBER() OVER (ORDER BY A.Qty DESC) Rn
      FROM Inw_data A) Inw
     FULL OUTER JOIN (SELECT A.Out_no,
                             A.Out_date,
                             A.Qty,
                             ROW_NUMBER() OVER (ORDER BY A.Qty DESC) Rn
                      FROM Out_data A) Out
         ON Inw.Rn = Out.Rn
ORDER BY NVL(Inw.Rn, Out.Rn)
Re: Running settlement of inward vs outward quantity [message #671266 is a reply to message #671265] Mon, 20 August 2018 07:46 Go to previous messageGo to next message
chintan.patel
Messages: 159
Registered: July 2008
Location: Ahmedabad
Senior Member
Out/2 should be shared against both inwards
Re: Running settlement of inward vs outward quantity [message #671267 is a reply to message #671266] Mon, 20 August 2018 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 66684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why don't you give the COMPLETE specification of what you want?

Re: Running settlement of inward vs outward quantity [message #671268 is a reply to message #671266] Mon, 20 August 2018 07:51 Go to previous messageGo to next message
Bill B
Messages: 1907
Registered: December 2004
Senior Member
Your specified out data is

Insert into OUT_DATA (OUT_NO, OUT_DATE, QTY) Values ('OUT/1', TO_DATE('07/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 500);
Insert into OUT_DATA (OUT_NO, OUT_DATE, QTY) Values ('OUT/2', TO_DATE('07/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 750);
Insert into OUT_DATA (OUT_NO, OUT_DATE, QTY) Values ('OUT/3', TO_DATE('07/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 250);

there is only 1 500 value and only 1 250. Are you saying that you have a new requirement that the 750 should be broken up into 2 rows of 500 and 250. If that is the new requirement what is the rule to determine what row gets split up and what are the rules for splinting up a data row?

The rules can't be specific to this very small subset of data. It must hold when you might have multiple days and thousands of rows.

Also is the requirement that each qty change on each row is tied to a qty check on the other side.

[Updated on: Mon, 20 August 2018 07:57]

Report message to a moderator

Re: Running settlement of inward vs outward quantity [message #671279 is a reply to message #671266] Mon, 20 August 2018 18:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2885
Registered: January 2010
Location: Connecticut, USA
Senior Member
WITH T1 AS (
            SELECT  INW_DATA.*,
                    ROW_NUMBER() OVER(ORDER BY INW_DATE,INW_NO,ROWID) RN,
                    SUM(QTY) OVER(ORDER BY INW_DATE,INW_NO,ROWID) - QTY + 1 PREV_RUNNING_QTY,
                    SUM(QTY) OVER(ORDER BY INW_DATE,INW_NO,ROWID) RUNNING_QTY
              FROM  INW_DATA
           ),
     T2 AS (
            SELECT  OUT_DATA.*,
                    ROW_NUMBER() OVER(ORDER BY OUT_DATE,OUT_NO,ROWID) RN,
                    SUM(QTY) OVER(ORDER BY OUT_DATE,OUT_NO,ROWID) - QTY + 1 PREV_RUNNING_QTY,
                    SUM(QTY) OVER(ORDER BY OUT_DATE,OUT_NO,ROWID) RUNNING_QTY
              FROM  OUT_DATA
           ),
     T3 AS (
            SELECT  T1.INW_NO,
                    T1.INW_DATE,
                    T1.QTY INW_QTY,
                    T2.OUT_NO,
                    T2.OUT_DATE,
                    T2.QTY OUT_QTY,
                    CASE
                      WHEN T1.RUNNING_QTY - T2.RUNNING_QTY < 0 THEN ABS(T1.RUNNING_QTY - T2.RUNNING_QTY)
                      ELSE 0
                    END LEFTOVER_QTY,
                    ROW_NUMBER() OVER(ORDER BY T1.RN NULLS LAST,T2.RN NULLS LAST) RN
              FROM      T1
                    FULL OUTER JOIN
                        T2
                      ON     T1.RUNNING_QTY >= T2.PREV_RUNNING_QTY
                         AND
                             T1.PREV_RUNNING_QTY < T2.RUNNING_QTY
           )
SELECT  INW_NO,
        INW_DATE,
        INW_QTY,
        OUT_NO,
        OUT_DATE,
        CASE LAG(LEFTOVER_QTY,1,0) OVER(ORDER BY RN)
          WHEN 0 THEN OUT_QTY - LEFTOVER_QTY
          ELSE LEAST(INW_QTY,OUT_QTY,LAG(LEFTOVER_QTY,1,0) OVER(ORDER BY RN))
        END OUT_QTY
  FROM  T3
  ORDER BY RN
/

INW_N INW_DATE     INW_QTY OUT_N OUT_DATE     OUT_QTY
----- --------- ---------- ----- --------- ----------
INW/1 01-JUL-18       1000 OUT/1 01-JUL-18        500
INW/1 01-JUL-18       1000 OUT/2 01-JUL-18        500
INW/2 01-JUL-18        500 OUT/2 01-JUL-18        250
INW/2 01-JUL-18        500 OUT/3 01-JUL-18        250

SQL> 

SY.
Re: Running settlement of inward vs outward quantity [message #671301 is a reply to message #671279] Tue, 21 August 2018 06:02 Go to previous messageGo to next message
chintan.patel
Messages: 159
Registered: July 2008
Location: Ahmedabad
Senior Member
Thank you Solomon Yakobson, its working
Re: Running settlement of inward vs outward quantity [message #671314 is a reply to message #671301] Tue, 21 August 2018 08:02 Go to previous message
quirks
Messages: 81
Registered: October 2014
Member
WITH
    DAT_INW AS(SELECT INW_DATA.*, TO_NUMBER(SUBSTR(INW_NO, INSTR(INW_NO, '/') + 1, LENGTH(INW_NO))) REC_NO FROM INW_DATA),
    DAT_OUT AS(SELECT OUT_DATA.*, TO_NUMBER(SUBSTR(OUT_NO, INSTR(OUT_NO, '/') + 1, LENGTH(OUT_NO))) REC_NO FROM OUT_DATA),
    PREPARE_REPORT (
                       INW_NO
                      ,INW_DATE
                      ,INW_QTY
                      ,OUT_NO
                      ,OUT_DATE
                      ,OUT_QTY
                      ,REST_IN
                      ,REST_OUT
                      ,NEXT_REC_NO_IN
                      ,NEXT_REC_NO_OUT
                   )
    AS
        (SELECT DAT_INW.INW_NO
               ,DAT_INW.INW_DATE
               ,DAT_INW.QTY AS INW_QTY
               ,DAT_OUT.OUT_NO
               ,DAT_OUT.OUT_DATE
               ,CASE WHEN DAT_INW.QTY > DAT_OUT.QTY THEN DAT_OUT.QTY ELSE DAT_INW.QTY END AS OUT_QTY
               ,CASE WHEN DAT_INW.QTY > DAT_OUT.QTY THEN DAT_INW.QTY - DAT_OUT.QTY END AS REST_IN
               ,CASE WHEN DAT_INW.QTY < DAT_OUT.QTY THEN DAT_OUT.QTY - DAT_INW.QTY END AS REST_OUT
               ,CASE WHEN DAT_INW.QTY > DAT_OUT.QTY THEN DAT_INW.REC_NO ELSE DAT_INW.REC_NO + 1 END AS NEXT_REC_NO_IN
               ,CASE WHEN DAT_INW.QTY < DAT_OUT.QTY THEN DAT_OUT.REC_NO ELSE DAT_OUT.REC_NO + 1 END AS NEXT_REC_NO_OUT
         FROM DAT_INW
              JOIN DAT_OUT ON (DAT_INW.REC_NO = 1 AND DAT_OUT.REC_NO = 1)
         UNION ALL
         SELECT DAT_INW.INW_NO
               ,DAT_INW.INW_DATE
               ,DAT_INW.QTY
                    AS INW_QTY
               ,DAT_OUT.OUT_NO
               ,DAT_OUT.OUT_DATE
               ,CASE WHEN PREPARE_REPORT.REST_IN IS NOT NULL
                    THEN
                        CASE WHEN PREPARE_REPORT.REST_IN > COALESCE(PREPARE_REPORT.REST_OUT, DAT_OUT.QTY)
                            THEN COALESCE(PREPARE_REPORT.REST_OUT, DAT_OUT.QTY)
                            ELSE PREPARE_REPORT.REST_IN
                        END
                    ELSE
                        CASE WHEN DAT_INW.QTY > COALESCE(PREPARE_REPORT.REST_OUT, DAT_OUT.QTY)
                            THEN COALESCE(PREPARE_REPORT.REST_OUT, DAT_OUT.QTY)
                            ELSE DAT_INW.QTY
                        END
                END
                    AS OUT_QTY
               ,CASE WHEN COALESCE(PREPARE_REPORT.REST_IN, DAT_INW.QTY) > COALESCE(PREPARE_REPORT.REST_OUT, DAT_OUT.QTY)
                    THEN COALESCE(PREPARE_REPORT.REST_IN, DAT_INW.QTY) - COALESCE(PREPARE_REPORT.REST_OUT, DAT_OUT.QTY)
                END
                    AS REST_IN
               ,CASE WHEN COALESCE(PREPARE_REPORT.REST_IN, DAT_INW.QTY) < COALESCE(PREPARE_REPORT.REST_OUT, DAT_OUT.QTY)
                    THEN COALESCE(PREPARE_REPORT.REST_OUT, DAT_OUT.QTY) - COALESCE(PREPARE_REPORT.REST_IN, DAT_INW.QTY)
                END
                    AS REST_OUT
               ,CASE WHEN COALESCE(PREPARE_REPORT.REST_IN, DAT_INW.QTY) > COALESCE(PREPARE_REPORT.REST_OUT, DAT_OUT.QTY)
                    THEN NEXT_REC_NO_IN
                    ELSE NEXT_REC_NO_IN + 1
                END
                    AS NEXT_REC_NO_IN
               ,CASE WHEN COALESCE(PREPARE_REPORT.REST_IN, DAT_INW.QTY) >= COALESCE(PREPARE_REPORT.REST_OUT, DAT_OUT.QTY)
                    THEN NEXT_REC_NO_OUT + 1
                    ELSE NEXT_REC_NO_OUT
                END
                    AS NEXT_REC_NO_OUT
         FROM PREPARE_REPORT
              JOIN DAT_INW ON (PREPARE_REPORT.NEXT_REC_NO_IN = DAT_INW.REC_NO)
              LEFT JOIN DAT_OUT ON (PREPARE_REPORT.NEXT_REC_NO_OUT = DAT_OUT.REC_NO))
        CYCLE NEXT_REC_NO_IN, NEXT_REC_NO_OUT SET CYCLE TO '1' DEFAULT '0'
SELECT INW_NO, INW_DATE, INW_QTY, OUT_NO, OUT_DATE, OUT_QTY
FROM PREPARE_REPORT

Damn, to late ... Sad

[Updated on: Tue, 21 August 2018 08:07]

Report message to a moderator

Previous Topic: Relation between two gold
Next Topic: consistency constraint ON store Procedure
Goto Forum:
  


Current Time: Thu Nov 14 18:51:23 CST 2019