Solved

T-SQL:  Trying to Not Pull Data for Two Rows That Do Not Meet the Criteria

Posted on 2016-09-18
15
69 Views
Last Modified: 2016-09-24
Hello:

Yes, I have twelve files embedded.  And, yes, once again I'm having trouble with very simple T-SQL syntax.

A lot of the files that I have attached are for background purposes.  What I'm having trouble with, specifically, is shown within the SQL file labeled "Combined_0100010453 and 266267.sql".  

Please don't ask me why.  But, per the results shown from its corresponding Excel file ("Combined_0100010453 and 266267.xlsx"), I do not want the two rows that are appearing for customer 266267.

Other than simply saying "where "RM20101.CUSTNMBR <> '266267", please let me know how I can use the criteria that I have specified in this query, while still allowing for these two rows to not be displayed in the results.  

I'm thinking that there must be something that I can place in that subquery "(select APFRDCNM from RM20201)".  But, I'm not smart enough to figure out what.

Again, please let me know how to keep the rows for 0100010453, while not keeping the rows for 266267.

By the way, you will see the amounts for these two rows within the .xlsx file entitled "RM20201_266267" in cells O34 and O35.

Thank you!  Much appreciated!

John

RM20101_266267.sql
RM20201_266267.sql
RM20101_0100010453.sql
RM20201_0100010453.sql
RM20101_266267.xlsx
RM20201_266267.xlsx
RM20101_0100010453.xlsx
RM20201_0100010453.xlsx
Combined_0100010453-and-266267.sql
Combined_0100010453-and-266267.xlsx
Waterfall-Results.sql
Waterfall-Results.xlsx
0
Comment
Question by:John Ellis
  • 6
  • 4
  • 2
  • +1
15 Comments
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@Author - Could you please post a query that you want us to modify for you .. I shall do it right away.
0
 

Author Comment

by:John Ellis
Comment Utility
Certainly.  I'm sorry.  I have of personal problems.  I forgot--literally--about posting the code.

Again, I'm sorry.  I'll do this, when I get into the office, tomorrow.

Please take it easy on me.  I cried today and considered ending myself.
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@John - Never think that sort of thing. Life if beautiful but sometime we may get hard time.  No problem you can take your time for posting the code.
1
 

Author Comment

by:John Ellis
Comment Utility
Thank you, Pawan!  I wish more people like you were as kind to those like me who have serious memory problems and mental illness.

It's bad enough that I have no skills.  But, I really was about to cry again when that other guy called me "Dude" and used capital letters on the word "CODE".  It just reminds me that I'm worthless and can't take care of myself.

Thanks again, though, for your kindness.  You don't see any of that, in this field.

John
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
In this file:
https://filedb.experts-exchange.com/incoming/2016/09_w39/1117317/Combined_0100010453-and-266267.sql
the existing code  contains
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN ('0100010453', '266267'))

Change that to:
WHERE RM20101.VOIDSTTS = 0 and RM20101.CUSTNMBR IN ('0100010453')
or
WHERE RM20101.VOIDSTTS = 0 and M20101.CUSTNMBR IN = '0100010453'

A more comprehensive revision to that query would include removing the GROUP BY and HAVING clause as well as the change shown above, like this:
DECLARE @AGE datetime
DECLARE @RUN datetime
SET @AGE = '2015-09-30 00:00:00.000'
SET @RUN = '2016-07-31 00:00:00.000'

SELECT
        RM20101.CUSTNMBR AS [CustomerID]
      , RM00101.CUSTNAME AS [CustomerName]
      , RM20101.DOCNUMBR AS [DocumentNumber]
      , CASE
                WHEN RM20101.RMDTYPAL > 6 AND
                        RM20201.APTODCDT > @AGE AND
                        RM20201.DATE1 > @AGE AND
                        RM20201.APFRDCDT < @AGE AND
                        RM20101.DOCNUMBR IN (SELECT
                                        APFRDCNM
                                FROM RM20201) AND
                        RM20101.ORTRXAMT <> RM20101.CURTRXAM AND
                        RM20101.DUEDATE <> '' AND
                        RM20101.RMDTYPAL <> RM20201.APTODCTY THEN RM20201.APPTOAMT * -1
                ELSE 0
        END              
        AS [OPEN A/R]
FROM RM20101
INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 ON RM20101.DOCNUMBR = RM20201.APFRDCNM
        AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE RM20101.VOIDSTTS = 0
AND RM20101.CUSTNMBR IN ('0100010453')
AND
   CASE
           WHEN RM20101.RMDTYPAL > 6 AND
                   RM20201.APTODCDT > @AGE AND
                   RM20201.DATE1 > @AGE AND
                   RM20201.APFRDCDT < @AGE AND
                   RM20101.DOCNUMBR IN (SELECT
                                   APFRDCNM
                           FROM RM20201) AND
                   RM20101.ORTRXAMT <> RM20101.CURTRXAM AND
                   RM20101.DUEDATE <> '' AND
                   RM20101.RMDTYPAL <> RM20201.APTODCTY THEN RM20201.APPTOAMT * -1
           ELSE 0
   END <> 0

Open in new window

The HAVING clause is necessary IF you are filtering on aggregated values such as you get from SUM()  or COUNT() etc.

As you are not use any aggregates you don't need the HAVING clause, and you probably do not need the GROUP BY clause (although I cannot be sure).

If you get more rows than you expected you could re-introduce the GROUP BY or use "select distinct" instead.
0
 

Author Comment

by:John Ellis
Comment Utility
Hi All:

I do, in fact, need the HAVING clause in order to filter out any values that = 0.  

Also, it's a long story, but it's not as simple as filtering out a customer ID.

Anyway, below is my code.  Again, as best as I can tell, I do not want or need those first two rows in the spreadsheet that I mentioned earlier.

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

select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName],  RM20101.DOCNUMBR as [DocumentNumber],
CASE WHEN RM20101.RMDTYPAL > 6 
and RM20201.APTODCDT > @AGE and RM20201.DATE1 > @AGE and RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' and RM20101.RMDTYPAL <> RM20201.APTODCTY
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN ('0100010453', '266267')) 
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 APFRDCNM from RM20201) and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' and RM20101.RMDTYPAL <> RM20201.APTODCTY
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0

Open in new window


RM20101_0100010453:
select * from RM20101 where CUSTNMBR = '0100010453'

Open in new window


RM20201_0100010453:
select * from RM20201 where CUSTNMBR = '0100010453'

Open in new window


RM20101_266267:
select * from RM20101 where CUSTNMBR = '266267'

Open in new window


RM20201_266267:
select * from RM20201 where CUSTNMBR = '266267' 

Open in new window


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

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

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>I do, in fact, need the HAVING clause in order to filter out any values that = 0.  
Paul is correct.  The HAVING is basically a WHERE clause for an aggregate value such as SUM, COUNT, etc., and I don't see any aggregates in the HAVING in the above code, so it would appear that that block should be in the WHERE clause.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:John Ellis
Comment Utility
Good point.  But, can we honestly say that removing the HAVING clause represents the silver bullet.  If it does, that's great.  I'm just trying to make sure.

John
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>But, can we honestly say that removing the HAVING clause represents the silver bullet
The only person that will be able to determine this is you, as experts here do not have the ability to connect to your data source(s), run queries, and view the results.   We are limited to reading requirements, looking at posted T-SQL, and making recommendations.

So you'll have to rewrite your queries to move the HAVING logic into the WHERE, run, and then tell us how it went.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
John.

I'm afraid the having clause comment really isn't the silver bullet. It was provided purely as a matter of syntax and performance.

The where clause is performed before a group by clause and it reduces the rows assessed by the group by. A having clause is performed after a group by clause and is designed to reduce the rows of the final result by being able to assess calculations performed during the group by.

