PiolotTH_ReportFrom/派诺报表SQL文件/应收情况概况表按SBU区分插入数据SQL.sql

ALTER PROCEDURE YSQKGKSBU_GZTH 
	 @FSDate NVARCHAR(100),
	 @FEDate NVARCHAR(100)
AS
BEGIN
	 -- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    DECLARE @StartDate DATE;
    DECLARE @EndDate DATE;
    DECLARE @LastDate Date;

    -- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ת<EFBFBD><D7AA>Ϊ<EFBFBD><CEAA><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    SET @StartDate = CONVERT(DATE, @FSDate, 120); -- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ڸ<EFBFBD>ʽΪ YYYY-MM-DD
    SET @EndDate = CONVERT(DATE, @FEDate, 120); -- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ڸ<EFBFBD>ʽΪ YYYY-MM-DD

    -- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>һ<EFBFBD><D2BB><EFBFBD><EFBFBD> 12 <20><> 31 <20><>
    SET @LastDate = DATEADD(YEAR, DATEDIFF(YEAR, 0, @StartDate) - 1, 0); -- <20><>ȡ @StartDate <20><><EFBFBD><EFBFBD><EFBFBD>ݵ<EFBFBD> 1 <20><> 1 <20><>
    SET @LastDate = DATEADD(DAY, -1, DATEADD(YEAR, 1, @StartDate)); -- <20><>ȡ<EFBFBD><C8A1>һ<EFBFBD><D2BB><EFBFBD><EFBFBD> 12 <20><> 31 <20><>

    TRUNCATE TABLE YSQKGK_SBU_GZTH
    --<EFBFBD><EFBFBD>ȡ<EFBFBD>ڳ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݣ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    INSERT INTO YSQKGK_SBU_GZTH([SBU],[<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD><EFBFBD><EFBFBD><EFBFBD>_<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD>ר<EFBFBD><EFBFBD>С<EFBFBD><EFBFBD>])
	SELECT 
    SBU,
    <EFBFBD><EFBFBD><EFBFBD>ڽ<EFBFBD><EFBFBD><EFBFBD>,
    CASE WHEN 
    <EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD> = 'Ӧ<EFBFBD>տ<EFBFBD>ר<EFBFBD><EFBFBD>С<EFBFBD><EFBFBD>' THEN <EFBFBD><EFBFBD><EFBFBD>ڽ<EFBFBD><EFBFBD><EFBFBD>
    ELSE null END AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>_<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD>ר<EFBFBD><EFBFBD>С<EFBFBD><EFBFBD>'
    FROM ZRP_YingShouMingXi_yuyubo
    where <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> <= @LastDate
    AND [<EFBFBD><EFBFBD><EFBFBD>ڽ<EFBFBD><EFBFBD><EFBFBD>] <> 0
	
    --<EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD>ʼ<EFBFBD><EFBFBD><EFBFBD>ںͽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ڵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    INSERT INTO YSQKGK_SBU_GZTH([SBU],[<EFBFBD><EFBFBD><EFBFBD>굽<EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD><EFBFBD><EFBFBD><EFBFBD>_<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD>ר<EFBFBD><EFBFBD>С<EFBFBD><EFBFBD>])
    SELECT 
    SBU,
    <EFBFBD><EFBFBD><EFBFBD>ڽ<EFBFBD><EFBFBD><EFBFBD>,
    CASE WHEN 
    <EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD> = 'Ӧ<EFBFBD>տ<EFBFBD>ר<EFBFBD><EFBFBD>С<EFBFBD><EFBFBD>' THEN <EFBFBD><EFBFBD><EFBFBD>ڽ<EFBFBD><EFBFBD><EFBFBD>
    ELSE null END AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>_<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD>ר<EFBFBD><EFBFBD>С<EFBFBD><EFBFBD>'
    FROM ZRP_YingShouMingXi_yuyubo
    where [<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] >= @FSDate
    AND [<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] <= @FEDate
    AND [<EFBFBD><EFBFBD><EFBFBD>ڽ<EFBFBD><EFBFBD><EFBFBD>] <> 0

    --<EFBFBD><EFBFBD><EFBFBD>з<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ϼƣ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>½<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>䵽YSQKGK_SBU_GZTH<EFBFBD><EFBFBD>
    SELECT
    SBU,
    SUM(<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD>) AS <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD>,
    SUM(<EFBFBD><EFBFBD><EFBFBD><EFBFBD>_<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD>ר<EFBFBD><EFBFBD>С<EFBFBD><EFBFBD>) AS <EFBFBD><EFBFBD><EFBFBD><EFBFBD>_<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD>ר<EFBFBD><EFBFBD>С<EFBFBD><EFBFBD>,
    SUM(<EFBFBD><EFBFBD><EFBFBD>굽<EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD>) AS <EFBFBD><EFBFBD><EFBFBD>굽<EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD>,
    SUM(<EFBFBD><EFBFBD><EFBFBD><EFBFBD>_<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD>ר<EFBFBD><EFBFBD>С<EFBFBD><EFBFBD>) AS <EFBFBD><EFBFBD><EFBFBD><EFBFBD>_<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD>ר<EFBFBD><EFBFBD>С<EFBFBD><EFBFBD>
    INTO #TEMP1
    FROM YSQKGK_SBU_GZTH
    GROUP BY SBU

    TRUNCATE TABLE YSQKGK_SBU_GZTH

    INSERT INTO YSQKGK_SBU_GZTH([SBU],[<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD><EFBFBD><EFBFBD><EFBFBD>_<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD>ר<EFBFBD><EFBFBD>С<EFBFBD><EFBFBD>],[<EFBFBD><EFBFBD><EFBFBD>굽<EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD><EFBFBD><EFBFBD><EFBFBD>_<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD>ר<EFBFBD><EFBFBD>С<EFBFBD><EFBFBD>])
    SELECT * FROM #TEMP1

    --<EFBFBD><EFBFBD>¼<EFBFBD><EFBFBD><EFBFBD>굽<EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ܺͣ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ռ<EFBFBD><EFBFBD><EFBFBD>ֶ<EFBFBD>
    DECLARE @SUMDQJE DECIMAL(23, 10);
    SELECT @SUMDQJE = SUM([<EFBFBD><EFBFBD><EFBFBD>굽<EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD>]) FROM YSQKGK_SBU_GZTH
    
    UPDATE YSQKGK_SBU_GZTH
    SET [ռ<EFBFBD><EFBFBD>] = FORMAT(ROUND([<EFBFBD><EFBFBD><EFBFBD>굽<EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD>] / @SUMDQJE * 100, 2), '0.##') + '%';

    --<EFBFBD><EFBFBD><EFBFBD>±仯<EFBFBD>ֶ<EFBFBD>
    UPDATE YSQKGK_SBU_GZTH
    SET [<EFBFBD>仯] = ISNULL([<EFBFBD><EFBFBD><EFBFBD>굽<EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD>],0) - ISNULL([<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD>],0)
END