John Ellis
asked on
T-SQL: How to Place a CTE Clause within a Query Containing "UNION" Statements
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
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;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]
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.
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.
ASKER
I cannot get either of these two solutions working.
John
John
What's the problem?
ASKER
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
(
Bad-CTE-Results.xlsx
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.CustomerIDBad-CTE-Results.xlsx
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];ASKER
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
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]ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, so much, Ste5an!!!
That was exactly what I needed! :)
For future reference, the final code is below. Again, thank you!
John
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