troubleshooting Question

T-SQL: Syntax for "HAVING COUNT"

Avatar of John Ellis
John Ellis asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
7 Comments1 Solution96 ViewsLast Modified:
Hello:

The results of my first code block below are shown in the attached Excel spreadsheet called "Clause Results".  I don't want the last two rows.  I only want the first two rows.

The second block of code below shows the results that I want--one iteration of the APTODCNM field.

The third block of code shows three iterations of APTODCNM.  I don't want this.

What sort of syntax do I place in the first block of code to tell it to return data where there is only one APTODCNM?  I tried the "HAVING COUNT" syntax but could not get it to work.

Thank you!  Much appreciated!

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'

select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT > @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR not in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
AND RM20201.ORAPTOAM = RM20101.ORTRXAMT
GROUP BY RM20201.APTODCNM, RM20201.APTODCDT, RM20201.APFRDCNM
HAVING COUNT(RM20201.APFRDCNM) = 1)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT 
AND RM20101.DUEDATE <> ''
THEN RM20201.APFRMAPLYAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160'))  
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 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.ORAPTOAM
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.ORAPTOAM = RM20101.ORTRXAMT
GROUP BY RM20201.APTODCNM, RM20201.APTODCDT, RM20201.APFRDCNM
HAVING COUNT(RM20201.APFRDCNM) = 1)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT 
AND RM20101.DUEDATE <> ''
THEN RM20201.APFRMAPLYAMT * -1 ELSE 0 END
<> 0
Clause-Results.xlsx
CustomerID	DocumentNumber	OPEN A/R
0100012160	P0192700A           -7.86
0100012160	P10315EHF           -239.81
266267	G1676571A           -7.73
266267	G1676571A           -3.07


select * from RM20101 where CUSTNMBR  = '0100012160' AND DOCNUMBR IN ('P0192700A')
select * from RM20201 where CUSTNMBR = '0100012160' AND APTODCNM IN ('P0519167')
One APTODCNM

select * from RM20101 where CUSTNMBR = '266267' AND DOCNUMBR = 'G1676571'
select * from RM20201 where CUSTNMBR = '266267' AND APFRDCNM = 'G1676571A'
Three APTODCNMs
ASKER CERTIFIED SOLUTION
John Ellis

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros