有一张仓库每月明细表WHTAB，结构是ID 序号,CODE 物品代码,QUALITY 数量,STOCK 当前库存,DATE 操作日期
ID CODE QUALITY STOCK DATE
1 A 100 1000 2014-09-29
2 A -100 900 2014-10-01
3 A 1000 1900 2014-10-25
4 B 500 500 2014-10-25
5 B -300 200 2014-10-30
用SQL查询想得到的结果是
CODE 上月库存 本月入库 本月消耗 本月库存 月份
A 1000 1000 -100 1900 2014-10
B 0 500 -300 200 2014-10
请问怎样才能实现
------解决思路----------------------
#2对当月没记录的物品就不统计，比如下面例子中的 C。
还是先取全部物品然后再 OUTER APPLY 吧。
WITH WHTAB(ID,CODE,QUALITY,STOCK,DATE) AS (
SELECT 1,'A', 100,1000,Convert(datetime,'2014-09-29',120) UNION ALL
SELECT 2,'A',-100, 900,Convert(datetime,'2014-10-01',120) UNION ALL
SELECT 3,'A',1000,1900,Convert(datetime,'2014-10-25',120) UNION ALL
SELECT 4,'B', 500, 500,Convert(datetime,'2014-10-25',120) UNION ALL
SELECT 5,'B',-300, 200,Convert(datetime,'2014-10-30',120) UNION ALL
SELECT 6,'C', 400, 400,Convert(datetime,'2014-08-30',120)
)
,m AS (
SELECT DISTINCT
CODE,
Convert(datetime,Convert(varchar(7),GetDate(),120)+'-01',120) ThisMonth,
Convert(datetime,Convert(varchar(7),DateAdd(month,1,GetDate()),120)+'-01',120) NextMonth
FROM WHTAB
)
SELECT m.CODE,
ISNULL(c1.STOCK,0) 上月库存,
ISNULL(c2.QUALITY,0) 本月入库,
ISNULL(c3.QUALITY,0) 本月消耗,
ISNULL(c4.STOCK,0) 本月库存,
Convert(varchar(7),ThisMonth,120) 月份
FROM m
OUTER APPLY ( SELECT TOP 1 STOCK
FROM WHTAB
WHERE CODE = m.CODE
AND DATE < m.ThisMonth
ORDER BY DATE DESC
) c1
OUTER APPLY ( SELECT SUM(QUALITY) QUALITY
FROM WHTAB
WHERE CODE = m.CODE
AND DATE >= m.ThisMonth
AND DATE < m.NextMonth
AND QUALITY > 0
) c2
OUTER APPLY ( SELECT SUM(QUALITY) QUALITY
FROM WHTAB
WHERE CODE = m.CODE
AND DATE >= m.ThisMonth
AND DATE < m.NextMonth
AND QUALITY < 0
) c3
OUTER APPLY ( SELECT TOP 1 STOCK
FROM WHTAB
WHERE CODE = m.CODE
AND DATE < m.NextMonth
ORDER BY DATE DESC
) c4
CODE 上月库存 本月入库 本月消耗 本月库存 月份
---- ----------- ----------- ----------- ----------- -------
A 1000 1000 -100 1900 2014-10
B 0 500 -300 200 2014-10
C 400 0 0 400 2014-10