October 10, 2016 at 5:44 pm
Hello:
I know that this is a broad question. But, below is my SQL query code.
I have tried running it, at times, for close to an hour and it does not finish executing.
How can I make this query run faster?
One thing that I tried that frankly did not help was to place SQL's "WITH(INDEX(..." syntax, after the name of every "from
" 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
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),
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)
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)
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 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)
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)
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 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)
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)
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)
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)
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]
October 10, 2016 at 8:39 pm
Did you look at the estimated and actual execution plans yet, or are you playing a guessing game?
October 11, 2016 at 12:55 am
Without visibility of your table structures, indexes, and actual query plans, it's difficult to look for the issues.
That said, that's quite a large query with a lot of UNIONs.
At a quick glance the same 4 tables (RM20201, RM20101, RM00101, CN00500) are being hammered over and over again. Depending on the size of these tables, it may help to extract a subset of these tables into a #Temp table, or even a staging table, and then run your query against that subset. Depending on the size of the subset of data, indexing the #Temp table/ staging table may also add performance gains.
Try to divide and conquer.
* Run each section (cte or union query) as a stand alone query, and optimise it.
* Rather than force the use of an existing index, try creating one or more indexes that SQL can use effectively. A good starting point for indexes is ... Index On Table (Columns used in WHERE, Columns used in JOIN) INCLUDE (Columns required for output)
* Try add the output of each section into its own #Temp table then union the results with a final query
October 11, 2016 at 2:21 am
This query has more repeats than the Disney channel. It's pointless attempting to tune it as it is. Pass it on to a professional TSQL dev to rewrite from scratch. The end result is likely to be tiny in comparison - and tunable.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 11, 2016 at 3:36 am
John Roy Ellis (10/10/2016)
One thing that I tried that frankly did not help was to place SQL's "WITH(INDEX(..." syntax, after the name of every "from" 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?
Oh, no, no, no, don't do that.
The query optimiser chooses a good index based on the data and the query. An index hint will override the optimiser's choice, forcing it to use the index that you select (and it has to be one). Picking one index at random and forcing the optimiser to use it no matter what is a near guaranteed performance degradation.
Stay away from hints unless you know exactly what they do and are sure that you know better than the query optimiser how to run the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 11, 2016 at 3:45 am
Here's the query formatted to be readable. A single query roughly 850 lines long. Tuning that will take a professional days, and will probably involve splitting the query up into smaller parts.
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
),
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)
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(WATERFALL2.[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 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)
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)
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 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)
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)
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)
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)
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,
cte.Cnt,
cte.[OPEN A/R]
FROM cte
WHERE cte.Cnt > 1
UNION
SELECT CustomerID,
CustomerName,
DocumentNumber,
cte2.Cnt,
cte2.[OPEN A/R]
FROM cte2
UNION
SELECT CustomerID,
CustomerName,
DocumentNumber,
cte3.Cnt,
cte3.[OPEN A/R]
FROM cte3
WHERE (cte3.CustomerID + '' + cte3.DocumentNumber) NOT IN (SELECT (cte.CustomerID + '' + cte.DocumentNumber)
FROM cte)
UNION
SELECT CustomerID,
CustomerName,
DocumentNumber,
cte4.Cnt,
cte4.[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;
Some obvious problems:
(cte3.CustomerID + '' + cte3.DocumentNumber) NOT IN (SELECT (cte.CustomerID + '' + cte.DocumentNumber)
Guaranteed to prevent good index usage. Try replacing the NOT IN with a NOT EXISTS
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
HAVING is for filters on aggregates, there's no aggregate there. There's also no reason I can see for the Group By, as the only aggregation anywhere in that query is a COUNT() OVER (...), which doesn't need a Group By, plus the presence of the CASE prevents good index usage
Same goes for just about every other portion of that query.
Break this up. Replace the CTEs with temp tables, sort out the near-duplicates with UNION statements (looks like they could be ORs within the CASE instead) and tune it in pieces.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 11, 2016 at 3:49 am
It shouldn't be too hard to replace this awful mess with something sensible and streamlined.
Here's an attempt to rewrite cte2 along more conventional lines. It's about 10 minutes worth
of effort so don't expect the results to exactly match the original - but they will be close.
I'm sure the other cte's would be as economical to do. To establish whether or not it's worth doing,
just compare the execution times. Remember, this is UNTUNED. The old version would be very tricky to tune
because of the crazy GROUP BY / HAVING. The new version would be quite easy.
-- Old CTE2
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
------------------------------------------------------------------------
-- New CTE2
-- Redundant join removed
-- Redundant filter removed
-- Crazy GROUP BY / HAVING replaced with WHERE
-- Table aliases added to aid readability and facilitate subqueries
SELECT
[CustomerID]= doc.CUSTNMBR,
[CustomerName]= cst.CUSTNAME,
[DocumentNumber] = doc.DOCNUMBR,
[Cnt]= 0,
[OPEN A/R]= app.APPTOAMT * -1
FROM RM20101 doc
INNER JOIN RM00101 cst
ON doc.CUSTNMBR = cst.CUSTNMBR
INNER JOIN RM20201 app
ON doc.DOCNUMBR = app.APFRDCNM --
AND doc.CUSTNMBR = app.CUSTNMBR
AND doc.ORTRXAMT = app.APFRMAPLYAMT
--LEFT OUTER JOIN CN00500
--ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE doc.VOIDSTTS = 0
AND doc.RMDTYPAL > 6
AND doc.DOCDATE <= @AGE
AND doc.ORTRXAMT <> doc.CURTRXAM
AND doc.DUEDATE <> ''
--AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201)
AND app.DATE1 > @AGE
AND app.APTODCDT > @AGE
AND app.APFRDCDT <= @AGE
AND app.ApplyFromGLPostDate <= @AGE
AND app.APPTOAMT <> 0
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 11, 2016 at 6:28 am
What everyone else has said, plus.
If you're going to attempt to tune the query, you absolutely have to have the execution plans. There is no way you can poke your way through guesses at query hints to better performance. You need to work at it from a position of knowledge, of T-SQL, of the optimizer, and, of the choices the optimizer is making on your behalf. Follow the advice above, but most of all, get the execution plans in order to understand what's happening as you start working through the tuning process. Remember to make small, incremental changes so that you know what's going wrong. Also, frequently, it's a good idea to slowly rebuild the query. Start at the most basic and build it back up in order to arrive at the correct conclusion, while consulting the execution plans. Then you'll have a better understanding of exactly where things go wrong.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 11, 2016 at 7:19 am
Grant Fritchey (10/11/2016)
What everyone else has said, plus.If you're going to attempt to tune the query, you absolutely have to have the execution plans. There is no way you can poke your way through guesses at query hints to better performance. You need to work at it from a position of knowledge, of T-SQL, of the optimizer, and, of the choices the optimizer is making on your behalf. Follow the advice above, but most of all, get the execution plans in order to understand what's happening as you start working through the tuning process. Remember to make small, incremental changes so that you know what's going wrong. Also, frequently, it's a good idea to slowly rebuild the query. Start at the most basic and build it back up in order to arrive at the correct conclusion, while consulting the execution plans. Then you'll have a better understanding of exactly where things go wrong.
The OPs biggest problem here is that he doesn't have the actual execution plan.
John Roy Ellis (10/10/2016)
I have tried running it, at times, for close to an hour and it does not finish executing.
So he will have to break the current query down and build it back up again, tuning it as he goes.
October 11, 2016 at 8:15 am
DesNorton (10/11/2016)
Grant Fritchey (10/11/2016)
What everyone else has said, plus.If you're going to attempt to tune the query, you absolutely have to have the execution plans. There is no way you can poke your way through guesses at query hints to better performance. You need to work at it from a position of knowledge, of T-SQL, of the optimizer, and, of the choices the optimizer is making on your behalf. Follow the advice above, but most of all, get the execution plans in order to understand what's happening as you start working through the tuning process. Remember to make small, incremental changes so that you know what's going wrong. Also, frequently, it's a good idea to slowly rebuild the query. Start at the most basic and build it back up in order to arrive at the correct conclusion, while consulting the execution plans. Then you'll have a better understanding of exactly where things go wrong.
The OPs biggest problem here is that he doesn't have the actual execution plan.
John Roy Ellis (10/10/2016)
I have tried running it, at times, for close to an hour and it does not finish executing.So he will have to break the current query down and build it back up again, tuning it as he goes.
I'd suggest he just get the estimated plan. Most of the time they're going to be the same (except in the case of recompiles caused by changes in stats). Plus, if the query takes hours+ to run, just get the estimated plan to start. However, 100% agreement on the breaking the query down. There's really no other way to go about it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 11, 2016 at 8:24 am
Grant Fritchey (10/11/2016)
DesNorton (10/11/2016)
Grant Fritchey (10/11/2016)
What everyone else has said, plus.If you're going to attempt to tune the query, you absolutely have to have the execution plans. There is no way you can poke your way through guesses at query hints to better performance. You need to work at it from a position of knowledge, of T-SQL, of the optimizer, and, of the choices the optimizer is making on your behalf. Follow the advice above, but most of all, get the execution plans in order to understand what's happening as you start working through the tuning process. Remember to make small, incremental changes so that you know what's going wrong. Also, frequently, it's a good idea to slowly rebuild the query. Start at the most basic and build it back up in order to arrive at the correct conclusion, while consulting the execution plans. Then you'll have a better understanding of exactly where things go wrong.
The OPs biggest problem here is that he doesn't have the actual execution plan.
John Roy Ellis (10/10/2016)
I have tried running it, at times, for close to an hour and it does not finish executing.So he will have to break the current query down and build it back up again, tuning it as he goes.
I'd suggest he just get the estimated plan. Most of the time they're going to be the same (except in the case of recompiles caused by changes in stats). Plus, if the query takes hours+ to run, just get the estimated plan to start. However, 100% agreement on the breaking the query down. There's really no other way to go about it.
Absolutely. Each of those UNIONed blocks can be run and tuned independently of the others. That makes a rewrite much easier than it might otherwise be, because there's already a trusted query to test results against.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply