I have a discussion with an accountant guy about his logic about the inventory on FIFO basis.
Actually, I already solve this with the use of temporary table and derived tables.
But it is not acceptable for me to have 214 lines to solve the problem. Can you show me you're approach?


select '4718460001111' itemid, 5 period, 155 qty_in, 54 qty_out, -58 logic,   0 rem union all
select '4718460001111' itemid, 4 period, 345 qty_in, 61	qty_out, 287 logic, 287 rem union all
select '4718460001111' itemid, 3 period, 215 qty_in, 18	qty_out, 215 logic, 215 rem union all
select '4718460001111' itemid, 2 period, 185 qty_in, 30	qty_out, 185 logic, 185 rem union all
select '4718460001111' itemid, 1 period, 531 qty_in, 50	qty_out, 531 logic, 531 rem union all
select '5011321835337' itemid, 5 period, 150 qty_in, 44	qty_out, -96 logic,  -0 rem union all
select '5011321835337' itemid, 4 period, 354 qty_in, 66	qty_out, 258 logic, 258 rem union all
select '5011321835337' itemid, 3 period, 488 qty_in, 32	qty_out, 488 logic, 488 rem union all
select '5011321835337' itemid, 2 period, 254 qty_in, 46	qty_out, 254 logic, 254 rem union all
select '5011321835337' itemid, 1 period, 418 qty_in, 58	qty_out, 418 logic, 418 rem

the rows are arranged by itemid and period descending. 

for the logic column:
	1. the last period (5th) is calculated by qty_in less with the sum of qty_out per itemid.
	2. for periods 4,3,2 and 1 are calculated by getting the previous logic column value.
	   4 will get from 5, 3 will get from 4 and so on. It will check if the previous 
	   logic column value is negative. If so, then the previous value will be added to current
	   row's qty_in. Now if the previous logic column is positive, just display the row's qty_in.

for the rem column:
	the result is base on the logic column. If it is negative, then the value is zero else 
	retain the logic column value.

CREATE TABLE [Fifo] (
	[itemid] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[period] [int] NOT NULL ,
	[qty_in] [int] NOT NULL ,
	[qty_out] [int] NOT NULL 
) ON [PRIMARY]
GO


insert into Fifo
select '4718460001111' itemid, 5 period, 155 qty_in, 54 qty_out union all
select '4718460001111' itemid, 4 period, 345 qty_in, 61	qty_out union all
select '4718460001111' itemid, 3 period, 215 qty_in, 18	qty_out union all
select '4718460001111' itemid, 2 period, 185 qty_in, 30	qty_out union all
select '4718460001111' itemid, 1 period, 531 qty_in, 50	qty_out union all
select '5011321835337' itemid, 5 period, 150 qty_in, 44	qty_out union all
select '5011321835337' itemid, 4 period, 354 qty_in, 66	qty_out union all
select '5011321835337' itemid, 3 period, 488 qty_in, 32	qty_out union all
select '5011321835337' itemid, 2 period, 254 qty_in, 46	qty_out union all
select '5011321835337' itemid, 1 period, 418 qty_in, 58	qty_out

select * from #Fifo

drop table #Fifo



The result i want is...
[code]
select '4718460001111' itemid, 5 period, 155 qty_in, 54 qty_out, -58 logic,   0 rem union all
select '4718460001111' itemid, 4 period, 345 qty_in, 61	qty_out, 287 logic, 287 rem union all
select '4718460001111' itemid, 3 period, 215 qty_in, 18	qty_out, 215 logic, 215 rem union all
select '4718460001111' itemid, 2 period, 185 qty_in, 30	qty_out, 185 logic, 185 rem union all
select '4718460001111' itemid, 1 period, 531 qty_in, 50	qty_out, 531 logic, 531 rem union all
select '5011321835337' itemid, 5 period, 150 qty_in, 44	qty_out, -96 logic,  -0 rem union all
select '5011321835337' itemid, 4 period, 354 qty_in, 66	qty_out, 258 logic, 258 rem union all
select '5011321835337' itemid, 3 period, 488 qty_in, 32	qty_out, 488 logic, 488 rem union all
select '5011321835337' itemid, 2 period, 254 qty_in, 46	qty_out, 254 logic, 254 rem union all
select '5011321835337' itemid, 1 period, 418 qty_in, 58	qty_out, 418 logic, 418 rem

