CREATE
TABLE
[dbo].[i_invent](
[pri_key] [
int
] IDENTITY(1,1)
NOT
NULL
,
[department] [
char
](10)
[category] [
[item] [
[invent_id] [
]
[trans_type] [
](1)
[ref_no] [
numeric
](17, 0)
[quantity] [
](8, 2)
[unit_cost] [money]
[locatn_id] [
[message] [
varchar
](25)
[exportd_on] [datetime]
[operator] [
](6)
[salespoint] [
[date_time] [datetime]
[po_link] [
[adj_type] [
CONSTRAINT
[i_invent_track_no]
PRIMARY
KEY
CLUSTERED
(
[pri_key]
ASC
)
WITH
(PAD_INDEX =
ON
, STATISTICS_NORECOMPUTE =
OFF
, IGNORE_DUP_KEY =
, ALLOW_ROW_LOCKS =
, ALLOW_PAGE_LOCKS =
, FILLFACTOR = 75)
[SaleTransactions]
NONCLUSTERED
INDEX
[date_time]
[dbo].[i_invent]
, SORT_IN_TEMPDB =
, DROP_EXISTING =
, ONLINE =
[department]
[category]
[item]
[invent_id]
[quantity]
[locatn_id]
[i_invent_po_link]
[po_link]
[ref_no]
GO
ALTER
NOCHECK
ADD
[FK_i_invent_i_items]
FOREIGN
([invent_id])
REFERENCES
[dbo].[i_items] ([invent_id])
FOR
REPLICATION
[dbo].[i_invent] NOCHECK
[FK_i_invent_i_locatn]
([locatn_id])
[dbo].[i_locatn] ([locat_id])
CHECK
[FK_i_invent_i_pchord]
([po_link])
[dbo].[i_pchord] ([pri_key])
[FK_i_invent_items]
([department], [category], [item])
[dbo].[items] ([department], [category], [item])
--==========================================================
/* SP that
returns
total quantity
and
cost
of
goods
on
hand
by
department, category, item, invent_id,
locatn_id,
using FIFO (
first
in
/
out
) method
cost valuation:
To
retrieve the total (FIFO) cost
for
all
inventory
location,
department:
EXECUTE
dbo.siriussp_CostOfGoodsOnHand_FIFO 1
locatn_id department QuantityOnHand CostOfGoodsOnHand
----------- ---------- -------------- ---------------------
-999 RETAIL 2 0.90
1 RETAIL 2359 31567.73
3 RETAIL 1609 19001.21
*/
--=========================================================
PROCEDURE
dbo.siriussp_CostOfGoodsOnHand_FIFO
@bIncludeZeroes
BIT
= 1 /* If 1,
then
include records
items
with
zero
-hand */
AS
BEGIN
SET
NOCOUNT
;
cteInventorySum
SELECT
department,
category,
item,
invent_ID,
locatn_ID,
SUM
(quantity)
TotalInventory,
MAX
(date_time)
LastDateTime
FROM
#Inventory
GROUP
BY
locatn_ID),
cteReverseInSum
(/* Perform a rolling balance (
reverse
order
) through the inventory movements
s.department,
s.category,
s.item,
s.invent_ID,
s.locatn_ID,
s.Fifo_Rank,
(i.quantity)
i
WHERE
i.department = s.department
AND
i.category = s.category
i.item = s.item
i.invent_id = s.invent_id
i.locatn_id = s.locatn_id
i.trans_Type
IN
'P'
'A'
'T'
i.Fifo_Rank >= s.Fifo_Rank)
RollingInventory,
(s.Quantity)
ThisInventory
s
s.Trans_Type
s.Department,
s.Category,
s.Item,
s.Invent_ID,
s.Locatn_ID,
s.Fifo_Rank),
cteWithLastTranDate
w.Department,
w.Category,
w.Item,
w.Invent_ID,
w.Locatn_ID,
w.LastDateTime,
w.TotalInventory,
COALESCE
(LastPartialInventory.Fifo_Rank,0)
Fifo_Rank,
(LastPartialInventory.InventoryToUse,0)
InventoryToUse,
(LastPartialInventory.RunningTotal,0)
RunningTotal,
w.TotalInventory -
(LastPartialInventory.RunningTotal,0) +
UseThisInventory
w
OUTER
APPLY (
TOP
( 1 ) z.Fifo_Rank,
z.ThisInventory
z.RollingInventory
RunningTotal
z
z.Department = w.Department
z.Category = w.Category
z.Item = w.Item
z.Invent_ID = w.Invent_ID
z.Locatn_ID = w.Locatn_ID
z.RollingInventory >= w.TotalInventory
ORDER
z.Fifo_Rank
DESC
LastPartialInventory),
LastCost
DISTINCT
Cogs.department,
Cogs.category,
Cogs.item,
Cogs.invent_id,
LastCost.LastCost
cteWithLastTranDate Cogs
CROSS
APPLY dbo.siriusfn_LastCostUpToDate(Cogs.department,Cogs.category,Cogs.item,Cogs.invent_id, Cogs.LastDateTime) LastCost
Cogs.UseThisInventory
IS
OR
Cogs.UseThisInventory = 0
Cogs.TotalInventory
Cogs.TotalInventory = 0),
cteSource
y.Department,
y.Category,
y.Item,
y.Invent_ID,
y.Locatn_ID,
y.TotalInventory
as
QuantityOnHand,
CASE
WHEN
e.Fifo_Rank = y.Fifo_Rank
THEN
y.UseThisInventory
ELSE
e.Quantity
END
* Price.Unit_Cost)
CostOfGoodsOnHand,
y
LEFT
JOIN
e
e.Department = y.Department
e.Category = y.Category
e.Item = y.Item
e.Invent_ID = y.Invent_ID
e.Locatn_ID = y.Locatn_ID
e.Fifo_Rank >= y.Fifo_Rank
e.Trans_Type
y.Department = LastCost.Department
y.Category = LastCost.Category
y.Item = LastCost.Item
y.Invent_ID = LastCost.Invent_ID
/* Find the Price
the item
(1) p.Unit_Cost
p
p.Department = e.Department
p.Category = e.Category
p.Item = e.Item
p.Invent_ID = e.Invent_ID
p.Locatn_ID = e.Locatn_ID
p.Fifo_Rank <= e.Fifo_Rank
p.Trans_Type
p.Fifo_Rank
Price
y.TotalInventory,
LastCost.LastCost)
Department,
Category,
Item,
Invent_ID,
Locatn_ID,
CONVERT
INT
,QuantityOnHand)
(CostOfGoodsOnHand,0)
QuantityOnHand <> 0
CostOfGoodsOnHand <> 0
CostOfGoodsOnHand / QuantityOnHand
, 0)
AverageCost
@bIncludeZeroes = 1
(@bIncludeZeroes = 0
CostOfGoodsOnHand <> 0)
Locatn_ID;
/* Test Cases
[dbo].[#Inventory](
[fifo_rank] [
bigint
[
IDENTITY_INSERT [dbo].[#Inventory]
TRANSACTION
INSERT
INTO
[dbo].[#Inventory]([pri_key], [ref_no], [locatn_id], [date_time], [fifo_rank], [department], [category], [item], [invent_id], [trans_type], [quantity], [unit_cost])
774, 0, 1,
'20120627 11:58:26.000'
, 1, N
'RETAIL '
, N
'SUPPLIES '
'BUG_SPRAY '
, 0, N
, 10.00, 2.0000
UNION
ALL
775, 129005001, 1,
'20120627 13:02:57.000'
, 2, N
'S'
, -9.00, 0.0000
778, 0, 1,
'20120627 13:06:07.000'
, 3, N
, 10.00, 2.6667
779, 130005001, 1,
'20120627 13:17:46.000'
, 4, N
, -7.00, 0.0000
780, 131005001, 1,
'20120627 13:18:16.000'
, 5, N
, 3.00, 0.0000
772, 24, 3,
'20120627 11:57:17.000'
, 20.00, 2.0000
773, 0, 3,
, -10.00, 2.0000
776, 24, 3,
'20120627 13:04:29.000'
, 20.00, 3.0000
777, 0, 3,
, -10.00, 2.6667
COMMIT
RAISERROR (N
'[dbo].[#Inventory]: Insert Batch: 1.....Done!'
, 10, 1)
NOWAIT;
PRINT
'FIFO Calculation Cost:'
declare
@
Time
datetime2(7) = SYSDATETIME(), @Elapsed
set
@Elapsed = DATEDIFF(microsecond,@
time
, getdate())
print
'Elapsed: '
+
convert
(10),@Elapsed) +
' microseconds'
go
FUNCTION
[dbo].[siriusfn_LastCostUpToDate] (
@cDepartment
CHAR
(10)
,@cCategory
,@cItem
,@iInventID
,@dtEnd DATETIME
-- cut off date
RETURNS
--==============================================
/*
Function
that
the
last
unit cost value
every matrix item within the given range. It evaluates,
, until it finds the
applicable record:
1.
received cost
at
the matrix
level
2.
3.
ordered cost
4.
5. If
no
history
is
found,
zero.
RETURN
cteItemsOnly
i.department
,i.category
,i.item
,i.inventory
dbo.items i
i.department = @cDepartment
i.category = @cCategory
i.item = @cItem
,cteItems
ISNULL
(ii.invent_id, 0)
invent_id
,inventory
cteItemsOnly i
dbo.i_items ii
i.inventory = 0
ii.department = i.department
ii.category = i.category
ii.item = i.item
ii.invent_id = @iInventID
,cteRcvdMatrix
,i.invent_id
,F.unit_cost
cteItems i
1 unit_cost
dbo.i_invent ii
trans_type
i.department = ii.department
i.category = ii.category
i.item = ii.item
i.invent_id = ii.invent_id
ii.date_time <= @dtEnd
ii.date_time
,unit_cost
) F
,cteRcvdItem
*
cteRcvdMatrix
cteRcvdMatrix i
i.LastCost
,ctePOMatrix
cteRcvdItem
cteRcvdItem i
(1) unit_cost
dbo.i_pchord ii
,ctePOItem
ctePOMatrix
ctePOMatrix i
coalesce
(i.LastCost, 0)
ctePOItem i
statistics
io
dbo.siriusfn_LastCost(
'RT34HANDW'
'058GLOVEL'
'19599 '
, 409)
-- RT34HANDW 058GLOVEL 19599
dbo.siriusfn_LastCostUpToDate(
, 409,
'20040101'
-- select top (1) * from dbo.i_invent where invent_id = 409 and trans_type in ('A','P','T') and quantity > 0 order by date_time desc
off
IF OBJECT_ID(
'TempDB..#MovingInventory'
'U'
DROP
#MovingInventory;
[dbo].[#MovingInventory] (
InvNo
,fifo_rank
,quantity
,unit_cost MONEY
,Removed
,Returned
,CurrentQuantity
pkMovingInventory
#MovingInventory (
,0
quantity > 0
,fifo_rank;
'TempDB..#Sales'
#Sales;
[dbo].[#Sales] (
,[trans_no] [
,[locatn_id] [
,[date_time] [datetime]
,[department] [
COLLATE
DATABASE_DEFAULT
,[category] [
,[item] [
,[invent_id] [
'TempDB..#Removed'
#Removed;
[dbo].[#Removed] (
unit_cost MONEY
WHILE (@@FETCH_STATUS = 0)
@fifo_rank =
(fifo_rank)
,@Removed = - 1 *
date_time < @dtStart
trans_type =
quantity < 0
InvNo = @InvNo;
IF
(@Removed, 0) > 0
-- what to do when we start with returns - unlikely to happen, though?
IF @Debug = 1
'Calculating starting inventory'
;;
(@Removed) s.unit_Cost
,s.fifo_rank
,s.quantity
#MovingInventory
CAST
(s.Quantity
)) ROW_NUMBER() OVER (
number
n
dbo.numbers n5
number > 0
f(n)
s.InvNo = @InvNo
s.fifo_rank < @fifo_rank
s.fifo_rank
,cteRemoved
unit_Cost
COUNT
(*)
Removed
UPDATE
M
Removed = R.Removed
,CurrentQuantity = M.CurrentQuantity - R.Removed
#MovingInventory M
INNER
cteRemoved R
M.fifo_rank = R.fifo_rank
M.InvNo = @InvNo;
-- We can also check if Removed = @Removed (if less, we have negative inventory - unlikely situation)
IF @quantity < 0
-- Sale or transfer
@Message =
'Sale or transfer with quantity = '
(- 1 * @quantity
VARCHAR
(20))
RAISERROR (
@Message
,10
,1
@Removed = - 1 * @quantity;
,s.CurrentQuantity
(s.CurrentQuantity) ROW_NUMBER() OVER (
s.CurrentQuantity > 0
I
CurrentQuantity = I.CurrentQuantity - R.Removed
,Removed = I.Removed + R.Removed
OUTPUT
Inserted.unit_cost
,Inserted.Removed - deleted.Removed
#Removed(unit_cost, Removed)
#MovingInventory I
I.fifo_rank = R.fifo_rank
I.InvNo = @InvNo;
'Current Moving Inventory after Sale or Return'
NOWAIT
IF @trans_type =
@date_time >= @dtStart
#Sales (
trans_no
,InvNo
,locatn_id
,date_time
,department
,category
,item
,invent_id
@ref_no
,@InvNo
,@locatn_id
,@date_time
,@department
,@category
,@item
,@invent_id
--- Need to check for situations when we sell more than currently in the inventory (rare cases)
@Difference = @Removed -
((
(Removed)
#Removed
), 0);
IF @Difference > 0
-- Sold more than were in the inventory
'Sold more than in the inventory - Difference = '
(@Difference
(10))
@LastCost = 0;
@LastCost = LastCost.LastCost
dbo.siriusfn_LastCostUpToDate(@department, @category, @item, @invent_id, @date_time) LastCost;
,@LastCost
,@Difference
@Returned = @quantity;
(@Returned) s.unit_Cost
(s.Removed - s.Returned) ROW_NUMBER() OVER (
(s.Removed - s.Returned) > 0
-- returns in the LIFO order
,cteReturned
Returned
CurrentQuantity = I.CurrentQuantity + R.Returned
,Returned = I.Returned + R.Returned
,Inserted.Returned - deleted.Returned
cteReturned R
'Result after return'
,(- 1) * Removed
-- handle returns
-- Need to check for situations when we return what we didn't have in the inventory before
'
Current
Sales
after
return
WITH NOWAIT;
SELECT @Difference = @Returned - COALESCE((
SELECT SUM(Removed)
FROM #Removed
IF @Difference > 0 -- Returned more than were in the inventory originally, use Last Cost
SET @Message = '
Returned more than removed - Difference=
' + CAST(@Difference AS VARCHAR(10)) + '
Last
Cost = ' +
(@LastCost
(20));
,- 1 * @Difference;
ANSI_NULLS
QUOTED_IDENTIFIER
ANSI_PADDING
---------------- #Inventory test object creation so the script below doesn't complain about #Inventory table -----------
IF OBJECT_ID('tempdb..#Inventory
', N'
U
') IS NOT NULL
DROP TABLE #Inventory;
CREATE TABLE [dbo].[#Inventory] (
[ref_no] [numeric](17, 0) NOT NULL
,[locatn_id] [int] NOT NULL
,[date_time] [datetime] NOT NULL
,[fifo_rank] [bigint] NULL
,[InvNo] [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
) ON [PRIMARY]
SET ANSI_PADDING OFF
INSERT [dbo].[#Inventory] (
,[locatn_id]
,[date_time]
,[fifo_rank]
,[InvNo]
,[department]
,[category]
,[item]
,[invent_id]
,[trans_type]
,[quantity]
,[unit_cost]
VALUES (
CAST(53 AS NUMERIC(17, 0))
,CAST(0x0000A20000FF6D74 AS DATETIME)
,N'
RETAIL
BK-CHILD
DSCATTEST
P
,CAST(40.00 AS NUMERIC(8, 2))
,10.0000
,2
,5.0000
CAST(136005001 AS NUMERIC(17, 0))
,CAST(0x0000A200011967D8 AS DATETIME)
,3
S
,CAST(- 50.00 AS NUMERIC(8, 2))
,0.0000
CAST(54 AS NUMERIC(17, 0))
,CAST(0x0000A200011967DA AS DATETIME)
,4
,7.5000
CAST(136005002 AS NUMERIC(17, 0))
,CAST(0x0000A200011967DE AS DATETIME)
,5
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = '
siriussp_CostOfGoodsSold_FIFO
AND ROUTINE_TYPE = '
EXECUTE ('
dbo.siriussp_CostOfGoodsSold_FIFO
');
ALTER PROCEDURE dbo.siriussp_CostOfGoodsSold_FIFO (
@dtStart DATETIME
,@Debug BIT = 0
--=============================================================
/* 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/10/2012
Modified on 07/19/2013 - 7/26/2013
SET NOCOUNT ON;
--IF CAST(LEFT(CAST(serverproperty('
ProductVersion
') AS VARCHAR(max)), 2) AS DECIMAL(10, 2)) >= 11
-- AND OBJECT_ID('
dbo.siriussp_CostOfGoodsSold_FIFO_2012
', '
--BEGIN
-- PRINT '
Using 2012 version
the stored
procedure
-- EXECUTE sp_ExecuteSQL N'
dbo.siriussp_CostOfGoodsSold_FIFO_2012 @dtStart, @Debug
-- ,N'
@dtStart DATETIME, @Debug
-- ,@dtStart, @Debug ;
-- RETURN;
--END
--PRINT '
Using
cursor
based version
IF OBJECT_ID('
TempDB..#Sales
DROP TABLE #Sales;
CREATE TABLE [dbo].[#Sales] (
InvNo INT NOT NULL
,[trans_no] [numeric](17, 0) NOT NULL
,[department] [char](10) COLLATE DATABASE_DEFAULT NOT NULL
,[category] [char](10) COLLATE DATABASE_DEFAULT NOT NULL
,[item] [char](10) COLLATE DATABASE_DEFAULT NOT NULL
,quantity INT
TempDB..#Removed
DROP TABLE #Removed;
CREATE TABLE [dbo].[#Removed] (
,Removed INT
TempDB..#MovingInventory
DROP TABLE #MovingInventory;
CREATE TABLE [dbo].[#MovingInventory] (
,fifo_rank INT NOT NULL
,Returned INT
,CurrentQuantity INT
,CONSTRAINT pkMovingInventory PRIMARY KEY (
INSERT INTO #MovingInventory (
SELECT InvNo
FROM #Inventory
WHERE trans_type IN (
,'
A
T
AND quantity > 0
ORDER BY InvNo
SELECT NAME
FROM TempDB.sys.sysindexes
WHERE NAME = '
idx_Inventory_fifo_rank
CREATE INDEX idx_Inventory_fifo_rank ON #Inventory (
);
DECLARE @InvNo INT
,@ref_no NUMERIC(17, 0)
,@locatn_id INT
,@date_time DATETIME
,@fifo_rank INT
,@department CHAR(10)
,@category CHAR(10)
,@item CHAR(10)
,@invent_id INT
,@trans_type CHAR(1)
,@quantity INT
,@unit_cost MONEY
,@LastCost MONEY
,@CurInvNo INT
,@Removed INT
,@Returned INT
,@Elapsed INT
,@StartTime DATETIME
,@Message VARCHAR(MAX)
,@Difference INT;
SET @StartTime = CURRENT_TIMESTAMP;
DECLARE curMainProcess CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
SELECT DISTINCT InvNo
ORDER BY InvNo;
OPEN curMainProcess;
FETCH NEXT
FROM curMainProcess
INTO @InvNo;
SELECT @fifo_rank = MAX(fifo_rank)
,@Removed = - 1 * SUM(quantity)
WHERE date_time < @dtStart
AND (
trans_type = '
OR quantity < 0
AND InvNo = @InvNo;
IF COALESCE(@Removed, 0) > 0 -- what to do when we start with returns - unlikely to happen, though?
PRINT '
Calculating starting inventory
';;
WITH cteSource
AS (
SELECT TOP (@Removed) s.unit_Cost
FROM #MovingInventory AS s
CROSS APPLY (
SELECT TOP (CAST(s.Quantity AS INT)) ROW_NUMBER() OVER (
ORDER BY number
) AS n
FROM dbo.numbers n5
WHERE number > 0
) AS f(n)
WHERE s.InvNo = @InvNo
AND s.fifo_rank < @fifo_rank
ORDER BY s.fifo_rank
SELECT unit_Cost
,COUNT(*) AS Removed
FROM cteSource
GROUP BY unit_Cost
UPDATE M
SET Removed = R.Removed
FROM #MovingInventory M
INNER JOIN cteRemoved R ON M.fifo_rank = R.fifo_rank
WHERE M.InvNo = @InvNo;
FROM #MovingInventory
WHERE InvNo = @InvNo;
Done
prior
inventory - starting checking Sales we
''
re interested
DECLARE curProcess CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
,ref_no
,trans_type
WHERE InvNo = @InvNo
AND date_time >= @dtStart -- now process only the Sales we'
OPEN
curProcess
FETCH
NEXT
@InvNo
,@ref_no
,@fifo_rank
,@quantity
,@unit_cost
,@trans_type
'Last Cost = '
#Sales
'Currently in #Sales'
-- Returns
'Return with quantity = '
(@quantity
Cost =
' + CAST(@LastCost AS VARCHAR(20));
SET @LastCost = 0;
SELECT @LastCost = LastCost.LastCost
FROM dbo.siriusfn_LastCostUpToDate(@department, @category, @item, @invent_id, @date_time) LastCost;
INSERT INTO #Sales (
SELECT @ref_no
TRUNCATE TABLE #Removed;-- done with this table for this iteration
FROM curProcess
INTO @InvNo
END -- while
CLOSE curProcess
DEALLOCATE curProcess
CLOSE curMainProcess
DEALLOCATE curMainProcess
SET @Elapsed = datediff(second, @StartTime, CURRENT_TIMESTAMP);
Finished
the creation
#Sales tables using
' + cast(@Elapsed AS VARCHAR(30)) + '
seconds
';
SELECT S.trans_no
,S.department
,S.category
,S.item
,S.invent_id
,S.locatn_id
,SUM(S.quantity) AS QuantitySold
,CAST(SUM(S.quantity * S.unit_cost) AS MONEY) AS CostOfGoodsSold
FROM #Sales S
GROUP BY S.trans_no
,S.locatn_id;
the final selection
RETURN;
TempDB..#Inventory
',N'
') 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'
' AS [fifo_rank], N'
' AS [InvNo], N'
' AS [department], N'
SUPPLIES
' AS [category], N'
BATT_TEST
' AS [item], N'
0
' AS [invent_id], N'
' AS [trans_type], N'
100.00
' AS [quantity], N'
1.00
' AS [unit_cost] UNION ALL
SELECT N'
133005001
2012-06-29 17:00:13.000
2
-90.00
0.00
2012-06-29 17:26:47.000
3
2.00
135005001
2012-06-29 17:28:19.000
4
10.00
2012-06-27 11:58:26.000
BUG_SPRAY
129005001
2012-06-27 13:02:57.000
-9.00
2012-06-27 13:06:07.000
2.6667
130005001
2012-06-27 13:17:46.000
-7.00
131005001
2012-06-27 13:18:16.000
5
3.00
24
2012-06-27 11:57:17.000
20.00
-10.00
2012-06-27 13:04:29.000
2011-04-03 18:34:44.000
GRANOLABAR
24.00
0.75
11005001
2011-04-07 09:57:51.000
-1.00
33005001
2011-04-07 10:04:39.000
103005001
2011-07-06 17:55:17.000
108005001
2011-07-06 17:55:47.000
115005001
2011-08-01 17:47:11.000
6
41005001
2011-09-04 11:24:03.000
7
-2.00
48005001
2011-09-04 11:38:31.000
8
-3.00
65005001
2011-09-04 11:59:59.000
9
2012-06-26 17:02:19.000
10
5.00
2012-06-26 17:09:46.000
11
0.10
2012-06-26 17:15:05.000
12
0.5469
2012-06-26 17:15:47.000
13
125005001
2012-06-26 18:00:26.000
14
126005001
2012-06-26 18:01:05.000
15
127005001
2012-06-26 18:02:07.000
16
-50.00
128005001
2012-06-26 18:02:51.000
17
30.00
2011-04-03 16:41:21.000
60.00
2011-04-03 17:46:45.000
-24.00
23
2012-06-26 17:00:58.000
2012-06-26 17:04:59.000
-5.00
' 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
DECLARE @Time datetime, @Elapsed int, @dtStart datetime
set @dtStart = '
20120629
SET @time = GETDATE()
EXECUTE dbo.siriussp_CostOfGoodsSold_FIFO_TEST @dtStart = '
20010629
set @Elapsed = DATEDIFF(second,@time, getdate())
print '
Elapsed
SQL 2005-2008: -
version
' + convert(varchar(10),@Elapsed) + '
EXECUTE dbo.siriussp_CostOfGoodsSold_FIFO @dtStart= '
Prior
--EXECUTE dbo.siriussp_CostOfGoodsSold_FIFO_2012 '
--SET @time = GETDATE()
--set @Elapsed = DATEDIFF(second,@time, getdate())
--print '
SQL 2012:
seconds'