>>>
>>>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
If it's not broken, fix it until it is.
My Blog 
