数据多次分摊方案一

最新推荐文章于 2020-11-21 00:47:33 发布

NO66781

最新推荐文章于 2020-11-21 00:47:33 发布

阅读量325

点赞数

分类专栏：浪潮GS

本文链接：https://blog.csdn.net/NO66781/article/details/109840391

版权

浪潮GS 专栏收录该内容

9 篇文章 3 订阅

订阅专栏

数据源1：2020年1月份厂家销往1级经销商的销售记录

数据源2：厂家采集到2020年1月1级经销商的销售流向

需求：

1）识别采集到的经销商流向的SL（数量）是否有效；

2）由于不采集经销商流向的售价，故需要将销售记录进行分摊；

3）厂家2020年1月份的销售记录，含上月结转1级经销商的库存，以及本月的销售记录，分摊顺序为先“分上月结转，再分本月销售”，可能会存在一条记录多次分摊的问题；

解决方案：

DECLARE @GSID VARCHAR(5)
SET @GSID ='01001'

/*数据据1*/
CREATE TABLE #THZ(
    ID INT NOT NULL IDENTITY(1,1)
   ,GSID  VARCHAR(5)  NULL DEFAULT ''
   ,KH  VARCHAR(40) NULL DEFAULT ''
   ,WL  VARCHAR(40) NULL DEFAULT ''
   ,PH  VARCHAR(40) NULL DEFAULT ''
   ,DJ  DECIMAL(20,8) NULL DEFAULT 0.00000000
   ,QCSL DECIMAL(14,2) NULL DEFAULT 0.00 
   ,SYSL DECIMAL(14,2) NULL DEFAULT 0.00 
   ,FTWC VARCHAR(1) NULL DEFAULT 'N'   /*默认为N，已分摊但未完成为Y，已分摊完成为W*/
)
INSERT INTO #THZ (GSID,KH,WL,PH,DJ,QCSL)
SELECT '01001','01001-JZT166','01001-JQ000015','190220',1.00000000,10.00  UNION ALL 
SELECT '01001','01001-JZT166','01001-JQ000005','190220',2.00000000,2.00  UNION ALL 
SELECT '01001','01001-JZT166','01001-JQ000005','190220',3.00000000,300.00  UNION ALL 
SELECT '01001','01001-JZT166','01001-JQ000005','190220',3.10000000,12.00 UNION ALL 
SELECT '01001','01001-JZT166','01001-JQ000005','190220',3.20000000,12.00 UNION ALL 
SELECT '01001','01001-JZT166','01001-JQ000005','190220',3.30000000,1.00  UNION ALL 
SELECT '01001','01001-JZT166','01001-JQ000005','190220',3.40000000,2.00  UNION ALL 
SELECT '01001','01001-JZT167','01001-JQ000005','190220',3.50000000,3.00  

/*数据源2*/
CREATE TABLE #LX_JH(
    ID INT NOT NULL IDENTITY(1,1)
   ,GSID  VARCHAR(5)  NULL DEFAULT ''
   ,NY VARCHAR(6) NULL DEFAULT ''
   ,LSBH  VARCHAR(40) NULL DEFAULT ''
   ,FLBH  VARCHAR(40) NULL DEFAULT ''
   ,YWRQ  VARCHAR(40) NULL DEFAULT ''
   ,KH  VARCHAR(40) NULL DEFAULT ''
   ,WL  VARCHAR(40) NULL DEFAULT ''
   ,PH VARCHAR(40) NULL DEFAULT ''
   ,SL DECIMAL(14,2) NULL DEFAULT 0.00
   ,YE DECIMAL(14,2) NULL DEFAULT 0.00
)
INSERT INTO #LX_JH (GSID,NY,LSBH,FLBH,YWRQ,KH,WL,PH,SL)
SELECT '01001','202001','No0001','0001','20200116','01001-JZT166','01001-JQ000005','190220',3000.00 UNION ALL 
SELECT '01001','202001','No0002','0001','20200107','01001-JZT166','01001-JQ000005','190220',20.00 UNION ALL 
SELECT '01001','202001','No0003','0001','20200107','01001-JZT166','01001-JQ000005','190220',3.00 UNION ALL 
SELECT '01001','202001','No0004','0001','20200106','01001-JZT166','01001-JQ000005','190220',20.00 UNION ALL 
SELECT '01001','202001','No0005','0001','20200116','01001-JZT167','01001-JQ000005','190220',8.00 UNION ALL 
SELECT '01001','202001','No0006','0001','20200107','01001-JZT167','01001-JQ000005','190220',20.00 UNION ALL 
SELECT '01001','202001','No0007','0001','20200107','01001-JZT167','01001-JQ000005','190220',3.00 UNION ALL 
SELECT '01001','202001','No0008','0001','20200106','01001-JZT167','01001-JQ000005','190220',5.00 

/*分摊结果存储表*/
CREATE TABLE #T(
    ID INT NOT NULL 
   ,GSID  VARCHAR(5)  NULL DEFAULT ''
   ,KH  VARCHAR(40) NULL DEFAULT ''
   ,WL  VARCHAR(40) NULL DEFAULT ''
   ,PH  VARCHAR(40) NULL DEFAULT ''
   ,DJ  DECIMAL(20,8) NULL DEFAULT 0.00000000
   ,QCSL DECIMAL(14,2) NULL DEFAULT 0.00 
   ,JHSL DECIMAL(14,2) NULL DEFAULT 0.00
   ,XGID INT NOT NULL 
   ,NY VARCHAR(6) NULL DEFAULT ''
   ,LSBH  VARCHAR(40) NULL DEFAULT ''
   ,FLBH  VARCHAR(40) NULL DEFAULT ''
   ,YWRQ  VARCHAR(40) NULL DEFAULT ''
   ,FTSL DECIMAL(14,2) NULL DEFAULT 0.00	
)

/*分摊过程中使用的临时表*/
CREATE TABLE #LX_JHFT(
    ID INT NOT NULL 
   ,XGID INT NOT NULL 
   ,GSID  VARCHAR(5)  NULL DEFAULT ''
   ,NY VARCHAR(6) NULL DEFAULT ''
   ,LSBH  VARCHAR(40) NULL DEFAULT ''
   ,FLBH  VARCHAR(40) NULL DEFAULT ''
   ,YWRQ  VARCHAR(40) NULL DEFAULT ''
   ,KH  VARCHAR(40) NULL DEFAULT ''
   ,WL  VARCHAR(40) NULL DEFAULT ''
   ,PH VARCHAR(40) NULL DEFAULT ''
   ,SL DECIMAL(14,2) NULL DEFAULT 0.00
   ,FTSL DECIMAL(14,2) NULL DEFAULT 0.00
   ,YE DECIMAL(14,2) NULL DEFAULT 0.00
)

/*定义分摊主变量*/
DECLARE @FT_KH VARCHAR(40)
       ,@FT_WL VARCHAR(40)
       ,@FT_PH VARCHAR(40)
       ,@FT_DJ DECIMAL(20,8)
       ,@FT_QCSL DECIMAL(14,2)
       ,@FT_SYSL DECIMAL(14,2)
       ,@FT_FTWC VARCHAR(1) 
       ,@FT_ID INT
       ,@FT_COUNT INT
/*主循环变量赋值与取值*/
SET @FT_ID = 1
SELECT @FT_COUNT = COUNT(*) FROM #THZ
--SET @FT_COUNT = 1
/*主循环*/
WHILE @FT_ID <= @FT_COUNT 
BEGIN
	/*分摊变量值初始*/
	SET @FT_KH =''
	SET @FT_WL =''
	SET @FT_PH =''
	SET @FT_DJ =0
	SET @FT_QCSL = 0
	SET @FT_SYSL = 0
	SET @FT_FTWC = 'N' 
	/*取数据源1*/
	SELECT @FT_KH = KH,@FT_WL = WL,@FT_PH = PH,@FT_DJ = DJ,@FT_QCSL = QCSL - SYSL,@FT_SYSL = SYSL,@FT_FTWC = FTWC
	FROM #THZ
	WHERE GSID = @GSID AND ID = @FT_ID AND QCSL - SYSL >= 0 AND FTWC <>'W' /*未分摊完成*/
	
	--SELECT @FT_QCSL  
	
	IF @FT_QCSL > 0 
       AND (SELECT COUNT(*) FROM #LX_JH WHERE GSID = @GSID AND KH = @FT_KH AND WL = @FT_WL AND PH = @FT_PH) > 0
	BEGIN 
        /*取数据源2，来分摊*/ 
		INSERT INTO #LX_JHFT (ID,XGID,GSID,NY,LSBH,FLBH,YWRQ,KH,WL,PH,SL,YE)
		SELECT ROW_NUMBER()OVER(ORDER BY YWRQ ASC,LSBH ASC),ID,GSID,NY,LSBH,FLBH,YWRQ,KH,WL,PH,SL - YE,0 AS YE
		FROM #LX_JH
		WHERE GSID = @GSID 
			  AND KH = @FT_KH AND WL = @FT_WL AND PH = @FT_PH
			  AND SL - YE > 0
		ORDER BY YWRQ ASC,LSBH ASC 
		
		DECLARE @LX_ID INT,@LX_COUNT INT,@SL INT,@XGID INT,@YE INT
		SET @LX_ID = 1
		SELECT @LX_COUNT = COUNT(*) FROM #LX_JHFT

		/*子循环*/
		WHILE @LX_ID <=  @LX_COUNT 
		BEGIN
			SET @SL   = 0
			SET @XGID = 0
		    SET @YE = 0
			SELECT @SL = SL ,@XGID = XGID,@YE = YE FROM #LX_JHFT WHERE ID = @LX_ID 		
					
			
			IF @SL - @FT_QCSL < 0 AND @FT_FTWC <>'W' 
			BEGIN	
				UPDATE #LX_JHFT SET FTSL = @SL WHERE ID = @LX_ID
								
				UPDATE #THZ SET SYSL = SYSL + @SL  WHERE ID = @FT_ID 
				
				UPDATE #THZ SET FTWC = CASE WHEN QCSL - SYSL > 0 THEN 'Y'
				                            WHEN QCSL - SYSL = 0 THEN 'W'
				                            WHEN QCSL - SYSL < 0 THEN 'N'
				                        END
				WHERE ID = @FT_ID  
				
				UPDATE #LX_JH SET YE = YE + @SL  WHERE ID = @XGID
				
				SET @FT_QCSL = 0 
				
			END
			ELSE
			BEGIN	
				UPDATE #LX_JHFT SET FTSL = @FT_QCSL WHERE ID = @LX_ID
				
				
				UPDATE #THZ SET SYSL = SYSL + @FT_QCSL WHERE ID = @FT_ID  
				
				UPDATE #THZ SET FTWC = CASE WHEN QCSL - SYSL > 0 THEN 'Y'
				                            WHEN QCSL - SYSL = 0 THEN 'W'
				                            WHEN QCSL - SYSL < 0 THEN 'N'
				                        END
				WHERE ID = @FT_ID  
				 
				 
				UPDATE #LX_JH SET YE = YE + @FT_QCSL  WHERE ID = @XGID
				
				SET @FT_QCSL = @FT_QCSL 
			END   
			/*分摊结果存为临时表*/
			INSERT INTO #T (ID,GSID,KH,WL,PH,DJ,QCSL,JHSL,XGID,NY,LSBH,FLBH,YWRQ,FTSL)
			SELECT @FT_ID,@GSID,@FT_KH,@FT_WL,@FT_PH,@FT_DJ,@FT_SYSL,0,XGID,NY,LSBH,FLBH,YWRQ,FTSL
			FROM #LX_JHFT
			WHERE FTSL > 0
			
			/*清空分摊结果*/
			DELETE #LX_JHFT
		
				
			SET @LX_ID = @LX_ID + 1
		END 
	END
	ELSE
	BEGIN 
	    /*分摊已完成，没有可再分摊的，强制结束*/
		IF ISNULL((SELECT SUM(SL - YE) FROM #LX_JH WHERE GSID = @GSID AND KH = @FT_KH AND WL = @FT_WL AND PH = @FT_PH ),0) = 0  
		   AND (SELECT COUNT(*) FROM #LX_JH WHERE GSID = @GSID AND KH = @FT_KH AND WL = @FT_WL AND PH = @FT_PH ) > 0
		BEGIN
			BREAK
		END 
		ELSE
		BEGIN 	
			IF EXISTS(SELECT 1 FROM #THZ WHERE ID = @FT_ID AND FTWC <>'W') 
		       AND (SELECT COUNT(*) FROM #LX_JH WHERE GSID = @GSID AND KH = @FT_KH AND WL = @FT_WL AND PH = @FT_PH ) > 0
			BEGIN
				/*未分摊完的，再次分摊数据源*/
				SELECT @FT_KH = KH,@FT_WL = WL,@FT_PH = PH,@FT_DJ = DJ,@FT_QCSL = QCSL - SYSL,@FT_SYSL = SYSL,@FT_FTWC = FTWC
				FROM #THZ
				WHERE GSID = @GSID AND ID = @FT_ID AND QCSL - SYSL >= 0 AND FTWC <>'W'
				
			END
			ELSE
			BEGIN 
				SET @FT_ID = @FT_ID + 1
			END 
		END
	END 
END


/*结果展现*/
SELECT * FROM #THZ
SELECT * FROM #LX_JH WHERE KH ='01001-JZT166' ORDER BY YWRQ ASC,LSBH ASC
SELECT * FROM #T

/*删除临时表*/
DROP TABLE #THZ
DROP TABLE #LX_JH
DROP TABLE #T
DROP TABLE #LX_JHFT

分摊结果：

难点：

NO66781

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
数据多次分摊方案一

数据源1：2020年1月份厂家销往1级经销商的销售记录数据源2：厂家采集到2020年1月1级经销商的销售流向需求：1）识别采集到的经销商流向的SL（数量）是否有效；2）由于不采集经销商流向的售价，故需要将销售记录进行分摊；3）厂家2020年1月份的销售记录，含上月结转1级经销商的库存，以及本月的销售记录，分摊顺序为先“分上月结转，再分本月销售”，可能会存在一条记录多次分摊的问题；解决方案：DECLARE @GSID VARCHAR(5)SET @GSID ='0100
复制链接

扫一扫