Q:

Union query removing blank rows

I'm trying to union two different results but I'm not sure how to remove the blank values. The paid_date and check_date are sometimes different. What do I need to do to modify the code? I've tried doing MAX but didn't seem to work. Thanks.
Results:

What I want:

WITH PAID AS
(
SELECT PIDATE
,SUM(PAY) AS PAID
,ROW_NUMBER() OVER(ORDER BY PAYDATE DESC) AS ROW_NUM
FROM CLAIM
WHERE PAY<>0
GROUP BY PAYDATE
)
,TOTAL_PAID AS
(
SELECT CONVERT(DATE,CONVERT(VARCHAR(10),PAYDATE)) AS PAID_DATE
,FORMAT(PAID,'C','EN-US') AS PAID
FROM PAID
WHERE ROW_NUM <= 10
)

,CHECKS AS
(
SELECT DISTINCT CHECK_DATE
,SUM(PAYMENT) AS PAYMENT
,ROW_NUMBER() OVER(ORDER BY CHECK_DATE DESC) AS ROW_NUM
FROM CR 
GROUP BY CHECK_DATE
)
,TOTAL_CHECKS AS
(
SELECT CONVERT(DATE,CONVERT(VARCHAR(10),CHECK_DATE)) AS CHECK_DATE
,FORMAT(PAYMENT,'C','EN-US') AS PAYMENT
FROM CHECKS
WHERE ROW_NUM <= 10
)

,FINAL AS
(
SELECT PAID_DATE
,PAID
,'' AS CHECK_DATE
,'' AS PAYMENT
FROM TOTAL_PAID

UNION ALL

SELECT '' AS PAID_DATE
,'' AS PAID
,CHECK_DATE 
,PAYMENT 
FROM TOTAL_CHECKS
)

SELECT *
FROM FINAL

A:

In this scenario, you must not use union. you must use join instead.
Try join two sequence on paid_date = check_date and select proper field from each set.

