SQL Running total over the same key
0
edit: As User Larna Requested a create/insert for the subquery tables I have added these as well.
The below query returns:
DocNum ItemCode Planned Qty WhsCode BinCode QuantityToPick
901903 A00001 12.000000 98 98-A001 7.000000
901903 A00001 12.000000 98 98-A002 5.000000 --sum of 5 and 7 makes 12
901904 A00001 17.000000 98 98-A001 7.000000
901904 A00001 17.000000 98 98-A002 6.000000
901904 A00001 17.000000 98 98-A003 4.000000 --sum of 7,6 and 4 makes 17
But I want to have this result:
DocNum ItemCode Planned Qty WhsCode BinCode QuantityToPick
901903 A00001 12.000000 98 98-A001 7.000000
901903 A00001 12.000000 98 98-A002 5.000000 --sum of 5 and 7 makes 12
-- continue from bin code 98A002 for the new docnum as we had 5 out of 6 where consumed
901904 A00001 17.000000 98 98-A002 1.000000
901904 A00001 17.000000 98 98-A003 12.000000
901904 A00001 17.000000 98 98-A004 4.000000 --sum of 1,12 and 4 makes 17
So basically I want to continue where I left off after using the first DocNum,ItemCode,Quantity to eat the first bin and the second bin partially
and continue with the second DocNum,ItemCode,Quantity to "eat" remaining quantity of the second bin and then the other left over bins until I reach 0
But I'm a bit stuck on how to do this.
Does anyone has any advice/solution?
-- Create Tables for Subquerys + insert relevant data
CREATE TABLE [dbo].[WOR1](-- Removed the unimportant columns
[DocEntry] [int] NOT NULL,
[LineNum] [int] NOT NULL,
[ItemCode] [nvarchar](50) NULL,
[PlannedQty] [numeric](19, 6) NULL,
[IssuedQty] [numeric](19, 6) NULL,
[wareHouse] [nvarchar](8) NULL
)
INSERT INTO [dbo].[WOR1]
VALUES(154 ,0 ,'A00001' ,12.000000 ,0.000000, '01')
,(154 ,1 ,'P10001' ,12.000000 ,0.000000, '01')
,(154 ,2 ,'L10001' ,12.000000 ,0.000000, '01')
,(155 ,0 ,'A00001' ,17.000000 ,0.000000, '01')
,(155 ,1 ,'P10001' ,17.000000 ,0.000000, '01')
,(155 ,2 ,'L10001' ,17.000000 ,0.000000, '01')
CREATE TABLE [dbo].[OWOR](-- Removed the unimportant columns
[DocEntry] [int] NOT NULL,
[DocNum] [int] NOT NULL,
[Status] [char](1) NULL
)
INSERT INTO [dbo].[OWOR]
VALUES( 154, 901903, 'R')
,( 155, 901904, 'R')
CREATE TABLE [dbo].[OIBQ](-- Removed the unimportant columns
[ItemCode] [nvarchar](50) NOT NULL,
[BinAbs] [int] NOT NULL,
[OnHandQty] [numeric](19, 6) NULL,
[WhsCode] [nvarchar](8) NULL
)
INSERT INTO [dbo].[OIBQ]
VALUES('A00001', 7, 15.000000, '98')
,('A00001', 2, 7.000000, '98')
,('A00001', 3, 6.000000, '98')
,('A00001', 4, 12.000000, '98')
CREATE TABLE [dbo].[OBIN](-- Removed the unimportant columns
[AbsEntry] [int] NOT NULL,
[BinCode] [nvarchar](228) NOT NULL,
[WhsCode] [nvarchar](8) NULL
)
INSERT INTO [dbo].[OBIN]
VALUES(1, '98-SYSTEM-BIN-LOCATION', '98')
,(2, '98-A001', '98')
,(3, '98-A002', '98')
,(4, '98-A003', '98')
,(5, '98-A004', '98')
,(6, '98-A005', '98')
,(7, '98-A006', '98')
Query with subqueries:
SELECT
WOPICK.DocNum,
WOPICK.ItemCode,
WOPICK.Quantity as 'Planned Qty',
BINSTOCK.WhsCode,
BINSTOCK.BinCode,
CASE WHEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity)) <BINSTOCK.Quantity
THEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity))
ELSE BINSTOCK.Quantity END as 'QuantityToPick'-- FinalQty
FROM (
SELECT IBQ.ItemCode, SUM(IBQ.OnHandQty)as 'Quantity',BIN.WhsCode, BIN.BinCode ,
SUM(SUM(OnHandQty)) OVER (PARTITION BY IBQ.ItemCode
ORDER BY IBQ.ItemCode,BIN.BinCode ) as 'RunningTotalQty'
FROM OIBQ IBQ INNER JOIN OBIN BIN on IBQ.BinAbs = BIN.AbsEntry
GROUP BY IBQ.ItemCode, BIN.WhsCode, BIN.BinCode
) BINSTOCK
INNER JOIN (
SELECT distinct WOR.DocNum, WOR1.LineNum, WOR1.ItemCode, WOR1.PlannedQty - WOR1.IssuedQty as 'Quantity'
FROM OWOR WOR
INNER JOIN WOR1 WOR1 on WOR.DocEntry = WOR1.DocEntry
INNER JOIN OIBQ IBQ on IBQ.ItemCode = WOR1.ItemCode
where WOR.Status = 'R'
) WOPICK on BINSTOCK.ItemCode = WOPICK.ItemCode
WHERE BINSTOCK.RunningTotalQty - BINSTOCK.Quantity < WOPICK.Quantity
order by WOPICK.LineNum;
Query without subqueries:
-- SIMPLIFIED Version (Subquery results in tables BINSTOCK & WOPICK)
CREATE TABLE [dbo].[BINSTOCK](
ItemCode NVARCHAR(10) NOT NULL
,Quantity DECIMAL (19,6) NOT NULL
,WhsCode INT NOT NULL
,BinCode NVARCHAR (10) NOT NULL
,RunningTotalQty DECIMAL (19,6) NOT NULL
)
INSERT INTO [dbo].[BINSTOCK]
VALUES('A00001' , 7.000000, 98, '98-A001' , 7.000000),
('A00001' , 6.000000, 98, '98-A002' , 13.000000),
('A00001' , 12.000000, 98, '98-A003' , 25.000000),
('A00001' , 15.000000, 98, '98-A006' , 40.000000)
CREATE TABLE [dbo].[WOPICK](
DocNum INT NOT NULL
,LineNum INT NOT NULL
,ItemCode NVARCHAR (10) NOT NULL
,Quantity DECIMAL (19,6) NOT NULL
)
INSERT INTO [dbo].[WOPICK]
VALUES(901903,0,'A00001',12.000000),
(901904,0,'A00001',17.000000)
SELECT
WOPICK.DocNum,
WOPICK.ItemCode,
WOPICK.Quantity as 'Planned Qty',
BINSTOCK.WhsCode,
BINSTOCK.BinCode,
CASE WHEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity)) <BINSTOCK.Quantity
THEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity))
ELSE BINSTOCK.Quantity END as 'QuantityToPick'-- FinalQty
FROM BINSTOCK
INNER JOIN WOPICK on BINSTOCK.ItemCode = WOPICK.ItemCode
WHERE BINSTOCK.RunningTotalQty - BINSTOCK.Quantity < WOPICK.Quantity
order by WOPICK.LineNum;
sql
sql-server asked Nov 28 '18 at 10:22
Chester van ReeChester van Ree
757
|
show 6 more comments
0
edit: As User Larna Requested a create/insert for the subquery tables I have added these as well.
The below query returns:
DocNum ItemCode Planned Qty WhsCode BinCode QuantityToPick
901903 A00001 12.000000 98 98-A001 7.000000
901903 A00001 12.000000 98 98-A002 5.000000 --sum of 5 and 7 makes 12
901904 A00001 17.000000 98 98-A001 7.000000
901904 A00001 17.000000 98 98-A002 6.000000
901904 A00001 17.000000 98 98-A003 4.000000 --sum of 7,6 and 4 makes 17
But I want to have this result:
DocNum ItemCode Planned Qty WhsCode BinCode QuantityToPick
901903 A00001 12.000000 98 98-A001 7.000000
901903 A00001 12.000000 98 98-A002 5.000000 --sum of 5 and 7 makes 12
-- continue from bin code 98A002 for the new docnum as we had 5 out of 6 where consumed
901904 A00001 17.000000 98 98-A002 1.000000
901904 A00001 17.000000 98 98-A003 12.000000
901904 A00001 17.000000 98 98-A004 4.000000 --sum of 1,12 and 4 makes 17
So basically I want to continue where I left off after using the first DocNum,ItemCode,Quantity to eat the first bin and the second bin partially
and continue with the second DocNum,ItemCode,Quantity to "eat" remaining quantity of the second bin and then the other left over bins until I reach 0
But I'm a bit stuck on how to do this.
Does anyone has any advice/solution?
-- Create Tables for Subquerys + insert relevant data
CREATE TABLE [dbo].[WOR1](-- Removed the unimportant columns
[DocEntry] [int] NOT NULL,
[LineNum] [int] NOT NULL,
[ItemCode] [nvarchar](50) NULL,
[PlannedQty] [numeric](19, 6) NULL,
[IssuedQty] [numeric](19, 6) NULL,
[wareHouse] [nvarchar](8) NULL
)
INSERT INTO [dbo].[WOR1]
VALUES(154 ,0 ,'A00001' ,12.000000 ,0.000000, '01')
,(154 ,1 ,'P10001' ,12.000000 ,0.000000, '01')
,(154 ,2 ,'L10001' ,12.000000 ,0.000000, '01')
,(155 ,0 ,'A00001' ,17.000000 ,0.000000, '01')
,(155 ,1 ,'P10001' ,17.000000 ,0.000000, '01')
,(155 ,2 ,'L10001' ,17.000000 ,0.000000, '01')
CREATE TABLE [dbo].[OWOR](-- Removed the unimportant columns
[DocEntry] [int] NOT NULL,
[DocNum] [int] NOT NULL,
[Status] [char](1) NULL
)
INSERT INTO [dbo].[OWOR]
VALUES( 154, 901903, 'R')
,( 155, 901904, 'R')
CREATE TABLE [dbo].[OIBQ](-- Removed the unimportant columns
[ItemCode] [nvarchar](50) NOT NULL,
[BinAbs] [int] NOT NULL,
[OnHandQty] [numeric](19, 6) NULL,
[WhsCode] [nvarchar](8) NULL
)
INSERT INTO [dbo].[OIBQ]
VALUES('A00001', 7, 15.000000, '98')
,('A00001', 2, 7.000000, '98')
,('A00001', 3, 6.000000, '98')
,('A00001', 4, 12.000000, '98')
CREATE TABLE [dbo].[OBIN](-- Removed the unimportant columns
[AbsEntry] [int] NOT NULL,
[BinCode] [nvarchar](228) NOT NULL,
[WhsCode] [nvarchar](8) NULL
)
INSERT INTO [dbo].[OBIN]
VALUES(1, '98-SYSTEM-BIN-LOCATION', '98')
,(2, '98-A001', '98')
,(3, '98-A002', '98')
,(4, '98-A003', '98')
,(5, '98-A004', '98')
,(6, '98-A005', '98')
,(7, '98-A006', '98')
Query with subqueries:
SELECT
WOPICK.DocNum,
WOPICK.ItemCode,
WOPICK.Quantity as 'Planned Qty',
BINSTOCK.WhsCode,
BINSTOCK.BinCode,
CASE WHEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity)) <BINSTOCK.Quantity
THEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity))
ELSE BINSTOCK.Quantity END as 'QuantityToPick'-- FinalQty
FROM (
SELECT IBQ.ItemCode, SUM(IBQ.OnHandQty)as 'Quantity',BIN.WhsCode, BIN.BinCode ,
SUM(SUM(OnHandQty)) OVER (PARTITION BY IBQ.ItemCode
ORDER BY IBQ.ItemCode,BIN.BinCode ) as 'RunningTotalQty'
FROM OIBQ IBQ INNER JOIN OBIN BIN on IBQ.BinAbs = BIN.AbsEntry
GROUP BY IBQ.ItemCode, BIN.WhsCode, BIN.BinCode
) BINSTOCK
INNER JOIN (
SELECT distinct WOR.DocNum, WOR1.LineNum, WOR1.ItemCode, WOR1.PlannedQty - WOR1.IssuedQty as 'Quantity'
FROM OWOR WOR
INNER JOIN WOR1 WOR1 on WOR.DocEntry = WOR1.DocEntry
INNER JOIN OIBQ IBQ on IBQ.ItemCode = WOR1.ItemCode
where WOR.Status = 'R'
) WOPICK on BINSTOCK.ItemCode = WOPICK.ItemCode
WHERE BINSTOCK.RunningTotalQty - BINSTOCK.Quantity < WOPICK.Quantity
order by WOPICK.LineNum;
Query without subqueries:
-- SIMPLIFIED Version (Subquery results in tables BINSTOCK & WOPICK)
CREATE TABLE [dbo].[BINSTOCK](
ItemCode NVARCHAR(10) NOT NULL
,Quantity DECIMAL (19,6) NOT NULL
,WhsCode INT NOT NULL
,BinCode NVARCHAR (10) NOT NULL
,RunningTotalQty DECIMAL (19,6) NOT NULL
)
INSERT INTO [dbo].[BINSTOCK]
VALUES('A00001' , 7.000000, 98, '98-A001' , 7.000000),
('A00001' , 6.000000, 98, '98-A002' , 13.000000),
('A00001' , 12.000000, 98, '98-A003' , 25.000000),
('A00001' , 15.000000, 98, '98-A006' , 40.000000)
CREATE TABLE [dbo].[WOPICK](
DocNum INT NOT NULL
,LineNum INT NOT NULL
,ItemCode NVARCHAR (10) NOT NULL
,Quantity DECIMAL (19,6) NOT NULL
)
INSERT INTO [dbo].[WOPICK]
VALUES(901903,0,'A00001',12.000000),
(901904,0,'A00001',17.000000)
SELECT
WOPICK.DocNum,
WOPICK.ItemCode,
WOPICK.Quantity as 'Planned Qty',
BINSTOCK.WhsCode,
BINSTOCK.BinCode,
CASE WHEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity)) <BINSTOCK.Quantity
THEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity))
ELSE BINSTOCK.Quantity END as 'QuantityToPick'-- FinalQty
FROM BINSTOCK
INNER JOIN WOPICK on BINSTOCK.ItemCode = WOPICK.ItemCode
WHERE BINSTOCK.RunningTotalQty - BINSTOCK.Quantity < WOPICK.Quantity
order by WOPICK.LineNum;
sql
sql-server asked Nov 28 '18 at 10:22
Chester van ReeChester van Ree
757
2
Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3). It's even worse here, due to the reuse of aliases. I have literally no idea what each alias is referring to.a, for example, can beOWOR,OIBQ, or your first subquery, depending on where you are...
– Larnu
Nov 28 '18 at 10:41
You are right, let me change the aliases.
– Chester van Ree
Nov 28 '18 at 10:44
Your sample data, also has 2 tables. They are unnamed for I'm going to refer to them asItemandDocument. Your Query, however, has 4 tablesOWOR,OBIN,WOR1andOIBQ; with no reference to which is which. Which of those are theItemtable, and which are theDocumenttable?
– Larnu
Nov 28 '18 at 10:45
Hopefully this clears it up a bit.
– Chester van Ree
Nov 28 '18 at 10:51
So the sample data is the result set from the subqueries, not the tables? You would be far better off giving us the sample data from your tablesOIBQ,OBIN,OWORandWOR1, and far preferable as DDL (CREATE) andDML(INSERT`) statements.
– Larnu
Nov 28 '18 at 10:54
|
show 6 more comments
0
0
0
edit: As User Larna Requested a create/insert for the subquery tables I have added these as well.
The below query returns:
DocNum ItemCode Planned Qty WhsCode BinCode QuantityToPick
901903 A00001 12.000000 98 98-A001 7.000000
901903 A00001 12.000000 98 98-A002 5.000000 --sum of 5 and 7 makes 12
901904 A00001 17.000000 98 98-A001 7.000000
901904 A00001 17.000000 98 98-A002 6.000000
901904 A00001 17.000000 98 98-A003 4.000000 --sum of 7,6 and 4 makes 17
But I want to have this result:
DocNum ItemCode Planned Qty WhsCode BinCode QuantityToPick
901903 A00001 12.000000 98 98-A001 7.000000
901903 A00001 12.000000 98 98-A002 5.000000 --sum of 5 and 7 makes 12
-- continue from bin code 98A002 for the new docnum as we had 5 out of 6 where consumed
901904 A00001 17.000000 98 98-A002 1.000000
901904 A00001 17.000000 98 98-A003 12.000000
901904 A00001 17.000000 98 98-A004 4.000000 --sum of 1,12 and 4 makes 17
So basically I want to continue where I left off after using the first DocNum,ItemCode,Quantity to eat the first bin and the second bin partially
and continue with the second DocNum,ItemCode,Quantity to "eat" remaining quantity of the second bin and then the other left over bins until I reach 0
But I'm a bit stuck on how to do this.
Does anyone has any advice/solution?
-- Create Tables for Subquerys + insert relevant data
CREATE TABLE [dbo].[WOR1](-- Removed the unimportant columns
[DocEntry] [int] NOT NULL,
[LineNum] [int] NOT NULL,
[ItemCode] [nvarchar](50) NULL,
[PlannedQty] [numeric](19, 6) NULL,
[IssuedQty] [numeric](19, 6) NULL,
[wareHouse] [nvarchar](8) NULL
)
INSERT INTO [dbo].[WOR1]
VALUES(154 ,0 ,'A00001' ,12.000000 ,0.000000, '01')
,(154 ,1 ,'P10001' ,12.000000 ,0.000000, '01')
,(154 ,2 ,'L10001' ,12.000000 ,0.000000, '01')
,(155 ,0 ,'A00001' ,17.000000 ,0.000000, '01')
,(155 ,1 ,'P10001' ,17.000000 ,0.000000, '01')
,(155 ,2 ,'L10001' ,17.000000 ,0.000000, '01')
CREATE TABLE [dbo].[OWOR](-- Removed the unimportant columns
[DocEntry] [int] NOT NULL,
[DocNum] [int] NOT NULL,
[Status] [char](1) NULL
)
INSERT INTO [dbo].[OWOR]
VALUES( 154, 901903, 'R')
,( 155, 901904, 'R')
CREATE TABLE [dbo].[OIBQ](-- Removed the unimportant columns
[ItemCode] [nvarchar](50) NOT NULL,
[BinAbs] [int] NOT NULL,
[OnHandQty] [numeric](19, 6) NULL,
[WhsCode] [nvarchar](8) NULL
)
INSERT INTO [dbo].[OIBQ]
VALUES('A00001', 7, 15.000000, '98')
,('A00001', 2, 7.000000, '98')
,('A00001', 3, 6.000000, '98')
,('A00001', 4, 12.000000, '98')
CREATE TABLE [dbo].[OBIN](-- Removed the unimportant columns
[AbsEntry] [int] NOT NULL,
[BinCode] [nvarchar](228) NOT NULL,
[WhsCode] [nvarchar](8) NULL
)
INSERT INTO [dbo].[OBIN]
VALUES(1, '98-SYSTEM-BIN-LOCATION', '98')
,(2, '98-A001', '98')
,(3, '98-A002', '98')
,(4, '98-A003', '98')
,(5, '98-A004', '98')
,(6, '98-A005', '98')
,(7, '98-A006', '98')
Query with subqueries:
SELECT
WOPICK.DocNum,
WOPICK.ItemCode,
WOPICK.Quantity as 'Planned Qty',
BINSTOCK.WhsCode,
BINSTOCK.BinCode,
CASE WHEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity)) <BINSTOCK.Quantity
THEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity))
ELSE BINSTOCK.Quantity END as 'QuantityToPick'-- FinalQty
FROM (
SELECT IBQ.ItemCode, SUM(IBQ.OnHandQty)as 'Quantity',BIN.WhsCode, BIN.BinCode ,
SUM(SUM(OnHandQty)) OVER (PARTITION BY IBQ.ItemCode
ORDER BY IBQ.ItemCode,BIN.BinCode ) as 'RunningTotalQty'
FROM OIBQ IBQ INNER JOIN OBIN BIN on IBQ.BinAbs = BIN.AbsEntry
GROUP BY IBQ.ItemCode, BIN.WhsCode, BIN.BinCode
) BINSTOCK
INNER JOIN (
SELECT distinct WOR.DocNum, WOR1.LineNum, WOR1.ItemCode, WOR1.PlannedQty - WOR1.IssuedQty as 'Quantity'
FROM OWOR WOR
INNER JOIN WOR1 WOR1 on WOR.DocEntry = WOR1.DocEntry
INNER JOIN OIBQ IBQ on IBQ.ItemCode = WOR1.ItemCode
where WOR.Status = 'R'
) WOPICK on BINSTOCK.ItemCode = WOPICK.ItemCode
WHERE BINSTOCK.RunningTotalQty - BINSTOCK.Quantity < WOPICK.Quantity
order by WOPICK.LineNum;
Query without subqueries:
-- SIMPLIFIED Version (Subquery results in tables BINSTOCK & WOPICK)
CREATE TABLE [dbo].[BINSTOCK](
ItemCode NVARCHAR(10) NOT NULL
,Quantity DECIMAL (19,6) NOT NULL
,WhsCode INT NOT NULL
,BinCode NVARCHAR (10) NOT NULL
,RunningTotalQty DECIMAL (19,6) NOT NULL
)
INSERT INTO [dbo].[BINSTOCK]
VALUES('A00001' , 7.000000, 98, '98-A001' , 7.000000),
('A00001' , 6.000000, 98, '98-A002' , 13.000000),
('A00001' , 12.000000, 98, '98-A003' , 25.000000),
('A00001' , 15.000000, 98, '98-A006' , 40.000000)
CREATE TABLE [dbo].[WOPICK](
DocNum INT NOT NULL
,LineNum INT NOT NULL
,ItemCode NVARCHAR (10) NOT NULL
,Quantity DECIMAL (19,6) NOT NULL
)
INSERT INTO [dbo].[WOPICK]
VALUES(901903,0,'A00001',12.000000),
(901904,0,'A00001',17.000000)
SELECT
WOPICK.DocNum,
WOPICK.ItemCode,
WOPICK.Quantity as 'Planned Qty',
BINSTOCK.WhsCode,
BINSTOCK.BinCode,
CASE WHEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity)) <BINSTOCK.Quantity
THEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity))
ELSE BINSTOCK.Quantity END as 'QuantityToPick'-- FinalQty
FROM BINSTOCK
INNER JOIN WOPICK on BINSTOCK.ItemCode = WOPICK.ItemCode
WHERE BINSTOCK.RunningTotalQty - BINSTOCK.Quantity < WOPICK.Quantity
order by WOPICK.LineNum;
sql
sql-server asked Nov 28 '18 at 10:22
Chester van ReeChester van Ree
757
edit: As User Larna Requested a create/insert for the subquery tables I have added these as well.
The below query returns:
DocNum ItemCode Planned Qty WhsCode BinCode QuantityToPick
901903 A00001 12.000000 98 98-A001 7.000000
901903 A00001 12.000000 98 98-A002 5.000000 --sum of 5 and 7 makes 12
901904 A00001 17.000000 98 98-A001 7.000000
901904 A00001 17.000000 98 98-A002 6.000000
901904 A00001 17.000000 98 98-A003 4.000000 --sum of 7,6 and 4 makes 17
But I want to have this result:
DocNum ItemCode Planned Qty WhsCode BinCode QuantityToPick
901903 A00001 12.000000 98 98-A001 7.000000
901903 A00001 12.000000 98 98-A002 5.000000 --sum of 5 and 7 makes 12
-- continue from bin code 98A002 for the new docnum as we had 5 out of 6 where consumed
901904 A00001 17.000000 98 98-A002 1.000000
901904 A00001 17.000000 98 98-A003 12.000000
901904 A00001 17.000000 98 98-A004 4.000000 --sum of 1,12 and 4 makes 17
So basically I want to continue where I left off after using the first DocNum,ItemCode,Quantity to eat the first bin and the second bin partially
and continue with the second DocNum,ItemCode,Quantity to "eat" remaining quantity of the second bin and then the other left over bins until I reach 0
But I'm a bit stuck on how to do this.
Does anyone has any advice/solution?
-- Create Tables for Subquerys + insert relevant data
CREATE TABLE [dbo].[WOR1](-- Removed the unimportant columns
[DocEntry] [int] NOT NULL,
[LineNum] [int] NOT NULL,
[ItemCode] [nvarchar](50) NULL,
[PlannedQty] [numeric](19, 6) NULL,
[IssuedQty] [numeric](19, 6) NULL,
[wareHouse] [nvarchar](8) NULL
)
INSERT INTO [dbo].[WOR1]
VALUES(154 ,0 ,'A00001' ,12.000000 ,0.000000, '01')
,(154 ,1 ,'P10001' ,12.000000 ,0.000000, '01')
,(154 ,2 ,'L10001' ,12.000000 ,0.000000, '01')
,(155 ,0 ,'A00001' ,17.000000 ,0.000000, '01')
,(155 ,1 ,'P10001' ,17.000000 ,0.000000, '01')
,(155 ,2 ,'L10001' ,17.000000 ,0.000000, '01')
CREATE TABLE [dbo].[OWOR](-- Removed the unimportant columns
[DocEntry] [int] NOT NULL,
[DocNum] [int] NOT NULL,
[Status] [char](1) NULL
)
INSERT INTO [dbo].[OWOR]
VALUES( 154, 901903, 'R')
,( 155, 901904, 'R')
CREATE TABLE [dbo].[OIBQ](-- Removed the unimportant columns
[ItemCode] [nvarchar](50) NOT NULL,
[BinAbs] [int] NOT NULL,
[OnHandQty] [numeric](19, 6) NULL,
[WhsCode] [nvarchar](8) NULL
)
INSERT INTO [dbo].[OIBQ]
VALUES('A00001', 7, 15.000000, '98')
,('A00001', 2, 7.000000, '98')
,('A00001', 3, 6.000000, '98')
,('A00001', 4, 12.000000, '98')
CREATE TABLE [dbo].[OBIN](-- Removed the unimportant columns
[AbsEntry] [int] NOT NULL,
[BinCode] [nvarchar](228) NOT NULL,
[WhsCode] [nvarchar](8) NULL
)
INSERT INTO [dbo].[OBIN]
VALUES(1, '98-SYSTEM-BIN-LOCATION', '98')
,(2, '98-A001', '98')
,(3, '98-A002', '98')
,(4, '98-A003', '98')
,(5, '98-A004', '98')
,(6, '98-A005', '98')
,(7, '98-A006', '98')
Query with subqueries:
SELECT
WOPICK.DocNum,
WOPICK.ItemCode,
WOPICK.Quantity as 'Planned Qty',
BINSTOCK.WhsCode,
BINSTOCK.BinCode,
CASE WHEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity)) <BINSTOCK.Quantity
THEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity))
ELSE BINSTOCK.Quantity END as 'QuantityToPick'-- FinalQty
FROM (
SELECT IBQ.ItemCode, SUM(IBQ.OnHandQty)as 'Quantity',BIN.WhsCode, BIN.BinCode ,
SUM(SUM(OnHandQty)) OVER (PARTITION BY IBQ.ItemCode
ORDER BY IBQ.ItemCode,BIN.BinCode ) as 'RunningTotalQty'
FROM OIBQ IBQ INNER JOIN OBIN BIN on IBQ.BinAbs = BIN.AbsEntry
GROUP BY IBQ.ItemCode, BIN.WhsCode, BIN.BinCode
) BINSTOCK
INNER JOIN (
SELECT distinct WOR.DocNum, WOR1.LineNum, WOR1.ItemCode, WOR1.PlannedQty - WOR1.IssuedQty as 'Quantity'
FROM OWOR WOR
INNER JOIN WOR1 WOR1 on WOR.DocEntry = WOR1.DocEntry
INNER JOIN OIBQ IBQ on IBQ.ItemCode = WOR1.ItemCode
where WOR.Status = 'R'
) WOPICK on BINSTOCK.ItemCode = WOPICK.ItemCode
WHERE BINSTOCK.RunningTotalQty - BINSTOCK.Quantity < WOPICK.Quantity
order by WOPICK.LineNum;
Query without subqueries:
-- SIMPLIFIED Version (Subquery results in tables BINSTOCK & WOPICK)
CREATE TABLE [dbo].[BINSTOCK](
ItemCode NVARCHAR(10) NOT NULL
,Quantity DECIMAL (19,6) NOT NULL
,WhsCode INT NOT NULL
,BinCode NVARCHAR (10) NOT NULL
,RunningTotalQty DECIMAL (19,6) NOT NULL
)
INSERT INTO [dbo].[BINSTOCK]
VALUES('A00001' , 7.000000, 98, '98-A001' , 7.000000),
('A00001' , 6.000000, 98, '98-A002' , 13.000000),
('A00001' , 12.000000, 98, '98-A003' , 25.000000),
('A00001' , 15.000000, 98, '98-A006' , 40.000000)
CREATE TABLE [dbo].[WOPICK](
DocNum INT NOT NULL
,LineNum INT NOT NULL
,ItemCode NVARCHAR (10) NOT NULL
,Quantity DECIMAL (19,6) NOT NULL
)
INSERT INTO [dbo].[WOPICK]
VALUES(901903,0,'A00001',12.000000),
(901904,0,'A00001',17.000000)
SELECT
WOPICK.DocNum,
WOPICK.ItemCode,
WOPICK.Quantity as 'Planned Qty',
BINSTOCK.WhsCode,
BINSTOCK.BinCode,
CASE WHEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity)) <BINSTOCK.Quantity
THEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity))
ELSE BINSTOCK.Quantity END as 'QuantityToPick'-- FinalQty
FROM BINSTOCK
INNER JOIN WOPICK on BINSTOCK.ItemCode = WOPICK.ItemCode
WHERE BINSTOCK.RunningTotalQty - BINSTOCK.Quantity < WOPICK.Quantity
order by WOPICK.LineNum;
sql
sql-server sql
sql-server asked Nov 28 '18 at 10:22
Chester van ReeChester van Ree
757
asked Nov 28 '18 at 10:22
Chester van ReeChester van Ree
757
edited Nov 28 '18 at 16:13
Chester van Ree
asked Nov 28 '18 at 10:22
Chester van ReeChester van Ree
757
asked Nov 28 '18 at 10:22
Chester van ReeChester van Ree
757
asked Nov 28 '18 at 10:22
Chester van ReeChester van Ree
757
757
2
Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3). It's even worse here, due to the reuse of aliases. I have literally no idea what each alias is referring to.a, for example, can beOWOR,OIBQ, or your first subquery, depending on where you are...
– Larnu
Nov 28 '18 at 10:41
You are right, let me change the aliases.
– Chester van Ree
Nov 28 '18 at 10:44
Your sample data, also has 2 tables. They are unnamed for I'm going to refer to them asItemandDocument. Your Query, however, has 4 tablesOWOR,OBIN,WOR1andOIBQ; with no reference to which is which. Which of those are theItemtable, and which are theDocumenttable?
– Larnu
Nov 28 '18 at 10:45
Hopefully this clears it up a bit.
– Chester van Ree
Nov 28 '18 at 10:51
So the sample data is the result set from the subqueries, not the tables? You would be far better off giving us the sample data from your tablesOIBQ,OBIN,OWORandWOR1, and far preferable as DDL (CREATE) andDML(INSERT`) statements.
– Larnu
Nov 28 '18 at 10:54
|
show 6 more comments
2
Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3). It's even worse here, due to the reuse of aliases. I have literally no idea what each alias is referring to.a, for example, can beOWOR,OIBQ, or your first subquery, depending on where you are...
– Larnu
Nov 28 '18 at 10:41
You are right, let me change the aliases.
– Chester van Ree
Nov 28 '18 at 10:44
Your sample data, also has 2 tables. They are unnamed for I'm going to refer to them asItemandDocument. Your Query, however, has 4 tablesOWOR,OBIN,WOR1andOIBQ; with no reference to which is which. Which of those are theItemtable, and which are theDocumenttable?
– Larnu
Nov 28 '18 at 10:45
Hopefully this clears it up a bit.
– Chester van Ree
Nov 28 '18 at 10:51
So the sample data is the result set from the subqueries, not the tables? You would be far better off giving us the sample data from your tablesOIBQ,OBIN,OWORandWOR1, and far preferable as DDL (CREATE) andDML(INSERT`) statements.
– Larnu
Nov 28 '18 at 10:54
2
2
Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3). It's even worse here, due to the reuse of aliases. I have literally no idea what each alias is referring to.
a, for example, can be OWOR, OIBQ, or your first subquery, depending on where you are...– Larnu
Nov 28 '18 at 10:41
Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3). It's even worse here, due to the reuse of aliases. I have literally no idea what each alias is referring to.
a, for example, can be OWOR, OIBQ, or your first subquery, depending on where you are...– Larnu
Nov 28 '18 at 10:41
You are right, let me change the aliases.
– Chester van Ree
Nov 28 '18 at 10:44
You are right, let me change the aliases.
– Chester van Ree
Nov 28 '18 at 10:44
Your sample data, also has 2 tables. They are unnamed for I'm going to refer to them as
Item and Document. Your Query, however, has 4 tables OWOR, OBIN, WOR1 and OIBQ; with no reference to which is which. Which of those are the Item table, and which are the Document table?– Larnu
Nov 28 '18 at 10:45
Your sample data, also has 2 tables. They are unnamed for I'm going to refer to them as
Item and Document. Your Query, however, has 4 tables OWOR, OBIN, WOR1 and OIBQ; with no reference to which is which. Which of those are the Item table, and which are the Document table?– Larnu
Nov 28 '18 at 10:45
Hopefully this clears it up a bit.
– Chester van Ree
Nov 28 '18 at 10:51
Hopefully this clears it up a bit.
– Chester van Ree
Nov 28 '18 at 10:51
So the sample data is the result set from the subqueries, not the tables? You would be far better off giving us the sample data from your tables
OIBQ,OBIN,OWOR and WOR1, and far preferable as DDL (CREATE) and DML (INSERT`) statements.– Larnu
Nov 28 '18 at 10:54
So the sample data is the result set from the subqueries, not the tables? You would be far better off giving us the sample data from your tables
OIBQ,OBIN,OWOR and WOR1, and far preferable as DDL (CREATE) and DML (INSERT`) statements.– Larnu
Nov 28 '18 at 10:54
|
show 6 more comments
2 Answers
2
active
oldest
votes
2
This is actually easier than it first sounds. But you're going to want to wrap you're head around something first - you need running totals for the needed quantities as well:
SELECT OWOR.docNum, WOR1.itemCode, plannedQty,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) AS runningTotalNeeded
FROM OWOR
JOIN WOR1
ON WOR1.docEntry = OWOR.docEntry
WHERE OWOR.status = 'R'
.... with the combination of the "previous" running-needed column (and assuming a previous total quantity), we're now looking at an overlapping range query.
In combination with (a slightly modified version of) your BINSTOCK:
SELECT *
FROM (SELECT itemCode, whsCode, binCode, quantity,
SUM(quantity) OVER (PARTITION BY itemCode ORDER BY binCode) - quantity AS prevTotalQuantity,
SUM(quantity) OVER (PARTITION BY itemCode ORDER BY binCode) AS runningTotalQuantity
FROM (SELECT OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode, SUM(OIBQ.onHandQty) AS quantity
FROM OIBQ
JOIN OBIN
ON OBIN.absEntry = OIBQ.binAbs
GROUP BY OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode) TotalQuantity
) BINSTOCK
JOIN (SELECT OWOR.docNum, WOR1.itemCode, plannedQty,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode ORDER BY OWOR.docEntry) AS runningTotalNeeded
FROM OWOR
JOIN WOR1
ON WOR1.docEntry = OWOR.docEntry
WHERE OWOR.status = 'R') WOPICK
ON WOPICK.itemCode = BINSTOCK.itemCode
AND WOPICK.prevTotalNeeded <= BINSTOCK.runningTotalQuantity
AND WOPICK.runningTotalNeeded >= BINSTOCK.prevTotalQuantity
... which yields the following table:
itemCode | whsCode | binCode | quantity | prevTotalQuantity | runningTotalQuantity | docNum | itemCode | plannedQty | prevTotalNeeded | runningTotalNeeded
:------- | :------ | :------ | :-------- | :---------------- | :------------------- | -----: | :------- | :--------- | :-------------- | :-----------------
A00001 | 98 | 98-A001 | 7.000000 | 0.000000 | 7.000000 | 901903 | A00001 | 12.000000 | 0.000000 | 12.000000
A00001 | 98 | 98-A002 | 6.000000 | 7.000000 | 13.000000 | 901903 | A00001 | 12.000000 | 0.000000 | 12.000000
A00001 | 98 | 98-A002 | 6.000000 | 7.000000 | 13.000000 | 901904 | A00001 | 17.000000 | 12.000000 | 29.000000
A00001 | 98 | 98-A003 | 12.000000 | 13.000000 | 25.000000 | 901904 | A00001 | 17.000000 | 12.000000 | 29.000000
A00001 | 98 | 98-A006 | 15.000000 | 25.000000 | 40.000000 | 901904 | A00001 | 17.000000 | 12.000000 | 29.000000
....And then, with a judicious CASE statement, we can turn the three four (not three) conditions for the overlaps into our final totals:
SELECT WOPICK.docNum, WOPICK.itemCode, WOPICK.plannedQty,
BINSTOCK.whsCode, BINSTOCK.binCode,
CASE WHEN WOPICK.prevTotalNeeded < BINSTOCK.prevTotalQuantity
AND WOPICK.runningTotalNeeded > BINSTOCK.runningTotalQuantity
THEN BINSTOCK.quantity
WHEN WOPICK.prevTotalNeeded >= BINSTOCK.prevTotalQuantity
AND WOPICK.runningTotalNeeded <= BINSTOCK.runningTotalQuantity
THEN WOPICK.neededQuantity
WHEN WOPICK.runningTotalNeeded <= BINSTOCK.runningTotalQuantity
THEN WOPICK.runningTotalNeeded - BINSTOCK.prevTotalQuantity
WHEN WOPICK.prevTotalNeeded >= BINSTOCK.prevTotalQuantity
THEN BINSTOCK.runningTotalQuantity - WOPICK.prevTotalNeeded
END AS quantityToPick
FROM (SELECT itemCode, whsCode, binCode, quantity,
SUM(quantity) OVER (PARTITION BY itemCode
ORDER BY binCode) - quantity AS prevTotalQuantity,
SUM(quantity) OVER (PARTITION BY itemCode
ORDER BY binCode) AS runningTotalQuantity
FROM (SELECT OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode, SUM(OIBQ.onHandQty) AS quantity
FROM OIBQ
JOIN OBIN
ON OBIN.absEntry = OIBQ.binAbs
GROUP BY OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode) TotalQuantity
) BINSTOCK
JOIN (SELECT OWOR.docNum, WOR1.itemCode, plannedQty, plannedQty - issuedQty AS neededQuantity,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) AS runningTotalNeeded
FROM OWOR
JOIN WOR1
ON WOR1.docEntry = OWOR.docEntry
WHERE OWOR.status = 'R') WOPICK
ON WOPICK.itemCode = BINSTOCK.itemCode
AND WOPICK.prevTotalNeeded <= BINSTOCK.runningTotalQuantity
AND WOPICK.runningTotalNeeded >= BINSTOCK.prevTotalQuantity
Fiddle example
...which yields the desired results:
docNum | itemCode | plannedQty | whsCode | binCode | quantityToPick
-----: | :------- | :--------- | :------ | :------ | :-------------
901903 | A00001 | 12.000000 | 98 | 98-A001 | 7.000000
901903 | A00001 | 12.000000 | 98 | 98-A002 | 5.000000
901904 | A00001 | 17.000000 | 98 | 98-A002 | 1.000000
901904 | A00001 | 17.000000 | 98 | 98-A003 | 12.000000
901904 | A00001 | 17.000000 | 98 | 98-A006 | 4.000000
(ordering has been omitted, because the original one seemed unusual. It may be possible to remove the reference to OBIN inside of BINSTOCK, depending on your source data)
answered Nov 29 '18 at 0:00
Clockwork-MuseClockwork-Muse
10.3k32142
Thank you so much for explaining it in detail, This is exactly what I was searching for. I couldn't get my head around it :/ but now I understand it. Hopefully I can write a similar query myself in the future with this complexity
– Chester van Ree
Nov 29 '18 at 10:23
add a comment |
1
This is clearly a job for a stored procedure. It would be madness to even attempt it using a query. (Edit after Clockwork's answer: Doh!)
That said:
WITH Bin AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY BinCode) AS BinRank
FROM BINSTOCK
), Pick AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY DocNum) AS PickRank
FROM WOPICK
), BinPick AS (
SELECT
b.ItemCode,
b.Quantity AS BinQty,
b.BinCode,
b.BinRank,
p.Quantity AS PickQty,
p.DocNum,
p.PickRank,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN b.Quantity
ELSE p.Quantity
END
AS DECIMAL) AS Picked,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN p.Quantity - b.Quantity
ELSE 0
END
AS DECIMAL) AS Required,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN 0
ELSE b.Quantity - p.Quantity
END
AS DECIMAL) AS Remaining,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN null
ELSE b.BinRank
END
AS NVARCHAR) AS LastFullBinRank
FROM Bin b
JOIN Pick p ON p.ItemCode = b.ItemCode
WHERE b.BinRank = 1
AND p.PickRank = 1
UNION ALL
SELECT
ItemCode,
BinQty,
BinCode,
BinRank,
PickQty,
DocNum,
PickRank,
CAST (
CASE WHEN Remaining < Required
THEN Remaining
ELSE Required
END
AS DECIMAL) AS Picked,
CAST (
CASE WHEN Remaining < Required
THEN Required - Remaining
ELSE 0
END
AS DECIMAL) AS Required,
CAST (
CASE WHEN Remaining < Required
THEN 0
ELSE Remaining - Required
END
AS DECIMAL) AS Remaining,
CAST (
CASE WHEN Remaining < Required
THEN null
ELSE BinRank
END
AS NVARCHAR) AS LastFullBinRank
FROM (
SELECT
b.ItemCode,
b.Quantity AS BinQty,
b.BinCode,
b.BinRank,
p.Quantity AS PickQty,
p.DocNum,
p.PickRank,
bp.DocNum AS PrevDocNum,
bp.Picked,
bp.LastFullBinRank,
CASE WHEN p.DocNum = bp.DocNum
-- Same order. Continue with required from previous.
THEN bp.Required
-- New order. Required is new order quantity.
ELSE p.Quantity
END
AS Required,
CASE WHEN p.DocNum = bp.DocNum
-- Same order. Hence fresh bin. Use quanity from this bin.
THEN b.Quantity
-- New order. Hence used bin. Use remaining carried over.
ELSE bp.Remaining
END
AS Remaining
FROM BinPick bp
JOIN Pick p
ON p.ItemCode = bp.ItemCode
AND (
-- Order complete. Move to next order
(bp.Required = 0 AND p.PickRank = bp.PickRank + 1)
OR
-- Order incomlete. Continue with current Order
(bp.Required > 0 AND p.PickRank = bp.PickRank)
)
JOIN Bin b
ON b.ItemCode = bp.ItemCode
AND (
-- Order complete. Resume at last full bin.
(bp.Required = 0 AND b.BinRank = bp.LastFullBinRank)
OR
-- Order incomlete. Move to next bin.
(bp.Required > 0 AND bp.LastFullBinRank IS NULL AND b.BinRank = bp.BinRank + 1)
)
) sub1
) SELECT * FROM BinPick
Note: I've barely tested this. I'll leave it to those interested to correct any mistakes, but I think the principle is correct. Use a recursive CTE to identity the next Bin/Order and maintain a bunch of variables in columns to keep track of your working!
See DB Fiddle
answered Nov 28 '18 at 21:56
Andy NAndy N
428316
Thank you so Much Andy! This is a really complex query, I don't quite understand it yet as i have much difficulty understanding CTE's in general. but I will try both yours and Clockworks version
– Chester van Ree
Nov 29 '18 at 10:27
Thanks - personally I'd go with Clockworks (+1). I didn't think you could avoid a recursive CTE, but seeing that you can - you should!
– Andy N
Nov 29 '18 at 13:27
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53517172%2fsql-running-total-over-the-same-key%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
This is actually easier than it first sounds. But you're going to want to wrap you're head around something first - you need running totals for the needed quantities as well:
SELECT OWOR.docNum, WOR1.itemCode, plannedQty,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) AS runningTotalNeeded
FROM OWOR
JOIN WOR1
ON WOR1.docEntry = OWOR.docEntry
WHERE OWOR.status = 'R'
.... with the combination of the "previous" running-needed column (and assuming a previous total quantity), we're now looking at an overlapping range query.
In combination with (a slightly modified version of) your BINSTOCK:
SELECT *
FROM (SELECT itemCode, whsCode, binCode, quantity,
SUM(quantity) OVER (PARTITION BY itemCode ORDER BY binCode) - quantity AS prevTotalQuantity,
SUM(quantity) OVER (PARTITION BY itemCode ORDER BY binCode) AS runningTotalQuantity
FROM (SELECT OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode, SUM(OIBQ.onHandQty) AS quantity
FROM OIBQ
JOIN OBIN
ON OBIN.absEntry = OIBQ.binAbs
GROUP BY OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode) TotalQuantity
) BINSTOCK
JOIN (SELECT OWOR.docNum, WOR1.itemCode, plannedQty,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode ORDER BY OWOR.docEntry) AS runningTotalNeeded
FROM OWOR
JOIN WOR1
ON WOR1.docEntry = OWOR.docEntry
WHERE OWOR.status = 'R') WOPICK
ON WOPICK.itemCode = BINSTOCK.itemCode
AND WOPICK.prevTotalNeeded <= BINSTOCK.runningTotalQuantity
AND WOPICK.runningTotalNeeded >= BINSTOCK.prevTotalQuantity
... which yields the following table:
itemCode | whsCode | binCode | quantity | prevTotalQuantity | runningTotalQuantity | docNum | itemCode | plannedQty | prevTotalNeeded | runningTotalNeeded
:------- | :------ | :------ | :-------- | :---------------- | :------------------- | -----: | :------- | :--------- | :-------------- | :-----------------
A00001 | 98 | 98-A001 | 7.000000 | 0.000000 | 7.000000 | 901903 | A00001 | 12.000000 | 0.000000 | 12.000000
A00001 | 98 | 98-A002 | 6.000000 | 7.000000 | 13.000000 | 901903 | A00001 | 12.000000 | 0.000000 | 12.000000
A00001 | 98 | 98-A002 | 6.000000 | 7.000000 | 13.000000 | 901904 | A00001 | 17.000000 | 12.000000 | 29.000000
A00001 | 98 | 98-A003 | 12.000000 | 13.000000 | 25.000000 | 901904 | A00001 | 17.000000 | 12.000000 | 29.000000
A00001 | 98 | 98-A006 | 15.000000 | 25.000000 | 40.000000 | 901904 | A00001 | 17.000000 | 12.000000 | 29.000000
....And then, with a judicious CASE statement, we can turn the three four (not three) conditions for the overlaps into our final totals:
SELECT WOPICK.docNum, WOPICK.itemCode, WOPICK.plannedQty,
BINSTOCK.whsCode, BINSTOCK.binCode,
CASE WHEN WOPICK.prevTotalNeeded < BINSTOCK.prevTotalQuantity
AND WOPICK.runningTotalNeeded > BINSTOCK.runningTotalQuantity
THEN BINSTOCK.quantity
WHEN WOPICK.prevTotalNeeded >= BINSTOCK.prevTotalQuantity
AND WOPICK.runningTotalNeeded <= BINSTOCK.runningTotalQuantity
THEN WOPICK.neededQuantity
WHEN WOPICK.runningTotalNeeded <= BINSTOCK.runningTotalQuantity
THEN WOPICK.runningTotalNeeded - BINSTOCK.prevTotalQuantity
WHEN WOPICK.prevTotalNeeded >= BINSTOCK.prevTotalQuantity
THEN BINSTOCK.runningTotalQuantity - WOPICK.prevTotalNeeded
END AS quantityToPick
FROM (SELECT itemCode, whsCode, binCode, quantity,
SUM(quantity) OVER (PARTITION BY itemCode
ORDER BY binCode) - quantity AS prevTotalQuantity,
SUM(quantity) OVER (PARTITION BY itemCode
ORDER BY binCode) AS runningTotalQuantity
FROM (SELECT OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode, SUM(OIBQ.onHandQty) AS quantity
FROM OIBQ
JOIN OBIN
ON OBIN.absEntry = OIBQ.binAbs
GROUP BY OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode) TotalQuantity
) BINSTOCK
JOIN (SELECT OWOR.docNum, WOR1.itemCode, plannedQty, plannedQty - issuedQty AS neededQuantity,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) AS runningTotalNeeded
FROM OWOR
JOIN WOR1
ON WOR1.docEntry = OWOR.docEntry
WHERE OWOR.status = 'R') WOPICK
ON WOPICK.itemCode = BINSTOCK.itemCode
AND WOPICK.prevTotalNeeded <= BINSTOCK.runningTotalQuantity
AND WOPICK.runningTotalNeeded >= BINSTOCK.prevTotalQuantity
Fiddle example
...which yields the desired results:
docNum | itemCode | plannedQty | whsCode | binCode | quantityToPick
-----: | :------- | :--------- | :------ | :------ | :-------------
901903 | A00001 | 12.000000 | 98 | 98-A001 | 7.000000
901903 | A00001 | 12.000000 | 98 | 98-A002 | 5.000000
901904 | A00001 | 17.000000 | 98 | 98-A002 | 1.000000
901904 | A00001 | 17.000000 | 98 | 98-A003 | 12.000000
901904 | A00001 | 17.000000 | 98 | 98-A006 | 4.000000
(ordering has been omitted, because the original one seemed unusual. It may be possible to remove the reference to OBIN inside of BINSTOCK, depending on your source data)
answered Nov 29 '18 at 0:00
Clockwork-MuseClockwork-Muse
10.3k32142
Thank you so much for explaining it in detail, This is exactly what I was searching for. I couldn't get my head around it :/ but now I understand it. Hopefully I can write a similar query myself in the future with this complexity
– Chester van Ree
Nov 29 '18 at 10:23
add a comment |
2
This is actually easier than it first sounds. But you're going to want to wrap you're head around something first - you need running totals for the needed quantities as well:
SELECT OWOR.docNum, WOR1.itemCode, plannedQty,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) AS runningTotalNeeded
FROM OWOR
JOIN WOR1
ON WOR1.docEntry = OWOR.docEntry
WHERE OWOR.status = 'R'
.... with the combination of the "previous" running-needed column (and assuming a previous total quantity), we're now looking at an overlapping range query.
In combination with (a slightly modified version of) your BINSTOCK:
SELECT *
FROM (SELECT itemCode, whsCode, binCode, quantity,
SUM(quantity) OVER (PARTITION BY itemCode ORDER BY binCode) - quantity AS prevTotalQuantity,
SUM(quantity) OVER (PARTITION BY itemCode ORDER BY binCode) AS runningTotalQuantity
FROM (SELECT OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode, SUM(OIBQ.onHandQty) AS quantity
FROM OIBQ
JOIN OBIN
ON OBIN.absEntry = OIBQ.binAbs
GROUP BY OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode) TotalQuantity
) BINSTOCK
JOIN (SELECT OWOR.docNum, WOR1.itemCode, plannedQty,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode ORDER BY OWOR.docEntry) AS runningTotalNeeded
FROM OWOR
JOIN WOR1
ON WOR1.docEntry = OWOR.docEntry
WHERE OWOR.status = 'R') WOPICK
ON WOPICK.itemCode = BINSTOCK.itemCode
AND WOPICK.prevTotalNeeded <= BINSTOCK.runningTotalQuantity
AND WOPICK.runningTotalNeeded >= BINSTOCK.prevTotalQuantity
... which yields the following table:
itemCode | whsCode | binCode | quantity | prevTotalQuantity | runningTotalQuantity | docNum | itemCode | plannedQty | prevTotalNeeded | runningTotalNeeded
:------- | :------ | :------ | :-------- | :---------------- | :------------------- | -----: | :------- | :--------- | :-------------- | :-----------------
A00001 | 98 | 98-A001 | 7.000000 | 0.000000 | 7.000000 | 901903 | A00001 | 12.000000 | 0.000000 | 12.000000
A00001 | 98 | 98-A002 | 6.000000 | 7.000000 | 13.000000 | 901903 | A00001 | 12.000000 | 0.000000 | 12.000000
A00001 | 98 | 98-A002 | 6.000000 | 7.000000 | 13.000000 | 901904 | A00001 | 17.000000 | 12.000000 | 29.000000
A00001 | 98 | 98-A003 | 12.000000 | 13.000000 | 25.000000 | 901904 | A00001 | 17.000000 | 12.000000 | 29.000000
A00001 | 98 | 98-A006 | 15.000000 | 25.000000 | 40.000000 | 901904 | A00001 | 17.000000 | 12.000000 | 29.000000
....And then, with a judicious CASE statement, we can turn the three four (not three) conditions for the overlaps into our final totals:
SELECT WOPICK.docNum, WOPICK.itemCode, WOPICK.plannedQty,
BINSTOCK.whsCode, BINSTOCK.binCode,
CASE WHEN WOPICK.prevTotalNeeded < BINSTOCK.prevTotalQuantity
AND WOPICK.runningTotalNeeded > BINSTOCK.runningTotalQuantity
THEN BINSTOCK.quantity
WHEN WOPICK.prevTotalNeeded >= BINSTOCK.prevTotalQuantity
AND WOPICK.runningTotalNeeded <= BINSTOCK.runningTotalQuantity
THEN WOPICK.neededQuantity
WHEN WOPICK.runningTotalNeeded <= BINSTOCK.runningTotalQuantity
THEN WOPICK.runningTotalNeeded - BINSTOCK.prevTotalQuantity
WHEN WOPICK.prevTotalNeeded >= BINSTOCK.prevTotalQuantity
THEN BINSTOCK.runningTotalQuantity - WOPICK.prevTotalNeeded
END AS quantityToPick
FROM (SELECT itemCode, whsCode, binCode, quantity,
SUM(quantity) OVER (PARTITION BY itemCode
ORDER BY binCode) - quantity AS prevTotalQuantity,
SUM(quantity) OVER (PARTITION BY itemCode
ORDER BY binCode) AS runningTotalQuantity
FROM (SELECT OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode, SUM(OIBQ.onHandQty) AS quantity
FROM OIBQ
JOIN OBIN
ON OBIN.absEntry = OIBQ.binAbs
GROUP BY OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode) TotalQuantity
) BINSTOCK
JOIN (SELECT OWOR.docNum, WOR1.itemCode, plannedQty, plannedQty - issuedQty AS neededQuantity,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) AS runningTotalNeeded
FROM OWOR
JOIN WOR1
ON WOR1.docEntry = OWOR.docEntry
WHERE OWOR.status = 'R') WOPICK
ON WOPICK.itemCode = BINSTOCK.itemCode
AND WOPICK.prevTotalNeeded <= BINSTOCK.runningTotalQuantity
AND WOPICK.runningTotalNeeded >= BINSTOCK.prevTotalQuantity
Fiddle example
...which yields the desired results:
docNum | itemCode | plannedQty | whsCode | binCode | quantityToPick
-----: | :------- | :--------- | :------ | :------ | :-------------
901903 | A00001 | 12.000000 | 98 | 98-A001 | 7.000000
901903 | A00001 | 12.000000 | 98 | 98-A002 | 5.000000
901904 | A00001 | 17.000000 | 98 | 98-A002 | 1.000000
901904 | A00001 | 17.000000 | 98 | 98-A003 | 12.000000
901904 | A00001 | 17.000000 | 98 | 98-A006 | 4.000000
(ordering has been omitted, because the original one seemed unusual. It may be possible to remove the reference to OBIN inside of BINSTOCK, depending on your source data)
answered Nov 29 '18 at 0:00
Clockwork-MuseClockwork-Muse
10.3k32142
Thank you so much for explaining it in detail, This is exactly what I was searching for. I couldn't get my head around it :/ but now I understand it. Hopefully I can write a similar query myself in the future with this complexity
– Chester van Ree
Nov 29 '18 at 10:23
add a comment |
2
2
2
This is actually easier than it first sounds. But you're going to want to wrap you're head around something first - you need running totals for the needed quantities as well:
SELECT OWOR.docNum, WOR1.itemCode, plannedQty,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) AS runningTotalNeeded
FROM OWOR
JOIN WOR1
ON WOR1.docEntry = OWOR.docEntry
WHERE OWOR.status = 'R'
.... with the combination of the "previous" running-needed column (and assuming a previous total quantity), we're now looking at an overlapping range query.
In combination with (a slightly modified version of) your BINSTOCK:
SELECT *
FROM (SELECT itemCode, whsCode, binCode, quantity,
SUM(quantity) OVER (PARTITION BY itemCode ORDER BY binCode) - quantity AS prevTotalQuantity,
SUM(quantity) OVER (PARTITION BY itemCode ORDER BY binCode) AS runningTotalQuantity
FROM (SELECT OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode, SUM(OIBQ.onHandQty) AS quantity
FROM OIBQ
JOIN OBIN
ON OBIN.absEntry = OIBQ.binAbs
GROUP BY OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode) TotalQuantity
) BINSTOCK
JOIN (SELECT OWOR.docNum, WOR1.itemCode, plannedQty,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode ORDER BY OWOR.docEntry) AS runningTotalNeeded
FROM OWOR
JOIN WOR1
ON WOR1.docEntry = OWOR.docEntry
WHERE OWOR.status = 'R') WOPICK
ON WOPICK.itemCode = BINSTOCK.itemCode
AND WOPICK.prevTotalNeeded <= BINSTOCK.runningTotalQuantity
AND WOPICK.runningTotalNeeded >= BINSTOCK.prevTotalQuantity
... which yields the following table:
itemCode | whsCode | binCode | quantity | prevTotalQuantity | runningTotalQuantity | docNum | itemCode | plannedQty | prevTotalNeeded | runningTotalNeeded
:------- | :------ | :------ | :-------- | :---------------- | :------------------- | -----: | :------- | :--------- | :-------------- | :-----------------
A00001 | 98 | 98-A001 | 7.000000 | 0.000000 | 7.000000 | 901903 | A00001 | 12.000000 | 0.000000 | 12.000000
A00001 | 98 | 98-A002 | 6.000000 | 7.000000 | 13.000000 | 901903 | A00001 | 12.000000 | 0.000000 | 12.000000
A00001 | 98 | 98-A002 | 6.000000 | 7.000000 | 13.000000 | 901904 | A00001 | 17.000000 | 12.000000 | 29.000000
A00001 | 98 | 98-A003 | 12.000000 | 13.000000 | 25.000000 | 901904 | A00001 | 17.000000 | 12.000000 | 29.000000
A00001 | 98 | 98-A006 | 15.000000 | 25.000000 | 40.000000 | 901904 | A00001 | 17.000000 | 12.000000 | 29.000000
....And then, with a judicious CASE statement, we can turn the three four (not three) conditions for the overlaps into our final totals:
SELECT WOPICK.docNum, WOPICK.itemCode, WOPICK.plannedQty,
BINSTOCK.whsCode, BINSTOCK.binCode,
CASE WHEN WOPICK.prevTotalNeeded < BINSTOCK.prevTotalQuantity
AND WOPICK.runningTotalNeeded > BINSTOCK.runningTotalQuantity
THEN BINSTOCK.quantity
WHEN WOPICK.prevTotalNeeded >= BINSTOCK.prevTotalQuantity
AND WOPICK.runningTotalNeeded <= BINSTOCK.runningTotalQuantity
THEN WOPICK.neededQuantity
WHEN WOPICK.runningTotalNeeded <= BINSTOCK.runningTotalQuantity
THEN WOPICK.runningTotalNeeded - BINSTOCK.prevTotalQuantity
WHEN WOPICK.prevTotalNeeded >= BINSTOCK.prevTotalQuantity
THEN BINSTOCK.runningTotalQuantity - WOPICK.prevTotalNeeded
END AS quantityToPick
FROM (SELECT itemCode, whsCode, binCode, quantity,
SUM(quantity) OVER (PARTITION BY itemCode
ORDER BY binCode) - quantity AS prevTotalQuantity,
SUM(quantity) OVER (PARTITION BY itemCode
ORDER BY binCode) AS runningTotalQuantity
FROM (SELECT OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode, SUM(OIBQ.onHandQty) AS quantity
FROM OIBQ
JOIN OBIN
ON OBIN.absEntry = OIBQ.binAbs
GROUP BY OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode) TotalQuantity
) BINSTOCK
JOIN (SELECT OWOR.docNum, WOR1.itemCode, plannedQty, plannedQty - issuedQty AS neededQuantity,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) AS runningTotalNeeded
FROM OWOR
JOIN WOR1
ON WOR1.docEntry = OWOR.docEntry
WHERE OWOR.status = 'R') WOPICK
ON WOPICK.itemCode = BINSTOCK.itemCode
AND WOPICK.prevTotalNeeded <= BINSTOCK.runningTotalQuantity
AND WOPICK.runningTotalNeeded >= BINSTOCK.prevTotalQuantity
Fiddle example
...which yields the desired results:
docNum | itemCode | plannedQty | whsCode | binCode | quantityToPick
-----: | :------- | :--------- | :------ | :------ | :-------------
901903 | A00001 | 12.000000 | 98 | 98-A001 | 7.000000
901903 | A00001 | 12.000000 | 98 | 98-A002 | 5.000000
901904 | A00001 | 17.000000 | 98 | 98-A002 | 1.000000
901904 | A00001 | 17.000000 | 98 | 98-A003 | 12.000000
901904 | A00001 | 17.000000 | 98 | 98-A006 | 4.000000
(ordering has been omitted, because the original one seemed unusual. It may be possible to remove the reference to OBIN inside of BINSTOCK, depending on your source data)
answered Nov 29 '18 at 0:00
Clockwork-MuseClockwork-Muse
10.3k32142
This is actually easier than it first sounds. But you're going to want to wrap you're head around something first - you need running totals for the needed quantities as well:
SELECT OWOR.docNum, WOR1.itemCode, plannedQty,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) AS runningTotalNeeded
FROM OWOR
JOIN WOR1
ON WOR1.docEntry = OWOR.docEntry
WHERE OWOR.status = 'R'
.... with the combination of the "previous" running-needed column (and assuming a previous total quantity), we're now looking at an overlapping range query.
In combination with (a slightly modified version of) your BINSTOCK:
SELECT *
FROM (SELECT itemCode, whsCode, binCode, quantity,
SUM(quantity) OVER (PARTITION BY itemCode ORDER BY binCode) - quantity AS prevTotalQuantity,
SUM(quantity) OVER (PARTITION BY itemCode ORDER BY binCode) AS runningTotalQuantity
FROM (SELECT OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode, SUM(OIBQ.onHandQty) AS quantity
FROM OIBQ
JOIN OBIN
ON OBIN.absEntry = OIBQ.binAbs
GROUP BY OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode) TotalQuantity
) BINSTOCK
JOIN (SELECT OWOR.docNum, WOR1.itemCode, plannedQty,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode ORDER BY OWOR.docEntry) AS runningTotalNeeded
FROM OWOR
JOIN WOR1
ON WOR1.docEntry = OWOR.docEntry
WHERE OWOR.status = 'R') WOPICK
ON WOPICK.itemCode = BINSTOCK.itemCode
AND WOPICK.prevTotalNeeded <= BINSTOCK.runningTotalQuantity
AND WOPICK.runningTotalNeeded >= BINSTOCK.prevTotalQuantity
... which yields the following table:
itemCode | whsCode | binCode | quantity | prevTotalQuantity | runningTotalQuantity | docNum | itemCode | plannedQty | prevTotalNeeded | runningTotalNeeded
:------- | :------ | :------ | :-------- | :---------------- | :------------------- | -----: | :------- | :--------- | :-------------- | :-----------------
A00001 | 98 | 98-A001 | 7.000000 | 0.000000 | 7.000000 | 901903 | A00001 | 12.000000 | 0.000000 | 12.000000
A00001 | 98 | 98-A002 | 6.000000 | 7.000000 | 13.000000 | 901903 | A00001 | 12.000000 | 0.000000 | 12.000000
A00001 | 98 | 98-A002 | 6.000000 | 7.000000 | 13.000000 | 901904 | A00001 | 17.000000 | 12.000000 | 29.000000
A00001 | 98 | 98-A003 | 12.000000 | 13.000000 | 25.000000 | 901904 | A00001 | 17.000000 | 12.000000 | 29.000000
A00001 | 98 | 98-A006 | 15.000000 | 25.000000 | 40.000000 | 901904 | A00001 | 17.000000 | 12.000000 | 29.000000
....And then, with a judicious CASE statement, we can turn the three four (not three) conditions for the overlaps into our final totals:
SELECT WOPICK.docNum, WOPICK.itemCode, WOPICK.plannedQty,
BINSTOCK.whsCode, BINSTOCK.binCode,
CASE WHEN WOPICK.prevTotalNeeded < BINSTOCK.prevTotalQuantity
AND WOPICK.runningTotalNeeded > BINSTOCK.runningTotalQuantity
THEN BINSTOCK.quantity
WHEN WOPICK.prevTotalNeeded >= BINSTOCK.prevTotalQuantity
AND WOPICK.runningTotalNeeded <= BINSTOCK.runningTotalQuantity
THEN WOPICK.neededQuantity
WHEN WOPICK.runningTotalNeeded <= BINSTOCK.runningTotalQuantity
THEN WOPICK.runningTotalNeeded - BINSTOCK.prevTotalQuantity
WHEN WOPICK.prevTotalNeeded >= BINSTOCK.prevTotalQuantity
THEN BINSTOCK.runningTotalQuantity - WOPICK.prevTotalNeeded
END AS quantityToPick
FROM (SELECT itemCode, whsCode, binCode, quantity,
SUM(quantity) OVER (PARTITION BY itemCode
ORDER BY binCode) - quantity AS prevTotalQuantity,
SUM(quantity) OVER (PARTITION BY itemCode
ORDER BY binCode) AS runningTotalQuantity
FROM (SELECT OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode, SUM(OIBQ.onHandQty) AS quantity
FROM OIBQ
JOIN OBIN
ON OBIN.absEntry = OIBQ.binAbs
GROUP BY OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode) TotalQuantity
) BINSTOCK
JOIN (SELECT OWOR.docNum, WOR1.itemCode, plannedQty, plannedQty - issuedQty AS neededQuantity,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode
ORDER BY OWOR.docEntry) AS runningTotalNeeded
FROM OWOR
JOIN WOR1
ON WOR1.docEntry = OWOR.docEntry
WHERE OWOR.status = 'R') WOPICK
ON WOPICK.itemCode = BINSTOCK.itemCode
AND WOPICK.prevTotalNeeded <= BINSTOCK.runningTotalQuantity
AND WOPICK.runningTotalNeeded >= BINSTOCK.prevTotalQuantity
Fiddle example
...which yields the desired results:
docNum | itemCode | plannedQty | whsCode | binCode | quantityToPick
-----: | :------- | :--------- | :------ | :------ | :-------------
901903 | A00001 | 12.000000 | 98 | 98-A001 | 7.000000
901903 | A00001 | 12.000000 | 98 | 98-A002 | 5.000000
901904 | A00001 | 17.000000 | 98 | 98-A002 | 1.000000
901904 | A00001 | 17.000000 | 98 | 98-A003 | 12.000000
901904 | A00001 | 17.000000 | 98 | 98-A006 | 4.000000
(ordering has been omitted, because the original one seemed unusual. It may be possible to remove the reference to OBIN inside of BINSTOCK, depending on your source data)
answered Nov 29 '18 at 0:00
Clockwork-MuseClockwork-Muse
10.3k32142
edited Nov 29 '18 at 16:53
answered Nov 29 '18 at 0:00
Clockwork-MuseClockwork-Muse
10.3k32142
answered Nov 29 '18 at 0:00
Clockwork-MuseClockwork-Muse
10.3k32142
answered Nov 29 '18 at 0:00
Clockwork-MuseClockwork-Muse
10.3k32142
10.3k32142
Thank you so much for explaining it in detail, This is exactly what I was searching for. I couldn't get my head around it :/ but now I understand it. Hopefully I can write a similar query myself in the future with this complexity
– Chester van Ree
Nov 29 '18 at 10:23
add a comment |
Thank you so much for explaining it in detail, This is exactly what I was searching for. I couldn't get my head around it :/ but now I understand it. Hopefully I can write a similar query myself in the future with this complexity
– Chester van Ree
Nov 29 '18 at 10:23
Thank you so much for explaining it in detail, This is exactly what I was searching for. I couldn't get my head around it :/ but now I understand it. Hopefully I can write a similar query myself in the future with this complexity
– Chester van Ree
Nov 29 '18 at 10:23
Thank you so much for explaining it in detail, This is exactly what I was searching for. I couldn't get my head around it :/ but now I understand it. Hopefully I can write a similar query myself in the future with this complexity
– Chester van Ree
Nov 29 '18 at 10:23
add a comment |
1
This is clearly a job for a stored procedure. It would be madness to even attempt it using a query. (Edit after Clockwork's answer: Doh!)
That said:
WITH Bin AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY BinCode) AS BinRank
FROM BINSTOCK
), Pick AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY DocNum) AS PickRank
FROM WOPICK
), BinPick AS (
SELECT
b.ItemCode,
b.Quantity AS BinQty,
b.BinCode,
b.BinRank,
p.Quantity AS PickQty,
p.DocNum,
p.PickRank,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN b.Quantity
ELSE p.Quantity
END
AS DECIMAL) AS Picked,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN p.Quantity - b.Quantity
ELSE 0
END
AS DECIMAL) AS Required,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN 0
ELSE b.Quantity - p.Quantity
END
AS DECIMAL) AS Remaining,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN null
ELSE b.BinRank
END
AS NVARCHAR) AS LastFullBinRank
FROM Bin b
JOIN Pick p ON p.ItemCode = b.ItemCode
WHERE b.BinRank = 1
AND p.PickRank = 1
UNION ALL
SELECT
ItemCode,
BinQty,
BinCode,
BinRank,
PickQty,
DocNum,
PickRank,
CAST (
CASE WHEN Remaining < Required
THEN Remaining
ELSE Required
END
AS DECIMAL) AS Picked,
CAST (
CASE WHEN Remaining < Required
THEN Required - Remaining
ELSE 0
END
AS DECIMAL) AS Required,
CAST (
CASE WHEN Remaining < Required
THEN 0
ELSE Remaining - Required
END
AS DECIMAL) AS Remaining,
CAST (
CASE WHEN Remaining < Required
THEN null
ELSE BinRank
END
AS NVARCHAR) AS LastFullBinRank
FROM (
SELECT
b.ItemCode,
b.Quantity AS BinQty,
b.BinCode,
b.BinRank,
p.Quantity AS PickQty,
p.DocNum,
p.PickRank,
bp.DocNum AS PrevDocNum,
bp.Picked,
bp.LastFullBinRank,
CASE WHEN p.DocNum = bp.DocNum
-- Same order. Continue with required from previous.
THEN bp.Required
-- New order. Required is new order quantity.
ELSE p.Quantity
END
AS Required,
CASE WHEN p.DocNum = bp.DocNum
-- Same order. Hence fresh bin. Use quanity from this bin.
THEN b.Quantity
-- New order. Hence used bin. Use remaining carried over.
ELSE bp.Remaining
END
AS Remaining
FROM BinPick bp
JOIN Pick p
ON p.ItemCode = bp.ItemCode
AND (
-- Order complete. Move to next order
(bp.Required = 0 AND p.PickRank = bp.PickRank + 1)
OR
-- Order incomlete. Continue with current Order
(bp.Required > 0 AND p.PickRank = bp.PickRank)
)
JOIN Bin b
ON b.ItemCode = bp.ItemCode
AND (
-- Order complete. Resume at last full bin.
(bp.Required = 0 AND b.BinRank = bp.LastFullBinRank)
OR
-- Order incomlete. Move to next bin.
(bp.Required > 0 AND bp.LastFullBinRank IS NULL AND b.BinRank = bp.BinRank + 1)
)
) sub1
) SELECT * FROM BinPick
Note: I've barely tested this. I'll leave it to those interested to correct any mistakes, but I think the principle is correct. Use a recursive CTE to identity the next Bin/Order and maintain a bunch of variables in columns to keep track of your working!
See DB Fiddle
answered Nov 28 '18 at 21:56
Andy NAndy N
428316
Thank you so Much Andy! This is a really complex query, I don't quite understand it yet as i have much difficulty understanding CTE's in general. but I will try both yours and Clockworks version
– Chester van Ree
Nov 29 '18 at 10:27
Thanks - personally I'd go with Clockworks (+1). I didn't think you could avoid a recursive CTE, but seeing that you can - you should!
– Andy N
Nov 29 '18 at 13:27
add a comment |
1
This is clearly a job for a stored procedure. It would be madness to even attempt it using a query. (Edit after Clockwork's answer: Doh!)
That said:
WITH Bin AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY BinCode) AS BinRank
FROM BINSTOCK
), Pick AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY DocNum) AS PickRank
FROM WOPICK
), BinPick AS (
SELECT
b.ItemCode,
b.Quantity AS BinQty,
b.BinCode,
b.BinRank,
p.Quantity AS PickQty,
p.DocNum,
p.PickRank,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN b.Quantity
ELSE p.Quantity
END
AS DECIMAL) AS Picked,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN p.Quantity - b.Quantity
ELSE 0
END
AS DECIMAL) AS Required,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN 0
ELSE b.Quantity - p.Quantity
END
AS DECIMAL) AS Remaining,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN null
ELSE b.BinRank
END
AS NVARCHAR) AS LastFullBinRank
FROM Bin b
JOIN Pick p ON p.ItemCode = b.ItemCode
WHERE b.BinRank = 1
AND p.PickRank = 1
UNION ALL
SELECT
ItemCode,
BinQty,
BinCode,
BinRank,
PickQty,
DocNum,
PickRank,
CAST (
CASE WHEN Remaining < Required
THEN Remaining
ELSE Required
END
AS DECIMAL) AS Picked,
CAST (
CASE WHEN Remaining < Required
THEN Required - Remaining
ELSE 0
END
AS DECIMAL) AS Required,
CAST (
CASE WHEN Remaining < Required
THEN 0
ELSE Remaining - Required
END
AS DECIMAL) AS Remaining,
CAST (
CASE WHEN Remaining < Required
THEN null
ELSE BinRank
END
AS NVARCHAR) AS LastFullBinRank
FROM (
SELECT
b.ItemCode,
b.Quantity AS BinQty,
b.BinCode,
b.BinRank,
p.Quantity AS PickQty,
p.DocNum,
p.PickRank,
bp.DocNum AS PrevDocNum,
bp.Picked,
bp.LastFullBinRank,
CASE WHEN p.DocNum = bp.DocNum
-- Same order. Continue with required from previous.
THEN bp.Required
-- New order. Required is new order quantity.
ELSE p.Quantity
END
AS Required,
CASE WHEN p.DocNum = bp.DocNum
-- Same order. Hence fresh bin. Use quanity from this bin.
THEN b.Quantity
-- New order. Hence used bin. Use remaining carried over.
ELSE bp.Remaining
END
AS Remaining
FROM BinPick bp
JOIN Pick p
ON p.ItemCode = bp.ItemCode
AND (
-- Order complete. Move to next order
(bp.Required = 0 AND p.PickRank = bp.PickRank + 1)
OR
-- Order incomlete. Continue with current Order
(bp.Required > 0 AND p.PickRank = bp.PickRank)
)
JOIN Bin b
ON b.ItemCode = bp.ItemCode
AND (
-- Order complete. Resume at last full bin.
(bp.Required = 0 AND b.BinRank = bp.LastFullBinRank)
OR
-- Order incomlete. Move to next bin.
(bp.Required > 0 AND bp.LastFullBinRank IS NULL AND b.BinRank = bp.BinRank + 1)
)
) sub1
) SELECT * FROM BinPick
Note: I've barely tested this. I'll leave it to those interested to correct any mistakes, but I think the principle is correct. Use a recursive CTE to identity the next Bin/Order and maintain a bunch of variables in columns to keep track of your working!
See DB Fiddle
answered Nov 28 '18 at 21:56
Andy NAndy N
428316
Thank you so Much Andy! This is a really complex query, I don't quite understand it yet as i have much difficulty understanding CTE's in general. but I will try both yours and Clockworks version
– Chester van Ree
Nov 29 '18 at 10:27
Thanks - personally I'd go with Clockworks (+1). I didn't think you could avoid a recursive CTE, but seeing that you can - you should!
– Andy N
Nov 29 '18 at 13:27
add a comment |
1
1
1
This is clearly a job for a stored procedure. It would be madness to even attempt it using a query. (Edit after Clockwork's answer: Doh!)
That said:
WITH Bin AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY BinCode) AS BinRank
FROM BINSTOCK
), Pick AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY DocNum) AS PickRank
FROM WOPICK
), BinPick AS (
SELECT
b.ItemCode,
b.Quantity AS BinQty,
b.BinCode,
b.BinRank,
p.Quantity AS PickQty,
p.DocNum,
p.PickRank,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN b.Quantity
ELSE p.Quantity
END
AS DECIMAL) AS Picked,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN p.Quantity - b.Quantity
ELSE 0
END
AS DECIMAL) AS Required,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN 0
ELSE b.Quantity - p.Quantity
END
AS DECIMAL) AS Remaining,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN null
ELSE b.BinRank
END
AS NVARCHAR) AS LastFullBinRank
FROM Bin b
JOIN Pick p ON p.ItemCode = b.ItemCode
WHERE b.BinRank = 1
AND p.PickRank = 1
UNION ALL
SELECT
ItemCode,
BinQty,
BinCode,
BinRank,
PickQty,
DocNum,
PickRank,
CAST (
CASE WHEN Remaining < Required
THEN Remaining
ELSE Required
END
AS DECIMAL) AS Picked,
CAST (
CASE WHEN Remaining < Required
THEN Required - Remaining
ELSE 0
END
AS DECIMAL) AS Required,
CAST (
CASE WHEN Remaining < Required
THEN 0
ELSE Remaining - Required
END
AS DECIMAL) AS Remaining,
CAST (
CASE WHEN Remaining < Required
THEN null
ELSE BinRank
END
AS NVARCHAR) AS LastFullBinRank
FROM (
SELECT
b.ItemCode,
b.Quantity AS BinQty,
b.BinCode,
b.BinRank,
p.Quantity AS PickQty,
p.DocNum,
p.PickRank,
bp.DocNum AS PrevDocNum,
bp.Picked,
bp.LastFullBinRank,
CASE WHEN p.DocNum = bp.DocNum
-- Same order. Continue with required from previous.
THEN bp.Required
-- New order. Required is new order quantity.
ELSE p.Quantity
END
AS Required,
CASE WHEN p.DocNum = bp.DocNum
-- Same order. Hence fresh bin. Use quanity from this bin.
THEN b.Quantity
-- New order. Hence used bin. Use remaining carried over.
ELSE bp.Remaining
END
AS Remaining
FROM BinPick bp
JOIN Pick p
ON p.ItemCode = bp.ItemCode
AND (
-- Order complete. Move to next order
(bp.Required = 0 AND p.PickRank = bp.PickRank + 1)
OR
-- Order incomlete. Continue with current Order
(bp.Required > 0 AND p.PickRank = bp.PickRank)
)
JOIN Bin b
ON b.ItemCode = bp.ItemCode
AND (
-- Order complete. Resume at last full bin.
(bp.Required = 0 AND b.BinRank = bp.LastFullBinRank)
OR
-- Order incomlete. Move to next bin.
(bp.Required > 0 AND bp.LastFullBinRank IS NULL AND b.BinRank = bp.BinRank + 1)
)
) sub1
) SELECT * FROM BinPick
Note: I've barely tested this. I'll leave it to those interested to correct any mistakes, but I think the principle is correct. Use a recursive CTE to identity the next Bin/Order and maintain a bunch of variables in columns to keep track of your working!
See DB Fiddle
answered Nov 28 '18 at 21:56
Andy NAndy N
428316
This is clearly a job for a stored procedure. It would be madness to even attempt it using a query. (Edit after Clockwork's answer: Doh!)
That said:
WITH Bin AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY BinCode) AS BinRank
FROM BINSTOCK
), Pick AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY DocNum) AS PickRank
FROM WOPICK
), BinPick AS (
SELECT
b.ItemCode,
b.Quantity AS BinQty,
b.BinCode,
b.BinRank,
p.Quantity AS PickQty,
p.DocNum,
p.PickRank,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN b.Quantity
ELSE p.Quantity
END
AS DECIMAL) AS Picked,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN p.Quantity - b.Quantity
ELSE 0
END
AS DECIMAL) AS Required,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN 0
ELSE b.Quantity - p.Quantity
END
AS DECIMAL) AS Remaining,
CAST (
CASE
WHEN b.Quantity < p.Quantity
THEN null
ELSE b.BinRank
END
AS NVARCHAR) AS LastFullBinRank
FROM Bin b
JOIN Pick p ON p.ItemCode = b.ItemCode
WHERE b.BinRank = 1
AND p.PickRank = 1
UNION ALL
SELECT
ItemCode,
BinQty,
BinCode,
BinRank,
PickQty,
DocNum,
PickRank,
CAST (
CASE WHEN Remaining < Required
THEN Remaining
ELSE Required
END
AS DECIMAL) AS Picked,
CAST (
CASE WHEN Remaining < Required
THEN Required - Remaining
ELSE 0
END
AS DECIMAL) AS Required,
CAST (
CASE WHEN Remaining < Required
THEN 0
ELSE Remaining - Required
END
AS DECIMAL) AS Remaining,
CAST (
CASE WHEN Remaining < Required
THEN null
ELSE BinRank
END
AS NVARCHAR) AS LastFullBinRank
FROM (
SELECT
b.ItemCode,
b.Quantity AS BinQty,
b.BinCode,
b.BinRank,
p.Quantity AS PickQty,
p.DocNum,
p.PickRank,
bp.DocNum AS PrevDocNum,
bp.Picked,
bp.LastFullBinRank,
CASE WHEN p.DocNum = bp.DocNum
-- Same order. Continue with required from previous.
THEN bp.Required
-- New order. Required is new order quantity.
ELSE p.Quantity
END
AS Required,
CASE WHEN p.DocNum = bp.DocNum
-- Same order. Hence fresh bin. Use quanity from this bin.
THEN b.Quantity
-- New order. Hence used bin. Use remaining carried over.
ELSE bp.Remaining
END
AS Remaining
FROM BinPick bp
JOIN Pick p
ON p.ItemCode = bp.ItemCode
AND (
-- Order complete. Move to next order
(bp.Required = 0 AND p.PickRank = bp.PickRank + 1)
OR
-- Order incomlete. Continue with current Order
(bp.Required > 0 AND p.PickRank = bp.PickRank)
)
JOIN Bin b
ON b.ItemCode = bp.ItemCode
AND (
-- Order complete. Resume at last full bin.
(bp.Required = 0 AND b.BinRank = bp.LastFullBinRank)
OR
-- Order incomlete. Move to next bin.
(bp.Required > 0 AND bp.LastFullBinRank IS NULL AND b.BinRank = bp.BinRank + 1)
)
) sub1
) SELECT * FROM BinPick
Note: I've barely tested this. I'll leave it to those interested to correct any mistakes, but I think the principle is correct. Use a recursive CTE to identity the next Bin/Order and maintain a bunch of variables in columns to keep track of your working!
See DB Fiddle
answered Nov 28 '18 at 21:56
Andy NAndy N
428316
edited Nov 29 '18 at 16:09
answered Nov 28 '18 at 21:56
Andy NAndy N
428316
answered Nov 28 '18 at 21:56
Andy NAndy N
428316
answered Nov 28 '18 at 21:56
Andy NAndy N
428316
428316
Thank you so Much Andy! This is a really complex query, I don't quite understand it yet as i have much difficulty understanding CTE's in general. but I will try both yours and Clockworks version
– Chester van Ree
Nov 29 '18 at 10:27
Thanks - personally I'd go with Clockworks (+1). I didn't think you could avoid a recursive CTE, but seeing that you can - you should!
– Andy N
Nov 29 '18 at 13:27
add a comment |
Thank you so Much Andy! This is a really complex query, I don't quite understand it yet as i have much difficulty understanding CTE's in general. but I will try both yours and Clockworks version
– Chester van Ree
Nov 29 '18 at 10:27
Thanks - personally I'd go with Clockworks (+1). I didn't think you could avoid a recursive CTE, but seeing that you can - you should!
– Andy N
Nov 29 '18 at 13:27
Thank you so Much Andy! This is a really complex query, I don't quite understand it yet as i have much difficulty understanding CTE's in general. but I will try both yours and Clockworks version
– Chester van Ree
Nov 29 '18 at 10:27
Thank you so Much Andy! This is a really complex query, I don't quite understand it yet as i have much difficulty understanding CTE's in general. but I will try both yours and Clockworks version
– Chester van Ree
Nov 29 '18 at 10:27
Thanks - personally I'd go with Clockworks (+1). I didn't think you could avoid a recursive CTE, but seeing that you can - you should!
– Andy N
Nov 29 '18 at 13:27
Thanks - personally I'd go with Clockworks (+1). I didn't think you could avoid a recursive CTE, but seeing that you can - you should!
– Andy N
Nov 29 '18 at 13:27
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53517172%2fsql-running-total-over-the-same-key%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3). It's even worse here, due to the reuse of aliases. I have literally no idea what each alias is referring to.
a, for example, can beOWOR,OIBQ, or your first subquery, depending on where you are...– Larnu
Nov 28 '18 at 10:41
You are right, let me change the aliases.
– Chester van Ree
Nov 28 '18 at 10:44
Your sample data, also has 2 tables. They are unnamed for I'm going to refer to them as
ItemandDocument. Your Query, however, has 4 tablesOWOR,OBIN,WOR1andOIBQ; with no reference to which is which. Which of those are theItemtable, and which are theDocumenttable?– Larnu
Nov 28 '18 at 10:45
Hopefully this clears it up a bit.
– Chester van Ree
Nov 28 '18 at 10:51
So the sample data is the result set from the subqueries, not the tables? You would be far better off giving us the sample data from your tables
OIBQ,OBIN,OWORandWOR1, and far preferable as DDL (CREATE) andDML(INSERT`) statements.– Larnu
Nov 28 '18 at 10:54