October 7, 2014 at 7:06 am
Hello,
I found next script for batch item allocation based on FEFO/FIFO method (according to DateCol interpretation) :
DECLARE @Table TABLE
(
RowID int identity Primary Key,
DOCTYPE varchar(40),
DateCol datetime,
QTY int,
PRD varchar(10),
LOT varchar(30)
)
INSERT INTO @Table (DOCTYPE, DateCol, QTY, PRD, LOT)
SELECT'Purchase','01/01/2007',20,'AA','2007FW'
UNION SELECT 'Purchase','01/04/2007',50,'AA','2007SS'
UNION SELECT 'Purchase','01/09/2007',10,'AA','2007FW'
UNION SELECT 'Sale','01/03/2007',-30,'AA',NULL
UNION SELECT 'Sale','01/04/2007',-20,'AA',NULL
ORDER BY 2
SELECT
*
FROM @Table
DECLARE @SalesLoop int
DECLARE @SalesQty int
DECLARE @StockLoop int
DECLARE @StockQty int
DECLARE @LotString varchar(100)
SELECT
@SalesLoop = MIN(RowID)
FROM @Table
WHERE LOT IS NULL
AND DOCTYPE = 'Sale'
WHILE @SalesLoop IS NOT NULL
BEGIN
SET @LotString = ''
SELECT
@SalesQty = -1 * QTY
FROM @Table
WHERE RowID = @SalesLoop
WHILE @SalesQty > 0
BEGIN
SELECT
@StockLoop = MIN(RowID)
FROM @Table
WHERE QTY > 0
AND DOCTYPE = 'Purchase'
SELECT
@StockQty = QTY,
@LotString =
CASE
WHEN @LotString = '' THEN LOT ELSE @LotString + '!' + LOT
END
FROM @Table
WHERE RowID = @StockLoop
IF (@SalesQty > @StockQty OR @SalesQty = @StockQty)
BEGIN
SET @SalesQty = @SalesQty - @StockQty
UPDATE @Table
SET QTY = 0
WHERE RowID = @StockLoop
END
ELSE IF @SalesQty < @StockQty
BEGIN
UPDATE @Table
SET QTY = QTY - @SalesQty
WHERE RowID = @StockLoop
SET @SalesQty = 0
END
END
UPDATE @Table
SET LOT = @LotString
WHERE RowID = @SalesLoop
-----
SELECT
@SalesLoop = MIN(RowID)
FROM @Table
WHERE LOT IS NULL
AND DOCTYPE = 'Sale'
END
SELECT
*
FROM @Table
the result looks like :
RowIDDOCTYPEDateCol QTYPRDLOT
1 Purchase2007-01-01 00:00:00.0000AA2007FW
2 Sale 2007-01-03 00:00:00.000-30AA2007FW!2007SS
3 Purchase2007-01-04 00:00:00.00020AA2007SS
4 Sale 2007-01-04 00:00:00.000-20AA2007SS
5 Purchase2007-01-09 00:00:00.00010AA2007FW
The issue is on the second line. What I need is to split that record in 2 or more lines, every line containing one single lot record
Please help :crying: !
Thanks in advance!!
October 7, 2014 at 7:11 am
The script will be slow because of the WHILE loop.
You already have a decent script to set-up the sample data:
DECLARE @Table TABLE
(
RowID int identity Primary Key,
DOCTYPE varchar(40),
DateCol datetime,
QTY int,
PRD varchar(10),
LOT varchar(30)
);
INSERT INTO @Table (DOCTYPE, DateCol, QTY, PRD, LOT)
SELECT 'Purchase', '01/01/2007', 20, 'AA','2007FW'
UNION SELECT 'Purchase','01/04/2007', 50, 'AA','2007SS'
UNION SELECT 'Purchase','01/09/2007', 10, 'AA','2007FW'
UNION SELECT 'Sale', '01/03/2007', -30,'AA',NULL
UNION SELECT 'Sale', '01/04/2007', -20,'AA',NULL
ORDER BY 2
SELECT * FROM @Table;
Could you now give the desired results and explain which rules need to be followed?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 7, 2014 at 7:22 am
The result should look like this:
RowIDDOCTYPEDateColQTYPRDLOT
1Purchase00:00.00AA2007FW
2Sale00:00.0-20AA2007FW
6Sale00:00.0-10AA2007SS
3Purchase00:00.020AA2007SS
4Sale00:00.0-20AA2007SS
5Purchase00:00.010AA2007FW
I don't care about RowID for Sale type.
Regarding the loop method, do u have a better advice? My neuron is quite depressed because of this kind of allocation :sick:
October 7, 2014 at 7:29 am
Where do you get the numbers for Qty?
Why is it 0 on the first line? Why is it -10 on the third line?
And so on...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 7, 2014 at 9:05 am
My mistake,
I think i started from the bottom to the top:
Short story long:
Considering next script:
DECLARE @Table TABLE
(
RowID int identity Primary Key,
DOCTYPE varchar(40),
DocNum varchar(40),
Date datetime,
QTY int,
Item varchar(10),
LOT varchar(30)
)
SELECT 'Input'
INSERT INTO @Table (DOCTYPE,DocNum, Date , QTY, Item, LOT)
SELECT'Purchase','P1','01/01/2014',30,'AA','2007FW'
UNION SELECT 'Purchase','P2','01/04/2014',20,'AA','2007SS'
UNION SELECT 'Purchase','P3','01/04/2014',10,'AA','2007FW'
UNION SELECT 'Sale','I1', '01/03/2014',-25,'AA',NULL
UNION SELECT 'Sale','I2','01/04/2014',-30,'AA',NULL
SELECT * FROM @Table
DELETE FROM @Table
SELECT 'Output'
INSERT INTO @Table (DOCTYPE,DocNum, Date , QTY, Item, LOT)
SELECT 'Purchase','P1','01/01/2014',30,'AA','2007FW'
UNION SELECT 'Purchase','P2','04/01/2014',20,'AA','2007SS'
UNION SELECT 'Purchase','P3','04/01/2014',10,'AA','2007SW'
UNION SELECT 'Sale','I1','04/01/2014',-25,'AA','2007FW'
UNION SELECT 'Sale','I2','03/01/2014',-5,'AA','2007FW'
UNION SELECT 'Sale','I2','03/01/2014',-20,'AA','2007SS'
UNION SELECT 'Sale','I2','03/01/2014',-5,'AA','2007SW'
UNION SELECT 'Purchase','P4','09/01/2014',10,'AA','2007FW'
SELECT * FROM @Table
Based on the input result, i need a query to determine the lot attached to sales transactions. In our case, the transaction identified with DocNum "I1" was made using items from lot 2007FW (DocNum P1) because the lot was received before the sales date and was already on stock. The transaction with identified DocNum "I2" was made using items from three different lots. In this case, I need to see 3 distinct lines with 3 distinct batches and the lot quantity (QTY) attached to this transaction according to FirstInFirstOut Method. The output needed is in the upper script.
Hope this is enough.:( If not, let me know 🙁
Thanks in advance! 🙁
October 7, 2014 at 10:32 am
Untill you clearly and accurately specify what is your input sample and what is expected output based on that exact input sample, it is really hard to understand what you really need or can do.
1. You refer to the first set of data inserted into @Table as INPUT and to the second one as OUTPUT, but then in description you states that "The output needed is in the upper script."
2. What links "Sale" I1 transaction to "Purchase" P1? If input is the first set of data, then LOT cannot be used as it is set to NULL in "Sale" transaction. Do you really want to match "I1" to "P1" just by the number after the letter?
3. In your OUTPUT (second set of data inserted into @Table), you have a transaction listed "P4". Where does it come from if it is not found in your INPUT (of cause if your INPUT is INPUT and not OUTPUT...)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply