Solved

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

Posted on 2016-09-27
9
64 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 32

Expert Comment

by:Stefan Hoffmann
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
 
LVL 32

Expert Comment

by:Stefan Hoffmann
ID: 41818420
What's the problem?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 32

Expert Comment

by:Stefan Hoffmann
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 32

Accepted Solution

by:
Stefan Hoffmann 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now