问题遇到的现象和发生背景
问题相关代码,请勿粘贴截图
DECLARE
@OperatorPointCode NVARCHAR(32) = 'JCFT' ,
@OperatorCode NVARCHAR(32) = 'JCFT' ,
@PointCode NVARCHAR(32) = 'JCFT' ,
@BeginDT DATETIME='2022-3-2',
@EndDT DATETIME=NULL,
@SearchFlag INT = 0
BEGIN
IF @PointCode IS NULL OR @PointCode = ''
SET @PointCode = @OperatorPointCode
DECLARE @PayPrice DECIMAL(15,2)
IF @BeginDT IS NULL
SET @BeginDT = '1900-01-01'
SET @BeginDT = CONVERT(NCHAR(11),@BeginDT ,120) + '00:00:00'
IF @EndDT IS NULL
SET @EndDT = GETDATE()
SET @EndDT = CONVERT(NCHAR(11),@EndDT ,120) + ' 23:59:59:998'
SELECT
COUNT(1) AS 序号,
T.[网点号] AS 网点号,
T.[网点名称] AS 网点名称,
T.[业务类型] AS 业务类型,
T.[收款日期] AS 收款日期,
SUM(T.[收款金额]) AS 收款金额,
T.[支付方式] AS 支付方式,
SUM(T.[支付金额]) AS 支付金额
INTO #PayDetail
FROM
(
--备件用品
SELECT
--ROW_NUMBER() OVER(ORDER BY S1.SerialNo) 序号,
S1.PointCode AS 网点号,
B.PayBillCode AS 收款单号,
S1.SerialNo AS 业务单号,
T_PointInfo.PointName AS 网点名称,
'备件/用品销售' 业务类型,
CONVERT(NVARCHAR(10),ISNULL(B.BusinessDT,S1.PayDT),120) AS 收款日期,
CAST (ISNULL(S1.PayBalance,B.PayBalance) AS NUMERIC(15,5))AS 收款金额,
CASE WHEN ISNULL(T_TY_PayMentDetail.PayWay,'') = '' THEN '其他' ELSE ISNULL(T_TY_PayMentDetail.PayWay,'') END AS 支付方式,
CAST (ISNULL(T_TY_PayMentDetail.PayBalance,0) AS NUMERIC(15,5)) AS 支付金额
FROM T_SparepartsPaymentDetail S1 WITH (NOLOCK)
LEFT JOIN T_PointInfo WITH(NOLOCK) ON (T_PointInfo.PointCode = S1.PointCode)
LEFT JOIN (SELECT * FROM T_TY_PayMent WITH (NOLOCK) WHERE BillType='SparepartsPay') B ON S1.PointCode=B.PointCode AND S1.SerialNo= B.BillCode
LEFT JOIN T_TY_PayMentDetail WITH (NOLOCK) ON B.PointCode=T_TY_PayMentDetail.PointCode AND B.BillCode=T_TY_PayMentDetail.BillCode AND B.BillType = T_TY_PayMentDetail.BillType
WHERE (S1.PointCode IN (SELECT PointCode FROM DBO.F_GetRelationPointCollection(@PointCode, 1, 1, -1 * ISNULL(@SearchFlag, 0), 1)))
AND (ISNULL(B.BusinessDT,S1.PayDT) BETWEEN @BeginDT AND @EndDT)
UNION
--整车销售
SELECT
C2.PointCode AS 网点号,
B.PayBillCode AS 收款单号,
CS_T.SaleBillCode AS 业务单号,
T_PointInfo.PointName AS 网点名称,
'整车销售' 业务类型,
CONVERT(NVARCHAR(10),ISNULL(CS_T.BalanceDT,Bmin.BusinessDT),120) AS 收款日期,
CAST (ISNULL(Bmin.PayBalance,CS_T.PayedSaleTotalSale) AS NUMERIC(15,5))AS 收款金额,
CASE WHEN ISNULL(C2.PayWay,'') = '' THEN '其他' ELSE ISNULL(C2.PayWay,'') END AS 支付方式,
CAST (ISNULL(Bmin.PayBalance,C2.PayBalance) AS NUMERIC(15,5)) AS 支付金额
FROM T_TY_PayMentDetail C2 WITH (NOLOCK)
LEFT JOIN T_PointInfo WITH(NOLOCK) ON (T_PointInfo.PointCode = C2.PointCode)
LEFT JOIN
(SELECT PointCode,BillCode,BillType, MIN(PayBillCode) AS PayBillCode
FROM T_TY_PayMent
WITH (NOLOCK) WHERE BillType='CarSales'
GROUP BY PointCode,BillCode,BillType
)AS B ON C2.PointCode=B.PointCode AND C2.BillCode= B.BillCode AND B.BillType = C2.BillType
LEFT JOIN T_TY_PayMent Bmin ON Bmin.PointCode=B.PointCode AND Bmin.BillCode=B.BillCode AND Bmin.PayBillCode=B.PayBillCode
LEFT JOIN T_CS_CarSaleBillDetail CS_T WITH (NOLOCK) ON C2.PointCode=CS_T.PointCode AND C2.BillCode=CS_T.SaleBillCode
LEFT JOIN T_CS_CarSaleBill CS_T1 WITH (NOLOCK) ON CS_T1.PointCode=CS_T.PointCode AND CS_T1.SaleBillCode=CS_T.SaleBillCode
LEFT JOIN (SELECT PointCode,BillType,BillCode,MAX(Status) Status
FROM T_TY_OperateStatusSummaryInfo WITH(NOLOCK)
WHERE BillType='CarSales'
GROUP BY PointCode,BillType,BillCode) CS_T2 ON CS_T1.PointCode=CS_T2.PointCode AND CS_T1.SaleBillCode=CS_T2.BillCode
LEFT JOIN T_TY_StatusChangeInfo CS_T3 WITH(NOLOCK) ON CS_T2.BillType=CS_T3.BillType AND CS_T2.Status=CS_T3.Status
WHERE (C2.PointCode IN (SELECT PointCode FROM DBO.F_GetRelationPointCollection(@PointCode, 1, 1, -1 * ISNULL(@SearchFlag, 0), 1)))
AND (ISNULL(CS_T1.Status,0) >= 1 AND ISNULL(CS_T3.StatusDescription,'')='财务收款' AND C2.BillType='CarSales')
AND (ISNULL(CS_T.BalanceDT,Bmin.BusinessDT) BETWEEN @BeginDT AND @EndDT)
)AS T
GROUP BY
T.网点号,
T.[网点名称],
T.[业务类型] ,
T.[收款日期],
T.[支付方式]
SELECT
[收款金额]
INTO #SumPrice
FROM #PayDetail
GROUP BY [收款金额]
SELECT @PayPrice=SUM([收款金额]) FROM #SumPrice
SELECT *
FROM #PayDetail
UNION ALL
SELECT
NULL AS 序号,
NULL AS 网点号,
NULL AS 网点名称,
NULL AS 业务类型,
NULL AS 收款日期,
@PayPrice AS 收款金额,
[支付方式],
SUM([支付金额]) AS 支付金额
FROM #PayDetail
GROUP BY [支付方式]
DROP TABLE #SumPrice
DROP TABLE #PayDetail
END
运行结果及报错内容
我的解答思路和尝试过的方法
进行分组,
我想要达到的结果
不同日期的数据进行合并