CREATE TABLE  #Fifo  (
[itemid] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[period] [int] NOT NULL ,
[qty_in] [int] NOT NULL ,
[qty_out] [int] NOT NULL ,
[logic] [int] not null
) ON [PRIMARY]
GO

create clustered index idx1 on #Fifo (itemid asc, period desc)

truncate table #fifo
insert into #Fifo
select '4718460001111' itemid, 5 period, 155 qty_in, 54 qty_out,0  union all
select '4718460001111' itemid, 4 period, 345 qty_in, 61 qty_out,0   union all
select '4718460001111' itemid, 3 period, 215 qty_in, 18 qty_out,0   union all
select '4718460001111' itemid, 2 period, 185 qty_in, 30 qty_out,0   union all
select '4718460001111' itemid, 1 period, 531 qty_in, 50 qty_out,0   union all
select '5011321835337' itemid, 5 period, 150 qty_in, 44 qty_out,0   union all
select '5011321835337' itemid, 4 period, 354 qty_in, 66 qty_out,0   union all
select '5011321835337' itemid, 3 period, 488 qty_in, 32 qty_out,0   union all
select '5011321835337' itemid, 2 period, 254 qty_in, 46 qty_out,0   union all
select '5011321835337' itemid, 1 period, 418 qty_in, 58 qty_out,0   
-----------------------------------------------------------------------------------------------------
select '4718460001111' itemid, 5 period, 155 qty_in, 54 qty_out, -58 logic,   0 rem union all
select '4718460001111' itemid, 4 period, 345 qty_in, 61	qty_out, 287 logic, 287 rem union all
select '4718460001111' itemid, 3 period, 215 qty_in, 18	qty_out, 215 logic, 215 rem union all
select '4718460001111' itemid, 2 period, 185 qty_in, 30	qty_out, 185 logic, 185 rem union all
select '4718460001111' itemid, 1 period, 531 qty_in, 50	qty_out, 531 logic, 531 rem union all
select '5011321835337' itemid, 5 period, 150 qty_in, 44	qty_out, -96 logic,  -0 rem union all
select '5011321835337' itemid, 4 period, 354 qty_in, 66	qty_out, 258 logic, 258 rem union all
select '5011321835337' itemid, 3 period, 488 qty_in, 32	qty_out, 488 logic, 488 rem union all
select '5011321835337' itemid, 2 period, 254 qty_in, 46	qty_out, 254 logic, 254 rem union all
select '5011321835337' itemid, 1 period, 418 qty_in, 58	qty_out, 418 logic, 418 rem

-----------------------------------------------------------------------------------------------------

UPDATE f
SET logic = f.qty_in - t.qty_out
FROM #Fifo f
INNER JOIN
   (select itemid,sum(qty_out)as qty_out from #fifo group by itemid) t
ON
     f.itemid = t.itemid
WHERE f.period = 5
------------------------------------------------------------------------------------------------

UPDATE fifo
SET logic = logic.NewLogic  

FROM #fifo fifo
INNER JOIN
(
SELECT ff.*,CASE WHEN f.logic < 0 then f.logic + ff.qty_in else ff.qty_in end as NewLogic  
FROM
     #fifo f
LEFT JOIN
     #fifo ff
ON
    f.itemid = ff.itemid
and f.period  - 1 = ff.period
) logic

 ON
    fifo.itemid = logic.itemid
and fifo.period = logic.period
WHERE fifo.period < 5

select * from #fifo