日期:2014-05-17 浏览次数:20476 次
if not object_id(N'[入库表]') is null
drop table [入库表]
go
CREATE TABLE [入库表](产品代码 NVARCHAR(20),产品名称 NVARCHAR(20),产品型号 NVARCHAR(20), 入库数量 INT,入库单价 MONEY,入库时间 DATETIME)
INSERT 入库表
select N'001',N'名称1',N'型号1',20,25,'2011-10-9' union all
select N'001',N'名称1',N'型号1',25,20,'2011-10-10' union all
select N'001',N'名称1',N'型号1',22,23.5,'2011-11-11' UNION ALL
select N'001',N'名称1',N'型号1',100,50,'2011-11-12'
GO
if not object_id(N'出库表') is null
drop table 出库表
Go
Create table 出库表([产品代码] nvarchar(3),[产品名称] nvarchar(3),[产品型号] nvarchar(3),[出库数量] int,[出库时间] Datetime)
Insert 出库表
select N'001',N'名称1',N'型号1',55,'2011-11-13'
Go
SELECT
[产品代码],
[产品名称],
[出库时间],
SUM([数量]) AS [先出数量],
CAST( SUM([数量]*入库单价)/SUM([数量]) AS decimal(18,2) )AS [先出单价],
SUM([数量]*入库单价) AS 先出金额,
SUM(入库数量) AS 结存数量 ,
CAST(SUM(入库数量*入库单价)/SUM(入库数量)AS decimal(18,2) ) AS 结存单价,
SUM(入库数量*入库单价) AS 结存金额
FROM
(
SELECT
a.[产品代码],
a.[产品名称],
b.[出库时间],
[数量]=CASE WHEN a.sumQty-a.入库数量 <b.sumQty then case when b.sumQty <a.sumQty then b.sumQty else a.sumQty END -
case when b.sumQty-b.[出库数量] <a.sumQty-a.入库数量 then a.sumQty-a.入库数量 else b.sumQty-b.[出库数量] END
ELSE 0 end
,a.入库单价,
CASE WHEN (a.sumQty-a.入库数量) >b.sumQty then a.入库数量 WHEN a.sumQty>b.sumQty THEN a.sumQty-b.sumQty ELSE 0 END AS 入库数量
FROM (SELECT *,(SELECT sum(入库数量) FROM 入库表 WHERE 产品代码=a.产品代码 AND 入库时间<=a.入库时间 ) sumQty FROM 入库表 AS a)a
left JOIN
(SELECT *,(SELECT sum([出库数量]) FROM 出库表 WHERE 产品代码=a.产品代码 AND [出库时间]<=a.[出库时间] ) sumQty FROM 出库表 AS a)b ON a.产品代码=b.产品代码
WHERE
b.sumQty-b.[出库数量] <a.sumQty
)T
GROUP BY
[产品代码],
[产品名称],
[出库时间]
/*
产品代码 产品名称 出库时间 先出数量 先出单价 先出金额 结存数量 结存单价 结存金额
001 名称1 2011-11-13 00:00:00.000 55 22.45 1235.00 112 47.16 5282.00
*/
--先创建视图,这个的好处是能够把复杂的sql语句化为视图,直接访问视图
CREATE V