March 27, 2019 at 6:22 am
Hello Everyone,
I am working with a poorly designed database that we are not allowed to change. I just love companies that do not code anything, but purchase everything.
DECLARE @ItemNumber Table
(
ItemNumber varchar(10)
, PONumber varchar(10)
, Quantity int
, ReceiptDate date
)
INSERT INTO @ItemNumber
SELECT 'NX35112', '004-0013', 7, '2018-09-04'
DECLARE @InventoryTransaction TABLE
(
PoNumber varchar(10)
, ItemNumber varchar(10)
, ActualDate datetime
, OrderQuantity int
)
INSERT INTO @InventoryTransaction
SELECT 'NX35112', '004-0013', '2018-09-04',1 UNION ALL
SELECT 'NX35112', '004-0013', '2018-09-10',2 UNION ALL
SELECT 'NX35112', '004-0013', '2018-09-12',1 UNION ALL
SELECT 'NX35112', '004-0013', '2018-09-16',1 UNION ALL
SELECT 'NX35112', '004-0013', '2018-09-20',1 UNION ALL
SELECT 'NX35112', '004-0013', '2018-09-22',2 UNION ALL
SELECT 'NX35112', '004-0013', '2018-09-26',1 UNION ALL
SELECT 'NX35112', '004-0013', '2018-09-30',2 UNION ALL
SELECT 'NX35112', '004-0013', '2018-10-03',4 UNION ALL
SELECT 'NX35112', '004-0013', '2018-10-05',1
I would like to Begin the select of the the ActualDate from the @InventoryTransaction Table that is greater than the @ItemNumber.ActualDate. In this case, the first row that qualifies would be '2018-09-10'. Now using the Quantity of 7 from the @ItemNumber table, SUM the OrderQuantity in the @InventoryTransaction table, until that number equals 7(which is the Quantity from the @ItemNumber table.
The result would be:
NX35112, 004-0013, 2018-09-10, 2
NX35112, 004-0013, 2018-09-12, 1
NX35112, 004-0013, 2018-09-16, 1
NX35112, 004-0013, 2018-09-20, 1
NX35112, 004-0013, 2018-09-22, 2
The SUM of the OrderQuantity Must equal 7. And those are the only rows to be selected in order of the ActualDate ASC.
Thank You Very Much in Advance fr your time
Andrew SQLDBA
March 27, 2019 at 6:40 am
This should get you started
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @ItemNumber Table
(
PONumber varchar(10)
, ItemNumber varchar(10)
, Quantity int
, ReceiptDate date
)
INSERT INTO @ItemNumber
SELECT 'NX35112', '004-0013', 7, '2018-09-04'
DECLARE @InventoryTransaction TABLE
(
PoNumber varchar(10)
, ItemNumber varchar(10)
, ActualDate datetime
, OrderQuantity int
);
INSERT INTO @InventoryTransaction
SELECT 'NX35112', '004-0013', '2018-09-04',1 UNION ALL
SELECT 'NX35112', '004-0013', '2018-09-10',2 UNION ALL
SELECT 'NX35112', '004-0013', '2018-09-12',1 UNION ALL
SELECT 'NX35112', '004-0013', '2018-09-16',1 UNION ALL
SELECT 'NX35112', '004-0013', '2018-09-20',1 UNION ALL
SELECT 'NX35112', '004-0013', '2018-09-22',2 UNION ALL
SELECT 'NX35112', '004-0013', '2018-09-26',1 UNION ALL
SELECT 'NX35112', '004-0013', '2018-09-30',2 UNION ALL
SELECT 'NX35112', '004-0013', '2018-10-03',4 UNION ALL
SELECT 'NX35112', '004-0013', '2018-10-05',1;
;WITH BASE_DATA AS
(
SELECT
IT.PoNumber
,IT.ItemNumber
,IT.ActualDate
,IT.OrderQuantity
,CASE
WHEN SUM(IT.OrderQuantity) OVER
(
PARTITION BY IT.PoNumber
ORDER BY IT.ActualDate ASC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) <= ITN.Quantity THEN 1
ELSE 0
END AS TFLG
FROM @InventoryTransaction IT
INNER JOIN @ItemNumber ITN
ON IT.PoNumber = ITN.PONumber
AND IT.ActualDate > ITN.ReceiptDate
)
SELECT
BD.PoNumber
,BD.ItemNumber
,BD.ActualDate
,BD.OrderQuantity
FROM BASE_DATA BD
WHERE BD.TFLG = 1
ORDER BY BD.ActualDate ASC;
Output
PoNumber ItemNumber ActualDate OrderQuantity
---------- ---------- ----------------------- -------------
NX35112 004-0013 2018-09-10 00:00:00.000 2
NX35112 004-0013 2018-09-12 00:00:00.000 1
NX35112 004-0013 2018-09-16 00:00:00.000 1
NX35112 004-0013 2018-09-20 00:00:00.000 1
NX35112 004-0013 2018-09-22 00:00:00.000 2
March 27, 2019 at 12:58 pm
Thank You Sir,
You are correct, this is very close. I was trying to take it one small step at a time. I think by doing that, it bit me. The code that you posted works perfect for the one ItemNumber that was given. When I tried to add multiples, it is not returning the data as planned. But I am working on it.
Thank you again for your time
Andrew
AndewSQLDBA
March 28, 2019 at 12:58 am
AndrewSQLDBA - Wednesday, March 27, 2019 12:58 PMThank You Sir,
You are correct, this is very close. I was trying to take it one small step at a time. I think by doing that, it bit me. The code that you posted works perfect for the one ItemNumber that was given. When I tried to add multiples, it is not returning the data as planned. But I am working on it.
Thank you again for your timeAndrew
AndewSQLDBA
You'll have to change the code to use ItemNumber instead of PoNumber
😎
This should work for multiple ItemNumbers
;WITH BASE_DATA AS
(
SELECT
IT.PoNumber
,IT.ItemNumber
,IT.ActualDate
,IT.OrderQuantity
,CASE
WHEN SUM(IT.OrderQuantity) OVER
(
PARTITION BY IT.ItemNumber
ORDER BY IT.ActualDate ASC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) <= ITN.Quantity THEN 1
ELSE 0
END AS TFLG
FROM @InventoryTransaction IT
INNER JOIN @ItemNumber ITN
ON IT.ItemNumber = ITN.ItemNumber
AND IT.ActualDate > ITN.ReceiptDate
)
SELECT
BD.PoNumber
,BD.ItemNumber
,BD.ActualDate
,BD.OrderQuantity
FROM BASE_DATA BD
WHERE BD.TFLG = 1
ORDER BY BD.ActualDate ASC;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply