易飞自定义报(采购进货数量月统计表)之动态SQL进阶

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_MYJHSLTJ]
@DT1 CHAR(8),
@DT2 CHAR(8)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT TH004'
SELECT @SQL=@SQL+',SUM(CASE TG014 WHEN'''+TG014+''' THEN TH007 ELSE 0 END) AS ['+TG014+']'
FROM (SELECT DISTINCT SUBSTRING(TG014,1,6) AS TG014
FROM PURTG LEFT JOIN PURTH ON TH001=TG001 AND TH002=TG002 WHERE (TG013 = 'Y') AND TG014>=@DT1 AND TG014<=@DT2
GROUP BY TG014,TH004 ) AS A ORDER BY TG014
SELECT @SQL=@SQL+' FROM (
SELECT SUBSTRING(TG014,1,6)TG014,TH004,TH007 FROM (
SELECT TG014,TH004,TH007 FROM PURTH LEFT JOIN PURTG ON TG001=TH001 AND TG002=TH002 
WHERE TG014>='''+@DT1+'''AND TG014<='''+@DT2+''' AND TG013 = ''Y''
) A ) B GROUP BY TH004 ORDER BY TH004'
EXEC (@SQL)
END
GO
EXEC [dbo].[SP_MYJHSLTJ] '20160601','20160831'
GO

📱 扫码关注公众号

扫描二维码关注我们，获取更多精彩内容
实时更新 · 干货满满