[GS知识]渠道报表-处方品种销售数据统计表

本文链接：https://blog.csdn.net/NO66781/article/details/105533882
/*
区域	医院	医院级别	商业公司	2019月均销量	1月	2月	3月	4月	5月	6月	7月	8月	9月	10月	11月	12月	合计
*/
DECLARE @NIAN INT           /*必填：年*/
       ,@GSID VARCHAR(5)    /*必填：公司*/
       ,@WLID VARCHAR(50)   /*必填：物料*/
       ,@DQID VARCHAR(10)   /*选填：省份*/
       ,@SYB  VARCHAR(10)   /*选填：体系(事业部)*/
SET @NIAN =2019
SET @GSID ='01001'
SET @WLID ='01001-JQ000008'
SET @DQID =''
SET @SYB  =''

/*固定列数据*/
/*区域	医院	医院级别	商业公司*/
DECLARE @COLUMN TABLE(GSID VARCHAR(5),DQID VARCHAR(10),KH VARCHAR(50),YY VARCHAR(50),YYJB VARCHAR(50),SYB VARCHAR(50))
IF LTRIM(RTRIM(@DQID)) ='' AND LTRIM(RTRIM(@SYB)) =''
BEGIN
	INSERT INTO @COLUMN (GSID,DQID,KH,YY,YYJB,SYB) 
	SELECT DISTINCT 
		   a.YXLXD_GSID  AS GSID
		  ,ISNULL(d.YXKHZD_DQID,'') AS DQID 
		  ,a.YXLXD_KH AS KH
		  ,b.YXLXDMX_KH AS YY 
		  ,ISNULL(d.YXKHZD_YYJB,'') AS YYJB 
		  ,ISNULL(a.YXLXD_SYBBH,'') AS SYB
	FROM YXLXD AS a 
		 INNER JOIN YXLXDMX AS b ON a.YXLXD_LSBH = b.YXLXDMX_LSBH 
		 INNER JOIN YXKHZD AS c ON a.YXLXD_KH = c.YXKHZD_NM AND a.YXLXD_GSID = c.YXKHZD_GSID AND c.YXKHZD_KHLX IN ('1','6','8','A')
		 INNER JOIN YXKHZD AS d ON b.YXLXDMX_KH  = d.YXKHZD_NM AND a.YXLXD_GSID = d.YXKHZD_GSID AND d.YXKHZD_KHLX ='2'
		 LEFT  JOIN YXDQZD AS e ON d.YXKHZD_DQID = e.YXDQZD_DQBH
	WHERE a.YXLXD_GSID =@GSID                        /*公司*/
		  AND YEAR(b.YXLXDMX_XSRQ) = @NIAN           /*年(流向销售日期)*/
		  AND b.YXLXDMX_PZ = LTRIM(RTRIM(@WLID))     /*物料*/ 
    
