Solved

How to Make T-SQL Queries "Run Faster"

Posted on 2016-09-28
75
208 Views
Last Modified: 2016-11-19
Hello:

Below is my code that is essentially a combination of two CTE queries, plus a separate query.

Separately, the three queries run pretty quickly.  When combined and run in SQL Management Studio, though, no data is returned.  

SQL processes the combined query without errors, yes.  But, in trying to run this code once yesterday afternoon and once this morning, each instance "ran" for over an hour with no data returned.

What can I do, in general, to make queries process and return data more quickly?

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)  
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 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 RM20201.ApplyFromGLPostDate < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
AND RM20201.APFRMAPLYAMT = RM20101.ORTRXAMT
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)     
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, RM20201.ApplyFromGLPostDate
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT > @AGE and RM20201.APFRDCDT < @AGE and RM20201.ApplyFromGLPostDate < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
AND RM20201.APFRMAPLYAMT = RM20101.ORTRXAMT
THEN RM20201.APPTOAMT * -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)   
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.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.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) 
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) 
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) 
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) 
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) 
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)   
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) 
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
UNION
select [CustomerID], [CustomerName], [DocumentNumber], Cnt, [OPEN A/R]
from cte2 
)
as WATERFALL 
)
AS WATERFALL2 
GROUP BY [CustomerID], [CustomerName]
ORDER BY [CustomerID]

Open in new window

0
Comment
Question by:John Ellis
  • 27
  • 18
  • 12
  • +3
75 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Look at the execution plan in SSMS to see if any missing indexes can help and add NOLOCK hints to selects. "Include actual execution plan" is the best but also "Display estimated execution plan" under the Query menu option helps as well.
Also if you see parallelism in the query plan that may be another reason for queries to run slow when executed at same time as SQL not always handles this well.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
You have to rewrite the queries. Could you please post query plan for this query?

I shall make the changes for you.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@Icohan - NOLOCK hint will improve any performance. :)
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

it wouldn't help to repeat all the things which are written about query performance "in general" as this fills books and you should be able to find it by yourself using your favorite search engine.

But in case of your query one can say some things which would help to improve the performance of this one.

1. you use a CTE, why do you use an additional SELECT in the final SELECT of the CTE? Why not add it as further subquery into the CTE?
2. you use a lot of UNION queries - UNION queries are in general slower than normal queries.
3. you use a lot of GROUP BY and additional and more worse: They use HAVING. The HAVING clause applies to the result which GROUP BY produces. As this is a virtual table it has no indexes and because of that HAVING cannot use any index. If possible, you should rewrite all HAVINGs as WHERE for the basic table and also add indexes for all things you want to filter in a meaningful way.
4. SQL Server allows to add additional columns to an index which are only added but not used in the index. This allows to use these additional columns to be loaded into the result table without accessing the basic table. So if you have only a small number of fields you want to return from a table which are not part of the WHERE then you can add these columns to the index. SQL Server then loads the field values from the index when it searches for the result so it doesn't need to additionally load these fields from the table.

Using a lot UNIONs mostly comes from a data model which is not correct. Check the data model if you can avoid them in general.

Instead of trying to use that all in one query, create a stored procedure, create a temp table (or table variable) and insert the different parts you now adding with UNIONs into this temp table in separate queries. Then SELECT from the temp table instead at the end. This is no problem here as the result table would in all cases not be updatable because of the GROUP BY.

Cheers,

Christian
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
@pawan -  I'm not sure not sure what you mean by "NOLOCK hint will improve any performance. :)"
However to give an answer to that - yes it will improve (sometimes A LOT!) if you consider locking/blocking possibly eliminated by simply adding that hint to the SQL code objects using the same table in in particular where parallelism is involved.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Nolock does not mean it will not lock table. A simple example would be

Select something from a table WITH NOLOCK and

in another session try to alter the table.. It will not allow you , because it will put schema locks on your table.

Hope it helps.
0
 

Author Comment

by:John Ellis
Comment Utility
Hello Everyone:

Thank you, so much, for the quick responses!

So far, I have added "WITH (NOLOCK)" after each instance of "from [table]".  This did not "speed things up".

In the past, I have created a stored procedure and table, in these situations.  It is a good idea.  But, since I'm still trying to validate whether the data returned is accurate, I don't want to go to that "trouble", as of yet.  I have to see this data, first.  Right now, I cannot see it.

I have added indexes, as well, to tables in the past.  But, is it possible to do so for queries?

Otherwise, I guess I'll need to use the other idea voiced on modifying the third clause as a CTE.

John
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
I would suggest use temp table. They normally perform very good with huge data.

Yes indexes will help but put pressure on storage system as we have store and maintain indexes also.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

have you also tried to change the HAVING to WHERE? Otherwise NO index will be used as I said above.

Yes, you can also add indexes to views but that doesn't help to add performance here as the index will be created on the result set. It is a little bit complicate to add indexes to views as it needs several prerequisites like changing the view to "WITH SCHEMA_BINDING" and so on.

Cheers,

Christian
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Yes view are really bad as they have lot of conditions.

Try and rewrite the query from scratch if possible. Use temp table where data is huge.

If you can post tables and their data, and input and output required, i shall write it for you ! (make a text file put all there)

Waiting.
0
 

Author Comment

by:John Ellis
Comment Utility
Hello:

I don't know how to create a temp table.  If that's the solution, can someone please show me how?

Otherwise, I'm trying to embed the "third clause" as "cte3".  But, I'm not getting the syntax correct.  Can someone please let me know how to do this?  (I don't work with CTE's that much.)

Thanks, again!

John
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Examples

1.

SELECT *
INTO #temp
FROM TableName

2.

CREATE TABLE #Temp1 ( a int , b int )

INSERT INTO #Temp1
SELECT * FROM tablename
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

a temp table can only be created in a stored procedure, you cannot do that in a view or in a function.

The creation is very simple: Use the "SELECT All, your, fields INTO #tmp FROM...."  syntax in the first query, that will create the temp table automatically from the basic table fields of your query. In all further queries you need an "INSERT INTO #tmp .... SELECT All, your, fields FROM...." instead.

Rewriting the last query to be added into the CTE is only a cosmetic change, it will NOT change the performance.

Why don't you answer my question about HAVING and WHERE?

Cheers,

Christian
0
 

Author Comment

by:John Ellis
Comment Utility
Christian:

"Why don't you answer my question about HAVING and WHERE?"

You don't have to be so hateful.  But, to answer your question, the use of "HAVING" was the only way to pull in accurate data for those clauses of the query.

Again, Everyone, I need to see the results of my data pulled from this query.  I, therefore, need to have this query process and display the data quicker.  Otherwise, I cannot know if I need to modify any of the clauses.

I appreciate offers to rewrite.  But, I want to do this, on my own.

Otherwise, if there is no way of speeding up the processing of the data from a query until it is made into a table, please let me know.  

If that's the case, then I suppose I'll run each clause one at a time, review each clause's results and especially in the "Open A/R" field, add the figures up, and compare the combined total from the figures to those of the report that I'm trying to recreate in SQL.

Thank you!

John
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
We can create temp tables in a Adhoc SQL Scripts & Stored procs.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

why do you think I am answering "hateful"? I am very emotionless answering here.

If you got a possible solution from someone it's on your turn to answer all recommendations, otherwise it's not possible to help.

Just for trying to see if the result is OK it is the easiest way to execute the different parts of the UNION separately and then assemble all at the last step.

If you add the CASE construct i.e. in your last query to a SELECT and use WHERE here to filter out all conditions which would not fit to your result set then all the conditions you wrote in the CASE could use an index. The result set of this SELECT can then be used to GROUP BY and then you don't need the HAVING as none of the rows of the first query would contain a value which you want to use in the GROUP BY. That's what such queries should always be written. The HAVING clause should in most cases only be used for columns which are not part of the original table, like a SUM value you have aggregated - this can only be seen after GROUP BY so here a HAVING is correct.

Cheers,

Christian
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Did you checked the query plans? Estimated or Actual?

NOLOCK is a good hint to avoid locking/blocking but just that alone is not a "silver bullet" - and I missed to say it should be added to all JOIN's as well - basically any object in the FROM clause but again...

You must look at the query plan first and you can save it and post here is you don't know how to interpret it as it's almost impossible without having your SQL server database access give you other than general advice about what should be done.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Where's the Query Execution Plan?
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
This kind of question can only be answered by a book on the subject and I am not joking. That query is a bit too big and too many objects involved to be answered in EE.
0
 

Author Comment

by:John Ellis
Comment Utility
@BitQueezer:

Earlier, you had asked why am I using an additional SELECT in the final SELECT of the CTE and instead why not add it as a further subquery into the CTE?

Those are good questions.  

In the main body of my query, after the first instance of "cte3" is mentioned, I'd like to envelop the remainder of the query into another CTE.

Below is my latest code.  What syntax would I use to envelop the remainder of the body of my query into another CTE?

I ask this because, I cannot use other solutions like stored procedures and temp tables to get this to run faster, until I first know the results that are going to display by running this query.

Right now, it is taking over an hour to run this query, without my cancelling it.  So, I cannot feasibly even get an execution plan from SQL Studio either.

Until I know the results that are going to be returned by this query, as compared to a report generated from our ERP system, I cannot afford to waste time in creating temp tables or stored procedures or in rewriting this query.

That's why I'd like to know how I can embed the remainder of the body of the query, after the first mention of "cte3", into another CTE.

Thanks!

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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))  
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 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 RM20201.ApplyFromGLPostDate <= @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
AND RM20201.APFRMAPLYAMT = RM20101.ORTRXAMT
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))   
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, RM20201.ApplyFromGLPostDate
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE <= @AGE and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT > @AGE and RM20201.APFRDCDT <= @AGE and RM20201.ApplyFromGLPostDate <= @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
AND RM20201.APFRMAPLYAMT = RM20101.ORTRXAMT
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0),
cte3 as
(
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
RM20201.APTODCNM as [ApplyToDocNo], RM20201.APPTOAMT as [AppliedAmt], RM20101.ORTRXAMT AS [OriginalAmt],
COUNT(*) OVER (PARTITION BY RM20101.CUSTNMBR, RM20201.APFRDCNM) AS Cnt,
SUM(RM20201.APPTOAMT) OVER (PARTITION BY RM20201.CUSTNMBR, RM20201.APFRDCNM) as [CustomerPayment],
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
INNER JOIN RM20101 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM
GROUP BY RM20201.CUSTNMBR, RM20201.APFRDCNM, RM20101.ORTRXAMT
HAVING COUNT(RM20201.APFRDCNM) > 1)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981')) 
GROUP BY RM20101.CUSTNMBR, RM20201.CUSTNMBR, RM20101.RMDTYPAL, RM20101.DOCDATE, RM20201.DATE1, RM20201.APTODCDT, RM20201.APFRDCDT,
RM20101.DOCNUMBR, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APFRMAPLYAMT, RM20201.APPTOAMT,
RM00101.CUSTNAME, RM20201.APTODCNM, RM20201.APFRDCNM
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
INNER JOIN RM20101 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM
GROUP BY RM20201.CUSTNMBR, RM20201.APFRDCNM, RM20101.ORTRXAMT
HAVING COUNT(RM20201.APFRDCNM) > 1)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
THEN RM20201.APPTOAMT * -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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))  
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))  
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))   
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))   
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))    
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 RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE AND RM20201.APTODCDT < @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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))  
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 RM20201.APTODCDT < @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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))  
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))   
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))    
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 RM20201.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20201.APTODCNM as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20201.APFRDCTY > 6 and RM20201.APTODCDT = @AGE and RM20201.DATE1 > @AGE 
AND RM20201.APFRDCDT < @AGE
and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20101.DUEDATE <> ''
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20201
INNER JOIN RM00101 on RM20201.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20101 on RM20201.APTODCNM = RM20101.DOCNUMBR 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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))   
GROUP BY RM20201.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20201.APTODCDT, RM20201.APTODCNM, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT,
RM20201.APFRMAPLYAMT, RM20201.APFRDCTY
HAVING 
CASE WHEN RM20201.APFRDCTY > 6 and RM20201.APTODCDT = @AGE and RM20201.DATE1 > @AGE 
AND RM20201.APFRDCDT < @AGE
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 in (select APFRDCNM from RM20201)
THEN RM20101.CURTRXAM * -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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))    
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 in (select APFRDCNM from RM20201)
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 RM20101.ORTRXAMT <> RM20101.CURTRXAM
AND RM20201.DATE1 > @AGE AND RM20101.DUEDATE <> '' AND RM20201.APTODCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201)
THEN RM20101.CURTRXAM 
ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))     
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20101.RMDTYPAL, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20201.DATE1, RM20101.DUEDATE, RM20201.APTODCDT
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE <= @AGE and RM20101.ORTRXAMT <> RM20101.CURTRXAM
AND RM20201.DATE1 > @AGE AND RM20101.DUEDATE <> '' AND RM20201.APTODCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201)
THEN RM20101.CURTRXAM 
ELSE 0 END
<> 0 
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20201.APFRDCNM as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE
AND RM20201.APTODCDT < @AGE
AND RM20201.APTODCNM in (SELECT RM20101.DOCNUMBR FROM RM20101 INNER JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APTODCNM
where RM20101.CUSTNMBR = RM20201.CUSTNMBR
and
RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE
AND RM20101.ORTRXAMT = RM20201.APPTOAMT)
THEN RM20201.APPTOAMT * -1
ELSE 0 END
as [OPEN A/R]
from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))     
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20101.RMDTYPAL, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20201.DATE1, RM20101.DUEDATE, RM20201.APTODCDT, 
RM20201.APFRDCTY, RM20201.APFRDCDT, RM20201.APPTOAMT, RM20201.CUSTNMBR, RM20201.APFRDCNM, RM20201.APTODCTY,
RM20201.APFRMAPLYAMT, RM20201.APTODCNM
HAVING 
CASE WHEN RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE
AND RM20201.APTODCDT < @AGE
AND RM20201.APTODCNM in (SELECT RM20101.DOCNUMBR FROM RM20101 INNER JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APTODCNM
where RM20101.CUSTNMBR = RM20201.CUSTNMBR
and
RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE
AND RM20101.ORTRXAMT = RM20201.APPTOAMT)
THEN RM20201.APPTOAMT * -1
ELSE 0 END
<> 0
UNION
select [CustomerID], [CustomerName], [DocumentNumber], Cnt, [OPEN A/R]
from cte 
where Cnt > 1
UNION
select [CustomerID], [CustomerName], [DocumentNumber], Cnt, [OPEN A/R]
from cte2 
UNION
select [CustomerID], [CustomerName], [DocumentNumber], Cnt, [OPEN A/R]
from cte3 where (cte3.[CustomerID]+''+cte3.[DocumentNumber]) not in 
(select (cte.[CustomerID]+''+cte.[DocumentNumber]) from cte)
UNION
select [CustomerID], [CustomerName], [DocumentNumber], [Cnt], [OPEN A/R]
from
(select *,
ROW_NUMBER () OVER(PARTITION BY cte3.[CustomerID], cte3.[ApplyToDocNo] ORDER BY cte3.[OPEN A/R] ASC) as RowFromCTE
from cte3)
as cte4
where cte4.RowFromCTE > 1 and
cte4.CustomerPayment = cte4.OriginalAmt 
)
as WATERFALL 
)
AS WATERFALL2 
GROUP BY [CustomerID], [CustomerName]
ORDER BY [CustomerID]

Open in new window

0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi John,

again: The syntax of changing the query to use CTE in all cases does not affect the performance so you should not think about that first.

To verify the result you should break all these queries into single ones and then verify them one by one. If all are OK, go on and use the first UNION for two of these and verify them. Then add the others one by one so you a) can verify all steps in-between and b) see when it gets slow.

The estimated execution plan should be displayed before the query starts.

Cheers,

Christian
0
 

Author Comment

by:John Ellis
Comment Utility
Hi BitSqueezer:

Since the UNION statements at the end of the query work together, such as cte3 and cte, I don't understand how I can successfully run each UNION statement appearing throughout the query individually.  If I do, I'm going to get misleading results.

Also, I followed the instructions in using "Include actual execution plan" as discussed in the Microsoft article's URL below.  It states that the query has to be run, first.  So, I don't see how I can get the execution plan, prior to running the query.

https://msdn.microsoft.com/en-us/library/ms189562(v=sql.110).aspx

John
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi John,

try the correct link instead:
https://msdn.microsoft.com/en-us/library/ms191194(v=sql.110).aspx

That's the estimated execution plan which is sometimes not so accurate as the actual one your link shows (and which indeed is only be displayed after execution). The estimated one will be displayed instantly. You can simply press CTRL-L to display it in every query window of SSMS.

Cheers,

Christian
0
 

Author Comment

by:John Ellis
Comment Utility
Hi BitSqueezer:

Thank you, for that!  :)

Attached is the SQL Estimated Execution Plan.

John
Waterfall-SQL-Est-Execution-Plan.sqlplan
0
 

Author Comment

by:John Ellis
Comment Utility
Hello:

One thing that I tried that frankly did not help was to place SQL's "WITH(INDEX(..." syntax, after every "from [table]" clause.

Since I had never done this before, I had to fumble through it.  I simply looked at the properties of the applicable table and used the first index listed.

Now, the tables have at least five or six indexes.  So, instead of choosing only the first index in the list and placing that index within the "WITH(INDEX(..." syntax, should I have chosen all six indexes and placed them within the "WITH(INDEX(..." syntax?

In any case, would using indexes make this big query run faster?

Thanks!

John
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi John,

you don't need to choose the index on yourself the query optimizer does that job normally very good, you only need to create meaningful indexes on the columns you are filtering or joining.

But that all has no effect as long as you specify everything in HAVING instead of WHERE, as I said above. You need to first filter with WHERE as good as possible and then use HAVING only at the end if WHERE is not possible (or JOIN). HAVING doesn't use indexes.

I still read the execution plan, maybe I can tell more later (or someone else in the meantime).

Cheers,

Christian
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
This thread is already long and I'm somehow lost in the middle of many comments.
As resume, can you tell me if the issue is performance or not getting the expected result?
If performance, how long is taking full process?
If not getting result, what's returning and what are you expecting to return?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Just gave a look in your query. Basically you're working only with 4 tables, right?
  1. RM00101
  2. RM20101
  3. RM20201
  4. CN00500

Can you post the PK, FK and all indexes from those tables?

I can also see from the Estimated Execution Plan that parallelism would be used. This is not always a good thing but for now let it be and maybe later we can do something to avoid the parallelism.
0
 

Author Comment

by:John Ellis
Comment Utility
Hello All:

The issue is performance.  I could let the query run for over an hour and it would not finish.

I'm going to try some of the suggestions, in this thread, such as replacing "HAVING" as much as possible with "WHERE".

If I'm still unsuccessful, I'll post additional information, such as primary keys, foreign keys, and indexes.

Thanks, so much!

John
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Do one simple thing first. Copy the query code into a new window and don't execute it but click on the "Display Estimated Execution Plan" button and see if there are any suggestion in green color for indexes that are missing and what the impact would be if created, that is in percentage. If there are any suggestions you can right click on them > Index details to see the CREATE statement. You could use that or just as a starting point to get an idea what indexes you could build. Of course the main way to approach this "monster" is to tweak the code and make it more efficient.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
I'm going to try some of the suggestions, in this thread, such as replacing "HAVING" as much as possible with "WHERE".
I don't think this will help.

I don't understand why do you have CASE statements with RM20101.DOCNUMBR in (select APFRDCNM from RM20201) when you have those columns in the JOIN statement:
from RM20101
            INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM


RM20201has almost 2M rows so you're retrieving the same that in the CASE, INNER JOIN and then in the HAVING again. And this by each SELECT! Have the extra subselects removed and let only the INNER JOIN clause. It should be enough.


The indexes are also required so please post the existing indexes.
0
 

Author Comment

by:John Ellis
Comment Utility
Hi Vitor:

This is an ERP system's SQL data that I'm pulling from.  It's kind of hard to explain, without "showing" the accounting behind it.  

But, the reason I have to use all of those CASE statements is because I have to "tell" SQL to not return any records if a document is in or is not in another table.

RM20101 represents posted "receivables" documents such as invoices, cash receipts, credit memos, and payments.  

RM20201 shows how and when those documents are applied (i.e. "tied") to each other.

In a way, I'm trying to conduct an "historical" (i.e. "point in time") aging.  Unfortunately, the ERP system that I'm working with is complex, in terms of using SQL to write a point in time aging.  All consultants that work with this ERP system agree that it's not easy and represents the use of quite complex SQL query syntax.

The last time that I did this, years ago, I had to create a "function" and other items in SQL.  I'm trying to limit the creation of objects and simply pull data mainly through a select statement.

I'll post the indexes, as soon as I can.  I have tried a few suggestions, such as replacing "HAVING" with "WHERE".  And, you're right.  Even though that increased the performance, the results that were returned for the sample of the query that I used were incorrect.

John
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
So are you somehow limited for changes, meaning that you can't remove the subselects from the CASE and HAVING clauses?

have tried a few suggestions, such as replacing "HAVING" with "WHERE".  And, you're right.  Even though that increased the performance, the results that were returned for the sample of the query that I used were incorrect.
HAVING is a special WHERE clause that is only used to restrict the GROUP BY statement and that's why you can't go like that moving it to a WHERE clause. It will affect the results as you already stated.
0
 

Author Comment

by:John Ellis
Comment Utility
Hi Vitor:

I'm afraid that this sort of "reporting" is limited to changes, yes.

John
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Then I'm afraid that only the use of the correct indexes can help you.
0
 

Author Comment

by:John Ellis
Comment Utility
Very well.

Now, shall I do what you suggested earlier, which is post what those four tables' indexes, foreign keys, and primary keys are?

Or, is there a way within my query to "create" new indexes, without doing so within the four tables themselves?

John
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Try my suggestion above to see what the SQL server engine itself will come up with.
0
PRTG Network Monitor: Intuitive Network Monitoring

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

 

Author Comment

by:John Ellis
Comment Utility
I did.  There were no suggestions (green items) produced.

John
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Another thing you can try is to run the script bellow which will list indexes that are missing in your databases:
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, mid.statement as 'Database.Schema.Table',
    mid.equality_columns, mid.inequality_columns, mid.included_columns,
    migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
    FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
    INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
    ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
    ON mig.index_handle = mid.index_handle
    ORDER BY mid.statement, index_advantage DESC;

Open in new window

0
 

Author Comment

by:John Ellis
Comment Utility
No can do.  I can't update the databases, themselves.  That can cause serious damage to the ERP system.

John
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
That script doesn't update anything, just lists indexes that are missing based on internal statistics. Here is a modification to order by database first:
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, mid.statement as 'Database.Schema.Table',
    mid.equality_columns, mid.inequality_columns, mid.included_columns,
    migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
    FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
    INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
    ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
    ON mig.index_handle = mid.index_handle
    ORDER BY PARSENAME(mid.statement,3), index_advantage DESC;

Open in new window

0
 

Author Comment

by:John Ellis
Comment Utility
Thanks.  But, what do I do exactly with such a listing of indexes, after running this?

John
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
There might be some indexes suggested on the particular tables you are using in your query and the list will tell you on what columns to create them and what columns to include. You can use it as it is and then look for the indexes in the database you are interested in or on the 4 tables posted by Vitor above:
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, mid.statement as 'Database.Schema.Table',
    mid.equality_columns, mid.inequality_columns, mid.included_columns,
    migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
    FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
    INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
    ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
    ON mig.index_handle = mid.index_handle
where
	PARSENAME(mid.statement,1) in ('RM00101','RM20101','RM20201','CN00500')
    ORDER BY PARSENAME(mid.statement,3), index_advantage DESC;

Open in new window

0
 

Author Comment

by:John Ellis
Comment Utility
Hello:

Attached is a Word doc, showing four screenshots--one for each of the four tables.  The screenshots show the names of the indexes and PK/FK for each table.

I'm a novice, at a lot of things, and especially on creating indexes.  So, if someone can please also tell me how to create an index within my query, I'd be really grateful.  Even just an example that I can draw from would help.

Again, I can't create the indexes within the tables in the database itself.  I need to know how to do so, within my query.

Thanks, so much, for all of the responses!

John
Indexes.docx
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
That image is useless. We need to see what is the definition is those indexes, mainly on what columns they were created on. Do this:

Paste the 4 table names in a query window that is opened in the databases where the tables are:

RM00101
RM20101
RM20201
CN00500

Highlight one table name at a time by double clicking on it and then hit Alt+F1 keys. In the results that will open below look for the list with the index info. It will have these columns:

index_name      index_description      index_keys

Copy the whole content of that list by clicking on the left corner in the columns header. copy it and and paste it in an excel spreadsheet. Do that wit each table and then attach the excel file in your post.
0
 

Author Comment

by:John Ellis
Comment Utility
I will.  I'm in a conference call but will do this, ASAP.

Afterward, would you be able to tell me how to place indexes into the query?

John
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
You don't place indexes into a query but experts will be able to tell if those indexes are enough and what other you can create, if needed.
0
 

Author Comment

by:John Ellis
Comment Utility
Thank you.  

If I cannot place indexes into the query, but experts would tell me what indexes need to be created, I don't think that showing the indexes will do any good.  Again, I cannot add indexes to the ERP's SQL environment.

John
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Again, I cannot add indexes to the ERP's SQL environment.
Maybe I am wrong but you should have started with this statement and emphasize it to avoid wasting time. If you mentioned it somewhere obviously it got lost among other posts.
0
 

Author Comment

by:John Ellis
Comment Utility
I apologize, for my shortcomings.  So many people point those out to me, on a daily basis.  I must be as useless, as these people make me feel.

To anyone else out there, are we sure that I can't place indexes within the query itself or if that would do any good?

It sounds like working with indexes is, indeed, the answer.  But, as with most ERP systems, I cannot simply add indexes directly to the database.  That could compromise the system.

John
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
I took the time to format only the first query in the CTE and sorry to say but it seems that is not right. It doesn't make much sense to group by all those columns especially on a CASE statement that complex. Also what Vitor mentioned is right, why using the same CASE statement in the SELECT list when you can simply replace it with RM20201.APFRMAPLYAMT * -1 ?

I thing the approach to this query is wrong and it is very difficult to clean it up in order to help. This question is WAY beyond the purpose of EE and it looks like it needs more like a consulting service. Here is the formatted code so that one can see easier what we are dealing with and this is only the first query!
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)  
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

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
John, open a new query window in SSMS and run the following (each line at a time):
USE databaseName

sp_help spRM00101
sp_help RM20101
sp_help RM20201
sp_help CN00500

Open in new window

For each result of the sp_help command you'll see a bunch of lines. We need you to copy the ones that has the following header: index_name, index_description, index_keys

I know that ERP systems are closed systems but still give some flexibility. If you have an ERP administrator you just need to talk with him and present your suggestions to see if he can do something about it.
0
 

Author Comment

by:John Ellis
Comment Utility
Hi Vitor:

Attached are the requested results.

Thank you.

John
CN00500-Indexes.xlsx
RM00101-Indexes.xlsx
RM20101-Indexes.xlsx
RM20201-Indexes.xlsx
0
 

Author Comment

by:John Ellis
Comment Utility
Hi Again, Vitor:

I researched and found the article below saying, within reason, that indexes can be added to the four tables.

Based on the syntax mentioned in this article, can you please advise me on what indexes I need to create.  I can test in our development environment and advise our DBA of such.

Thank you!

John


When hundreds of thousands of records exist in a single table in the database, things can slow down a bit.  If the application is looking for information using a key, then this number of records should not be a problem.  But to look for a single customer, for example, using their phone number, when the phone number is not indexed, can take some time in a large table.
Supplemental indexes can be added to these tables!  Don't get too carried away as too many indexes will slow down the process of adding or updating records as all of the indexes need to be updated as well.  But having 3-4 additional indexes should not be a problem.
The following command is typed into the SQL Management Studio in a query window to create a new index.  Make sure to backup your database first and do this when no one else is on the system so mistakes can be corrected.
 
CREATE INDEX index_name ON table_name (column_name)

index_name is a name given to the index.  The ERP system uses the rule AKtablename99 to indicate an Alternate Key on the table and serializes the keys.  I use RLWKtablename99.  Use what you want.
table_name is the name of the table, like SOP10100 for the Sales Transaction
column_name is the name of the column the index should be built upon.  Two or more columns can be specified if needed but separate each with a comma.
If the index should be in decending order, add DESC at the end of the command.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Those existing indexes are not useful for your query. To speed up your query let start to create the following indexes:
-- RM20101 
CREATE INDEX AK8RM20101 ON RM20101 (DOCNUMBR)
CREATE INDEX AK9RM20101 ON RM20101 (RMDTYPAL, DOCDATE)
-- RM20201 
CREATE INDEX AK7RM20201 ON RM20201 (APFRDCNM)
CREATE INDEX AK8RM20201 ON RM20201 (DATE1, APTODCDT, APFRDCDT, ApplyFromGLPostDate)

Open in new window

After having those indexes created try to run the SQL script again to see if it is now faster.
0
 

Author Comment

by:John Ellis
Comment Utility
Hi Vitor:

Thank you, so much, for your help!  I will definitely work on testing these indexes, as soon as possible.

I did have a surprise project dropped on me, for today.  So, it may be tomorrow, before I can test this.

I would like to ask one thing, though.  When you create indexes, in general, how do you "know" which columns to conduct the indexing on?  Is it simply a matter of doing so on the "important" (key) fields?

John
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
how do you "know" which columns to conduct the indexing on?  
Mostly are the columns used in your join and filter criteria (WHERE clause). If you have many columns from a table then you just create a single index with all of them in the same order as they appear in the JOIN or WHERE clause.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
If you have many columns from a table then you just create a single index with all of them in the same order as they appear in the JOIN or WHERE clause
Not quite, actually. The columns order in the JOIN or WHERE clause doesn't matter but the order in the index does. In a cmposite index you would start with the columns that has more granular values distribution, i.e. you would put the City column before the state column and before the country column. When you use those columns in joins or where clause the order is irrelevant, SQL engine knows how to use them to match the index.

As a general rule you will add as part of the index the columns used in JOINS, that will reflect the relation between the tables, and then the ones used frequently in the WHERE clause. On top of that you can include columns, that is in the INCLUDE clause and NOT in the index itself, that are needed in the SELECT list. This way the data is all in one place and there is no need to do extra steps from the index to the table data itself.

In regards to the columns used in the JOIN clause, as I said, they should reflect the relation between the tables so in most cases one table is the parent and the other the child. The parent will have a primary key(PK) so there is no need for an extra index for that but the child will have some column(s) that point to that PK, which represents the foreign key(FK) and you want to make sure you have an index on that FK.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

here is a simple try to resolve the first CTE SELECT in your code to show what I meant above. Although I cannot prove the result I think that it will produce the same result.

And that's what I meant also above: If you break that monster query into small parts and test if they produce the correct result you'll find the problem a lot faster.

If you look into the execution plan you also see things like "table scan" (and with about 1 and 3 million records) which is always an alert to look for the indexes. Using HAVING kills any index usage. And it's wrong to say "HAVING is a special WHERE clause that is only used to restrict the GROUP BY statement and that's why you can't go like that moving it to a WHERE clause. It will affect the results as you already stated. ". Yes, HAVING is a special WHERE, yes, it restricts the result set which is produced by GROUP BY and yes, you cannot ALWAYS move that to WHERE. But you can do that in very most cases as a GROUP BY statement doesn't change any existing data, it only can add new data using aggregate functions. For example:

Country   City              CustomerID
USA          New York    1
USA          New York    2
Germany Koeln           3
Germany Koeln           4
Germany Berlin           5
SELECT Country, City, COUNT(*) AS CountOfCustomer FROM....
GROUP BY Country, City
HAVING COUNTRY = 'USA'

Open in new window


This will group all countries and cities and THEN filters out all countries except USA. As it is done AFTER GROUP BY (done on the result set GROUP BY creates and not the original table) it cannot use indexes on countries and cities.

SELECT Country, City, COUNT(*) AS CountOfCustomer FROM ....
WHERE Country = 'USA'
GROUP BY Country, City

Open in new window


Does exactly the same, only that the WHERE affects the table where the data comes from and so it can use the table indexes and THEN only the left rows (the first two) will be used to create the result set. This is, depending on the number of rows, in all cases a LOT faster than the first example.
So ALWAYS try to filter the base tables first as much as possible before you group or calculate anything and avoid HAVING the best you can. HAVING is, as I already said above, in most cases only useful if you want to filter a newly created value like the result of an aggregate. So for example, if you would want to filter out all cities with a customer count < 3 then this is something to be done using HAVING as only after GROUP BY the count is in the result set.

Knowing that you can also improve the sub-select in your CASE construct. In the example below the IN clause is changed by a "NOT EXISTS" clause. This is usually faster than IN and it doesn't produce a result set, it tests only if it would be returned depending on the criteria and if only one row would be returned the condition is fulfilled - because of that the SELECT can use "1" or whatever as no columns are needed. In this case also a GROUP BY is not needed here as, like said above, a GROUP BY does not produce new data, it only reduce the number of result rows. That's not needed here, costs time and memory only as the condition is: The DOCNUMBR should not be in the result set the SELECT would produce.

Next you should exchange all these strange table names (who invents names like "RM20201"? And then "RM00101" and "RM20101"! Who should separate them by reading that monster query and who knows what that means? Not to speak of the not better column names...) with a meaningful short one, two or three letter alias like shown in the example below. If you do that for the complete query it will be a lot more readable and a lot shorter.

Then you should use the CTE in the meaning of "waterfall" like you named it by yourself. As an example I've coded your long customer number hardcoded list into a kind of "temp table" by using the first CTE query with UNION ALL SELECTs. As it seems that the list is always the same in the whole monster (or even in many cases) you now can use "SELECT CNo FROM qCNo" at every place where you need it. That shortens your monster query again a lot. Normally the better way would be to insert these values into a table and then use that (and an index on the customer number) to produce a more performant result.

Next, you should try to use a base query to select all columns and calculate all needed values here (like your CASE construct), makes it very much easier to reuse the values also in further GROUP BY clauses like shown in the example below.

Of course I cannot say if this one works but you can test that in your database and adjust it to fit your needs. And then, if the result of this query is what you want (and performs fast enough, if not, check the execution plan, check indexes on your base tables and create some if they are not available yet) then go on and add the second CTE and rewrite it in a similar way - and so on until your query works.

I believe that there is a lot more to improve but this one is really too long for me, I can only show you the way, you must do the work on your own.

Cheers,

Christian


WITH qCNo AS
(
	SELECT 	' 320651' AS CNo	UNION ALL
	SELECT  '00065'				UNION ALL
	SELECT  '00351'				UNION ALL
	SELECT  '00370'				UNION ALL
	SELECT  '0035829'			UNION ALL
	SELECT  '0015200'			UNION ALL
	SELECT  '0100010149'		UNION ALL
	SELECT  '0100011881'		UNION ALL
	SELECT  '0100011303'		UNION ALL
	SELECT  '0100011766'		UNION ALL
	SELECT  '0100012897'		UNION ALL
	SELECT  '195145'			UNION ALL
	SELECT  '0100550714'		UNION ALL
	SELECT  '238624'			UNION ALL
	SELECT  '0100013309'		UNION ALL
	SELECT  '0100010453'		UNION ALL
	SELECT  '266267'			UNION ALL
	SELECT  '179520'			UNION ALL
	SELECT  '0100012517'		UNION ALL
	SELECT  '0100011519'		UNION ALL
	SELECT  '0100012160'		UNION ALL
	SELECT  '0100012558'		UNION ALL
	SELECT  '0100075597'		UNION ALL
	SELECT  '0100075290'		UNION ALL
	SELECT  '0100354828'		UNION ALL
	SELECT  '274173'			UNION ALL
	SELECT  '0100014190'		UNION ALL
	SELECT  '0100020981'
),
q1 AS
(
	SELECT	RD.CUSTNMBR		AS CustomerID
			,C.CUSTNAME		AS CustomerName
			,C.PYMTRMID		AS PID
			,C.COMMENT1
			,C.COMMENT2
			,RD.DOCNUMBR	AS DocumentNumber
			,RD.RMDTYPAL
			,RD.DUEDATE
			,RD.DOCDATE
			,RD.ORTRXAMT
			,RD.CURTRXAM
			,X.APTODCNM		AS XDocumentNumber
			,X.APTODCDT		AS XDocumentDate
			,X.APFRMAPLYAMT
			,X.DATE1
			,X.APPTOAMT
			,X.APFRDCDT
			,CN.CRDTMGR		-- and so on, use good readable aliases like above
			,
			,(CASE 
				WHEN RD.RMDTYPAL > 6
				AND RD.DOCDATE < @AGE
				AND X.DATE1 > @AGE
				AND X.APTODCDT > @AGE
				AND X.APFRDCDT < @AGE
				AND NOT EXISTS (SELECT 1 FROM RM20201 AS X2
								INNER JOIN RM20101 AS RD2 ON X2.CUSTNMBR = RD2.CUSTNMBR
										AND X2.APTODCNM = RD2.DOCNUMBR
										AND X2.APPTOAMT = RD2.ORTRXAMT
								WHERE X2.APFRDCNM = RD2.DOCNUMBR
								/*GROUP BY X2.APTODCNM,X2.APTODCDT,X2.APFRDCNM*/ -- not needed here
								)
				AND RD.ORTRXAMT <> RD.CURTRXAM
				AND X.APFRMAPLYAMT <> RD.ORTRXAMT
				AND RD.DUEDATE <> ''		-- If that's a date field and not a text field this would not work. Would be i.e. "RD.DUEDATE IS NOT NULL" instead.
				THEN X.APFRMAPLYAMT * - 1
			ELSE 0
			END AS [OPEN A/R]
	FROM RM20101 AS RD
	INNER JOIN RM00101 AS C ON RD.CUSTNMBR = C.CUSTNMBR
	INNER JOIN RM20201 AS X ON RD.DOCNUMBR = X.APFRDCNM
			AND RD.CUSTNMBR = X.CUSTNMBR
	LEFT OUTER JOIN CN00500 AS CN ON RD.CUSTNMBR = CN.CUSTNMBR
		WHERE	(RD.VOIDSTTS = 0)
		AND		(RD.CUSTNMBR IN (SELECT CNo FROM qCNo))
)
-- the first cte SELECT in your query would then be:
	SELECT q1.CustomerID, q1.CustomerName, q1.DocumentNumber, q1.[OPEN A/R] 
		   ,COUNT(*) OVER (PARTITION BY q1.CustomerID ,q1.XDocumentNumber,q1.XDocumentDate) AS Cnt
	FROM q1
	WHERE q1.[OPEN A/R] <> 0
	GROUP BY q1.CustomerID
			,q1.CUSTNAME
			,q1.PID
			,q1.CRDTMGR
			,q1.COMMENT1
			,q1.COMMENT2
			,q1.RMDTYPAL
			,q1.DUEDATE
			,q1.APFRMAPLYAMT
			,q1.DocumentNumber
			,q1.XDocumentNumber
			,q1.DOCDATE
			,q1.DATE1
			,q1.XDocumentDate
			,q1.APPTOAMT
			--,q1.XDocumentNumber    -- this one is a duplicate
			,q1.APFRDCDT
			,q1.ORTRXAMT
			,q1.CURTRXAM

Open in new window

0
 

Author Comment

by:John Ellis
Comment Utility
Well, Everyone, I don't know how else to proceed on this.

First of all, I am (believe it or not) the ERP Administrator.  But, our IT Dept does not want indexes done on the tables.

Below is code where I tried to create everything as a cte.  That made things worse.  Now, due to the ridiculous "GROUP BY" garbage at the end of my code that SQL made me put, several records in my results that were one record are now five or more!!!

By the way, I cannot do anything else that is suggested in this case.  I have tried.  It produces incorrect results.  Remember that this is an ERP system.  It's not just "regular" SQL data.  There is accounting logic here that has to be reckoned with.

I don't know if temp tables would solve this or not because (a) I have never done them, (b) I don't know if I have to make a view first, and (c) until I can get the blame code to run quickly and confirm or deny the results that I'm looking for, I don't know if I can pursue this any further.

I'm very discouraged.

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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))  
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 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 RM20201.ApplyFromGLPostDate <= @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
AND RM20201.APFRMAPLYAMT = RM20101.ORTRXAMT
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))    
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, RM20201.ApplyFromGLPostDate
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE <= @AGE and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT > @AGE and RM20201.APFRDCDT <= @AGE and RM20201.ApplyFromGLPostDate <= @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
AND RM20201.APFRMAPLYAMT = RM20101.ORTRXAMT
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0),
cte3 as
(
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
RM20201.APTODCNM as [ApplyToDocNo], RM20201.APPTOAMT as [AppliedAmt], RM20101.ORTRXAMT AS [OriginalAmt],
COUNT(*) OVER (PARTITION BY RM20101.CUSTNMBR, RM20201.APFRDCNM) AS Cnt,
SUM(RM20201.APPTOAMT) OVER (PARTITION BY RM20201.CUSTNMBR, RM20201.APFRDCNM) as [CustomerPayment],
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
INNER JOIN RM20101 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM
GROUP BY RM20201.CUSTNMBR, RM20201.APFRDCNM, RM20101.ORTRXAMT
HAVING COUNT(RM20201.APFRDCNM) > 1)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))  
GROUP BY RM20101.CUSTNMBR, RM20201.CUSTNMBR, RM20101.RMDTYPAL, RM20101.DOCDATE, RM20201.DATE1, 
RM20201.APTODCDT, RM20201.APFRDCDT,
RM20101.DOCNUMBR, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APFRMAPLYAMT, RM20201.APPTOAMT,
RM00101.CUSTNAME, RM20201.APTODCNM, RM20201.APFRDCNM--, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2
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
INNER JOIN RM20101 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM
GROUP BY RM20201.CUSTNMBR, RM20201.APFRDCNM, RM20101.ORTRXAMT
HAVING COUNT(RM20201.APFRDCNM) > 1)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0),
cte5
as
(
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))   
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),
cte6
as
( 
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))   
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),
cte7
as
( 
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
AND RM20201.ApplyFromGLPostDate >= @AGE
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))   
AND (RM20101.ORTRXAMT = RM20201.APPTOAMT) AND RM20201.ApplyFromGLPostDate >= @AGE
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, --RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20201.APTODCDT, RM20101.DOCNUMBR, 
RM20101.DOCDATE, RM20201.APTODCNM, 
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM,
RM20201.ApplyFromGLPostDate
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
AND RM20201.ApplyFromGLPostDate >= @AGE
THEN RM20101.ORTRXAMT ELSE RM20101.CURTRXAM END
<> 0),
cte8
as
( 
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))    
AND (RM20101.ORTRXAMT <> RM20201.APPTOAMT)
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, --RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20201.APTODCDT,  RM20101.DOCNUMBR, 
--RM20201.APFRDCDT, RM20201.APTODCNM, RM20101.DOCDATE,
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),
cte9
as
( 
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))   
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, --RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT
--RM20201.APFRDCDT, RM20201.APTODCDT, RM20201.APTODCNM, 
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),
cte10
as
( 
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))     
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),
cte11
as
( 
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 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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))   
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, --RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20201.APTODCDT, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT
--RM20201.APFRDCDT, RM20201.APTODCNM,
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE AND RM20201.APTODCDT < @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),
cte12
as
( 
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))   
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, --RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20201.APTODCDT, RM20101.DOCNUMBR, 
RM20101.DOCDATE,
RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT 
--RM20201.APTODCTY, RM20201.APFRDCDT, RM20201.APTODCNM, 
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),
cte13
as
( 
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))    
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, --RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20201.APTODCDT, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT,RM20201.APFRMAPLYAMT
--RM20201.APTODCNM, 
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),
cte14
as
( 
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))     
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, --RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20201.APTODCDT, RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20201.APFRDCDT, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT, RM20201.APFRMAPLYAMT
--RM20201.APTODCNM,
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),
cte15
as
( 
select RM20201.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20201.APTODCNM as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20201.APFRDCTY > 6 and RM20201.APTODCDT = @AGE and RM20201.DATE1 > @AGE 
AND RM20201.APFRDCDT < @AGE
and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20101.DUEDATE <> ''
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20201
INNER JOIN RM00101 on RM20201.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20101 on RM20201.APFRDCNM = RM20101.DOCNUMBR 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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))    
GROUP BY RM20201.CUSTNMBR, RM00101.CUSTNAME, --RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20201.APTODCDT, RM20201.APTODCNM, 
RM20201.APFRDCDT, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT, RM20201.APFRDCTY
--RM20201.APFRMAPLYAMT, 
--RM20101.DOCDATE,
HAVING 
CASE WHEN RM20201.APFRDCTY > 6 and RM20201.APTODCDT = @AGE and RM20201.DATE1 > @AGE 
AND RM20201.APFRDCDT < @AGE
and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20101.DUEDATE <> ''
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0),
cte16
as
( 
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 in (select APFRDCNM from RM20201)
THEN RM20101.CURTRXAM * -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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))     
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 in (select APFRDCNM from RM20201)
THEN RM20101.CURTRXAM * -1
ELSE 0 END
<> 0),
cte17
as
( 
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 RM20201.DATE1 > @AGE AND RM20101.DUEDATE <> '' AND RM20201.APTODCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201)
THEN RM20101.CURTRXAM 
ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))      
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, --RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20101.RMDTYPAL, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20201.DATE1, RM20101.DUEDATE, RM20201.APTODCDT
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE <= @AGE and RM20101.ORTRXAMT <> RM20101.CURTRXAM
AND RM20201.DATE1 > @AGE AND RM20101.DUEDATE <> '' AND RM20201.APTODCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201)
THEN RM20101.CURTRXAM 
ELSE 0 END
<> 0),
cte18
as
( 
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20201.APFRDCNM as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE
AND RM20201.APTODCDT < @AGE
AND RM20201.APTODCNM in (SELECT RM20101.DOCNUMBR FROM RM20101 INNER JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APTODCNM
where RM20101.CUSTNMBR = RM20201.CUSTNMBR
and
RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE
AND RM20101.ORTRXAMT = RM20201.APPTOAMT)
THEN RM20201.APPTOAMT * -1
ELSE 0 END
as [OPEN A/R]
from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
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',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049'))      
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, --RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
--RM20101.DOCNUMBR, RM20101.DOCDATE,
--RM20101.RMDTYPAL, RM20101.ORTRXAMT, RM20101.CURTRXAM, 
RM20201.DATE1,  RM20201.APTODCDT, 
RM20201.APFRDCTY, RM20201.APFRDCDT, RM20201.APPTOAMT, RM20201.APTODCNM, RM20201.APFRDCNM
--RM20201.APFRMAPLYAMT, RM20201.APTODCTY, RM20101.DUEDATE,
HAVING 
CASE WHEN RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE
AND RM20201.APTODCDT < @AGE
AND RM20201.APTODCNM in (SELECT RM20101.DOCNUMBR FROM RM20101 INNER JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APTODCNM
where RM20101.CUSTNMBR = RM20201.CUSTNMBR
and
RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE
AND RM20101.ORTRXAMT = RM20201.APPTOAMT)
THEN RM20201.APPTOAMT * -1
ELSE 0 END
<> 0)

select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte 
where Cnt > 1 
GROUP BY [CustomerID], [CustomerName]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte2 
GROUP BY [CustomerID], [CustomerName]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte3 
where (cte3.[CustomerID]+''+cte3.[DocumentNumber]) 
not in 
(select (cte.[CustomerID]+''+cte.[DocumentNumber])
from cte
GROUP BY [CustomerID], [CustomerName], [DocumentNumber], [Cnt], [OPEN A/R] 
)
GROUP BY [CustomerID], [CustomerName], [DocumentNumber], [Cnt], [OPEN A/R] 
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R])--, [DocumentNumber], Cnt, [OPEN A/R] 
from
(select *,
ROW_NUMBER () OVER(PARTITION BY cte3.[CustomerID], cte3.[ApplyToDocNo] 
ORDER BY cte3.[OPEN A/R] ASC 
) as RowFromCTE
from cte3
GROUP BY [CustomerID], [CustomerName], [DocumentNumber], [ApplyToDocNo], cte3.AppliedAmt, cte3.CustomerPayment, cte3.OriginalAmt, Cnt,  [OPEN A/R]
)
as cte4
where cte4.RowFromCTE > 1 and cte4.CustomerPayment = cte4.OriginalAmt 
GROUP BY [CustomerID], [CustomerName], [DocumentNumber], Cnt,  [OPEN A/R]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte5
GROUP BY [CustomerID], [CustomerName]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte6
GROUP BY [CustomerID], [CustomerName]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte7
GROUP BY [CustomerID], [CustomerName]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte8
GROUP BY [CustomerID], [CustomerName]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte9
GROUP BY [CustomerID], [CustomerName]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte10
GROUP BY [CustomerID], [CustomerName]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte11
GROUP BY [CustomerID], [CustomerName]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte12
GROUP BY [CustomerID], [CustomerName]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte13
GROUP BY [CustomerID], [CustomerName]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte14
GROUP BY [CustomerID], [CustomerName]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte15
GROUP BY [CustomerID], [CustomerName]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte16
GROUP BY [CustomerID], [CustomerName]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte17
GROUP BY [CustomerID], [CustomerName]
UNION
select [CustomerID], [CustomerName], SUM([OPEN A/R]) --[DocumentNumber], Cnt, [OPEN A/R] 
from cte18
GROUP BY [CustomerID], [CustomerName]
HAVING SUM([OPEN A/R]) <> 0
ORDER BY [CustomerID]

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Below is code where I tried to create everything as a cte.  
After all you can change the code? If so, I already told you in https://www.experts-exchange.com/questions/28972976/How-to-Make-T-SQL-Queries-Run-Faster.html#a41838403 part of what's wrong with the code.
Indexes it will be a great help so you should insist on their creation. You can use the excuse for testing purposes and if no improvement just drop the created indexes as this won't harm the database.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
our IT Dept does not want indexes done on the tables.
Who wrote that query in the first place? If someone from IT department then tell them that the query as it is is horrible and they need to fix it. For sure it is possible and I bet performance can be improved without any indexing but just by tweaking the code to make it more efficient. Just follow the suggestions given so far. However, if you create a couple of indexes on those tables IT WILL NOT HEART anything! The IT restriction is pure and simple ridiculous!
0
 

