FIFO problem....
-
Hi all
I’ve been using this brilliant solution for FIFO calculation that is found here, (Dave’s one).
However, I need to break this apart. The script only gives the total of all transactions at the end and what I need to obtain is the following.
Does this make a sense? Any help would be very appreciated.
Nick
-
Not really. What would make more sense is if you would provide the DDL (CREATE TABLE statement) for your table(s) involved, sample data (INSERT INTO statements) for your table(s), the expected results based on your sample data, and what you have currently written to solve your problem.
If you need help with any of this, please take the time to read the first article I reference below in my signature block, it provides step by step instructions.
-
Haven't really looked at the code properly, but I did notice this:
Aside from its clever use of use of SQL Server features, it relies on a very important insight into the nature of the problem being solved. The previous cursor solution kept track of the running total, one transaction at a time, as items were added and removed. David realized that in a FIFO problem, this is not necessary: we just need to focus on what is left after all processing is complete.
Which suggests this solution may not be a good starting point for something to have the functionality you're after.
I could be wrong of course!
-
Here is the code with some sample data
CREATE TABLE dbo.Stock (StockID INT IDENTITY(1, 1)
NOT NULL ,
ArticleID nchar (10) NOT NULL ,
TranDate DATETIME NOT NULL ,
TranCode VARCHAR(3) NOT NULL ,
Items INT NOT NULL ,
Price MONEY NULL ,
CONSTRAINT [PK_Stock] PRIMARY KEY CLUSTERED ( StockID ASC )
)
INSERT dbo.Stock (ArticleID, TranDate, TranCode, Items, Price) VALUES (1, '20120402 00:00:00:000', 'RET', 1.0000, NULL)
INSERT dbo.Stock (ArticleID, TranDate, TranCode, Items, Price) VALUES (1, '20120205 00:00:00:000', 'IN', 5.0000, 6.00)
INSERT dbo.Stock (ArticleID, TranDate, TranCode, Items, Price) VALUES (1, '20120103 00:00:00:000', 'OUT', 1.0000, NULL)
INSERT dbo.Stock (ArticleID, TranDate, TranCode, Items, Price) VALUES (1, '20120101 00:00:00:000', 'IN', 2.0000, 5.00)
INSERT dbo.Stock (ArticleID, TranDate, TranCode, Items, Price) VALUES (2, '20120212 00:00:00:000', 'OUT', 2.0000, NULL)
INSERT dbo.Stock (ArticleID, TranDate, TranCode, Items, Price) VALUES (2, '20120202 00:00:00:000', 'IN', 3.0000, 4.00)
INSERT dbo.Stock (ArticleID, TranDate, TranCode, Items, Price) VALUES (3, '20120311 00:00:00:000', 'OUT', 1.0000, NULL)
INSERT dbo.Stock (ArticleID, TranDate, TranCode, Items, Price) VALUES (3, '20120305 00:00:00:000', 'IN', 2.0000, 7.00)
--Adding Indexes
CREATE NONCLUSTERED INDEX [IX_Dave_General]
ON [dbo].[Stock]
(
[ArticleID] ASC,
[TranDate] DESC,
[TranCode] ASC
)
INCLUDE ( [Items], [Price])
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Dave_Items]
ON [dbo].[Stock]
(
[ArticleID] ASC,
[TranDate] ASC
)
INCLUDE ( [Items])
WHERE ([TranCode] IN ('IN', 'RET'))
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Dave_Price]
ON [dbo].[Stock]
(
[ArticleID] ASC,
[TranDate] ASC
)
INCLUDE ( [Price])
WHERE ([TranCode]='IN')
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)
ON [PRIMARY]
GO
--The Script
WITH cteStockSum
AS ( SELECT ArticleID ,
SUM(CASE WHEN TranCode = 'OUT' THEN 0 - Items
ELSE Items
END) AS TotalStock
FROM dbo.Stock
GROUP BY ArticleID
),
cteReverseInSum
AS ( SELECT s.ArticleID ,
s.TranDate ,
( SELECT SUM(i.Items)
FROM dbo.Stock AS i WITH ( INDEX ( IX_Dave_Items ) )
WHERE i.ArticleID = s.ArticleID
AND i.TranCode IN ( 'IN', 'RET' )
AND i.TranDate >= s.TranDate
) AS RollingStock ,
s.Items AS ThisStock
FROM dbo.Stock AS s
WHERE s.TranCode IN ( 'IN', 'RET' )
),
/* Using the rolling balance above find the first stock movement in that meets
(or exceeds) our required stock level */
/* and calculate how much stock is required from the earliest stock in */
cteWithLastTranDate
AS ( SELECT w.ArticleID ,
w.TotalStock ,
LastPartialStock.TranDate ,
LastPartialStock.StockToUse ,
LastPartialStock.RunningTotal ,
w.TotalStock - LastPartialStock.RunningTotal
+ LastPartialStock.StockToUse AS UseThisStock
FROM cteStockSum AS w
CROSS APPLY ( SELECT TOP ( 1 )
z.TranDate ,
z.ThisStock AS StockToUse ,
z.RollingStock AS RunningTotal
FROM cteReverseInSum AS z
WHERE z.ArticleID = w.ArticleID
AND z.RollingStock >= w.TotalStock
ORDER BY z.TranDate DESC
) AS LastPartialStock
)
/* Sum up the cost of 100% of the stock movements in after the returned stockid and for that stockid we need 'UseThisStock' items' */
SELECT y.ArticleID ,
y.TotalStock AS CurrentItems ,
SUM(CASE WHEN e.TranDate = y.TranDate THEN y.UseThisStock
ELSE e.Items
END * Price.Price) AS CurrentValue
FROM cteWithLastTranDate AS y
INNER JOIN dbo.Stock AS e WITH ( INDEX ( IX_Dave_Items ) )
ON e.ArticleID = y.ArticleID
AND e.TranDate >= y.TranDate
AND e.TranCode IN ('IN', 'RET' )
CROSS APPLY (
/* Find the Price of the item in */ SELECT TOP ( 1 )
p.Price
FROM dbo.Stock AS p
WITH ( INDEX ( IX_Dave_Price ) )
WHERE p.ArticleID = e.ArticleID
AND p.TranDate <= e.TranDate
AND p.TranCode = 'IN'
ORDER BY p.TranDate DESC
) AS Price
GROUP BY y.ArticleID ,y.TotalStock
ORDER BY y.ArticleID
The stock table can be fed with any data respectively. The order price of a product comes from an order with TranCode=’IN’. Sales have a Trancode=’OUT’ and returns of products are using the last price of the product entered. As I mentioned in my previous post, the script delivers the totals of all transactions and it is extremely fast for lots of records. I need to break this apart and achieve the following
Hope now is much more clear.
Nick
-
Two things. I can't read the image that is supposed to be your expected results. Second, how about putting that into a separate table that we could actually compare our output to using SQL?
-
Yeah , sorry bout the bad image quality here is a temp table and recs.
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TempStockMovements](
[ArticleID] [nchar](10) NULL,
[StartItems] [decimal](18, 4) NULL,
[StartValue] [decimal](18, 4) NULL,
[OrderedItems] [decimal](18, 4) NULL,
[OrderedValue] [decimal](18, 4) NULL,
[SoldItems] [decimal](18, 4) NULL,
[SoldValue] [decimal](18, 4) NULL,
[CurrentItems] [decimal](18, 4) NULL,
[CurrentValue] [decimal](18, 4) NULL
) ON [PRIMARY]
GO
INSERT dbo.TempStockMovements VALUES (N'1 ', 2.0000, 10.0000, 6.0000, 36.0000, 1.0000, 5.0000, 7.0000, 41.0000)
INSERT dbo.TempStockMovements VALUES (N'2 ', 0.0000, 0.0000, 3.0000, 12.0000, 2.0000, 8.0000, 1.0000, 4.0000)
Cheers
Nick
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply