34,590
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#C') is null
drop table #C
Go
Create table #C([客户编号] nvarchar(24),[客户抬头] nvarchar(23))
Insert #C
select N'0001',N'A公司' union all
select N'0002',N'B公司' union all
select N'0003',N'C公司'
GO
if not object_id(N'Tempdb..#X') is null
drop table #X
Go
Create table #X([客户编号] nvarchar(24),[日期] Date,[金额] int)
Insert #X
select N'0001','2018-9-12',2000 union all
select N'0001','2018-9-16',1500 union all
select N'0001','2018-10-23',3000 union all
select N'0002','2018-9-15',3200 union all
select N'0002','2018-10-19',5000
Go
if not object_id(N'Tempdb..#Y') is null
drop table #Y
Go
Create table #Y([客户编号] nvarchar(24),[日期] Date,[金额] int)
Insert #Y
select N'0001',N'2018-9-12',12000 union all
select N'0001',N'2018-10-16',10000 union all
select N'0001',N'2018-10-23',20000 union all
select N'0002',N'2018-11-15',13200 union all
select N'0002',N'2018-10-19',25000
Go
--测试数据结束
SELECT ISNULL(t.日期, t1.日期) AS 日期,
客户抬头,
t.金额 AS X业务应收款,
t1.金额 AS Y业务应收款
FROM #C
LEFT JOIN ( SELECT 客户编号,
RTRIM(YEAR(日期)) +'-'+ RTRIM(MONTH(日期)) AS 日期,
SUM(金额) AS 金额
FROM #X
GROUP BY 客户编号,
RTRIM(YEAR(日期)) +'-'+ RTRIM(MONTH(日期))) t
ON t.客户编号 = #C.客户编号
LEFT JOIN ( SELECT 客户编号,
RTRIM(YEAR(日期)) +'-'+ RTRIM(MONTH(日期)) AS 日期,
SUM(金额) AS 金额
FROM #Y
GROUP BY 客户编号,
RTRIM(YEAR(日期)) +'-'+ RTRIM(MONTH(日期))) t1
ON t1.客户编号 = #C.客户编号
AND t1.日期 = t.日期
WHERE ISNULL(t.日期, t1.日期) IS NOT NULL
ORDER BY 日期 DESC,#C.客户编号