Conditional Trailing Totals
-
Hi,
I am working on a job shortage report that first consumes inventory and then works through job supply one job at a time. The query is using sum over partition to chip away at the inventory and jobs in general. When the supply quantity (On Hand After) doesn't meet the Remaining Demand I am trying to add the remaining supply quantity to the new job supply quantity at each row. Currently, I can use LAG to look at the previous row but when I move to the next row I lose the value in the position. How can I keep carrying the remaining value of 2 throughout the subsequent rows?
Here is a consumable example for reference:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
JobNumber VARCHAR(14),
PartNumber VARCHAR(50),
MtlPartNumber VARCHAR(50),
ReqDueDate DATE,
RemainingDemand DECIMAL(12,5),
OnHandBefore DECIMAL(12,5),
OnHandAfter DECIMAL(12,5),
NextJobQty DECIMAL(12,5),
NextJobNum VARCHAR(14)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(
ID, JobNumber, PartNumber ,MtlPartNumber, ReqDueDate, RemainingDemand, OnHandBefore, OnHandAfter, NextJobQty, NextJobNum
)
SELECT 1,'25512','604C2500-069','604C2503-002','2021-09-23',10, 22, 12, 0, 'INVENTORY' UNION ALL
SELECT 2,'25497','604C2500-070','604C2503-002','2021-09-24',10, 12, 2, 0, 'INVENTORY' UNION ALL
SELECT 3,'25486','604C2500-071','604C2503-002','2021-09-25',3, 80, 77, 78, 'XYZ' UNION ALL
SELECT 4,'25514','604C2500-072','604C2503-002','2021-09-26',10, 75, 65, 78, 'XYZ' UNION ALL
SELECT 5,'25513','604C2500-069','604C2503-002','2021-09-27',10, 65, 55, 78, 'XYZ'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF
select * from #mytable
Any help is appreciated. I have tried to use a cursor with a variable in LAG (e.g., LAG(@C2OHAfter)) but it always returns a null.
Thanks,
Ross
- ross.hughes wrote:
I am working on a job shortage report that first consumes inventory and then works through job supply one job at a time. The query is using sum over partition to chip away at the inventory and jobs in general.
Why has the decision to go RBAR ("row by agonizing row") already been made? The example table seems to have already been derived from base tables and defines column labels containing words such as "before", "after", and "next". Instead of forcing SQL to make this work maybe it's possible to back up on the problem a little
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Hi Steve,
I would rather not do that but this is the final stage of a busier query. Initially, I am doing a running total against on hand inventory and creating a good parts and short parts tables. Then, I am trying to take the short parts table and compare them against a time phase table by deducting the values for each supply job until they are consumed. When it came to that last time phase step, I ended up in RBAR with a cursor. If there is a better way to do that, I would love to take it.
My issue is that I need to return the supply job number that is fulfilling each demand row shortage. I ended up exploding the time phase table into a table of single quantity job lines and "deducting" consumed rows with a bit flag. All works fine except when there is overlap between jobs and a remainder exists. Now, I am just looking to carry that remainder forward to subsequent rows.
Ross
-
Ok when you explode the lines the Demand is being split up to correspond incrementally to the date. However, when expanded the OnHand and NextJobQty are being replicated in full. Instead of replicating the elements which get added (in the SUM OVER) I zero'ed out all but the initial value. Maybe something like 'maybe_this'
drop table if exists #mytable;
--===== Create the test table with
CREATE TABLE #mytable(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
JobNumber VARCHAR(14),
PartNumber VARCHAR(50),
MtlPartNumber VARCHAR(50),
ReqDueDate DATE,
RemainingDemand DECIMAL(12,5),
OnHandBefore DECIMAL(12,5),
OnHandAfter DECIMAL(12,5),
NextJobQty DECIMAL(12,5),
NextJobNum VARCHAR(14));
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable(ID, JobNumber, PartNumber ,MtlPartNumber, ReqDueDate, RemainingDemand, OnHandBefore, OnHandAfter, NextJobQty, NextJobNum)
SELECT 1,'25512','604C2500-069','604C2503-002','2021-09-23',10, 22, 12, 0, 'INVENTORY' UNION ALL
SELECT 2,'25497','604C2500-070','604C2503-002','2021-09-24',10, 0, 2, 0, 'INVENTORY' UNION ALL
SELECT 3,'25486','604C2500-071','604C2503-002','2021-09-25',3, 0, 77, 78, 'XYZ' UNION ALL
SELECT 4,'25514','604C2500-072','604C2503-002','2021-09-26',10, 0, 65, 0, 'XYZ' UNION ALL
SELECT 5,'25513','604C2500-069','604C2503-002','2021-09-27',10, 0, 55, 0, 'XYZ'
SET IDENTITY_INSERT #mytable OFF
select *, sum(OnHandBefore-RemainingDemand+NextJobQty) over (order by id) maybe_this
from #mytable;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply