关于生产日期早的优先出货的算法

Question

declare @t table(CT datetime,NUM int)
insert into @t values('2008-01-01',15) 
insert into @t values('2008-05-10',25) declare @NUM int
set @NUM=20select
    CT  生产日期,
    (case when NUM+B_NUM>=@NUM and B_NUM<=@NUM then NUM+B_NUM-@NUM when NUM+B_NUM<=@NUM then 0 else NUM end) 库存数量,
    NUM-(case when NUM+B_NUM>=@NUM and B_NUM<=@NUM then NUM+B_NUM-@NUM when NUM+B_NUM<=@NUM then 0 else NUM end) 出货数量
from
    (select  
         CT,NUM,isnull((select sum(NUM) from @t where CT<t.CT),0) B_NUM
     from @t t) a/*
生产日期                                                   库存数量        出货数量        
------------------------------------------------------ ----------- ----------- 
2008-01-01 00:00:00.000                                0           15
2008-05-10 00:00:00.000                                20          5
*/

Answer 1

按日期先后顺序依次出货：
declare @t table(CT datetime,NUM int)
insert into @t values('2008-01-01',15) 
insert into @t values('2008-05-10',25) declare @NUM int
set @NUM=20select
    CT  生产日期,
    NUM 初期库存,
    (case when NUM+B_NUM>=@NUM and B_NUM<=@NUM then NUM+B_NUM-@NUM when NUM+B_NUM<=@NUM then 0 else NUM end) 库存数量,
    NUM-(case when NUM+B_NUM>=@NUM and B_NUM<=@NUM then NUM+B_NUM-@NUM when NUM+B_NUM<=@NUM then 0 else NUM end) 出货数量
from
    (select  
         CT,NUM,isnull((select sum(NUM) from @t where CT<t.CT),0) B_NUM
     from @t t) a/*
生产日期                        初期库存    库存数量     出货数量        
------------------------------ ----------- ----------- ----------- 
2008-01-01 00:00:00.000              15           0         15
2008-05-10 00:00:00.000              25          20          5
*/

Answer 2

DECLARE @TB TABLE(COL SMALLDATETIME, COL2 INT)
INSERT @TB
SELECT '2008-1-1',  5 UNION ALL 
SELECT '2008-5-10',  25SELECT COL,COL2,CASE WHEN COL2<20 THEN COL2 ELSE  20-TOTAL  END AS COL3
FROM (
SELECT *,TOTAL=ISNULL((SELECT SUM(COL2) FROM @TB WHERE COL<A.COL),0)
FROM @TB AS A
) T
/*
COL                                                    COL2        COL3        
------------------------------------------------------ ----------- ----------- 
2008-01-01 00:00:00                                    5           5
2008-05-10 00:00:00                                    25          15
*/

Answer 3

DECLARE @TB TABLE(COL SMALLDATETIME, COL2 INT)
INSERT @TB
SELECT '2008-1-1',  5 UNION ALL 
SELECT '2008-5-10',  25
DECLARE @OUT INT
SET @OUT=20SELECT COL,COL2,CASE WHEN TOTAL>=@OUT THEN 0 ELSE  CASE WHEN TOTAL2<@OUT THEN COL2 ELSE @OUT-TOTAL  END END AS COL3
FROM (
SELECT *,TOTAL=ISNULL((SELECT SUM(COL2) FROM @TB WHERE COL<A.COL),0)
,TOTAL2=ISNULL((SELECT SUM(COL2) FROM @TB WHERE COL<=A.COL),0)
FROM @TB AS A
) T
/*
COL                                                    COL2        COL3        
------------------------------------------------------ ----------- ----------- 
2008-01-01 00:00:00                                    5           5
2008-05-10 00:00:00                                    25          15
*/