效果图:
存储过程:
CREATE PROCEDURE [dbo].[P_Quck_MonthUse]
@opedate NVARCHAR(30)
AS
DECLARE
@staredate1 datetime,
@enddate1 DATETIME,
@staredate2 datetime,
@enddate2 DATETIME,
@staredate3 datetime,
@enddate3 DATETIME,
@staredate4 datetime,
@enddate4 DATETIME,
@staredate5 datetime,
@enddate5 DATETIME,
@staredate6 datetime,
@enddate6 DATETIME,
@staredate7 datetime,
@enddate7 DATETIME,
@staredate8 datetime,
@enddate8 DATETIME,
@staredate9 datetime,
@enddate9 DATETIME,
@staredate10 datetime,
@enddate10 DATETIME,
@staredate11 datetime,
@enddate11 DATETIME,
@staredate12 datetime,
@enddate12 DATETIME
set @staredate1=@opedate+'-01-01' --获取一个日期月开始
set @enddate1=convert(varchar(20),dateadd(d,-day(@opedate+'-01-01'),dateadd(m,1,@opedate+'-01-01')),23) --获取一个日期月结束
set @staredate2=@opedate+'-02-01' --获取一个日期月开始
set @enddate2=convert(varchar(20),dateadd(d,-day(@opedate+'-02-01'),dateadd(m,1,@opedate+'-02-01')),23) --获取一个日期月结束
set @staredate3=@opedate+'-03-01' --获取一个日期月开始
set @enddate3=convert(varchar(20),dateadd(d,-day(@opedate+'-03-01'),dateadd(m,1,@opedate+'-03-01')),23) --获取一个日期月结束
set @staredate4=@opedate+'-04-01' --获取一个日期月开始
set @enddate4=convert(varchar(20),dateadd(d,-day(@opedate+'-04-01'),dateadd(m,1,@opedate+'-04-01')),23) --获取一个日期月结束
set @staredate5=@opedate+'-05-01' --获取一个日期月开始
set @enddate5=convert(varchar(20),dateadd(d,-day(@opedate+'-05-01'),dateadd(m,1,@opedate+'-05-01')),23) --获取一个日期月结束
set @staredate6=@opedate+'-06-01' --获取一个日期月开始
set @enddate6=convert(varchar(20),dateadd(d,-day(@opedate+'-06-01'),dateadd(m,1,@opedate+'-06-01')),23) --获取一个日期月结束
set @staredate7=@opedate+'-07-01' --获取一个日期月开始
set @enddate7=convert(varchar(20),dateadd(d,-day(@opedate+'-07-01'),dateadd(m,1,@opedate+'-07-01')),23) --获取一个日期月结束
set @staredate8=@opedate+'-08-01' --获取一个日期月开始
set @enddate8=convert(varchar(20),dateadd(d,-day(@opedate+'-08-01'),dateadd(m,1,@opedate+'-08-01')),23) --获取一个日期月结束
set @staredate9=@opedate+'-09-01' --获取一个日期月开始
set @enddate9=convert(varchar(20),dateadd(d,-day(@opedate+'-09-01'),dateadd(m,1,@opedate+'-09-01')),23) --获取一个日期月结束
set @staredate10=@opedate+'-10-01' --获取一个日期月开始
set @enddate10=convert(varchar(20),dateadd(d,-day(@opedate+'-10-01'),dateadd(m,1,@opedate+'-10-01')),23) --获取一个日期月结束
set @staredate11=@opedate+'-11-01' --获取一个日期月开始
set @enddate11=convert(varchar(20),dateadd(d,-day(@opedate+'-11-01'),dateadd(m,1,@opedate+'-11-01')),23) --获取一个日期月结束
set @staredate12=@opedate+'-12-01' --获取一个日期月开始
set @enddate12=convert(varchar(20),dateadd(d,-day(@opedate+'-12-01'),dateadd(m,1,@opedate+'-12-01')),23) --获取一个日期月结束
--SELECT @staredate1,@enddate1,@staredate2,@enddate2,@staredate3,@enddate3,@staredate4,@enddate4,@staredate5,@enddate5,@staredate6,@enddate6,@staredate7,@enddate7,@staredate8,@enddate8,@staredate9,@enddate9
--,@staredate10,@enddate10,@staredate11,@enddate11,@staredate12,@enddate12
SELECT
DISTINCT a.存货编码,a.存货名称,a.规格型号,a.仓库,SUM(a.一月份) AS 一月,
SUM(a.二月份) AS 二月,SUM(a.三月份) AS 三月,SUM(a.四月份) 四月,SUM(a.五月份) AS 五月,SUM(a.六月份) AS 六月,SUM(a.七月份) AS 七月,SUM(a.八月份) AS 八月,
SUM(a.九月份) AS 九月,SUM(a.十月份) AS 十月,SUM(a.十一月份) AS 十一月,SUM(a.十二月份) AS 十二月
FROM
(
SELECT
--distinct
rdrecords11.cInvCode 存货编码,
Inventory.cInvName 存货名称,
Inventory.cInvStd 规格型号,
CASE WHEN CONVERT(VARCHAR(10), rdrecord11.cWhCode)IN ('50','51','52','60','69')
THEN '总部仓库' ELSE N'绿控仓库' END 仓库,
CASE WHEN rdrecord11.dnverifytime >=@staredate1 AND rdrecord11.dnverifytime<=@enddate1 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 一月份,
CASE WHEN rdrecord11.dnverifytime >=@staredate2 AND rdrecord11.dnverifytime<=@enddate2 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 二月份,
CASE WHEN rdrecord11.dnverifytime >=@staredate3 AND rdrecord11.dnverifytime<=@enddate3 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 三月份,
CASE WHEN rdrecord11.dnverifytime >=@staredate4 AND rdrecord11.dnverifytime<=@enddate4 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 四月份,
CASE WHEN rdrecord11.dnverifytime >=@staredate5 AND rdrecord11.dnverifytime<=@enddate5 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 五月份,
CASE WHEN rdrecord11.dnverifytime >=@staredate6 AND rdrecord11.dnverifytime<=@enddate6 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 六月份,
CASE WHEN rdrecord11.dnverifytime >=@staredate7 AND rdrecord11.dnverifytime<=@enddate7 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 七月份,
CASE WHEN rdrecord11.dnverifytime >=@staredate8 AND rdrecord11.dnverifytime<=@enddate8 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 八月份,
CASE WHEN rdrecord11.dnverifytime >=@staredate9 AND rdrecord11.dnverifytime<=@enddate9 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 九月份,
CASE WHEN rdrecord11.dnverifytime >=@staredate10 AND rdrecord11.dnverifytime<=@enddate10 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 十月份,
CASE WHEN rdrecord11.dnverifytime >=@staredate11 AND rdrecord11.dnverifytime<=@enddate11 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 十一月份,
CASE WHEN rdrecord11.dnverifytime >=@staredate12 AND rdrecord11.dnverifytime<=@enddate12 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 十二月份
FROM rdrecord11
LEFT JOIN rdrecords11 ON rdrecord11.ID=rdrecords11.ID
LEFT JOIN Inventory ON Inventory.cinvcode=rdrecords11.cInvCode
WHERE rdrecords11.cInvCode IS NOT NULL
AND rdrecord11.dnverifytime>=@staredate1
AND rdrecord11.cWhCode IN ('36','37','38','63','50','51','52','60','69')
AND ISNULL(rdrecords11.iQuantity,0)<>0 AND rdrecord11.cRdCode IN ('201','203')
GROUP BY rdrecords11.cInvCode ,Inventory.cInvName,Inventory.cInvStd,rdrecord11.dnverifytime,rdrecord11.cWhCode
UNION ALL
SELECT
--distinct
RdRecords09.cinvcode 存货编码,
Inventory.cInvName 存货名称,
Inventory.cInvStd 规格型号,
CASE WHEN CONVERT(VARCHAR(10), RdRecord09.cWhCode)IN ('50','51','52','60','69')
THEN '总部仓库' ELSE N'绿控仓库' END 仓库,
CASE WHEN RdRecord09.dVeriDate >=@staredate1 AND RdRecord09.dVeriDate<=@enddate1 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 一月份,
CASE WHEN RdRecord09.dVeriDate >=@staredate2 AND RdRecord09.dVeriDate<=@enddate2 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 二月份,
CASE WHEN RdRecord09.dVeriDate >=@staredate3 AND RdRecord09.dVeriDate<=@enddate3 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 三月份,
CASE WHEN RdRecord09.dVeriDate >=@staredate4 AND RdRecord09.dVeriDate<=@enddate4 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 四月份,
CASE WHEN RdRecord09.dVeriDate >=@staredate5 AND RdRecord09.dVeriDate<=@enddate5 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 五月份,
CASE WHEN RdRecord09.dVeriDate >=@staredate6 AND RdRecord09.dVeriDate<=@enddate6 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 六月份,
CASE WHEN RdRecord09.dVeriDate >=@staredate7 AND RdRecord09.dVeriDate<=@enddate7 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 七月份,
CASE WHEN RdRecord09.dVeriDate >=@staredate8 AND RdRecord09.dVeriDate<=@enddate8 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 八月份,
CASE WHEN RdRecord09.dVeriDate >=@staredate9 AND RdRecord09.dVeriDate<=@enddate9 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 九月份,
CASE WHEN RdRecord09.dVeriDate >=@staredate10 AND RdRecord09.dVeriDate<=@enddate10 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 十月份,
CASE WHEN RdRecord09.dVeriDate >=@staredate11 AND RdRecord09.dVeriDate<=@enddate11 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 十一月份,
CASE WHEN RdRecord09.dVeriDate >=@staredate12 AND RdRecord09.dVeriDate<=@enddate12 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 十二月份
FROM RdRecord09
LEFT JOIN RdRecords09 ON RdRecord09.id=RdRecords09.ID
LEFT JOIN Inventory ON Inventory.cinvcode=RdRecords09.cInvCode
WHERE ISNULL(RdRecords09.iQuantity,0) <>0
AND RdRecord09.dVeriDate>=@staredate1
AND rdrecord09.cWhCode IN ('36','37','38','63','50','51','52','60','69')
AND RdRecord09.cRdCode IN ('201','202','203','207','209','210','212')
GROUP BY RdRecords09.cinvcode,Inventory.cInvName,Inventory.cInvStd,RdRecord09.dVeriDate,RdRecord09.cWhCode
UNION ALL
SELECT
--distinct
rdrecords32.cInvCode 存货编码,
Inventory.cInvName 存货名称,
Inventory.cInvStd 规格型号,
CASE WHEN CONVERT(VARCHAR(10), rdrecord32.cWhCode) IN ('50','51','52','60','69')
THEN '总部仓库' ELSE N'绿控仓库' END 仓库,
CASE WHEN rdrecord32.dVeriDate >=@staredate1 AND rdrecord32.dVeriDate<=@enddate1 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 一月份,
CASE WHEN rdrecord32.dVeriDate >=@staredate2 AND rdrecord32.dVeriDate<=@enddate2 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 二月份,
CASE WHEN rdrecord32.dVeriDate >=@staredate3 AND rdrecord32.dVeriDate<=@enddate3 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 三月份,
CASE WHEN rdrecord32.dVeriDate >=@staredate4 AND rdrecord32.dVeriDate<=@enddate4 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 四月份,
CASE WHEN rdrecord32.dVeriDate >=@staredate5 AND rdrecord32.dVeriDate<=@enddate5 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 五月份,
CASE WHEN rdrecord32.dVeriDate >=@staredate6 AND rdrecord32.dVeriDate<=@enddate6 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 六月份,
CASE WHEN rdrecord32.dVeriDate >=@staredate7 AND rdrecord32.dVeriDate<=@enddate7 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 七月份,
CASE WHEN rdrecord32.dVeriDate >=@staredate8 AND rdrecord32.dVeriDate<=@enddate8 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 八月份,
CASE WHEN rdrecord32.dVeriDate >=@staredate9 AND rdrecord32.dVeriDate<=@enddate9 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 九月份,
CASE WHEN rdrecord32.dVeriDate >=@staredate10 AND rdrecord32.dVeriDate<=@enddate10 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 十月份,
CASE WHEN rdrecord32.dVeriDate >=@staredate11 AND rdrecord32.dVeriDate<=@enddate11 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 十一月份,
CASE WHEN rdrecord32.dVeriDate >=@staredate12 AND rdrecord32.dVeriDate<=@enddate12 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 十二月份
FROM rdrecord32
LEFT JOIN rdrecords32 ON rdrecord32.ID=rdrecords32.ID
LEFT JOIN Inventory ON Inventory.cinvcode=rdrecords32.cInvCode
WHERE rdrecord32.dVeriDate >=@staredate1
AND ISNULL(rdrecords32.iQuantity,0)<>0
AND rdrecord32.cRdCode ='202'
AND rdrecord32.cWhCode IN ('36','37','38','63','50','51','52','60','69')
AND rdrecords32.cInvCode IS NOT NULL
AND rdrecords32.cInvCode NOT LIKE 'A%'
GROUP BY rdrecords32.cInvCode ,Inventory.cInvName,Inventory.cInvStd,rdrecord32.dVeriDate,rdrecord32.cWhCode
) a --where 1=1
GROUP BY a.存货编码,a.存货名称,a.规格型号,a.仓库
-----改进写法
ALTER PROCEDURE [dbo].[P_Quck_MonthUse]
@opedate NVARCHAR(30)
AS
SELECT
DISTINCT a.存货编码,a.存货名称,a.规格型号,a.仓库,SUM(a.一月份) AS 一月,
SUM(a.二月份) AS 二月,SUM(a.三月份) AS 三月,SUM(a.四月份) 四月,SUM(a.五月份) AS 五月,SUM(a.六月份) AS 六月,SUM(a.七月份) AS 七月,SUM(a.八月份) AS 八月,
SUM(a.九月份) AS 九月,SUM(a.十月份) AS 十月,SUM(a.十一月份) AS 十一月,SUM(a.十二月份) AS 十二月
FROM
(
SELECT
rdrecords11.cInvCode 存货编码,
Inventory.cInvName 存货名称,
Inventory.cInvStd 规格型号,
CASE WHEN CONVERT(VARCHAR(10), rdrecord11.cWhCode)IN ('18','36','37','38','63','50','51','52','60','69')
THEN N'总部仓库' ELSE '' END 仓库,
CASE WHEN MONTH(rdrecord11.dnverifytime)=1 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 一月份,
CASE WHEN MONTH(rdrecord11.dnverifytime)=2 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 二月份,
CASE WHEN MONTH(rdrecord11.dnverifytime)=3 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 三月份,
CASE WHEN MONTH(rdrecord11.dnverifytime)=4 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 四月份,
CASE WHEN MONTH(rdrecord11.dnverifytime)=5 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 五月份,
CASE WHEN MONTH(rdrecord11.dnverifytime)=6 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 六月份,
CASE WHEN MONTH(rdrecord11.dnverifytime)=7 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 七月份,
CASE WHEN MONTH(rdrecord11.dnverifytime)=8 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 八月份,
CASE WHEN MONTH(rdrecord11.dnverifytime)=9 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 九月份,
CASE WHEN MONTH(rdrecord11.dnverifytime)=10 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 十月份,
CASE WHEN MONTH(rdrecord11.dnverifytime)=11 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 十一月份,
CASE WHEN MONTH(rdrecord11.dnverifytime)=12 THEN
SUM (ISNULL(rdrecords11.iQuantity,0)) ELSE 0 END 十二月份
FROM rdrecord11
LEFT JOIN rdrecords11 ON rdrecord11.ID=rdrecords11.ID
LEFT JOIN Inventory ON Inventory.cinvcode=rdrecords11.cInvCode
WHERE rdrecords11.cInvCode IS NOT NULL
AND YEAR(rdrecord11.dnverifytime)=@opedate
AND rdrecord11.cWhCode IN ('18','36','37','38','63','50','51','52','60','69')
AND ISNULL(rdrecords11.iQuantity,0)<>0 AND rdrecord11.cRdCode IN ('201','203')
GROUP BY rdrecords11.cInvCode ,Inventory.cInvName,Inventory.cInvStd,rdrecord11.dnverifytime,rdrecord11.cWhCode
UNION ALL
SELECT
RdRecords09.cinvcode 存货编码,
Inventory.cInvName 存货名称,
Inventory.cInvStd 规格型号,
CASE WHEN CONVERT(VARCHAR(10), RdRecord09.cWhCode)IN ('18','36','37','38','63','50','51','52','60','69','73')
THEN N'总部仓库' ELSE '' END 仓库,
CASE WHEN MONTH(RdRecord09.dVeriDate)=1 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 一月份,
CASE WHEN MONTH(RdRecord09.dVeriDate)=2 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 二月份,
CASE WHEN MONTH(RdRecord09.dVeriDate)=3 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 三月份,
CASE WHEN MONTH(RdRecord09.dVeriDate)=4 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 四月份,
CASE WHEN MONTH(RdRecord09.dVeriDate)=5 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 五月份,
CASE WHEN MONTH(RdRecord09.dVeriDate)=6 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 六月份,
CASE WHEN MONTH(RdRecord09.dVeriDate)=7 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 七月份,
CASE WHEN MONTH(RdRecord09.dVeriDate)=8 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 八月份,
CASE WHEN MONTH(RdRecord09.dVeriDate)=9 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 九月份,
CASE WHEN MONTH(RdRecord09.dVeriDate)=10 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 十月份,
CASE WHEN MONTH(RdRecord09.dVeriDate)=11 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 十一月份,
CASE WHEN MONTH(RdRecord09.dVeriDate)=12 THEN
SUM (ISNULL(RdRecords09.iQuantity,0)) ELSE 0 END 十二月份
FROM RdRecord09
LEFT JOIN RdRecords09 ON RdRecord09.id=RdRecords09.ID
LEFT JOIN Inventory ON Inventory.cinvcode=RdRecords09.cInvCode
WHERE ISNULL(RdRecords09.iQuantity,0) <>0
AND YEAR(RdRecord09.dVeriDate)=@opedate
AND rdrecord09.cWhCode IN ('18','36','37','38','63','50','51','52','60','69','73')
AND RdRecord09.cRdCode IN ('201','202','203','207','209','210','212')
GROUP BY RdRecords09.cinvcode,Inventory.cInvName,Inventory.cInvStd,RdRecord09.dVeriDate,RdRecord09.cWhCode
UNION ALL
SELECT
--distinct
rdrecords32.cInvCode 存货编码,
Inventory.cInvName 存货名称,
Inventory.cInvStd 规格型号,
CASE WHEN CONVERT(VARCHAR(10), rdrecord32.cWhCode) IN ('36','37','38','63','50','51','52','60','69')
THEN N'总部仓库' ELSE N'' END 仓库,
CASE WHEN MONTH(rdrecord32.dVeriDate)=1 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 一月份,
CASE WHEN MONTH(rdrecord32.dVeriDate)=2 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 二月份,
CASE WHEN MONTH(rdrecord32.dVeriDate)=3 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 三月份,
CASE WHEN MONTH(rdrecord32.dVeriDate)=4 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 四月份,
CASE WHEN MONTH(rdrecord32.dVeriDate)=5 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 五月份,
CASE WHEN MONTH(rdrecord32.dVeriDate)=6 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 六月份,
CASE WHEN MONTH(rdrecord32.dVeriDate)=7 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 七月份,
CASE WHEN MONTH(rdrecord32.dVeriDate)=8 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 八月份,
CASE WHEN MONTH(rdrecord32.dVeriDate)=9 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 九月份,
CASE WHEN MONTH(rdrecord32.dVeriDate)=10 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 十月份,
CASE WHEN MONTH(rdrecord32.dVeriDate)=11 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 十一月份,
CASE WHEN MONTH(rdrecord32.dVeriDate)=12 THEN
SUM (ISNULL(rdrecords32.iQuantity,0)) ELSE 0 END 十二月份
FROM rdrecord32
LEFT JOIN rdrecords32 ON rdrecord32.ID=rdrecords32.ID
LEFT JOIN Inventory ON Inventory.cinvcode=rdrecords32.cInvCode
WHERE YEAR(rdrecord32.dVeriDate)=@opedate
AND ISNULL(rdrecords32.iQuantity,0)<>0
AND rdrecord32.cRdCode ='202'
AND rdrecord32.cWhCode IN ('36','37','38','63','50','51','52','60','69')
AND rdrecords32.cInvCode IS NOT NULL
AND rdrecords32.cInvCode NOT LIKE 'A%'
GROUP BY rdrecords32.cInvCode ,Inventory.cInvName,Inventory.cInvStd,rdrecord32.dVeriDate,rdrecord32.cWhCode
) a
GROUP BY a.存货编码,a.存货名称,a.规格型号,a.仓库