END
ELSE
BEGIN
	IF LTRIM(RTRIM(@DQID)) !='' AND LTRIM(RTRIM(@SYB)) =''
	BEGIN
		INSERT INTO @COLUMN (GSID,DQID,KH,YY,YYJB,SYB) 
		SELECT DISTINCT 
			   a.YXLXD_GSID  AS GSID
			  ,ISNULL(d.YXKHZD_DQID,'') AS DQID 
			  ,a.YXLXD_KH AS KH
			  ,b.YXLXDMX_KH AS YY 
			  ,ISNULL(d.YXKHZD_YYJB,'') AS YYJB 
			  ,ISNULL(a.YXLXD_SYBBH,'') AS SYB
		FROM YXLXD AS a 
			 INNER JOIN YXLXDMX AS b ON a.YXLXD_LSBH = b.YXLXDMX_LSBH 
			 INNER JOIN YXKHZD AS c ON a.YXLXD_KH = c.YXKHZD_NM AND a.YXLXD_GSID = c.YXKHZD_GSID AND c.YXKHZD_KHLX IN ('1','6','8','A')
			 INNER JOIN YXKHZD AS d ON b.YXLXDMX_KH  = d.YXKHZD_NM AND a.YXLXD_GSID = d.YXKHZD_GSID AND d.YXKHZD_KHLX ='2'
			 LEFT  JOIN YXDQZD AS e ON d.YXKHZD_DQID = e.YXDQZD_DQBH
		WHERE a.YXLXD_GSID =@GSID                        /*公司*/
			  AND YEAR(b.YXLXDMX_XSRQ) = @NIAN           /*年(流向销售日期)*/
			  AND b.YXLXDMX_PZ = LTRIM(RTRIM(@WLID))     /*物料*/
			  AND ISNULL(d.YXKHZD_DQID,'') = @DQID       /*省份*/ 
	END
	ELSE
	BEGIN
		INSERT INTO @COLUMN (GSID,DQID,KH,YY,YYJB,SYB) 
		SELECT DISTINCT 
			   a.YXLXD_GSID  AS GSID
			  ,ISNULL(d.YXKHZD_DQID,'') AS DQID 
			  ,a.YXLXD_KH AS KH
			  ,b.YXLXDMX_KH AS YY 
			  ,ISNULL(d.YXKHZD_YYJB,'') AS YYJB 
			  ,ISNULL(a.YXLXD_SYBBH,'') AS SYB
		FROM YXLXD AS a 
			 INNER JOIN YXLXDMX AS b ON a.YXLXD_LSBH = b.YXLXDMX_LSBH 
			 INNER JOIN YXKHZD AS c ON a.YXLXD_KH = c.YXKHZD_NM AND a.YXLXD_GSID = c.YXKHZD_GSID AND c.YXKHZD_KHLX IN ('1','6','8','A')
			 INNER JOIN YXKHZD AS d ON b.YXLXDMX_KH  = d.YXKHZD_NM AND a.YXLXD_GSID = d.YXKHZD_GSID AND d.YXKHZD_KHLX ='2'
			 LEFT  JOIN YXDQZD AS e ON d.YXKHZD_DQID = e.YXDQZD_DQBH
		WHERE a.YXLXD_GSID =@GSID                        /*公司*/
			  AND YEAR(b.YXLXDMX_XSRQ) = @NIAN           /*年(流向销售日期)*/
			  AND b.YXLXDMX_PZ = LTRIM(RTRIM(@WLID))     /*物料*/ 
			  AND a.YXLXD_SYBBH = @SYB                   /*体系(事业部)*/
	END 
END  

/*2019月均销量*/

DECLARE @DATA TABLE(GSID VARCHAR(5),DQID VARCHAR(10),KH VARCHAR(50),YY VARCHAR(50),YYJB VARCHAR(50),SYB VARCHAR(50),SL DECIMAL(20,2),MONTH VARCHAR(10))
IF LTRIM(RTRIM(@DQID)) ='' AND LTRIM(RTRIM(@SYB)) =''
BEGIN 
	INSERT INTO @DATA (GSID,DQID,KH,YY,YYJB,SYB,SL,MONTH) 
	/*每月统计*/
	SELECT a.YXLXD_GSID AS GSID
		  ,ISNULL(d.YXKHZD_DQID,'') AS DQID 
		  ,a.YXLXD_KH AS KH
		  ,b.YXLXDMX_KH AS YY 
		  ,ISNULL(d.YXKHZD_YYJB,'') AS YYJB 
		  ,ISNULL(a.YXLXD_SYBBH,'') AS SYB
		  ,SUM(b.YXLXDMX_SL) AS SL
		  ,LTRIM(RTRIM(CAST(MONTH(b.YXLXDMX_XSRQ) AS VARCHAR(2))))+'月' AS MONTH
	FROM YXLXD AS a 
		 INNER JOIN YXLXDMX AS b ON a.YXLXD_LSBH = b.YXLXDMX_LSBH 
		 INNER JOIN YXKHZD AS c ON a.YXLXD_KH = c.YXKHZD_NM AND a.YXLXD_GSID = c.YXKHZD_GSID AND c.YXKHZD_KHLX IN ('1','6','8','A')
		 INNER JOIN YXKHZD AS d ON b.YXLXDMX_KH  = d.YXKHZD_NM AND a.YXLXD_GSID = d.YXKHZD_GSID AND d.YXKHZD_KHLX ='2'
		 LEFT  JOIN YXDQZD AS e ON d.YXKHZD_DQID = e.YXDQZD_DQBH
	WHERE a.YXLXD_GSID =@GSID                        /*公司*/
		  AND YEAR(b.YXLXDMX_XSRQ) = @NIAN           /*年(流向销售日期)*/
		  AND b.YXLXDMX_PZ = LTRIM(RTRIM(@WLID))     /*物料*/ 
	GROUP BY a.YXLXD_GSID,ISNULL(d.YXKHZD_DQID,''),a.YXLXD_KH,b.YXLXDMX_KH,ISNULL(d.YXKHZD_YYJB,''),a.YXLXD_SYBBH,MONTH(b.YXLXDMX_XSRQ)
	UNION ALL
	/*年统计*/
	SELECT a.YXLXD_GSID AS GSID
		  ,ISNULL(d.YXKHZD_DQID,'') AS DQID 
		  ,a.YXLXD_KH AS KH
		  ,b.YXLXDMX_KH AS YY 
		  ,ISNULL(d.YXKHZD_YYJB,'') AS YYJB 
		  ,ISNULL(a.YXLXD_SYBBH,'') AS SYB
		  ,SUM(b.YXLXDMX_SL) AS SL
		  ,'合计' AS MONTH
	FROM YXLXD AS a 
		 INNER JOIN YXLXDMX AS b ON a.YXLXD_LSBH = b.YXLXDMX_LSBH 
		 INNER JOIN YXKHZD AS c ON a.YXLXD_KH = c.YXKHZD_NM AND a.YXLXD_GSID = c.YXKHZD_GSID AND c.YXKHZD_KHLX IN ('1','6','8','A')
		 INNER JOIN YXKHZD AS d ON b.YXLXDMX_KH  = d.YXKHZD_NM AND a.YXLXD_GSID = d.YXKHZD_GSID AND d.YXKHZD_KHLX ='2'
		 LEFT  JOIN YXDQZD AS e ON d.YXKHZD_DQID = e.YXDQZD_DQBH
	WHERE a.YXLXD_GSID =@GSID                        /*公司*/
		  AND YEAR(b.YXLXDMX_XSRQ) = @NIAN           /*流向销售日期*/
		  AND b.YXLXDMX_PZ = LTRIM(RTRIM(@WLID))     /*物料*/ 
	GROUP BY a.YXLXD_GSID,ISNULL(d.YXKHZD_DQID,''),a.YXLXD_KH,b.YXLXDMX_KH,ISNULL(d.YXKHZD_YYJB,''),a.YXLXD_SYBBH 
END
ELSE
BEGIN
	IF LTRIM(RTRIM(@DQID)) !='' AND LTRIM(RTRIM(@SYB)) =''
	BEGIN
		INSERT INTO @DATA (GSID,DQID,KH,YY,YYJB,SYB,SL,MONTH) 
		/*每月统计*/
		SELECT a.YXLXD_GSID AS GSID
			  ,ISNULL(d.YXKHZD_DQID,'') AS DQID 
			  ,a.YXLXD_KH AS KH
			  ,b.YXLXDMX_KH AS YY 
			  ,ISNULL(d.YXKHZD_YYJB,'') AS YYJB 
			  ,ISNULL(a.YXLXD_SYBBH,'') AS SYB
			  ,SUM(b.YXLXDMX_SL) AS SL
			  ,LTRIM(RTRIM(CAST(MONTH(b.YXLXDMX_XSRQ) AS VARCHAR(2))))+'月' AS MONTH
		FROM YXLXD AS a 
			 INNER JOIN YXLXDMX AS b ON a.YXLXD_LSBH = b.YXLXDMX_LSBH 
		     INNER JOIN YXKHZD AS c ON a.YXLXD_KH = c.YXKHZD_NM AND a.YXLXD_GSID = c.YXKHZD_GSID AND c.YXKHZD_KHLX IN ('1','6','8','A')
		     INNER JOIN YXKHZD AS d ON b.YXLXDMX_KH  = d.YXKHZD_NM AND a.YXLXD_GSID = d.YXKHZD_GSID AND d.YXKHZD_KHLX ='2'
			 LEFT  JOIN YXDQZD AS e ON d.YXKHZD_DQID = e.YXDQZD_DQBH
		WHERE a.YXLXD_GSID =@GSID                        /*公司*/
			  AND YEAR(b.YXLXDMX_XSRQ) = @NIAN           /*年(流向销售日期)*/
			  AND b.YXLXDMX_PZ = LTRIM(RTRIM(@WLID))     /*物料*/
			  AND ISNULL(d.YXKHZD_DQID,'') = @DQID       /*省份*/ 
		GROUP BY a.YXLXD_GSID,ISNULL(d.YXKHZD_DQID,''),a.YXLXD_KH,b.YXLXDMX_KH,ISNULL(d.YXKHZD_YYJB,''),a.YXLXD_SYBBH,MONTH(b.YXLXDMX_XSRQ)
		UNION ALL
		/*年统计*/
		SELECT a.YXLXD_GSID AS GSID
			  ,ISNULL(d.YXKHZD_DQID,'') AS DQID 
			  ,a.YXLXD_KH AS KH
			  ,b.YXLXDMX_KH AS YY 
			  ,ISNULL(d.YXKHZD_YYJB,'') AS YYJB 
			  ,ISNULL(a.YXLXD_SYBBH,'') AS SYB
			  ,SUM(b.YXLXDMX_SL) AS SL
			  ,'合计' AS MONTH
		FROM YXLXD AS a 
			 INNER JOIN YXLXDMX AS b ON a.YXLXD_LSBH = b.YXLXDMX_LSBH 
		     INNER JOIN YXKHZD AS c ON a.YXLXD_KH = c.YXKHZD_NM AND a.YXLXD_GSID = c.YXKHZD_GSID AND c.YXKHZD_KHLX IN ('1','6','8','A')
		     INNER JOIN YXKHZD AS d ON b.YXLXDMX_KH  = d.YXKHZD_NM AND a.YXLXD_GSID = d.YXKHZD_GSID AND d.YXKHZD_KHLX ='2'
			 LEFT  JOIN YXDQZD AS e ON d.YXKHZD_DQID = e.YXDQZD_DQBH
		WHERE a.YXLXD_GSID =@GSID                        /*公司*/
			  AND YEAR(b.YXLXDMX_XSRQ) = @NIAN           /*流向销售日期*/
			  AND b.YXLXDMX_PZ = LTRIM(RTRIM(@WLID))     /*物料*/
			  AND ISNULL(d.YXKHZD_DQID,'') = @DQID       /*省份*/ 
		GROUP BY a.YXLXD_GSID,ISNULL(d.YXKHZD_DQID,''),a.YXLXD_KH,b.YXLXDMX_KH,ISNULL(d.YXKHZD_YYJB,''),a.YXLXD_SYBBH 
	END
	ELSE
	BEGIN
		INSERT INTO @DATA (GSID,DQID,KH,YY,YYJB,SYB,SL,MONTH) 
		/*每月统计*/
		SELECT a.YXLXD_GSID AS GSID
			  ,ISNULL(d.YXKHZD_DQID,'') AS DQID 
			  ,a.YXLXD_KH AS KH
			  ,b.YXLXDMX_KH AS YY 
			  ,ISNULL(d.YXKHZD_YYJB,'') AS YYJB 
			  ,ISNULL(a.YXLXD_SYBBH,'') AS SYB
			  ,SUM(b.YXLXDMX_SL) AS SL
			  ,LTRIM(RTRIM(CAST(MONTH(b.YXLXDMX_XSRQ) AS VARCHAR(2))))+'月' AS MONTH
		FROM YXLXD AS a 
			 INNER JOIN YXLXDMX AS b ON a.YXLXD_LSBH = b.YXLXDMX_LSBH 
		     INNER JOIN YXKHZD AS c ON a.YXLXD_KH = c.YXKHZD_NM AND a.YXLXD_GSID = c.YXKHZD_GSID AND c.YXKHZD_KHLX IN ('1','6','8','A')
		     INNER JOIN YXKHZD AS d ON b.YXLXDMX_KH  = d.YXKHZD_NM AND a.YXLXD_GSID = d.YXKHZD_GSID AND d.YXKHZD_KHLX ='2'
			 LEFT  JOIN YXDQZD AS e ON d.YXKHZD_DQID = e.YXDQZD_DQBH
		WHERE a.YXLXD_GSID =@GSID                        /*公司*/
			  AND YEAR(b.YXLXDMX_XSRQ) = @NIAN           /*年(流向销售日期)*/
			  AND b.YXLXDMX_PZ = LTRIM(RTRIM(@WLID))     /*物料*/ 
			  AND a.YXLXD_SYBBH = @SYB                   /*体系(事业部)*/
		GROUP BY a.YXLXD_GSID,ISNULL(d.YXKHZD_DQID,''),a.YXLXD_KH,b.YXLXDMX_KH,ISNULL(d.YXKHZD_YYJB,''),a.YXLXD_SYBBH,MONTH(b.YXLXDMX_XSRQ)
		UNION ALL
		/*年统计*/
		SELECT a.YXLXD_GSID AS GSID
			  ,ISNULL(d.YXKHZD_DQID,'') AS DQID 
			  ,a.YXLXD_KH AS KH
			  ,b.YXLXDMX_KH AS YY 
			  ,ISNULL(d.YXKHZD_YYJB,'') AS YYJB 
			  ,ISNULL(a.YXLXD_SYBBH,'') AS SYB
			  ,SUM(b.YXLXDMX_SL) AS SL
			  ,'合计' AS MONTH
		FROM YXLXD AS a 
			 INNER JOIN YXLXDMX AS b ON a.YXLXD_LSBH = b.YXLXDMX_LSBH 
		     INNER JOIN YXKHZD AS c ON a.YXLXD_KH = c.YXKHZD_NM AND a.YXLXD_GSID = c.YXKHZD_GSID AND c.YXKHZD_KHLX IN ('1','6','8','A')
		     INNER JOIN YXKHZD AS d ON b.YXLXDMX_KH  = d.YXKHZD_NM AND a.YXLXD_GSID = d.YXKHZD_GSID AND d.YXKHZD_KHLX ='2'
			 LEFT  JOIN YXDQZD AS e ON d.YXKHZD_DQID = e.YXDQZD_DQBH
		WHERE a.YXLXD_GSID =@GSID                        /*公司*/
			  AND YEAR(b.YXLXDMX_XSRQ) = @NIAN           /*流向销售日期*/
			  AND b.YXLXDMX_PZ = LTRIM(RTRIM(@WLID))     /*物料*/ 
			  AND a.YXLXD_SYBBH = @SYB                   /*体系(事业部)*/
		GROUP BY a.YXLXD_GSID,ISNULL(d.YXKHZD_DQID,''),a.YXLXD_KH,b.YXLXDMX_KH,ISNULL(d.YXKHZD_YYJB,''),a.YXLXD_SYBBH 
	END