Author Comment

by:John Ellis
Comment Utility
Vitor:

I appreciate your help and the help of everyone here!  You all are much smarter than I, and I'm blessed to have you all helping me!

But, I do need to address the comment that you just made as to your saying that you have already told me what's wrong.  If I'm not mistaken, I pasted the comment that you're referring to below in italics from that posting.

I have to conduct the CASE statements that way, because the sample of customers that I have pulled data for as part of my testing have shown incorrect results when I compare the results to the ERP system's report.  (That report is correct and is the standard to go by.)

I don't understand why do you have CASE statements with RM20101.DOCNUMBR in (select APFRDCNM from RM20201) when you have those columns in the JOIN statement:
from RM20101
             INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
You all are much smarter than I
Not smarter but more knowledge in database field. I guess that you are better than us in other fields.

With my comment I wanted to say that for example for the first CTE you can have a more simple solution:
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.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',
							'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981')
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.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),

Open in new window

Only thing I did was removing the code I stated in my comment above. Are you be able to perform this change and check if it returns the same records but faster than before?
0
 

Author Comment

by:John Ellis
Comment Utility
Hi Vitor:

Thank you, for making me feel better!  I appreciate that!

I will need to ask for your forgiveness, again.  When I look at the revised code that you just posted on the first of my CTEs, I'm having a hard time determining what the difference is.  I'm not seeing much in the way of how this code differs from mine.

Would you mind please showing the clauses that you changed, so that I may understand and test the difference?

Thanks, again!

John
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
In bold the piece of code that I've removed:

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',
                                          '0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981')
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),
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Oh, no.
Now that I'm reviewing it I just realize that I made a mistake. It should be cte2 and not cte1. Please let me provide you the correct solution.
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points (awarded by participants)
Comment Utility
So, new version of cte2:
cte2 as 
(
	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 RM20201.ApplyFromGLPostDate <= @AGE
				and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' AND RM20201.APFRMAPLYAMT = RM20101.ORTRXAMT 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',
									'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))   
	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, RM20201.ApplyFromGLPostDate
	HAVING 
		CASE 
			WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE <= @AGE and RM20201.DATE1 > @AGE and RM20201.APTODCDT > @AGE and RM20201.APFRDCDT <= @AGE and RM20201.ApplyFromGLPostDate <= @AGE
				and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' AND RM20201.APFRMAPLYAMT = RM20101.ORTRXAMT THEN RM20201.APPTOAMT * -1 
			ELSE 0 
		END	<> 0
		),

Open in new window


And what I've removed it's in bold and that's because that filter is already done by the underlined piece of code:

cte2 as
(
      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 RM20201.ApplyFromGLPostDate <= @AGE
                        AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' AND RM20201.APFRMAPLYAMT = RM20101.ORTRXAMT 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',
                                                      '0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))  
      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, RM20201.ApplyFromGLPostDate
      HAVING
            CASE
                  WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE <= @AGE and RM20201.DATE1 > @AGE and RM20201.APTODCDT > @AGE and RM20201.APFRDCDT <= @AGE and RM20201.ApplyFromGLPostDate <= @AGE
                        AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' AND RM20201.APFRMAPLYAMT = RM20101.ORTRXAMT THEN RM20201.APPTOAMT * -1
                  ELSE 0
            END      <> 0),
0
 

Author Comment

by:John Ellis
Comment Utility
Hi Vitor:

Yes, your modification makes cte2 faster!  :)  

Without the modification, it takes 1 minute, 58 seconds for the query to run.  

With the modification, it takes only 53 seconds.

And, both times, I ran cte2 for all customers--not just my sample list!

Very good!  Thank you!

Now, I've got to (a) wade through all of the other parts of the "monster query" to see what I can do similarly to fix it and (b) be careful on changing the biggest part of my query into multiple CTEs if I choose to do so.  

When I did "b" last night, as an experiment, I noticed this morning that it gave me inaccurate figures for at least one of my sample customers.  I don't know how I managed that, but....    :)

Okay.  The most important thing that I need to do, right now anyway, is "a".  I'll do so, now.  I hope to be able to draw from your wisdom and others here, in order to find what I need to modify.

Thanks, so much, for your kindness and support!

John
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points (awarded by participants)
Comment Utility
That's good news then even we still have much to do.
In the main SELECT you can find more RM20101.DOCNUMBR in (select APTODCNM from RM20201) expressions. If you remove all of them you'll have your main query improved.
0
 

Author Comment

by:John Ellis
Comment Utility
Thanks, again, Vitor!

John
0
 

Author Comment

by:John Ellis
Comment Utility
Hi Vitor:

Well, guess what?  After finding a few other redundant clauses to remove, I decided to run the revised query for all customers.

Instead of taking hours to execute, it only took 18 minutes!!!

That's still a tad too slow, for what's going to be expected of this report.  But, hey, to me that's still great!!!

And, if I cannot find other items that I can remove "affordably" in order to decrease the time, I can also use the other suggestion in this case on creating a stored procedure based on temp tables.

Now, there are some inaccuracies in the figures returned.  But, I have verified that those inaccuracies have absolutely nothing to do with the modifications that you and I made today!

We have definitely been able to cut down the amount of time to where I can comfortably run the query for all customers, until I get the correct amounts!  (In order to get the correct amounts, I'll need to review parts of the query individually to determine where the inaccuracies are.)

So, I have at least gotten mostly to the point that I wanted to "be at"!

Again, thanks so much, for your help!  

I'll continue to keep everyone posted!

John
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
That's great news, John.
Reports are not intended to be fast as regular queries because usually they deal with a large amount of data but nobody also want to wait large minutes to get a report.
If possible post here your actual query and the Query Execution Plan for the same query.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
John, do you want to close this question? It has enough information that helped you with the issue?
1
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
As stated by the author the removal of redundant queries solved the poor performance issue.
1

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 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

21 Experts available now in Live!

Get 1:1 Help Now