Because there are no calculations being performed there is no point in using a having clause, you can achieve the same filtering logic without it.

What I cannot determine is if you do really need the group by clause. I  suspect you don't nèed it but cannot be certain without the ability to test.

Despite the conversation on the use of having your question asks to remove 2 rows from the result. Both those rows refer to client 266267. Your existing where clause specifically includes that client. I see nothing in the query that would indicate you need to do that.

Could you try running your existing query with just the removal of  266267 from the where clause as I  suggested? What happens to the result? Don't worry about the having clause recommendation for this test.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
By the way. Because we cannot see any of your tables and data we may ask things you already know. That may be frustrating but we can only read the information on thls page.

You mention that there is a "long story" about the existing where clause, but I'm afraid that unless we understand that long story we might not find your solution.

You also state:
I'm thinking that there must be something that I can place in that subquery "(select APFRDCNM from RM20201)".
But you already have this condition in the joins:
               INNER JOIN RM20201 ON RM20101.DOCNUMBR = RM20201.APFRDCNM

Why not try without that subquery? e.g.
DECLARE @AGE datetime
DECLARE @RUN datetime
SET @AGE = '2015-09-30 00:00:00.000'
SET @RUN = '2016-07-31 00:00:00.000'

SELECT
      RM20101.CUSTNMBR AS [CustomerID]
    , RM00101.CUSTNAME AS [CustomerName]
    , RM20101.DOCNUMBR AS [DocumentNumber]
    , CASE WHEN RM20101.RMDTYPAL > 6 AND
      RM20201.APTODCDT > @AGE AND
      RM20201.DATE1 > @AGE AND
      RM20201.APFRDCDT < @AGE AND
--      RM20101.DOCNUMBR IN (
--            SELECT
--                  APFRDCNM
--            FROM RM20201
--      ) AND
      RM20101.ORTRXAMT <> RM20101.CURTRXAM AND
      RM20101.DUEDATE <> '' AND
      RM20101.RMDTYPAL <> RM20201.APTODCTY THEN RM20201.APPTOAMT * -1 ELSE 0 END
      AS [OPEN A/R]
FROM RM20101
INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 ON RM20101.DOCNUMBR = RM20201.APFRDCNM
            AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0)
      AND (RM20101.CUSTNMBR IN ('0100010453', '266267'))
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
        --                  APFRDCNM
        --            FROM RM20201
        --      ) AND
        RM20101.ORTRXAMT <> RM20101.CURTRXAM AND
        RM20101.DUEDATE <> '' AND
        RM20101.RMDTYPAL <> RM20201.APTODCTY THEN RM20201.APPTOAMT * -1 
  ELSE 0 END <> 0

Open in new window

What happens?
0
 

Author Comment

by:John Ellis
Comment Utility
Hello:

I solved this, by using "HAVING...COUNT", in the subquery, as shown below.

Thanks!

John

select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName],  RM20101.DOCNUMBR as [DocumentNumber],
CASE WHEN RM20101.RMDTYPAL > 6 
and RM20201.APTODCDT > @AGE and RM20201.DATE1 > @AGE and RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
where RM20201.APPTOAMT = RM20101.ORTRXAMT 
GROUP BY RM20201.APFRDCNM, RM20101.DOCNUMBR, RM20201.APPTOAMT
HAVING COUNT(RM20201.APTODCNM) = 1) and RM20101.ORTRXAMT 
<> RM20101.CURTRXAM AND RM20101.DUEDATE <> '' and RM20101.RMDTYPAL <> RM20201.APTODCTY
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267'))    
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

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
great! good work
1
 

Author Comment

by:John Ellis
Comment Utility
Thank you, PortletPaul!  Coming from a great expert such as yourself, that's quite a compliment!!!

John
0

Featured Post

How to run any project with ease

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

Join & Write a Comment

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 …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

11 Experts available now in Live!

Get 1:1 Help Now