Solved

T-SQL:  How to Place a CTE Clause within a Query Containing "UNION" Statements

Posted on 2016-09-27
9
70 Views
Last Modified: 2016-09-27
Hello:

The first block of code below is my cte statement, while the second block of code is my "main" query containing UNION syntax.

I need to place my cte clause within my main query, such that my cte statement is in essence simply another "UNION-ized" clause in the query.

I realize the my cte statement contains a field called "cnt", and the other clauses within my main query do not have this field.

But, this field to me acts simply as a variable to pull in the data that I needed for the clause within the cte statement.  

I know that I will need to place a reference to "cnt" within the other clauses of my main query, however,  that's not my concern.

My concern, right now, is that I'm having trouble providing the syntax to "embed" my cte statement within my main query.  Can someone please help?

Thank you!

John

;with cte as 
(
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
COUNT(*) OVER (PARTITION BY RM20101.CUSTNMBR, RM20201.APTODCNM, RM20201.APTODCDT) AS Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT > @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR not in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
and RM20201.APPTOAMT = RM20101.ORTRXAMT 
GROUP BY RM20201.APTODCNM, RM20201.APTODCDT, RM20201.APFRDCNM)
AND
RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT 
AND RM20101.DUEDATE <> ''
THEN RM20201.APFRMAPLYAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN ('266267',  '0100012160'))  
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20101.RMDTYPAL, RM20101.DUEDATE, RM20201.APFRMAPLYAMT, RM20101.DOCNUMBR, RM20201.APFRDCNM, RM20101.DOCDATE,
RM20201.DATE1, RM20201.APTODCDT, RM20201.APPTOAMT, RM20201.APTODCNM, RM20201.APFRDCDT, RM20101.ORTRXAMT, RM20101.CURTRXAM
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT > @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR not in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
and RM20201.APPTOAMT = RM20101.ORTRXAMT 
GROUP BY RM20201.APTODCNM, RM20201.APTODCDT, RM20201.APFRDCNM)
and
RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT and RM20201.APPTOAMT <> RM20101.ORTRXAMT 
AND RM20101.DUEDATE <> '' AND RM20101.DOCNUMBR = RM20201.APFRDCNM
THEN RM20201.APFRMAPLYAMT * -1 ELSE 0 END
<> 0)
select * from cte where Cnt > 1;

Open in new window



DECLARE @AGE DATETIME
DECLARE @RUN DATETIME
SET @AGE = '2015-09-30 00:00:00.000'
SET @RUN = '2016-07-31 00:00:00.000'

SELECT [CustomerID], [CustomerName], SUM([OPEN A/R]) as [OPEN A/R] FROM
(
SELECT TOP 100 PERCENT * FROM
(
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
CASE WHEN RM20101.RMDTYPAL < 7 and RM20101.DOCNUMBR NOT IN (select APTODCNM from RM20201)
and RM20101.DUEDATE < @AGE 
and RM20101.DOCDATE < @AGE 
and RM20101.ORTRXAMT = RM20101.CURTRXAM
THEN RM20101.ORTRXAMT ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160')) 
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20101.RMDTYPAL, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE
HAVING 
CASE WHEN RM20101.RMDTYPAL < 7 and RM20101.DOCNUMBR NOT IN (select APTODCNM from RM20201)
and RM20101.DUEDATE < @AGE 
and RM20101.DOCDATE < @AGE 
and RM20101.ORTRXAMT = RM20101.CURTRXAM
THEN RM20101.ORTRXAMT ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
CASE WHEN RM20101.RMDTYPAL < 7 AND RM20101.DOCNUMBR in (select APTODCNM from RM20201) and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT <= @AGE and RM20101.ORTRXAMT <> RM20101.CURTRXAM
THEN RM20101.ORTRXAMT ELSE RM20101.CURTRXAM END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APTODCNM 
AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160')) 
AND (RM20101.ORTRXAMT = RM20201.APPTOAMT)
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM
HAVING 
CASE WHEN RM20101.RMDTYPAL < 7 AND RM20101.DOCNUMBR in (select APTODCNM from RM20201) and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT <= @AGE and RM20101.ORTRXAMT <> RM20101.CURTRXAM
THEN RM20101.ORTRXAMT ELSE RM20101.CURTRXAM END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
CASE WHEN 
RM20101.RMDTYPAL < 7 AND RM20101.DOCNUMBR in (select APTODCNM from RM20201) and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT <= @AGE and RM20101.ORTRXAMT <> RM20101.CURTRXAM
THEN RM20201.APPTOAMT ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APTODCNM 
AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))
AND (RM20101.ORTRXAMT <> RM20201.APPTOAMT)
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, 
RM20201.APPTOAMT
HAVING 
CASE WHEN 
RM20101.RMDTYPAL < 7 AND RM20101.DOCNUMBR in (select APTODCNM from RM20201) and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT <= @AGE and RM20101.ORTRXAMT <> RM20101.CURTRXAM
THEN RM20201.APPTOAMT ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE = ''
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))   
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE = ''
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20101.ORTRXAMT = RM20101.CURTRXAM
AND RM20101.DOCNUMBR not in (select APFRDCNM from RM20201)
THEN RM20101.ORTRXAMT * -1 
ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160')) 
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20101.RMDTYPAL, RM20101.ORTRXAMT, RM20101.CURTRXAM
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20101.ORTRXAMT = RM20101.CURTRXAM
AND RM20101.DOCNUMBR not in (select APFRDCNM from RM20201)
THEN RM20101.ORTRXAMT * -1 
ELSE 0 END
<> 0 
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName],  RM20101.DOCNUMBR as [DocumentNumber],
CASE WHEN RM20101.RMDTYPAL > 6 
and RM20201.APTODCDT > @AGE and RM20201.DATE1 > @AGE and RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
where RM20201.APPTOAMT = RM20101.ORTRXAMT 
GROUP BY RM20201.APFRDCNM, RM20101.DOCNUMBR, RM20201.APPTOAMT
HAVING COUNT(RM20201.APTODCNM) = 1) and RM20101.ORTRXAMT 
<> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' and RM20101.RMDTYPAL <> RM20201.APTODCTY
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT, RM20201.APTODCTY
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 
and RM20201.APTODCDT > @AGE and RM20201.DATE1 > @AGE and RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
where RM20201.APPTOAMT = RM20101.ORTRXAMT 
GROUP BY RM20201.APFRDCNM, RM20101.DOCNUMBR, RM20201.APPTOAMT
HAVING COUNT(RM20201.APTODCNM) = 1) and RM20101.ORTRXAMT 
<> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' and RM20101.RMDTYPAL <> RM20201.APTODCTY
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APFRDCNM = RM20101.DOCNUMBR
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR
HAVING COUNT(RM20201.APTODCNM) = 1)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' AND RM20201.APPTOAMT <> RM20101.ORTRXAMT
THEN RM20101.ORTRXAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APFRDCNM = RM20101.DOCNUMBR
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR
HAVING COUNT(RM20201.APTODCNM) = 1)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' AND RM20201.APPTOAMT <> RM20101.ORTRXAMT
THEN RM20101.ORTRXAMT * -1 ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (
select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 on RM20201.APFRDCNM = RM20101.DOCNUMBR AND RM20201.CUSTNMBR = RM20101.CUSTNMBR
WHERE RM20201.APPTOAMT = RM20101.ORTRXAMT AND RM20201.APFRDCDT < @AGE AND RM20201.APTODCDT < @AGE 
AND RM20201.ApplyFromGLPostDate < @AGE
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR
)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' 
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160')) 
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, 
RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT,
RM20201.APTODCTY
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (
select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 on RM20201.APFRDCNM = RM20101.DOCNUMBR AND RM20201.CUSTNMBR = RM20101.CUSTNMBR
WHERE RM20201.APPTOAMT = RM20101.ORTRXAMT AND RM20201.APFRDCDT < @AGE AND RM20201.APTODCDT < @AGE 
AND RM20201.ApplyFromGLPostDate < @AGE
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR
)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' 
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 < @AGE 
and RM20201.APTODCDT < @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
AND RM20101.DUEDATE = ''
THEN RM20101.CURTRXAM * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))  
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT,
RM20201.APFRMAPLYAMT
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 < @AGE 
and RM20201.APTODCDT < @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
AND RM20101.DUEDATE = ''
THEN RM20101.CURTRXAM * -1 ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 < @AGE 
and RM20201.APTODCDT < @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
AND RM20101.DUEDATE <> ''
THEN RM20101.CURTRXAM * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))  
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT,
RM20201.APFRMAPLYAMT
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 < @AGE 
and RM20201.APTODCDT < @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
AND RM20101.DUEDATE <> ''
THEN RM20101.CURTRXAM * -1 ELSE 0 END
<> 0

)
as WATERFALL 
)
AS WATERFALL2 
GROUP BY [CustomerID], [CustomerName]
ORDER BY [CustomerID]

Open in new window

0
Comment
Question by:John Ellis
  • 4
  • 4
9 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 41818066
E.g.

-- Query 1
WITH    CTE
          AS ( SELECT   *
               FROM     A
             )
    SELECT  *
    FROM    CTE;

-- Query 2
SELECT  columnList
FROM    B
UNION
SELECT  columnList
FROM    C;

-- Result
WITH    CTE
          AS ( SELECT   *
               FROM     A
             )
    SELECT  columnList
    FROM    B
    UNION
    SELECT  columnList
    FROM    C
    UNION
    SELECT  columnList
    FROM    CTE;

Open in new window

0
 

Expert Comment

by:mrnicksmith
ID: 41818133
John,
 Wrap your main query in it's own CTE put it inside ( at the end of the first) of the CTE as
" ) ,cte2 as ( put main query here ) " , then select items from both queries putting  FROM cte and join the cte2 table to that based on a common data point in both tables,( i.e. looks like a common data point in your tables is customer number). One thing I found with my CTEs is that I need to add my group by to the end of each CTE that has a function, unlike unions where you must put the group by at the end.  Give it a try, I hope this helps.
0
 

Author Comment

by:John Ellis
ID: 41818394
I cannot get either of these two solutions working.

John
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 33

Expert Comment

by:ste5an
ID: 41818420
What's the problem?
0
 

Author Comment

by:John Ellis
ID: 41818448
As usual, what's "not" the problem?  LOL!

Anyway, below is the code as best as I could get it.  

As shown by the results that follow, it's only returning two rows of data.  And, there should be three columns.

Part of the problem is that I don't know the syntax for putting the following two clauses only at the beginning of the combined code:

SELECT [CustomerID], [CustomerName], SUM([OPEN A/R]) as [OPEN A/R] FROM
(
SELECT TOP 100 PERCENT * FROM
(



DECLARE @AGE DATETIME
DECLARE @RUN DATETIME
SET @AGE = '2015-09-30 00:00:00.000'
SET @RUN = '2016-07-31 00:00:00.000'

;with cte as 
(
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
COUNT(*) OVER (PARTITION BY RM20101.CUSTNMBR, RM20201.APTODCNM, RM20201.APTODCDT) AS Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT > @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR not in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
and RM20201.APPTOAMT = RM20101.ORTRXAMT 
GROUP BY RM20201.APTODCNM, RM20201.APTODCDT, RM20201.APFRDCNM)
AND
RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT 
AND RM20101.DUEDATE <> ''
THEN RM20201.APFRMAPLYAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN ('266267',  '0100012160'))  
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20101.RMDTYPAL, RM20101.DUEDATE, RM20201.APFRMAPLYAMT, RM20101.DOCNUMBR, RM20201.APFRDCNM, RM20101.DOCDATE,
RM20201.DATE1, RM20201.APTODCDT, RM20201.APPTOAMT, RM20201.APTODCNM, RM20201.APFRDCDT, RM20101.ORTRXAMT, RM20101.CURTRXAM
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT > @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR not in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
and RM20201.APPTOAMT = RM20101.ORTRXAMT 
GROUP BY RM20201.APTODCNM, RM20201.APTODCDT, RM20201.APFRDCNM)
and
RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT and RM20201.APPTOAMT <> RM20101.ORTRXAMT 
AND RM20101.DUEDATE <> '' AND RM20101.DOCNUMBR = RM20201.APFRDCNM
THEN RM20201.APFRMAPLYAMT * -1 ELSE 0 END
<> 0),
cte2 as
(
SELECT [CustomerID], [CustomerName], SUM([OPEN A/R]) as [OPEN A/R] FROM
(
SELECT TOP 100 PERCENT * FROM
(
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL < 7 and RM20101.DOCNUMBR NOT IN (select APTODCNM from RM20201)
and RM20101.DUEDATE < @AGE 
and RM20101.DOCDATE < @AGE 
and RM20101.ORTRXAMT = RM20101.CURTRXAM
THEN RM20101.ORTRXAMT ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160')) 
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20101.RMDTYPAL, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE
HAVING 
CASE WHEN RM20101.RMDTYPAL < 7 and RM20101.DOCNUMBR NOT IN (select APTODCNM from RM20201)
and RM20101.DUEDATE < @AGE 
and RM20101.DOCDATE < @AGE 
and RM20101.ORTRXAMT = RM20101.CURTRXAM
THEN RM20101.ORTRXAMT ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL < 7 AND RM20101.DOCNUMBR in (select APTODCNM from RM20201) and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT <= @AGE and RM20101.ORTRXAMT <> RM20101.CURTRXAM
THEN RM20101.ORTRXAMT ELSE RM20101.CURTRXAM END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APTODCNM 
AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160')) 
AND (RM20101.ORTRXAMT = RM20201.APPTOAMT)
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM
HAVING 
CASE WHEN RM20101.RMDTYPAL < 7 AND RM20101.DOCNUMBR in (select APTODCNM from RM20201) and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT <= @AGE and RM20101.ORTRXAMT <> RM20101.CURTRXAM
THEN RM20101.ORTRXAMT ELSE RM20101.CURTRXAM END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN 
RM20101.RMDTYPAL < 7 AND RM20101.DOCNUMBR in (select APTODCNM from RM20201) and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT <= @AGE and RM20101.ORTRXAMT <> RM20101.CURTRXAM
THEN RM20201.APPTOAMT ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APTODCNM 
AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))
AND (RM20101.ORTRXAMT <> RM20201.APPTOAMT)
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, 
RM20201.APPTOAMT
HAVING 
CASE WHEN 
RM20101.RMDTYPAL < 7 AND RM20101.DOCNUMBR in (select APTODCNM from RM20201) and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT <= @AGE and RM20101.ORTRXAMT <> RM20101.CURTRXAM
THEN RM20201.APPTOAMT ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE = ''
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))   
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE = ''
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20101.ORTRXAMT = RM20101.CURTRXAM
AND RM20101.DOCNUMBR not in (select APFRDCNM from RM20201)
THEN RM20101.ORTRXAMT * -1 
ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160')) 
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20101.RMDTYPAL, RM20101.ORTRXAMT, RM20101.CURTRXAM
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20101.ORTRXAMT = RM20101.CURTRXAM
AND RM20101.DOCNUMBR not in (select APFRDCNM from RM20201)
THEN RM20101.ORTRXAMT * -1 
ELSE 0 END
<> 0 
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName],  RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 
and RM20201.APTODCDT > @AGE and RM20201.DATE1 > @AGE and RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
where RM20201.APPTOAMT = RM20101.ORTRXAMT 
GROUP BY RM20201.APFRDCNM, RM20101.DOCNUMBR, RM20201.APPTOAMT
HAVING COUNT(RM20201.APTODCNM) = 1) and RM20101.ORTRXAMT 
<> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' and RM20101.RMDTYPAL <> RM20201.APTODCTY
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT, RM20201.APTODCTY
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 
and RM20201.APTODCDT > @AGE and RM20201.DATE1 > @AGE and RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
where RM20201.APPTOAMT = RM20101.ORTRXAMT 
GROUP BY RM20201.APFRDCNM, RM20101.DOCNUMBR, RM20201.APPTOAMT
HAVING COUNT(RM20201.APTODCNM) = 1) and RM20101.ORTRXAMT 
<> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' and RM20101.RMDTYPAL <> RM20201.APTODCTY
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APFRDCNM = RM20101.DOCNUMBR
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR
HAVING COUNT(RM20201.APTODCNM) = 1)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' AND RM20201.APPTOAMT <> RM20101.ORTRXAMT
THEN RM20101.ORTRXAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APFRDCNM = RM20101.DOCNUMBR
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR
HAVING COUNT(RM20201.APTODCNM) = 1)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' AND RM20201.APPTOAMT <> RM20101.ORTRXAMT
THEN RM20101.ORTRXAMT * -1 ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (
select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 on RM20201.APFRDCNM = RM20101.DOCNUMBR AND RM20201.CUSTNMBR = RM20101.CUSTNMBR
WHERE RM20201.APPTOAMT = RM20101.ORTRXAMT AND RM20201.APFRDCDT < @AGE AND RM20201.APTODCDT < @AGE 
AND RM20201.ApplyFromGLPostDate < @AGE
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR
)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' 
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160')) 
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, 
RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT,
RM20201.APTODCTY
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (
select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 on RM20201.APFRDCNM = RM20101.DOCNUMBR AND RM20201.CUSTNMBR = RM20101.CUSTNMBR
WHERE RM20201.APPTOAMT = RM20101.ORTRXAMT AND RM20201.APFRDCDT < @AGE AND RM20201.APTODCDT < @AGE 
AND RM20201.ApplyFromGLPostDate < @AGE
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR
)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' 
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 < @AGE 
and RM20201.APTODCDT < @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
AND RM20101.DUEDATE = ''
THEN RM20101.CURTRXAM * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))  
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT,
RM20201.APFRMAPLYAMT
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 < @AGE 
and RM20201.APTODCDT < @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
AND RM20101.DUEDATE = ''
THEN RM20101.CURTRXAM * -1 ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 < @AGE 
and RM20201.APTODCDT < @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
AND RM20101.DUEDATE <> ''
THEN RM20101.CURTRXAM * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))  
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT,
RM20201.APFRMAPLYAMT
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 < @AGE 
and RM20201.APTODCDT < @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
AND RM20101.DUEDATE <> ''
THEN RM20101.CURTRXAM * -1 ELSE 0 END
<> 0
)
as WATERFALL 
)
AS WATERFALL2 
GROUP BY [CustomerID], [CustomerName]
)
select * from cte2
LEFT OUTER JOIN cte on cte2.CustomerID = cte.CustomerID
where cte.Cnt > 1
ORDER BY cte2.CustomerID, cte.CustomerID

Open in new window


 Bad-CTE-Results.xlsx
0
 
LVL 33

Expert Comment

by:ste5an
ID: 41818458
DECLARE @AGE DATETIME;
DECLARE @RUN DATETIME;
SET @AGE = '2015-09-30 00:00:00.000';
SET @RUN = '2016-07-31 00:00:00.000';

WITH    cte
          AS ( SELECT   RM20101.CUSTNMBR AS [CustomerID] ,
                        RM00101.CUSTNAME AS [CustomerName] ,
                        RM20101.DOCNUMBR AS [DocumentNumber] ,
                        COUNT(*) OVER ( PARTITION BY RM20101.CUSTNMBR,
                                        RM20201.APTODCNM, RM20201.APTODCDT ) AS Cnt ,
                        CASE WHEN RM20101.RMDTYPAL > 6
                                  AND RM20101.DOCDATE < @AGE
                                  AND RM20201.DATE1 > @AGE
                                  AND RM20201.APTODCDT > @AGE
                                  AND RM20201.APFRDCDT < @AGE
                                  AND RM20101.DOCNUMBR NOT IN (
                                  SELECT    RM20201.APFRDCNM
                                  FROM      RM20201
                                            INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR
                                                              AND RM20201.APTODCNM = RM20101.DOCNUMBR
                                                              AND RM20201.APPTOAMT = RM20101.ORTRXAMT
                                  GROUP BY  RM20201.APTODCNM ,
                                            RM20201.APTODCDT ,
                                            RM20201.APFRDCNM )
                                  AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                  AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
                                  AND RM20101.DUEDATE <> ''
                             THEN RM20201.APFRMAPLYAMT * -1
                             ELSE 0
                        END AS [OPEN A/R]
               FROM     RM20101
                        INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
                        INNER JOIN RM20201 ON RM20101.DOCNUMBR = RM20201.APFRDCNM
                                              AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
                        LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
               WHERE    ( RM20101.VOIDSTTS = 0 )
                        AND ( RM20101.CUSTNMBR IN ( '266267', '0100012160' ) )
               GROUP BY RM20101.CUSTNMBR ,
                        RM00101.CUSTNAME ,
                        RM00101.PYMTRMID ,
                        CN00500.CRDTMGR ,
                        RM00101.COMMENT1 ,
                        RM00101.COMMENT2 ,
                        RM20101.RMDTYPAL ,
                        RM20101.DUEDATE ,
                        RM20201.APFRMAPLYAMT ,
                        RM20101.DOCNUMBR ,
                        RM20201.APFRDCNM ,
                        RM20101.DOCDATE ,
                        RM20201.DATE1 ,
                        RM20201.APTODCDT ,
                        RM20201.APPTOAMT ,
                        RM20201.APTODCNM ,
                        RM20201.APFRDCDT ,
                        RM20101.ORTRXAMT ,
                        RM20101.CURTRXAM
               HAVING   CASE WHEN RM20101.RMDTYPAL > 6
                                  AND RM20101.DOCDATE < @AGE
                                  AND RM20201.DATE1 > @AGE
                                  AND RM20201.APTODCDT > @AGE
                                  AND RM20201.APFRDCDT < @AGE
                                  AND RM20101.DOCNUMBR NOT IN (
                                  SELECT    RM20201.APFRDCNM
                                  FROM      RM20201
                                            INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR
                                                              AND RM20201.APTODCNM = RM20101.DOCNUMBR
                                                              AND RM20201.APPTOAMT = RM20101.ORTRXAMT
                                  GROUP BY  RM20201.APTODCNM ,
                                            RM20201.APTODCDT ,
                                            RM20201.APFRDCNM )
                                  AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                  AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
                                  AND RM20201.APPTOAMT <> RM20101.ORTRXAMT
                                  AND RM20101.DUEDATE <> ''
                                  AND RM20101.DOCNUMBR = RM20201.APFRDCNM
                             THEN RM20201.APFRMAPLYAMT * -1
                             ELSE 0
                        END <> 0
             )
    SELECT  [CustomerID] ,
            [CustomerName] ,
            SUM([OPEN A/R]) AS [OPEN A/R]
    FROM    ( SELECT TOP 100 PERCENT
                        *
              FROM      ( SELECT    RM20101.CUSTNMBR AS [CustomerID] ,
                                    RM00101.CUSTNAME AS [CustomerName] ,
                                    RM20101.DOCNUMBR AS [DocumentNumber] ,
                                    CASE WHEN RM20101.RMDTYPAL < 7
                                              AND RM20101.DOCNUMBR NOT IN (
                                              SELECT    APTODCNM
                                              FROM      RM20201 )
                                              AND RM20101.DUEDATE < @AGE
                                              AND RM20101.DOCDATE < @AGE
                                              AND RM20101.ORTRXAMT = RM20101.CURTRXAM
                                         THEN RM20101.ORTRXAMT
                                         ELSE 0
                                    END AS [OPEN A/R]
                          FROM      RM20101
                                    INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
                                    LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                          WHERE     ( RM20101.VOIDSTTS = 0 )
                                    AND ( RM20101.CUSTNMBR IN ( ' 320651',
                                                              '00065', '00351',
                                                              '00370',
                                                              '0035829',
                                                              '0015200',
                                                              '0100010149',
                                                              '0100011881',
                                                              '0100011303',
                                                              '0100011766',
                                                              '0100012897',
                                                              '195145',
                                                              '0100550714',
                                                              '238624',
                                                              '0100013309',
                                                              '0100010453',
                                                              '266267',
                                                              '179520',
                                                              '0100012517',
                                                              '0100011519',
                                                              '0100012160' ) )
                          GROUP BY  RM20101.CUSTNMBR ,
                                    RM00101.CUSTNAME ,
                                    RM00101.PYMTRMID ,
                                    CN00500.CRDTMGR ,
                                    RM00101.COMMENT1 ,
                                    RM00101.COMMENT2 ,
                                    RM20101.DOCNUMBR ,
                                    RM20101.DOCDATE ,
                                    RM20101.RMDTYPAL ,
                                    RM20101.ORTRXAMT ,
                                    RM20101.CURTRXAM ,
                                    RM20101.DUEDATE
                          HAVING    CASE WHEN RM20101.RMDTYPAL < 7
                                              AND RM20101.DOCNUMBR NOT IN (
                                              SELECT    APTODCNM
                                              FROM      RM20201 )
                                              AND RM20101.DUEDATE < @AGE
                                              AND RM20101.DOCDATE < @AGE
                                              AND RM20101.ORTRXAMT = RM20101.CURTRXAM
                                         THEN RM20101.ORTRXAMT
                                         ELSE 0
                                    END <> 0
                          UNION
                          SELECT    RM20101.CUSTNMBR AS [CustomerID] ,
                                    RM00101.CUSTNAME AS [CustomerName] ,
                                    RM20101.DOCNUMBR AS [DocumentNumber] ,
                                    CASE WHEN RM20101.RMDTYPAL < 7
                                              AND RM20101.DOCNUMBR IN ( SELECT
                                                              APTODCNM
                                                              FROM
                                                              RM20201 )
                                              AND RM20201.DATE1 > @AGE
                                              AND RM20201.APTODCDT <= @AGE
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                         THEN RM20101.ORTRXAMT
                                         ELSE RM20101.CURTRXAM
                                    END AS [OPEN A/R]
                          FROM      RM20101
                                    INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
                                    INNER JOIN RM20201 ON RM20101.DOCNUMBR = RM20201.APTODCNM
                                                          AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
                                    LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                          WHERE     ( RM20101.VOIDSTTS = 0 )
                                    AND ( RM20101.CUSTNMBR IN ( ' 320651',
                                                              '00065', '00351',
                                                              '00370',
                                                              '0035829',
                                                              '0015200',
                                                              '0100010149',
                                                              '0100011881',
                                                              '0100011303',
                                                              '0100011766',
                                                              '0100012897',
                                                              '195145',
                                                              '0100550714',
                                                              '238624',
                                                              '0100013309',
                                                              '0100010453',
                                                              '266267',
                                                              '179520',
                                                              '0100012517',
                                                              '0100011519',
                                                              '0100012160' ) )
                                    AND ( RM20101.ORTRXAMT = RM20201.APPTOAMT )
                          GROUP BY  RM20101.CUSTNMBR ,
                                    RM00101.CUSTNAME ,
                                    RM00101.PYMTRMID ,
                                    CN00500.CRDTMGR ,
                                    RM00101.COMMENT1 ,
                                    RM00101.COMMENT2 ,
                                    RM20201.APTODCDT ,
                                    RM20201.APTODCNM ,
                                    RM20101.DOCNUMBR ,
                                    RM20101.DOCDATE ,
                                    RM20201.APFRDCDT ,
                                    RM20101.RMDTYPAL ,
                                    RM20201.DATE1 ,
                                    RM20101.ORTRXAMT ,
                                    RM20101.CURTRXAM
                          HAVING    CASE WHEN RM20101.RMDTYPAL < 7
                                              AND RM20101.DOCNUMBR IN ( SELECT
                                                              APTODCNM
                                                              FROM
                                                              RM20201 )
                                              AND RM20201.DATE1 > @AGE
                                              AND RM20201.APTODCDT <= @AGE
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                         THEN RM20101.ORTRXAMT
                                         ELSE RM20101.CURTRXAM
                                    END <> 0
                          UNION
                          SELECT    RM20101.CUSTNMBR AS [CustomerID] ,
                                    RM00101.CUSTNAME AS [CustomerName] ,
                                    RM20101.DOCNUMBR AS [DocumentNumber] ,
                                    CASE WHEN RM20101.RMDTYPAL < 7
                                              AND RM20101.DOCNUMBR IN ( SELECT
                                                              APTODCNM
                                                              FROM
                                                              RM20201 )
                                              AND RM20201.DATE1 > @AGE
                                              AND RM20201.APTODCDT <= @AGE
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                         THEN RM20201.APPTOAMT
                                         ELSE 0
                                    END AS [OPEN A/R]
                          FROM      RM20101
                                    INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
                                    INNER JOIN RM20201 ON RM20101.DOCNUMBR = RM20201.APTODCNM
                                                          AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
                                    LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                          WHERE     ( RM20101.VOIDSTTS = 0 )
                                    AND ( RM20101.CUSTNMBR IN ( ' 320651',
                                                              '00065', '00351',
                                                              '00370',
                                                              '0035829',
                                                              '0015200',
                                                              '0100010149',
                                                              '0100011881',
                                                              '0100011303',
                                                              '0100011766',
                                                              '0100012897',
                                                              '195145',
                                                              '0100550714',
                                                              '238624',
                                                              '0100013309',
                                                              '0100010453',
                                                              '266267',
                                                              '179520',
                                                              '0100012517',
                                                              '0100011519',
                                                              '0100012160' ) )
                                    AND ( RM20101.ORTRXAMT <> RM20201.APPTOAMT )
                          GROUP BY  RM20101.CUSTNMBR ,
                                    RM00101.CUSTNAME ,
                                    RM00101.PYMTRMID ,
                                    CN00500.CRDTMGR ,
                                    RM00101.COMMENT1 ,
                                    RM00101.COMMENT2 ,
                                    RM20201.APTODCDT ,
                                    RM20201.APTODCNM ,
                                    RM20101.DOCNUMBR ,
                                    RM20101.DOCDATE ,
                                    RM20201.APFRDCDT ,
                                    RM20101.RMDTYPAL ,
                                    RM20201.DATE1 ,
                                    RM20101.ORTRXAMT ,
                                    RM20101.CURTRXAM ,
                                    RM20201.APPTOAMT
                          HAVING    CASE WHEN RM20101.RMDTYPAL < 7
                                              AND RM20101.DOCNUMBR IN ( SELECT
                                                              APTODCNM
                                                              FROM
                                                              RM20201 )
                                              AND RM20201.DATE1 > @AGE
                                              AND RM20201.APTODCDT <= @AGE
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                         THEN RM20201.APPTOAMT
                                         ELSE 0
                                    END <> 0
                          UNION
                          SELECT    RM20101.CUSTNMBR AS [CustomerID] ,
                                    RM00101.CUSTNAME AS [CustomerName] ,
                                    RM20101.DOCNUMBR AS [DocumentNumber] ,
                                    CASE WHEN RM20101.RMDTYPAL > 6
                                              AND RM20101.DOCDATE < @AGE
                                              AND RM20201.DATE1 > @AGE
                                              AND RM20101.DOCNUMBR IN ( SELECT
                                                              APFRDCNM
                                                              FROM
                                                              RM20201 )
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                              AND RM20101.DUEDATE = ''
                                         THEN RM20201.APPTOAMT * -1
                                         ELSE 0
                                    END AS [OPEN A/R]
                          FROM      RM20101
                                    INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
                                    INNER JOIN RM20201 ON RM20101.DOCNUMBR = RM20201.APFRDCNM
                                                          AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
                                    LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                          WHERE     ( RM20101.VOIDSTTS = 0 )
                                    AND ( RM20101.CUSTNMBR IN ( ' 320651',
                                                              '00065', '00351',
                                                              '00370',
                                                              '0035829',
                                                              '0015200',
                                                              '0100010149',
                                                              '0100011881',
                                                              '0100011303',
                                                              '0100011766',
                                                              '0100012897',
                                                              '195145',
                                                              '0100550714',
                                                              '238624',
                                                              '0100013309',
                                                              '0100010453',
                                                              '266267',
                                                              '179520',
                                                              '0100012517',
                                                              '0100011519',
                                                              '0100012160' ) )
                          GROUP BY  RM20101.CUSTNMBR ,
                                    RM00101.CUSTNAME ,
                                    RM00101.PYMTRMID ,
                                    CN00500.CRDTMGR ,
                                    RM00101.COMMENT1 ,
                                    RM00101.COMMENT2 ,
                                    RM20201.APTODCDT ,
                                    RM20201.APTODCNM ,
                                    RM20101.DOCNUMBR ,
                                    RM20101.DOCDATE ,
                                    RM20201.APFRDCDT ,
                                    RM20101.RMDTYPAL ,
                                    RM20201.DATE1 ,
                                    RM20101.ORTRXAMT ,
                                    RM20101.CURTRXAM ,
                                    RM20101.DUEDATE ,
                                    RM20201.APPTOAMT
                          HAVING    CASE WHEN RM20101.RMDTYPAL > 6
                                              AND RM20101.DOCDATE < @AGE
                                              AND RM20201.DATE1 > @AGE
                                              AND RM20101.DOCNUMBR IN ( SELECT
                                                              APFRDCNM
                                                              FROM
                                                              RM20201 )
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                              AND RM20101.DUEDATE = ''
                                         THEN RM20201.APPTOAMT * -1
                                         ELSE 0
                                    END <> 0
                          UNION
                          SELECT    RM20101.CUSTNMBR AS [CustomerID] ,
                                    RM00101.CUSTNAME AS [CustomerName] ,
                                    RM20101.DOCNUMBR AS [DocumentNumber] ,
                                    CASE WHEN RM20101.RMDTYPAL > 6
                                              AND RM20101.DOCDATE < @AGE
                                              AND RM20101.ORTRXAMT = RM20101.CURTRXAM
                                              AND RM20101.DOCNUMBR NOT IN (
                                              SELECT    APFRDCNM
                                              FROM      RM20201 )
                                         THEN RM20101.ORTRXAMT * -1
                                         ELSE 0
                                    END AS [OPEN A/R]
                          FROM      RM20101
                                    INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
                                    LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                          WHERE     ( RM20101.VOIDSTTS = 0 )
                                    AND ( RM20101.CUSTNMBR IN ( ' 320651',
                                                              '00065', '00351',
                                                              '00370',
                                                              '0035829',
                                                              '0015200',
                                                              '0100010149',
                                                              '0100011881',
                                                              '0100011303',
                                                              '0100011766',
                                                              '0100012897',
                                                              '195145',
                                                              '0100550714',
                                                              '238624',
                                                              '0100013309',
                                                              '0100010453',
                                                              '266267',
                                                              '179520',
                                                              '0100012517',
                                                              '0100011519',
                                                              '0100012160' ) )
                          GROUP BY  RM20101.CUSTNMBR ,
                                    RM00101.CUSTNAME ,
                                    RM00101.PYMTRMID ,
                                    CN00500.CRDTMGR ,
                                    RM00101.COMMENT1 ,
                                    RM00101.COMMENT2 ,
                                    RM20101.DOCNUMBR ,
                                    RM20101.DOCDATE ,
                                    RM20101.RMDTYPAL ,
                                    RM20101.ORTRXAMT ,
                                    RM20101.CURTRXAM
                          HAVING    CASE WHEN RM20101.RMDTYPAL > 6
                                              AND RM20101.DOCDATE < @AGE
                                              AND RM20101.ORTRXAMT = RM20101.CURTRXAM
                                              AND RM20101.DOCNUMBR NOT IN (
                                              SELECT    APFRDCNM
                                              FROM      RM20201 )
                                         THEN RM20101.ORTRXAMT * -1
                                         ELSE 0
                                    END <> 0
                          UNION
                          SELECT    RM20101.CUSTNMBR AS [CustomerID] ,
                                    RM00101.CUSTNAME AS [CustomerName] ,
                                    RM20101.DOCNUMBR AS [DocumentNumber] ,
                                    CASE WHEN RM20101.RMDTYPAL > 6
                                              AND RM20201.APTODCDT > @AGE
                                              AND RM20201.DATE1 > @AGE
                                              AND RM20201.APFRDCDT < @AGE
                                              AND RM20101.DOCNUMBR IN (
                                              SELECT    RM20201.APFRDCNM
                                              FROM      RM20201
                                                        INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR
                                                              AND RM20201.APTODCNM = RM20101.DOCNUMBR
                                              WHERE     RM20201.APPTOAMT = RM20101.ORTRXAMT
                                              GROUP BY  RM20201.APFRDCNM ,
                                                        RM20101.DOCNUMBR ,
                                                        RM20201.APPTOAMT
                                              HAVING    COUNT(RM20201.APTODCNM) = 1 )
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                              AND RM20101.DUEDATE <> ''
                                              AND RM20101.RMDTYPAL <> RM20201.APTODCTY
                                         THEN RM20201.APPTOAMT * -1
                                         ELSE 0
                                    END AS [OPEN A/R]
                          FROM      RM20101
                                    INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
                                    INNER JOIN RM20201 ON RM20101.DOCNUMBR = RM20201.APFRDCNM
                                                          AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
                                    LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                          WHERE     ( RM20101.VOIDSTTS = 0 )
                                    AND ( RM20101.CUSTNMBR IN ( ' 320651',
                                                              '00065', '00351',
                                                              '00370',
                                                              '0035829',
                                                              '0015200',
                                                              '0100010149',
                                                              '0100011881',
                                                              '0100011303',
                                                              '0100011766',
                                                              '0100012897',
                                                              '195145',
                                                              '0100550714',
                                                              '238624',
                                                              '0100013309',
                                                              '0100010453',
                                                              '266267',
                                                              '179520',
                                                              '0100012517',
                                                              '0100011519',
                                                              '0100012160' ) )
                          GROUP BY  RM20101.CUSTNMBR ,
                                    RM00101.CUSTNAME ,
                                    RM00101.PYMTRMID ,
                                    CN00500.CRDTMGR ,
                                    RM00101.COMMENT1 ,
                                    RM00101.COMMENT2 ,
                                    RM20201.APTODCDT ,
                                    RM20201.APTODCNM ,
                                    RM20101.DOCNUMBR ,
                                    RM20101.DOCDATE ,
                                    RM20201.APFRDCDT ,
                                    RM20101.RMDTYPAL ,
                                    RM20201.DATE1 ,
                                    RM20101.ORTRXAMT ,
                                    RM20101.CURTRXAM ,
                                    RM20101.DUEDATE ,
                                    RM20201.APPTOAMT ,
                                    RM20201.APTODCTY
                          HAVING    CASE WHEN RM20101.RMDTYPAL > 6
                                              AND RM20201.APTODCDT > @AGE
                                              AND RM20201.DATE1 > @AGE
                                              AND RM20201.APFRDCDT < @AGE
                                              AND RM20101.DOCNUMBR IN (
                                              SELECT    RM20201.APFRDCNM
                                              FROM      RM20201
                                                        INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR
                                                              AND RM20201.APTODCNM = RM20101.DOCNUMBR
                                              WHERE     RM20201.APPTOAMT = RM20101.ORTRXAMT
                                              GROUP BY  RM20201.APFRDCNM ,
                                                        RM20101.DOCNUMBR ,
                                                        RM20201.APPTOAMT
                                              HAVING    COUNT(RM20201.APTODCNM) = 1 )
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                              AND RM20101.DUEDATE <> ''
                                              AND RM20101.RMDTYPAL <> RM20201.APTODCTY
                                         THEN RM20201.APPTOAMT * -1
                                         ELSE 0
                                    END <> 0
                          UNION
                          SELECT    RM20101.CUSTNMBR AS [CustomerID] ,
                                    RM00101.CUSTNAME AS [CustomerName] ,
                                    RM20101.DOCNUMBR AS [DocumentNumber] ,
                                    CASE WHEN RM20101.RMDTYPAL > 6
                                              AND RM20101.DOCDATE < @AGE
                                              AND RM20201.DATE1 > @AGE
                                              AND RM20101.DOCNUMBR IN (
                                              SELECT    RM20201.APFRDCNM
                                              FROM      RM20201
                                                        INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR
                                                              AND RM20201.APFRDCNM = RM20101.DOCNUMBR
                                              GROUP BY  RM20201.APFRDCNM ,
                                                        RM20201.CUSTNMBR
                                              HAVING    COUNT(RM20201.APTODCNM) = 1 )
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                              AND RM20101.DUEDATE <> ''
                                              AND RM20201.APPTOAMT <> RM20101.ORTRXAMT
                                         THEN RM20101.ORTRXAMT * -1
                                         ELSE 0
                                    END AS [OPEN A/R]
                          FROM      RM20101
                                    INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
                                    INNER JOIN RM20201 ON RM20101.DOCNUMBR = RM20201.APFRDCNM
                                                          AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
                                    LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                          WHERE     ( RM20101.VOIDSTTS = 0 )
                                    AND ( RM20101.CUSTNMBR IN ( ' 320651',
                                                              '00065', '00351',
                                                              '00370',
                                                              '0035829',
                                                              '0015200',
                                                              '0100010149',
                                                              '0100011881',
                                                              '0100011303',
                                                              '0100011766',
                                                              '0100012897',
                                                              '195145',
                                                              '0100550714',
                                                              '238624',
                                                              '0100013309',
                                                              '0100010453',
                                                              '266267',
                                                              '179520',
                                                              '0100012517',
                                                              '0100011519',
                                                              '0100012160' ) )
                          GROUP BY  RM20101.CUSTNMBR ,
                                    RM00101.CUSTNAME ,
                                    RM00101.PYMTRMID ,
                                    CN00500.CRDTMGR ,
                                    RM00101.COMMENT1 ,
                                    RM00101.COMMENT2 ,
                                    RM20201.APTODCDT ,
                                    RM20201.APTODCNM ,
                                    RM20101.DOCNUMBR ,
                                    RM20101.DOCDATE ,
                                    RM20201.APFRDCDT ,
                                    RM20101.RMDTYPAL ,
                                    RM20201.DATE1 ,
                                    RM20101.ORTRXAMT ,
                                    RM20101.CURTRXAM ,
                                    RM20101.DUEDATE ,
                                    RM20201.APPTOAMT
                          HAVING    CASE WHEN RM20101.RMDTYPAL > 6
                                              AND RM20101.DOCDATE < @AGE
                                              AND RM20201.DATE1 > @AGE
                                              AND RM20101.DOCNUMBR IN (
                                              SELECT    RM20201.APFRDCNM
                                              FROM      RM20201
                                                        INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR
                                                              AND RM20201.APFRDCNM = RM20101.DOCNUMBR
                                              GROUP BY  RM20201.APFRDCNM ,
                                                        RM20201.CUSTNMBR
                                              HAVING    COUNT(RM20201.APTODCNM) = 1 )
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                              AND RM20101.DUEDATE <> ''
                                              AND RM20201.APPTOAMT <> RM20101.ORTRXAMT
                                         THEN RM20101.ORTRXAMT * -1
                                         ELSE 0
                                    END <> 0
                          UNION
                          SELECT    RM20101.CUSTNMBR AS [CustomerID] ,
                                    RM00101.CUSTNAME AS [CustomerName] ,
                                    RM20101.DOCNUMBR AS [DocumentNumber] ,
                                    CASE WHEN RM20101.RMDTYPAL > 6
                                              AND RM20101.DOCDATE < @AGE
                                              AND RM20201.DATE1 > @AGE
                                              AND RM20101.DOCNUMBR IN (
                                              SELECT    RM20201.APFRDCNM
                                              FROM      RM20201
                                                        INNER JOIN RM20101 ON RM20201.APFRDCNM = RM20101.DOCNUMBR
                                                              AND RM20201.CUSTNMBR = RM20101.CUSTNMBR
                                              WHERE     RM20201.APPTOAMT = RM20101.ORTRXAMT
                                                        AND RM20201.APFRDCDT < @AGE
                                                        AND RM20201.APTODCDT < @AGE
                                                        AND RM20201.ApplyFromGLPostDate < @AGE
                                              GROUP BY  RM20201.APFRDCNM ,
                                                        RM20201.CUSTNMBR )
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                              AND RM20101.DUEDATE <> ''
                                         THEN RM20201.APPTOAMT * -1
                                         ELSE 0
                                    END AS [OPEN A/R]
                          FROM      RM20101
                                    INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
                                    INNER JOIN RM20201 ON RM20101.DOCNUMBR = RM20201.APFRDCNM
                                                          AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
                                    LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                          WHERE     ( RM20101.VOIDSTTS = 0 )
                                    AND ( RM20101.CUSTNMBR IN ( ' 320651',
                                                              '00065', '00351',
                                                              '00370',
                                                              '0035829',
                                                              '0015200',
                                                              '0100010149',
                                                              '0100011881',
                                                              '0100011303',
                                                              '0100011766',
                                                              '0100012897',
                                                              '195145',
                                                              '0100550714',
                                                              '238624',
                                                              '0100013309',
                                                              '0100010453',
                                                              '266267',
                                                              '179520',
                                                              '0100012517',
                                                              '0100011519',
                                                              '0100012160' ) )
                          GROUP BY  RM20101.CUSTNMBR ,
                                    RM00101.CUSTNAME ,
                                    RM00101.PYMTRMID ,
                                    CN00500.CRDTMGR ,
                                    RM00101.COMMENT1 ,
                                    RM00101.COMMENT2 ,
                                    RM20201.APTODCDT ,
                                    RM20201.APTODCNM ,
                                    RM20101.DOCNUMBR ,
                                    RM20101.DOCDATE ,
                                    RM20201.APFRDCDT ,
                                    RM20101.RMDTYPAL ,
                                    RM20201.DATE1 ,
                                    RM20101.ORTRXAMT ,
                                    RM20101.CURTRXAM ,
                                    RM20101.DUEDATE ,
                                    RM20201.APPTOAMT ,
                                    RM20201.APTODCTY
                          HAVING    CASE WHEN RM20101.RMDTYPAL > 6
                                              AND RM20101.DOCDATE < @AGE
                                              AND RM20201.DATE1 > @AGE
                                              AND RM20101.DOCNUMBR IN (
                                              SELECT    RM20201.APFRDCNM
                                              FROM      RM20201
                                                        INNER JOIN RM20101 ON RM20201.APFRDCNM = RM20101.DOCNUMBR
                                                              AND RM20201.CUSTNMBR = RM20101.CUSTNMBR
                                              WHERE     RM20201.APPTOAMT = RM20101.ORTRXAMT
                                                        AND RM20201.APFRDCDT < @AGE
                                                        AND RM20201.APTODCDT < @AGE
                                                        AND RM20201.ApplyFromGLPostDate < @AGE
                                              GROUP BY  RM20201.APFRDCNM ,
                                                        RM20201.CUSTNMBR )
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                              AND RM20101.DUEDATE <> ''
                                         THEN RM20201.APPTOAMT * -1
                                         ELSE 0
                                    END <> 0
                          UNION
                          SELECT    RM20101.CUSTNMBR AS [CustomerID] ,
                                    RM00101.CUSTNAME AS [CustomerName] ,
                                    RM20101.DOCNUMBR AS [DocumentNumber] ,
                                    CASE WHEN RM20101.RMDTYPAL > 6
                                              AND RM20101.DOCDATE < @AGE
                                              AND RM20201.DATE1 < @AGE
                                              AND RM20201.APTODCDT < @AGE
                                              AND RM20201.APFRDCDT < @AGE
                                              AND RM20101.DOCNUMBR IN ( SELECT
                                                              APFRDCNM
                                                              FROM
                                                              RM20201 )
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                              AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
                                              AND RM20101.DUEDATE = ''
                                         THEN RM20101.CURTRXAM * -1
                                         ELSE 0
                                    END AS [OPEN A/R]
                          FROM      RM20101
                                    INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
                                    INNER JOIN RM20201 ON RM20101.DOCNUMBR = RM20201.APFRDCNM
                                                          AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
                                    LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                          WHERE     ( RM20101.VOIDSTTS = 0 )
                                    AND ( RM20101.CUSTNMBR IN ( ' 320651',
                                                              '00065', '00351',
                                                              '00370',
                                                              '0035829',
                                                              '0015200',
                                                              '0100010149',
                                                              '0100011881',
                                                              '0100011303',
                                                              '0100011766',
                                                              '0100012897',
                                                              '195145',
                                                              '0100550714',
                                                              '238624',
                                                              '0100013309',
                                                              '0100010453',
                                                              '266267',
                                                              '179520',
                                                              '0100012517',
                                                              '0100011519',
                                                              '0100012160' ) )
                          GROUP BY  RM20101.CUSTNMBR ,
                                    RM00101.CUSTNAME ,
                                    RM00101.PYMTRMID ,
                                    CN00500.CRDTMGR ,
                                    RM00101.COMMENT1 ,
                                    RM00101.COMMENT2 ,
                                    RM20201.APTODCDT ,
                                    RM20201.APTODCNM ,
                                    RM20101.DOCNUMBR ,
                                    RM20101.DOCDATE ,
                                    RM20201.APFRDCDT ,
                                    RM20101.RMDTYPAL ,
                                    RM20201.DATE1 ,
                                    RM20101.ORTRXAMT ,
                                    RM20101.CURTRXAM ,
                                    RM20101.DUEDATE ,
                                    RM20201.APPTOAMT ,
                                    RM20201.APFRMAPLYAMT
                          HAVING    CASE WHEN RM20101.RMDTYPAL > 6
                                              AND RM20101.DOCDATE < @AGE
                                              AND RM20201.DATE1 < @AGE
                                              AND RM20201.APTODCDT < @AGE
                                              AND RM20201.APFRDCDT < @AGE
                                              AND RM20101.DOCNUMBR IN ( SELECT
                                                              APFRDCNM
                                                              FROM
                                                              RM20201 )
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                              AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
                                              AND RM20101.DUEDATE = ''
                                         THEN RM20101.CURTRXAM * -1
                                         ELSE 0
                                    END <> 0
                          UNION
                          SELECT    RM20101.CUSTNMBR AS [CustomerID] ,
                                    RM00101.CUSTNAME AS [CustomerName] ,
                                    RM20101.DOCNUMBR AS [DocumentNumber] ,
                                    CASE WHEN RM20101.RMDTYPAL > 6
                                              AND RM20101.DOCDATE < @AGE
                                              AND RM20201.DATE1 < @AGE
                                              AND RM20201.APTODCDT < @AGE
                                              AND RM20201.APFRDCDT < @AGE
                                              AND RM20101.DOCNUMBR IN ( SELECT
                                                              APFRDCNM
                                                              FROM
                                                              RM20201 )
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                              AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
                                              AND RM20101.DUEDATE <> ''
                                         THEN RM20101.CURTRXAM * -1
                                         ELSE 0
                                    END AS [OPEN A/R]
                          FROM      RM20101
                                    INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
                                    INNER JOIN RM20201 ON RM20101.DOCNUMBR = RM20201.APFRDCNM
                                                          AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
                                    LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                          WHERE     ( RM20101.VOIDSTTS = 0 )
                                    AND ( RM20101.CUSTNMBR IN ( ' 320651',
                                                              '00065', '00351',
                                                              '00370',
                                                              '0035829',
                                                              '0015200',
                                                              '0100010149',
                                                              '0100011881',
                                                              '0100011303',
                                                              '0100011766',
                                                              '0100012897',
                                                              '195145',
                                                              '0100550714',
                                                              '238624',
                                                              '0100013309',
                                                              '0100010453',
                                                              '266267',
                                                              '179520',
                                                              '0100012517',
                                                              '0100011519',
                                                              '0100012160' ) )
                          GROUP BY  RM20101.CUSTNMBR ,
                                    RM00101.CUSTNAME ,
                                    RM00101.PYMTRMID ,
                                    CN00500.CRDTMGR ,
                                    RM00101.COMMENT1 ,
                                    RM00101.COMMENT2 ,
                                    RM20201.APTODCDT ,
                                    RM20201.APTODCNM ,
                                    RM20101.DOCNUMBR ,
                                    RM20101.DOCDATE ,
                                    RM20201.APFRDCDT ,
                                    RM20101.RMDTYPAL ,
                                    RM20201.DATE1 ,
                                    RM20101.ORTRXAMT ,
                                    RM20101.CURTRXAM ,
                                    RM20101.DUEDATE ,
                                    RM20201.APPTOAMT ,
                                    RM20201.APFRMAPLYAMT
                          HAVING    CASE WHEN RM20101.RMDTYPAL > 6
                                              AND RM20101.DOCDATE < @AGE
                                              AND RM20201.DATE1 < @AGE
                                              AND RM20201.APTODCDT < @AGE
                                              AND RM20201.APFRDCDT < @AGE
                                              AND RM20101.DOCNUMBR IN ( SELECT
                                                              APFRDCNM
                                                              FROM
                                                              RM20201 )
                                              AND RM20101.ORTRXAMT <> RM20101.CURTRXAM
                                              AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
                                              AND RM20101.DUEDATE <> ''
                                         THEN RM20101.CURTRXAM * -1
                                         ELSE 0
                                    END <> 0
                          UNION
-- Don't use the asterisk (*), specfiy the columns instead.
                          SELECT    *
                          FROM      cte
                          WHERE     Cnt > 1
                        ) AS WATERFALL
            ) AS WATERFALL2
    GROUP BY [CustomerID] ,
            [CustomerName]
    ORDER BY [CustomerID];

Open in new window

0
 

Author Comment

by:John Ellis
ID: 41818496
Thanks, so much!  That makes sense.

But, I'm getting the following error:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

I do have the "Cnt" field, in each clause.  Truthfully, though, I don't want it to display.  It's just a field that I created, in order to solve an issue that I had with the way data was at first appearing in cte.

So, the conclusion of the combined query is shown at the end of this posting.  What am I doing wrong?

John

UNION
select [CustomerID], [CustomerName], [OPEN A/R] 
from cte 
where Cnt > 1
)
as WATERFALL 
)
AS WATERFALL2 
GROUP BY [CustomerID], [CustomerName]
ORDER BY [CustomerID]

Open in new window

0
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 41818519
Well, you need four columns ([CustomerID] , [CustomerName] , [DocumentNumber] , [OPEN A/R]) from your CTE..
0
 

Author Comment

by:John Ellis
ID: 41818538
Thank you, so much, Ste5an!!!

That was exactly what I needed!  :)

For future reference, the final code is below.  Again, thank you!

John

DECLARE @AGE DATETIME;
DECLARE @RUN DATETIME;
SET @AGE = '2015-09-30 00:00:00.000';
SET @RUN = '2016-07-31 00:00:00.000';

with cte as 
(
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
COUNT(*) OVER (PARTITION BY RM20101.CUSTNMBR, RM20201.APTODCNM, RM20201.APTODCDT) AS Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT > @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR not in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
and RM20201.APPTOAMT = RM20101.ORTRXAMT 
GROUP BY RM20201.APTODCNM, RM20201.APTODCDT, RM20201.APFRDCNM)
AND
RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT 
AND RM20101.DUEDATE <> ''
THEN RM20201.APFRMAPLYAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))  
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20101.RMDTYPAL, RM20101.DUEDATE, RM20201.APFRMAPLYAMT, RM20101.DOCNUMBR, RM20201.APFRDCNM, RM20101.DOCDATE,
RM20201.DATE1, RM20201.APTODCDT, RM20201.APPTOAMT, RM20201.APTODCNM, RM20201.APFRDCDT, RM20101.ORTRXAMT, RM20101.CURTRXAM
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT > @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR not in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
and RM20201.APPTOAMT = RM20101.ORTRXAMT 
GROUP BY RM20201.APTODCNM, RM20201.APTODCDT, RM20201.APFRDCNM)
and
RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT and RM20201.APPTOAMT <> RM20101.ORTRXAMT 
AND RM20101.DUEDATE <> '' AND RM20101.DOCNUMBR = RM20201.APFRDCNM
THEN RM20201.APFRMAPLYAMT * -1 ELSE 0 END
<> 0)
SELECT [CustomerID], [CustomerName], SUM([OPEN A/R]) as [OPEN A/R] FROM
(
SELECT TOP 100 PERCENT * FROM
(
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL < 7 and RM20101.DOCNUMBR NOT IN (select APTODCNM from RM20201)
and RM20101.DUEDATE < @AGE 
and RM20101.DOCDATE < @AGE 
and RM20101.ORTRXAMT = RM20101.CURTRXAM
THEN RM20101.ORTRXAMT ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160')) 
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20101.RMDTYPAL, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE
HAVING 
CASE WHEN RM20101.RMDTYPAL < 7 and RM20101.DOCNUMBR NOT IN (select APTODCNM from RM20201)
and RM20101.DUEDATE < @AGE 
and RM20101.DOCDATE < @AGE 
and RM20101.ORTRXAMT = RM20101.CURTRXAM
THEN RM20101.ORTRXAMT ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL < 7 AND RM20101.DOCNUMBR in (select APTODCNM from RM20201) and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT <= @AGE and RM20101.ORTRXAMT <> RM20101.CURTRXAM
THEN RM20101.ORTRXAMT ELSE RM20101.CURTRXAM END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APTODCNM 
AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160')) 
AND (RM20101.ORTRXAMT = RM20201.APPTOAMT)
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM
HAVING 
CASE WHEN RM20101.RMDTYPAL < 7 AND RM20101.DOCNUMBR in (select APTODCNM from RM20201) and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT <= @AGE and RM20101.ORTRXAMT <> RM20101.CURTRXAM
THEN RM20101.ORTRXAMT ELSE RM20101.CURTRXAM END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN 
RM20101.RMDTYPAL < 7 AND RM20101.DOCNUMBR in (select APTODCNM from RM20201) and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT <= @AGE and RM20101.ORTRXAMT <> RM20101.CURTRXAM
THEN RM20201.APPTOAMT ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APTODCNM 
AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))
AND (RM20101.ORTRXAMT <> RM20201.APPTOAMT)
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, 
RM20201.APPTOAMT
HAVING 
CASE WHEN 
RM20101.RMDTYPAL < 7 AND RM20101.DOCNUMBR in (select APTODCNM from RM20201) and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT <= @AGE and RM20101.ORTRXAMT <> RM20101.CURTRXAM
THEN RM20201.APPTOAMT ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE = ''
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))   
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE = ''
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20101.ORTRXAMT = RM20101.CURTRXAM
AND RM20101.DOCNUMBR not in (select APFRDCNM from RM20201)
THEN RM20101.ORTRXAMT * -1 
ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160')) 
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20101.RMDTYPAL, RM20101.ORTRXAMT, RM20101.CURTRXAM
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20101.ORTRXAMT = RM20101.CURTRXAM
AND RM20101.DOCNUMBR not in (select APFRDCNM from RM20201)
THEN RM20101.ORTRXAMT * -1 
ELSE 0 END
<> 0 
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName],  RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 
and RM20201.APTODCDT > @AGE and RM20201.DATE1 > @AGE and RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
where RM20201.APPTOAMT = RM20101.ORTRXAMT 
GROUP BY RM20201.APFRDCNM, RM20101.DOCNUMBR, RM20201.APPTOAMT
HAVING COUNT(RM20201.APTODCNM) = 1) and RM20101.ORTRXAMT 
<> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' and RM20101.RMDTYPAL <> RM20201.APTODCTY
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT, RM20201.APTODCTY
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 
and RM20201.APTODCDT > @AGE and RM20201.DATE1 > @AGE and RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
where RM20201.APPTOAMT = RM20101.ORTRXAMT 
GROUP BY RM20201.APFRDCNM, RM20101.DOCNUMBR, RM20201.APPTOAMT
HAVING COUNT(RM20201.APTODCNM) = 1) and RM20101.ORTRXAMT 
<> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' and RM20101.RMDTYPAL <> RM20201.APTODCTY
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APFRDCNM = RM20101.DOCNUMBR
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR
HAVING COUNT(RM20201.APTODCNM) = 1)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' AND RM20201.APPTOAMT <> RM20101.ORTRXAMT
THEN RM20101.ORTRXAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APFRDCNM = RM20101.DOCNUMBR
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR
HAVING COUNT(RM20201.APTODCNM) = 1)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' AND RM20201.APPTOAMT <> RM20101.ORTRXAMT
THEN RM20101.ORTRXAMT * -1 ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (
select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 on RM20201.APFRDCNM = RM20101.DOCNUMBR AND RM20201.CUSTNMBR = RM20101.CUSTNMBR
WHERE RM20201.APPTOAMT = RM20101.ORTRXAMT AND RM20201.APFRDCDT < @AGE AND RM20201.APTODCDT < @AGE 
AND RM20201.ApplyFromGLPostDate < @AGE
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR
)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' 
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160')) 
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, 
RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT,
RM20201.APTODCTY
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
AND RM20101.DOCNUMBR in (
select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 on RM20201.APFRDCNM = RM20101.DOCNUMBR AND RM20201.CUSTNMBR = RM20101.CUSTNMBR
WHERE RM20201.APPTOAMT = RM20101.ORTRXAMT AND RM20201.APFRDCDT < @AGE AND RM20201.APTODCDT < @AGE 
AND RM20201.ApplyFromGLPostDate < @AGE
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR
)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' 
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 < @AGE 
and RM20201.APTODCDT < @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
AND RM20101.DUEDATE = ''
THEN RM20101.CURTRXAM * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))  
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT,
RM20201.APFRMAPLYAMT
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 < @AGE 
and RM20201.APTODCDT < @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
AND RM20101.DUEDATE = ''
THEN RM20101.CURTRXAM * -1 ELSE 0 END
<> 0
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 < @AGE 
and RM20201.APTODCDT < @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
AND RM20101.DUEDATE <> ''
THEN RM20101.CURTRXAM * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))  
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT,
RM20201.APFRMAPLYAMT
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 < @AGE 
and RM20201.APTODCDT < @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
AND RM20101.DUEDATE <> ''
THEN RM20101.CURTRXAM * -1 ELSE 0 END
<> 0
UNION
select [CustomerID], [CustomerName], [DocumentNumber], Cnt, [OPEN A/R]
from cte 
where Cnt > 1
)
as WATERFALL 
)
AS WATERFALL2 
GROUP BY [CustomerID], [CustomerName]
ORDER BY [CustomerID]

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question