>>>>
>>>>CREATE TABLE #myOtherTable (cost money not null)
>>>>
>>>>select CAST(cost as money) as cost
>>>>into #newtable
>>>>from #myOtherTable
>>>>
>>>>INSERT into #newtable VALUES (NULL)
>>>>
>>>>SELECT * FROM #newtable
>>>>
>>>>DROP TABLE #myOtherTable
>>>>DROP TABLE #newtable
>>>>
>>>
>>>Hmm, yesterday something similar didn't work in my tests, so I resorted to coalesce trick.
>>
>>CAST() do the job. Your's test is bad somewhere. I'm sure.
>
>I've been working on this problem all Saturday's night and yesterday without much of a success. This is my latest version of the SP, I can use some help of figuring out how to properly match #CostTiers vs. #Sales. That query is taking the most of the time. I figured how can I do a single tier (see the first insert query), but I can not figure out how to match correctly when I need to span multiple tiers and I use the original idea of my colleague with joining with the Numbers table.
>
>Also, you should be able to run this SP if you comment out references to i_pchord table (not important here). Take a look at the test cases at the very bottom of this SP.
>
>IF NOT EXISTS (
> SELECT *
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_NAME = 'siriussp_CostOfGoodsSold_FIFO_2012'
> AND ROUTINE_TYPE = 'PROCEDURE'
> )
> EXECUTE ('CREATE PROCEDURE dbo.siriussp_CostOfGoodsSold_FIFO_2012 AS SET NOCOUNT ON;');
>GO
>
>ALTER PROCEDURE dbo.siriussp_CostOfGoodsSold_FIFO_2012 (@dtStart DATETIME)
> --=============================================================
> /* SP that returns total quantity and cost of goods sold
> by department, category, item, invent_id, and locatn_id,
> using FIFO (First IN, First OUT) method of cost valuation.
> Modified on 07/09/2012
>--=============================================================
>*/
>AS
>BEGIN
> SET NOCOUNT ON;
>
> IF NOT EXISTS (
> SELECT NAME
> FROM TempDB.sys.sysindexes
> WHERE NAME = 'idx_Inventory_fifo_rank'
> )
> CREATE INDEX idx_Inventory_fifo_rank ON #Inventory (
> InvNo
> ,fifo_rank
> ) INCLUDE (
> ref_no
> ,date_time
> ,department
> ,category
> ,item
> ,invent_id
> ,locatn_id
> ,trans_type
> ,quantity
> ,unit_cost
> );
>
> IF OBJECT_ID('TempDB..#CostTiers', N'U') IS NOT NULL
> DROP TABLE #CostTiers;
>
> SELECT date_time
> ,InvNo
> ,unit_cost
> ,department
> ,category
> ,item
> ,invent_id
> ,units_thru - quantity + 1 AS units_from
> ,units_thru
> INTO #CostTiers
> FROM (
> SELECT ii3.date_time
> ,ii3.InvNo
> ,ii3.unit_cost
> ,ii3.quantity
> ,ii3.department
> ,ii3.category
> ,ii3.item
> ,ii3.invent_id
> ,SUM(ii3.quantity) OVER (
> PARTITION BY ii3.InvNo ORDER BY ii3.fifo_rank ROWS BETWEEN UNBOUNDED PRECEDING
> AND CURRENT ROW
> ) AS units_thru
> FROM #Inventory ii3
> WHERE ii3.trans_type IN (
> 'P'
> ,'A'
> ,'T'
> )
> AND ii3.quantity > 0
> ) X
> OPTION (RECOMPILE);
>
> IF OBJECT_ID('TempDB..#Sales', N'U') IS NOT NULL
> DROP TABLE #Sales;
>
> SELECT trans_no
> ,date_time
> ,InvNo
> ,department
> ,category
> ,item
> ,invent_id
> ,locatn_id
> ,Sold -- or Returned
> ,RemovedPrior
> INTO #Sales
> FROM (
> SELECT I.ref_no AS trans_no
> ,I.date_time
> ,I.InvNo
> ,I.department
> ,I.category
> ,I.item
> ,I.invent_id
> ,I.locatn_id
> ,I.trans_type
> ,0 - I.quantity AS Sold -- or Returned
> ,(
> 0 - SUM(I.quantity) OVER (
> PARTITION BY I.InvNo ORDER BY I.fifo_rank ROWS BETWEEN UNBOUNDED PRECEDING
> AND CURRENT ROW
> )
> ) + I.quantity AS RemovedPrior
> FROM #Inventory I
> WHERE I.Trans_Type = 'S'
> OR I.Quantity < 0 -- negative transfers
> ) I
> WHERE I.trans_type = 'S'
> AND I.date_time >= @dtStart
> OPTION (RECOMPILE);
>
> CREATE INDEX idx_CostTiers ON #CostTiers (
> InvNo
> ,date_time
> ,units_from
> ,units_thru
> ) INCLUDE (unit_cost);
>
> CREATE INDEX idx_Sales ON #Sales (
> InvNo
> ,date_time
> ,Sold
> ) INCLUDE (
> trans_no
> ,department
> ,category
> ,item
> ,invent_id
> ,locatn_id
> ,RemovedPrior
> );
>
> IF OBJECT_ID('TempDB..#SoldStack', N'U') IS NOT NULL
> DROP TABLE #SoldStack;
>
> SELECT S.trans_no
> ,S.department
> ,S.category
> ,S.item
> ,S.invent_id
> ,S.locatn_id
> ,S.date_time
> ,S.Sold AS QuantitySold
> ,CAST(COALESCE(CT.unit_cost,NULL) AS Money) AS unit_cost
> ,CAST(COALESCE(S.Sold * CT.unit_cost, NULL) AS Money) AS CostOfGoodsSold
> INTO #SoldStack
> FROM #Sales S
> INNER JOIN #CostTiers CT ON S.InvNo = CT.InvNO
> AND S.date_time >= CT.date_time
> AND (S.RemovedPrior <=0 OR S.RemovedPrior BETWEEN CT.units_from AND CT.units_thru)
> AND S.RemovedPrior + S.Sold BETWEEN CT.units_from AND CT.units_thru
>
> INSERT INTO #SoldStack
> (trans_no
> ,department
> ,category
> ,item
> ,invent_id
> ,locatn_id
> ,date_time
> ,QuantitySold
> ,unit_cost)
>
> SELECT SoldStack.trans_no
> ,SoldStack.department
> ,SoldStack.category
> ,SoldStack.item
> ,SoldStack.invent_id
> ,SoldStack.locatn_id
> ,SoldStack.date_time
> ,SoldStack.Sold AS QuantitySold
> ,CostTiers.unit_cost
> FROM (
> SELECT S.trans_no
> ,S.InvNo
> ,S.department
> ,S.category
> ,S.item
> ,S.invent_id
> ,S.locatn_id
> ,S.Sold
> ,S.RemovedPrior
> ,S.date_time
> ,CASE
> WHEN S.RemovedPrior > 0
> THEN S.RemovedPrior
> ELSE 0
> END + SIGN(S.Sold) * N.number AS SoldStack
> FROM #Sales S
> INNER JOIN dbo.numbers N on N.number > 0
> AND N.number <= ABS(S.Sold)
> WHERE NOT EXISTS (SELECT 1 FROM #SoldStack SS WHERE SS.trans_no = S.trans_no)
> ) SoldStack
>
> LEFT JOIN #CostTiers CostTiers ON SoldStack.InvNo = CostTiers.InvNo
> AND SoldStack.date_time >= CostTiers.date_time
> AND SoldStack.SoldStack BETWEEN CostTiers.units_from
> AND CostTiers.units_thru
> ;
>
> CREATE INDEX idsSoldStack_DCI ON #SoldStack (
> department
> ,category
> ,item
> ,invent_id
> ,date_time
> )
> WHERE unit_cost IS NULL;
>
> UPDATE #SoldStack
> SET unit_cost = LastMatrixCost.LastCost
> FROM #SoldStack SoldStack
> OUTER APPLY (
> SELECT TOP (1) unit_cost AS LastCost
> FROM #CostTiers CT
> WHERE CT.department = SoldStack.department
> AND CT.category = SoldStack.category
> AND CT.item = SoldStack.item
> AND CT.invent_id = SoldStack.invent_id
> AND CT.date_time <= SoldStack.date_time
> ORDER BY date_time DESC
> ) LastMatrixCost
> WHERE SoldStack.unit_cost IS NULL;
>
> UPDATE #SoldStack
> SET unit_cost = LastCost.LastCost
> FROM #SoldStack SoldStack
> OUTER APPLY (
> SELECT TOP (1) unit_cost AS LastCost
> FROM #CostTiers CT
> WHERE CT.department = SoldStack.department
> AND CT.category = SoldStack.category
> AND CT.item = SoldStack.item
> AND CT.date_time <= SoldStack.date_time
> ORDER BY date_time DESC
> ) LastCost
> WHERE SoldStack.unit_cost IS NULL
>
> UPDATE #SoldStack
> SET unit_cost = LastMatrixPO.LastCost
> FROM #SoldStack SoldStack
> OUTER APPLY (
> SELECT TOP (1) unit_cost AS LastCost
> FROM dbo.i_pchord ii
> WHERE ii.department = SoldStack.department
> AND ii.category = SoldStack.category
> AND ii.item = SoldStack.item
> AND ii.invent_id = SoldStack.invent_id
> AND ii.date_time <= SoldStack.date_time
> ORDER BY ii.date_time DESC
> ) LastMatrixPO
> WHERE SoldStack.unit_cost IS NULL
> AND SoldStack.invent_id <> 0;
>
> UPDATE #SoldStack
> SET unit_cost = LastPO.LastCost
> FROM #SoldStack SoldStack
> OUTER APPLY (
> SELECT TOP (1) unit_cost AS LastCost
> FROM dbo.i_pchord ii
> WHERE ii.department = SoldStack.department
> AND ii.category = SoldStack.category
> AND ii.item = SoldStack.item
> AND ii.date_time <= SoldStack.date_time
> ORDER BY ii.date_time DESC
> ) LastPO
> WHERE SoldStack.unit_cost IS NULL;
>
> UPDATE #SoldStack
> SET unit_cost = 0
> FROM #SoldStack SoldStack
> WHERE unit_cost IS NULL;
>
> SELECT SoldStack.trans_no
> ,SoldStack.department
> ,SoldStack.category
> ,SoldStack.item
> ,SoldStack.invent_id
> ,SoldStack.locatn_id
>
> ,SoldStack.QuantitySold
> ,CASE WHEN SoldStack.CostOfGoodsSold IS NULL THEN CAST(SIGN(SoldStack.QuantitySold) * SUM(unit_cost) AS MONEY)
> ELSE SoldStack.CostOfGoodsSold END
> AS CostOfGoodsSold
> FROM #SoldStack SoldStack
> GROUP BY SoldStack.trans_no
> ,SoldStack.department
> ,SoldStack.category
> ,SoldStack.item
> ,SoldStack.invent_id
> ,SoldStack.locatn_id
> ,SoldStack.QuantitySold
> ,SoldStack.CostOfGoodsSold;
>
> RETURN;
>END
>GO
>
>/* Test Cases
>IF OBJECT_ID('TempDB..#Inventory',N'U') IS NOT NULL DROP TABLE #Inventory;
>CREATE TABLE [dbo].[#Inventory](
> [InvNo] [int] NOT NULL,
> [ref_no] [numeric](17, 0) NOT NULL,
> [locatn_id] [int] NOT NULL,
> [date_time] [datetime] NOT NULL,
> [fifo_rank] [bigint] NULL,
> [department] [char](10) NOT NULL,
> [category] [char](10) NOT NULL,
> [item] [char](10) NOT NULL,
> [invent_id] [int] NOT NULL,
> [trans_type] [char](1) NOT NULL,
> [quantity] [numeric](8, 2) NOT NULL,
> [unit_cost] [money] NOT NULL
>)
>;with cte as (SELECT N'25' AS [ref_no], N'1' AS [locatn_id], N'2012-06-29 16:48:39.000' AS [date_time], N'1' AS [fifo_rank], N'1' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BATT_TEST' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'100.00' AS [quantity], N'1.00' AS [unit_cost] UNION ALL
>SELECT N'133005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-29 17:00:13.000' AS [date_time], N'2' AS [fifo_rank], N'1' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BATT_TEST' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-90.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'25' AS [ref_no], N'1' AS [locatn_id], N'2012-06-29 17:26:47.000' AS [date_time], N'3' AS [fifo_rank], N'1' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BATT_TEST' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'100.00' AS [quantity], N'2.00' AS [unit_cost] UNION ALL
>SELECT N'135005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-29 17:28:19.000' AS [date_time], N'4' AS [fifo_rank], N'1' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BATT_TEST' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'10.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'0' AS [ref_no], N'1' AS [locatn_id], N'2012-06-27 11:58:26.000' AS [date_time], N'1' AS [fifo_rank], N'2' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'10.00' AS [quantity], N'2.00' AS [unit_cost] UNION ALL
>SELECT N'129005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-27 13:02:57.000' AS [date_time], N'2' AS [fifo_rank], N'2' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-9.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'0' AS [ref_no], N'1' AS [locatn_id], N'2012-06-27 13:06:07.000' AS [date_time], N'3' AS [fifo_rank], N'2' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'10.00' AS [quantity], N'2.6667' AS [unit_cost] UNION ALL
>SELECT N'130005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-27 13:17:46.000' AS [date_time], N'4' AS [fifo_rank], N'2' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-7.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'131005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-27 13:18:16.000' AS [date_time], N'5' AS [fifo_rank], N'2' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'3.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'24' AS [ref_no], N'3' AS [locatn_id], N'2012-06-27 11:57:17.000' AS [date_time], N'1' AS [fifo_rank], N'3' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'20.00' AS [quantity], N'2.00' AS [unit_cost] UNION ALL
>SELECT N'0' AS [ref_no], N'3' AS [locatn_id], N'2012-06-27 11:58:26.000' AS [date_time], N'2' AS [fifo_rank], N'3' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'-10.00' AS [quantity], N'2.00' AS [unit_cost] UNION ALL
>SELECT N'24' AS [ref_no], N'3' AS [locatn_id], N'2012-06-27 13:04:29.000' AS [date_time], N'3' AS [fifo_rank], N'3' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'20.00' AS [quantity], N'3.00' AS [unit_cost] UNION ALL
>SELECT N'0' AS [ref_no], N'3' AS [locatn_id], N'2012-06-27 13:06:07.000' AS [date_time], N'4' AS [fifo_rank], N'3' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'-10.00' AS [quantity], N'2.6667' AS [unit_cost] UNION ALL
>SELECT N'4' AS [ref_no], N'1' AS [locatn_id], N'2011-04-03 18:34:44.000' AS [date_time], N'1' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'24.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
>SELECT N'11005001' AS [ref_no], N'1' AS [locatn_id], N'2011-04-07 09:57:51.000' AS [date_time], N'2' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'33005001' AS [ref_no], N'1' AS [locatn_id], N'2011-04-07 10:04:39.000' AS [date_time], N'3' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'103005001' AS [ref_no], N'1' AS [locatn_id], N'2011-07-06 17:55:17.000' AS [date_time], N'4' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'108005001' AS [ref_no], N'1' AS [locatn_id], N'2011-07-06 17:55:47.000' AS [date_time], N'5' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'115005001' AS [ref_no], N'1' AS [locatn_id], N'2011-08-01 17:47:11.000' AS [date_time], N'6' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'41005001' AS [ref_no], N'1' AS [locatn_id], N'2011-09-04 11:24:03.000' AS [date_time], N'7' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-2.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'48005001' AS [ref_no], N'1' AS [locatn_id], N'2011-09-04 11:38:31.000' AS [date_time], N'8' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-3.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'65005001' AS [ref_no], N'1' AS [locatn_id], N'2011-09-04 11:59:59.000' AS [date_time], N'9' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'1' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 17:02:19.000' AS [date_time], N'10' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'A' AS [trans_type], N'5.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
>SELECT N'0' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 17:09:46.000' AS [date_time], N'11' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'A' AS [trans_type], N'5.00' AS [quantity], N'0.10' AS [unit_cost] UNION ALL
>SELECT N'0' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 17:15:05.000' AS [date_time], N'12' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'5.00' AS [quantity], N'0.5469' AS [unit_cost] UNION ALL
>SELECT N'0' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 17:15:47.000' AS [date_time], N'13' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'5.00' AS [quantity], N'0.5469' AS [unit_cost] UNION ALL
>SELECT N'125005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 18:00:26.000' AS [date_time], N'14' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-10.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'126005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 18:01:05.000' AS [date_time], N'15' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'5.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'127005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 18:02:07.000' AS [date_time], N'16' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-50.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'128005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 18:02:51.000' AS [date_time], N'17' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'30.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
>SELECT N'5' AS [ref_no], N'3' AS [locatn_id], N'2011-04-03 16:41:21.000' AS [date_time], N'1' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'60.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
>SELECT N'1' AS [ref_no], N'3' AS [locatn_id], N'2011-04-03 17:46:45.000' AS [date_time], N'2' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'A' AS [trans_type], N'-2.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
>SELECT N'4' AS [ref_no], N'3' AS [locatn_id], N'2011-04-03 18:34:44.000' AS [date_time], N'3' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'-24.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
>SELECT N'23' AS [ref_no], N'3' AS [locatn_id], N'2012-06-26 17:00:58.000' AS [date_time], N'4' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'10.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
>SELECT N'23' AS [ref_no], N'3' AS [locatn_id], N'2012-06-26 17:04:59.000' AS [date_time], N'5' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'20.00' AS [quantity], N'0.10' AS [unit_cost] UNION ALL
>SELECT N'0' AS [ref_no], N'3' AS [locatn_id], N'2012-06-26 17:15:05.000' AS [date_time], N'6' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'-5.00' AS [quantity], N'0.5469' AS [unit_cost] UNION ALL
>SELECT N'0' AS [ref_no], N'3' AS [locatn_id], N'2012-06-26 17:15:47.000' AS [date_time], N'7' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'-5.00' AS [quantity], N'0.5469' AS [unit_cost] )
>insert #Inventory ([ref_no], [locatn_id], [date_time], [fifo_rank], [InvNo], [department], [category], [item], [invent_id], [trans_type], [quantity], [unit_cost])
>SELECT [ref_no], [locatn_id], [date_time], [fifo_rank], [InvNo], [department], [category], [item], [invent_id], [trans_type], [quantity], [unit_cost]
>from cte
>--CREATE INDEX idx_Inventory_fifo_rank ON #Inventory (InvNo, fifo_rank)
>SELECT * FROM #Inventory WHERE Item = 'Granolabar' ORDER BY InvNo, fifo_rank
>set statistics io on
>DECLARE @Time datetime2(7) = SYSDATETIME(), @Elapsed int, @dtStart datetime
>set @dtStart = '20120629'
>EXECUTE dbo.siriussp_CostOfGoodsSold_FIFO '20010629'
>set @Elapsed = DATEDIFF(millisecond,@time, SYSDATETIME())
>print 'Elapsed for SQL 2005: ' + convert(varchar(10),@Elapsed) + ' milliseconds'
>SET @Time = SYSDATETIME()
>EXECUTE dbo.siriussp_CostOfGoodsSold_FIFO_2012 '20010629'
>set @Elapsed = DATEDIFF(millisecond,@time, SYSDATETIME())
>print 'Elapsed for SQL 2012: ' + convert(varchar(10),@Elapsed) + ' milliseconds'
>set statistics io off
>go
>
>*/
>Do not have time to study this papyrus.
However, this join is certainly not a great solution.
FROM #Sales S
INNER JOIN dbo.numbers N on N.number > 0
AND N.number <= ABS(S.Sold)
