sum value by Stroed Procedure

ALTER procedure [dbo].[SP_XXXXXX_P1]
@Txday_M VARCHAR(6), --月份查詢(必輸)
@AOCODE VARCHAR(6), --員工編號(必輸)
@ID_NO VARCHAR(11),--統編
@ERRCODE VARCHAR(100) OUTPUT --Errocode output
as
BEGIN
DECLARE @YYYY varchar(4)
SET @YYYY=LEFT(@Txday_M,4)
set @AOCODE=LEFT(@AOCODE,5)
IF (@ID_NO ='' or @ID_NO is null)
BEGIN
--多筆
if (select count(*) from tempdb.dbo.sysobjects where id=object_id('tempdb.dbo.#TMP_ID') ) >0
begin
drop table #TMP_ID
end

SELECT ID_NO
INTO #TMP_ID
--SELECT *--COUNT(*),AO_CODE1
FROM DM_MIS.DBO.CIF_MS_CUSTOMER
WHERE DATA_YM=@Txday_M
AND left(AO_CODE1,5)=@AOCODE

if (select count(*) from tempdb.dbo.sysobjects where id=object_id('tempdb.dbo.#TMP_P1') ) >0
begin
drop table #TMP_P1
end
--建立#TMP_P1
SELECT
identity(int,1,1) SNO,
A.ID_NO, --客戶ID
B.CUST_NAME,--戶名
SUM(ISNULL(A.NET_AMT,0)) AS NET_AMT, --淨收益(月)
SUM(ISNULL(A.OPEX_AMT,0))AS OPEX_AMT, --Opex(月)
SUM(ISNULL(A.PPOP_AMT,0))AS PPOP_AMT, --PPOP(月)
SUM(ISNULL(A.PROV_AMT,0))AS PROV_AMT, --呆帳費用(月)
SUM(ISNULL(A.PRF_AMT,0)) AS PRF_AMT, --稅前盈餘(月)
CAST(0 AS NUMERIC(15)) AS NET_AMT_Y, --淨收益(年度)
CAST(0 AS NUMERIC(15)) AS OPEX_AMT_Y,--Opex(年度)
CAST(0 AS NUMERIC(15)) AS PPOP_AMT_Y,--PPOP(年度)
CAST(0 AS NUMERIC(15)) AS PROV_AMT_Y,--呆帳費用(年度)
CAST(0 AS NUMERIC(15)) AS PRF_AMT_Y --稅前盈餘(年度)
INTO #TMP_P1
FROM DM_MIS.dbo.CIF_MS_AO_PROFIT A
INNER JOIN DM_MIS.dbo.CIF_MS_CUSTOMER B
ON A.ID_NO=B.ID_NO
INNER JOIN #TMP_ID C    --#TMP_P1.ID_NO
ON A.ID_NO=C.ID_NO
WHERE A.DATA_YM=@Txday_M
AND left(A.AO_CODE,5)=@AOCODE
GROUP BY A.ID_NO,B.CUST_NAME
ORDER BY A.ID_NO,B.CUST_NAME

--計算年度統計資料(更新CIF_MS_AO_PROFIT資料至#TMP_P)
UPDATE C
SET C.NET_AMT_Y=D.NET_AMT,
C.OPEX_AMT_Y=D.OPEX_AMT,
C.PPOP_AMT_Y=D.PPOP_AMT,
C.PROV_AMT_Y=D.PROV_AMT,
C.PRF_AMT_Y=D.PRF_AMT
FROM #TMP_P1 C
INNER JOIN (
SELECT
A.ID_NO,
SUM(ISNULL(NET_AMT,0))AS NET_AMT,
SUM(ISNULL(OPEX_AMT,0)) AS OPEX_AMT,
SUM(ISNULL(PPOP_AMT,0))AS PPOP_AMT,
SUM(ISNULL(PROV_AMT,0))AS PROV_AMT,
SUM(ISNULL(PRF_AMT,0)) AS PRF_AMT
FROM DM_MIS.dbo.CIF_MS_AO_PROFIT A
INNER JOIN #TMP_ID B  --join CIF_MS_CUSTOMER.ID_NO
ON A.ID_NO=B.ID_NO       --CIF_MS_AO_PROFIT.ID_NO=#TMP_ID.ID_NO
WHERE LEFT(A.DATA_YM,4)=@YYYY
GROUP BY A.ID_NO   --以CIF_MS_AO_PROFIT.ID_NO為群組，計算年度統計資料
) D
ON C.ID_NO=D.ID_NO    --#TMP_P1.ID_NO=D.ID_NO

--增加合計列
INSERT INTO #TMP_P1
(ID_NO,CUST_NAME,NET_AMT,OPEX_AMT,PPOP_AMT,PROV_AMT,PRF_AMT,NET_AMT_Y,OPEX_AMT_Y,PPOP_AMT_Y,PROV_AMT_Y,PRF_AMT_Y)
SELECT '合計','',
SUM(ISNULL(NET_AMT,0))AS NET_AMT,
SUM(ISNULL(OPEX_AMT,0)) AS OPEX_AMT,
SUM(ISNULL(PPOP_AMT,0))AS PPOP_AMT,
SUM(ISNULL(PROV_AMT,0))AS PROV_AMT,
SUM(ISNULL(PRF_AMT,0)) AS PRF_AMT,
SUM(ISNULL(NET_AMT_Y,0))AS NET_AMT_Y,
SUM(ISNULL(OPEX_AMT_Y,0)) AS OPEX_AMT_Y,
SUM(ISNULL(PPOP_AMT_Y,0))AS PPOP_AMT_Y,
SUM(ISNULL(PROV_AMT_Y,0))AS PROV_AMT_Y,
SUM(ISNULL(PRF_AMT_Y,0)) AS PRF_AMT_Y
FROM #TMP_P1
--從#TMP_P1傳回資料
SELECT
ID_NO,CUST_NAME,
NET_AMT,OPEX_AMT,PPOP_AMT,PROV_AMT,PRF_AMT,
NET_AMT_Y,OPEX_AMT_Y,PPOP_AMT_Y,PROV_AMT_Y,PRF_AMT_Y
FROM #TMP_P1
ORDER BY SNO
END
------------------------------------------------------------------------------------------
ELSE
BEGIN
--單筆
if (select count(*) from tempdb.dbo.sysobjects where id=object_id('tempdb.dbo.#TMP_P') ) >0
begin
drop table #TMP_P
end

--建立#TMP_P
SELECT
identity(int,1,1) SNO,
A.ID_NO, --客戶ID
B.CUST_NAME,--戶名
SUM(ISNULL(A.NET_AMT,0)) AS NET_AMT, --淨收益(月)
SUM(ISNULL(A.OPEX_AMT,0))AS OPEX_AMT, --Opex(月)
SUM(ISNULL(A.PPOP_AMT,0))AS PPOP_AMT, --PPOP(月)
SUM(ISNULL(A.PROV_AMT,0))AS PROV_AMT, --呆帳費用(月)
SUM(ISNULL(A.PRF_AMT,0)) AS PRF_AMT, --稅前盈餘(月)
CAST(0 AS NUMERIC(15)) AS NET_AMT_Y, --淨收益(年度)
CAST(0 AS NUMERIC(15)) AS OPEX_AMT_Y,--Opex(年度)
CAST(0 AS NUMERIC(15)) AS PPOP_AMT_Y,--PPOP(年度)
CAST(0 AS NUMERIC(15)) AS PROV_AMT_Y,--呆帳費用(年度)
CAST(0 AS NUMERIC(15)) AS PRF_AMT_Y --稅前盈餘(年度)
INTO #TMP_P
FROM DM_MIS.dbo.CIF_MS_AO_PROFIT A
INNER JOIN DM_MIS.dbo.CIF_MS_CUSTOMER B
ON A.ID_NO=B.ID_NO     --CIF_MS_AO_PROFIT.ID_NO=CIF_MS_CUSTOMER.ID_NO
WHERE A.DATA_YM=@Txday_M
AND left(A.AO_CODE,5)=@AOCODE
AND A.ID_NO=@ID_NO     --CIF_MS_AO_PROFIT.ID_NO=@ID_NO
GROUP BY A.ID_NO,B.CUST_NAME

----計算年度統計資料(更新CIF_MS_AO_PROFIT資料至#TMP_P)
UPDATE C
SET C.NET_AMT_Y=D.NET_AMT,
C.OPEX_AMT_Y=D.OPEX_AMT,
C.PPOP_AMT_Y=D.PPOP_AMT,
C.PROV_AMT_Y=D.PROV_AMT,
C.PRF_AMT_Y=D.PRF_AMT
FROM #TMP_P C
INNER JOIN (
SELECT
ID_NO,
SUM(ISNULL(NET_AMT,0))AS NET_AMT,
SUM(ISNULL(OPEX_AMT,0)) AS OPEX_AMT,
SUM(ISNULL(PPOP_AMT,0))AS PPOP_AMT,
SUM(ISNULL(PROV_AMT,0))AS PROV_AMT,
SUM(ISNULL(PRF_AMT,0)) AS PRF_AMT
FROM DM_MIS.dbo.CIF_MS_AO_PROFIT
WHERE ID_NO=@ID_NO          --CIF_MS_AO_PROFIT.ID_NO=@ID_NO
AND LEFT(DATA_YM,4)=@YYYY
GROUP BY ID_NO
) D
ON C.ID_NO=D.ID_NO

--增加合計列
INSERT INTO #TMP_P
(ID_NO,CUST_NAME,NET_AMT,OPEX_AMT,PPOP_AMT,PROV_AMT,PRF_AMT,NET_AMT_Y,OPEX_AMT_Y,PPOP_AMT_Y,PROV_AMT_Y,PRF_AMT_Y)
SELECT '合計','',
SUM(ISNULL(NET_AMT,0))AS NET_AMT,
SUM(ISNULL(OPEX_AMT,0)) AS OPEX_AMT,
SUM(ISNULL(PPOP_AMT,0))AS PPOP_AMT,
SUM(ISNULL(PROV_AMT,0))AS PROV_AMT,
SUM(ISNULL(PRF_AMT,0)) AS PRF_AMT,
SUM(ISNULL(NET_AMT_Y,0))AS NET_AMT_Y,
SUM(ISNULL(OPEX_AMT_Y,0)) AS OPEX_AMT_Y,
SUM(ISNULL(PPOP_AMT_Y,0))AS PPOP_AMT_Y,
SUM(ISNULL(PROV_AMT_Y,0))AS PROV_AMT_Y,
SUM(ISNULL(PRF_AMT_Y,0)) AS PRF_AMT_Y
FROM #TMP_P

--從#TMP_P1傳回資料
SELECT
ID_NO,CUST_NAME,
NET_AMT,OPEX_AMT,PPOP_AMT,PROV_AMT,PRF_AMT,
NET_AMT_Y,OPEX_AMT_Y,PPOP_AMT_Y,PROV_AMT_Y,PRF_AMT_Y
FROM #TMP_P
ORDER BY SNO

END --ELSE END

END