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 |
chintan.patel
Messages: 162 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 #671260 is a reply to message #671253] |
Mon, 20 August 2018 06:38 |
chintan.patel
Messages: 162 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 |
Bill B
Messages: 1971 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 #671265 is a reply to message #671264] |
Mon, 20 August 2018 07:38 |
Bill B
Messages: 1971 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 #671268 is a reply to message #671266] |
Mon, 20 August 2018 07:51 |
Bill B
Messages: 1971 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 |
Solomon Yakobson
Messages: 3285 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 #671314 is a reply to message #671301] |
Tue, 21 August 2018 08:02 |
|
quirks
Messages: 82 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 ...
[Updated on: Tue, 21 August 2018 08:07] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:40:40 CDT 2024
|