END
 
/*整理报表行转列*/
DECLARE @BBDATA TABLE(GSID VARCHAR(5),DQID VARCHAR(10),KH VARCHAR(50),YY VARCHAR(50),YYJB VARCHAR(50),SYB VARCHAR(50)
                     ,Y01 DECIMAL(20,2) NULL DEFAULT 0.00
                     ,Y02 DECIMAL(20,2) NULL DEFAULT 0.00 
                     ,Y03 DECIMAL(20,2) NULL DEFAULT 0.00 
                     ,Y04 DECIMAL(20,2) NULL DEFAULT 0.00 
                     ,Y05 DECIMAL(20,2) NULL DEFAULT 0.00 
                     ,Y06 DECIMAL(20,2) NULL DEFAULT 0.00 
                     ,Y07 DECIMAL(20,2) NULL DEFAULT 0.00 
                     ,Y08 DECIMAL(20,2) NULL DEFAULT 0.00 
                     ,Y09 DECIMAL(20,2) NULL DEFAULT 0.00 
                     ,Y10 DECIMAL(20,2) NULL DEFAULT 0.00 
                     ,Y11 DECIMAL(20,2) NULL DEFAULT 0.00 
                     ,Y12 DECIMAL(20,2) NULL DEFAULT 0.00 
                     ,Y13 DECIMAL(20,2) NULL DEFAULT 0.00  
                      )
