calculate closing Stock Quantity,price & value by

问题:

i am using sql server 2008.

i am calculating envetory as FIFO method like in tally.

please go through

http://accountingexplained.com/financial/inventories/fifo-method

i have table as

create table #Stock ( StoreId int, ProductId int, Transection_Date Date, transectionType varchar(20), Unit numeric(18,2), UnitCost numeric(18,2) )

insert into #Stock  select 1,201, '2014-03-01', 'Beginning Inventory', 68, 14
insert into #Stock  select 1,201,'2014-03-05', 'Purchase', 140, 15.50
insert into #Stock  select 1,201,'2014-03-09', 'Issue', 94, 0
insert into #Stock  select 1,201,'2014-03-11', 'Purchase', 40, 16
insert into #Stock  select 1,201,'2014-03-16', 'Purchase', 68, 14
insert into #Stock  select 1,201,'2014-03-20', 'Issue', 116, 0 
insert into #Stock  select 1,201,'2014-03-29', 'Issue', 62, 0

select * from #Stock drop table #Stock

i am looking sql function or store procedure which will accept parameter fromDate,toDate and sorteId

and display as given format

回答1:

How about:

--DROP TABLE #Stock
create table #Stock 
( 
Transection_Date Date,
transectionType varchar(20),
Unit numeric(18,2),
UnitCost numeric(18,2)
)

--Mar 1 Beginning Inventory 68 units @ $15.00 per unit 
--5 Purchase 140 units @ $15.50 per unit 
--9 Sale 94 units @ $19.00 per unit 
--11 Purchase 40 units @ $16.00 per unit 
--16 Purchase 78 units @ $16.50 per unit 
--20 Sale 116 units @ $19.50 per unit 
--29 Sale 62 units @ $21.00 per unit 

insert into #Stock  select '2014-03-01', 'Beginning Inventory', 68, 15
insert into #Stock  select '2014-03-05', 'Purchase', 140, 15.50
insert into #Stock  select '2014-03-09', 'Sale', 94, 19
insert into #Stock  select '2014-03-11', 'Purchase', 40, 16
insert into #Stock  select '2014-03-16', 'Purchase', 78, 16.5
insert into #Stock  select '2014-03-20', 'Sale', 116, 19.50 
insert into #Stock  select '2014-03-29', 'Sale', 62, 21.00

;WITH UnitsCTE
AS
(
-- GET Total Units Left
    SELECT SUM(
            CASE transectionType 
            WHEN 'Purchase' Then Unit 
            When 'Sale' THEN Unit * -1 
            ELSE Unit END) AS Units
    FROM #Stock
), PurchaseCTE
AS
(
-- Get only purchases in reverse order
    SELECT Unit, UnitCost, Transection_Date, ROW_NUMBER() OVER (ORDER BY Transection_Date DESC ) AS RN
    FROM #Stock
    WHERE transectionType <> 'Sale'
),
UnitCost
AS
(
-- Recursive CTE to get number of units left at each price
    SELECT CASE WHEN Unit > UnitsCTE.Units THEN UnitsCTE.Units ELSE Unit END As Units, UnitCost
    FROM PurchaseCTE 
    CROSS APPLY UnitsCTE
    WHERE RN = 1
    UNION ALL
    SELECT CASE WHEN P1.Unit > (UnitsCTE.Units - (SELECT SUM(Unit) FROM PurchaseCTE P3 WHERE p3.RN < p1.RN))
            THEN    CASE WHEN (UnitsCTE.Units - (SELECT SUM(Unit) FROM PurchaseCTE P3 WHERE p3.RN < p1.RN)) < 0 THEN 0
                        ELSE (UnitsCTE.Units - (SELECT SUM(Unit) FROM PurchaseCTE P3 WHERE p3.RN < p1.RN)) END
            ELSE P1.Unit END,
            P1.UnitCost 
    FROM PurchaseCTE P1
    INNER JOIN PurchaseCTE P2
        ON P1.RN = P2.RN + 1
    CROSS APPLY UnitsCTE
)
SELECT SUM(Units), SUM(UnitCost * Units) / SUM(Units) AS UnitCost, SUM(Units * UnitCost) AS TotalCost
FROM UnitCost
WHERE Units > 0