34,587
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
go
if not object_id('进货表') is null
drop table 进货表
Go
Create table 进货表([商品编码] nvarchar(4), [仓库名字] nvarchar(4),[进货价] decimal(18,1),[进货数量] int,[进货日期] Datetime)
Insert 进货表
select N'0001','aaa',10,20,'2008-5-1 10:05:01' union all
select N'0001','aaa',5,50,'2008-5-2 16:01:02' union all
select N'0002','aaa',6,80,'2008-5-1 10:05:01'
Go
if not object_id('销售表') is null
drop table 销售表
Go
Create table 销售表([商品编码] nvarchar(4),[仓库名字] nvarchar(4),[销售数量] int,[销售日期] Datetime)
Insert 销售表
select N'0001','aaa',10,'2008-5-3 11:01:05' union all
select N'0002','aaa',40,'2008-5-3 15:46:13'
Go
select ta.[商品编码],ta.[仓库名字],
ta.[商品编码],
[数量]=
sum(case when tb.出货sum <ta.进货sum-ta.[进货数量] then ta.[进货数量] else ta.进货sum-tb.出货sum END)
,
[库存成本]=sum(case when tb.出货sum <ta.进货sum-ta.[进货数量] then ta.[进货数量] else ta.进货sum-tb.出货sum END*ta.[进货价])
from
(select *
,进货sum=(select sum([进货数量]) from 进货表 where [商品编码]=a.[商品编码] AND [仓库名字]=a.[仓库名字] and [进货日期]!> a.[进货日期])
from 进货表 a
)ta
join
(select [仓库名字],[商品编码],SUM([销售数量]) AS [出货sum] from 销售表 GROUP BY [仓库名字],[商品编码] ) tb
on ta.[商品编码]=tb.[商品编码] and ta.[仓库名字]=tb.[仓库名字] AND tb.出货sum <ta.进货sum
group by ta.[商品编码],ta.[仓库名字]
/*
商品编码 仓库名字 商品编码 数量 库存成本
0001 aaa 0001 60 350.0
0002 aaa 0002 40 240.0
*/
;with Purchase
as
(select t1.[仓库名字],t1.[商品编码],
sum(t1.[进货数量]) as [Sum_进货]
from 进货表 t1
group by t1.[仓库名字],t1.[商品编码]
)
,Sales
as
(select t1.[仓库名字],t1.[商品编码],
sum(t1.[销售数量]) as [Sum_销售]
from 销售表 t1
group by t1.[仓库名字],t1.[商品编码]
)
select Purchase.[仓库名字],
Purchase.[商品编码],
[Sum_进货]-[Sum_销售] as [库存数量]
from Purchase
inner join Sales on Purchase.[仓库名字] = Sales.[仓库名字]
and Purchase.[商品编码] = Sales.[商品编码]