INSERT INTO @BBDATA (GSID,DQID,KH,YY,YYJB,SYB,Y01,Y02,Y03,Y04,Y05,Y06,Y07,Y08,Y09,Y10,Y11,Y12,Y13) 
SELECT *
FROM @DATA AS c
PIVOT(SUM(c.SL) FOR MONTH IN ([1月],[2月],[3月],[4月],[5月],[6月],[7月],[8月],[9月],[10月],[11月],[12月],[合计]) ) AS SL

/*报表展现*/
SELECT a.GSID,a.DQID,a.KH,a.YY,a.SYB
      ,ISNULL(e.YXDQZD_DQMC,'') AS DQMC
      ,c.YXKHZD_KHMC AS YYMCH
      ,a.YYJB
      ,b.YXKHZD_KHMC AS KHMCH
      ,ISNULL(d.Y01,0.00) AS Y01 
      ,ISNULL(d.Y02,0.00) AS Y02 
      ,ISNULL(d.Y03,0.00) AS Y03 
      ,ISNULL(d.Y04,0.00) AS Y04 
      ,ISNULL(d.Y05,0.00) AS Y05 
      ,ISNULL(d.Y06,0.00) AS Y06 
      ,ISNULL(d.Y07,0.00) AS Y07 
      ,ISNULL(d.Y08,0.00) AS Y08 
      ,ISNULL(d.Y09,0.00) AS Y09 
      ,ISNULL(d.Y10,0.00) AS Y10 
      ,ISNULL(d.Y11,0.00) AS Y11 
      ,ISNULL(d.Y12,0.00) AS Y12 
      ,ISNULL(d.Y13,0.00) AS Y13 
FROM @COLUMN AS a
	 INNER JOIN YXKHZD AS b ON a.KH = b.YXKHZD_NM AND a.GSID = b.YXKHZD_GSID AND b.YXKHZD_KHLX IN ('1','6','8','A')
	 INNER JOIN YXKHZD AS c ON a.YY  = c.YXKHZD_NM AND a.GSID = b.YXKHZD_GSID AND c.YXKHZD_KHLX ='2'
     LEFT JOIN @BBDATA AS d ON a.GSID = d.GSID AND a.DQID = d.DQID AND a.KH = d.KH AND a.YY = d.YY AND a.YYJB = d.YYJB AND a.SYB = d.SYB
     LEFT  JOIN YXDQZD AS e ON d.DQID = e.YXDQZD_DQBH
ORDER BY a.GSID,a.DQID,a.YY,a.KH

--SELECT * FROM @COLUMN