计算商品不同批次成本问题

Question

商品基本表
商品编码      商品名称       供应商编码     基准进价
0001        心相印纸巾     0010         13.6
0002        烟灰缸         0011         6.8
0003        一次性纸杯     0015         5.1进货表
商品编码      进货价       进货数量      进货日期
0001         13.5       100         2008-5-1 10:05:01
0002         6.9        80          2008-5-1 10:05:01
0001         13.7       150         2008-5-15 16:01:02
0001         13.8       120         2008-5-25 14:15:05销售表
商品编码      销售单价     销售数量     成本     销售日期
0001        17.5        90         1215    2008-5-2 11:01:05
0001        17.9        20          ?      2008-5-17 15:46:13"?"处应该是10*13.5+10*13.7=272
请问用T—SQL怎么计算出销售商品的实际成本？

Answer 1

--> --> (Roy)生成測試數據
 
if not object_id('进货表') is null
drop table 进货表
Go
Create table 进货表([商品编码] nvarchar(4),[进货价] decimal(18,1),[进货数量] int,[进货日期] Datetime)
Insert 进货表
select N'0001',13.5,100,'2008-5-1 10:05:01' union all
select N'0002',6.9,80,'2008-5-1 10:05:01' union all
select N'0001',13.7,150,'2008-5-15 16:01:02' union all
select N'0001',13.8,120,'2008-5-25 14:15:05'
Go
--> --> (Roy)生成測試數據
 
if not object_id('销售表') is null
drop table 销售表
Go
Create table 销售表([商品编码] nvarchar(4),[销售单价] decimal(18,1),[销售数量] int,[成本] nvarchar(1),[销售日期] Datetime)
Insert 销售表
select N'0001',17.5,90,N'?','2008-5-2 11:01:05' union all
select N'0001',17.9,20,N'?','2008-5-17 15:46:13'
Go;with Purchase
as
(select t1.[商品编码],t1.[进货价],t1.[进货数量],t1.[进货日期],sum(t2.[进货数量]) as [Sum_进货] from 进货表 t1 join 进货表 t2 on t1.[商品编码]=t2.[商品编码] and t1.[进货日期]>=t2.[进货日期] group by t1.[商品编码],t1.[进货价],t1.[进货数量],t1.[进货日期])
,Sales
as
(select t1.[商品编码],t1.[销售单价],t1.[销售数量],t1.[成本],t1.[销售日期],sum(t2.[销售数量]) as [Sum_销售] from 销售表 t1 join 销售表 t2 on t1.[商品编码]=t2.[商品编码] and t1.[销售日期]>=t2.[销售日期] group by t1.[商品编码],t1.[销售单价],t1.[销售数量],t1.[成本],t1.[销售日期])
select 
s.[商品编码],s.[销售单价],s.[销售数量],
[成本]=sum(p.[进货价]*
case when p.[Sum_进货]>s.[Sum_销售] then s.[Sum_销售] else p.[Sum_进货] end 
-case when s.[Sum_销售]-s.[销售数量]>p.[Sum_进货]-p.[进货数量] then s.[Sum_销售]-s.[销售数量] else p.[Sum_进货]-p.[进货数量] end),
s.[销售日期] 
from 
Purchase p
join
Sales s on p.[商品编码]=s.[商品编码]
where
p.[Sum_进货]>s.[Sum_销售]-s.[销售数量] and s.[Sum_销售]>p.[Sum_进货]-p.[进货数量]
group by s.[商品编码],s.[销售单价],s.[销售数量],s.[销售日期](2 行受影响)
商品编码 销售单价                                    销售数量        成本                                      销售日期
---- --------------------------------------- ----------- --------------------------------------- -----------------------
0001 17.5                                    90          1215.0                                  2008-05-02 11:01:05.000
0001 17.9                                    20          2667.0                                  2008-05-17 15:46:13.000(2 行受影响)

Answer 2

少了括号;加上
--> --> (Roy)生成測試數據
 
if not object_id('进货表') is null
drop table 进货表
Go
Create table 进货表([商品编码] nvarchar(4),[进货价] decimal(18,1),[进货数量] int,[进货日期] Datetime)
Insert 进货表
select N'0001',13.5,100,'2008-5-1 10:05:01' union all
select N'0002',6.9,80,'2008-5-1 10:05:01' union all
select N'0001',13.7,150,'2008-5-15 16:01:02' union all
select N'0001',13.8,120,'2008-5-25 14:15:05'
Go
--> --> (Roy)生成測試數據
 
if not object_id('销售表') is null
drop table 销售表
Go
Create table 销售表([商品编码] nvarchar(4),[销售单价] decimal(18,1),[销售数量] int,[成本] nvarchar(1),[销售日期] Datetime)
Insert 销售表
select N'0001',17.5,90,N'?','2008-5-2 11:01:05' union all
select N'0001',17.9,20,N'?','2008-5-17 15:46:13'
Go;with Purchase
as
(select t1.[商品编码],t1.[进货价],t1.[进货数量],t1.[进货日期],sum(t2.[进货数量]) as [Sum_进货] from 进货表 t1 join 进货表 t2 on t1.[商品编码]=t2.[商品编码] and t1.[进货日期]>=t2.[进货日期] group by t1.[商品编码],t1.[进货价],t1.[进货数量],t1.[进货日期])
,Sales
as
(select t1.[商品编码],t1.[销售单价],t1.[销售数量],t1.[成本],t1.[销售日期],sum(t2.[销售数量]) as [Sum_销售] from 销售表 t1 join 销售表 t2 on t1.[商品编码]=t2.[商品编码] and t1.[销售日期]>=t2.[销售日期] group by t1.[商品编码],t1.[销售单价],t1.[销售数量],t1.[成本],t1.[销售日期])
select 
s.[商品编码],s.[销售单价],s.[销售数量],
[成本]=sum(p.[进货价]*
(case when p.[Sum_进货]>s.[Sum_销售] then s.[Sum_销售] else p.[Sum_进货] end 
-case when s.[Sum_销售]-s.[销售数量]>p.[Sum_进货]-p.[进货数量] then s.[Sum_销售]-s.[销售数量] else p.[Sum_进货]-p.[进货数量] end)),
s.[销售日期] 
from 
Purchase p
join
Sales s on p.[商品编码]=s.[商品编码]
where
p.[Sum_进货]>s.[Sum_销售]-s.[销售数量] and s.[Sum_销售]>p.[Sum_进货]-p.[进货数量]
group by s.[商品编码],s.[销售单价],s.[销售数量],s.[销售日期]
(4 行受影响)(2 行受影响)
商品编码 销售单价                                    销售数量        成本                                      销售日期
---- --------------------------------------- ----------- --------------------------------------- -----------------------
0001 17.5                                    90          1215.0                                  2008-05-02 11:01:05.000
0001 17.9                                    20          272.0                                   2008-05-17 15:46:13.000(2 行受影响)

Answer 3

2000时改为嵌套select 
    s.[商品编码],s.[销售单价],s.[销售数量],
    [成本]=sum(p.[进货价]*
        (case when p.[Sum_进货]>s.[Sum_销售] then s.[Sum_销售] else p.[Sum_进货] end 
        -case when s.[Sum_销售]-s.[销售数量]>p.[Sum_进货]-p.[进货数量] then s.[Sum_销售]-s.[销售数量] else p.[Sum_进货]-p.[进货数量] end)),
    s.[销售日期] from 
(select t1.[商品编码],t1.[进货价],t1.[进货数量],t1.[进货日期],sum(t2.[进货数量]) as [Sum_进货] from 进货表 t1 join 进货表 t2 on t1.[商品编码]=t2.[商品编码] and t1.[进货日期]>=t2.[进货日期] group by t1.[商品编码],t1.[进货价],t1.[进货数量],t1.[进货日期])p
join 
(select t1.[商品编码],t1.[销售单价],t1.[销售数量],t1.[成本],t1.[销售日期],sum(t2.[销售数量]) as [Sum_销售] from 销售表 t1 join 销售表 t2 on t1.[商品编码]=t2.[商品编码] and t1.[销售日期]>=t2.[销售日期] group by t1.[商品编码],t1.[销售单价],t1.[销售数量],t1.[成本],t1.[销售日期])s
on  p.[商品编码]=s.[商品编码]
where 
    p.[Sum_进货]>s.[Sum_销售]-s.[销售数量] and s.[Sum_销售]>p.[Sum_进货]-p.[进货数量]
group by s.[商品编码],s.[销售单价],s.[销售数量],s.[销售日期]

Answer 4

如果是先进先出的话,应该是100*13.5的先出完,下一个销售的成本就是150*13.7的了.恩，楼主给的公式是正确的先进先出的算法.
楼上是高手,要我想的话,可能得需要临时表或者游标.
理解ING...........