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

[zhanghelpsgz]

商品基本表
商品编码 商品名称 供应商编码 基准进价
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怎么计算出销售商品的实际成本？

[ccq17]

就看你用什么方法算成本了，是先进先出还是用移动加权平均

[zhanghelpsgz]

先进先出

[ws_hgo]

我看下

[中国风]

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.[销售日期]

[xuexi456]

为什么会是？

"?"处应该是10*13.5+10*13.7=272

你的销售表是不是有问题？

[Andy-W]

路过！！！
帮up

[中国风]

先进先出

[中国风]

--> --> (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 行受影响)

[中国风]

少了括号;加上

--> --> (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 行受影响)

[netcup]

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

[CN_SQL]

没太明白，可能你需要解释清楚。
1：你是修改表的数据，还是生成一个结果集？
2：你的业务规则需要多加描述，才能明白。

[fcuandy]

看不懂。

[zhanghelpsgz]

修改表数据