Solved

T-SQL:  Syntax for "HAVING COUNT"

Posted on 2016-09-25
7
72 Views
Last Modified: 2016-10-02
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

Open in new window

Clause-Results.xlsx
CustomerID	DocumentNumber	OPEN A/R
0100012160	P0192700A           -7.86
0100012160	P10315EHF           -239.81
266267	G1676571A           -7.73
266267	G1676571A           -3.07

Open in new window



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

Open in new window

One APTODCNM

select * from RM20101 where CUSTNMBR = '266267' AND DOCNUMBR = 'G1676571'
select * from RM20201 where CUSTNMBR = '266267' AND APFRDCNM = 'G1676571A'

Open in new window

Three APTODCNMs
0
Comment
Question by:John Ellis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41815248
You can use a subquery like this (pseudocode):
where (select count(*) from <same logic as the main query...> ) = 1

Open in new window

0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41815268
John,

Your query syntax is incorrect thats why you are getting that error.

Can you post input and the output required. I shall write your query.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41815546
so the former question was not the solution?

You currently get:
CustomerID	DocumentNumber	OPEN A/R
0100012160	P0192700A           -7.86
0100012160	P10315EHF           -239.81
266267	G1676571A           -7.73
266267	G1676571A           -3.07

Open in new window

But only want:
CustomerID	DocumentNumber	OPEN A/R
0100012160	P0192700A           -7.86
0100012160	P10315EHF           -239.81

Open in new window


Why do you ask for that customer id the bit in bold below?

AND (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453'     , '266267'   ))
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:John Ellis
ID: 41817021
Hi Pawan and Portlet:

Please forgive me.  But, I thought that I had already given the input and output.  Please let me know what I'm not providing.

I don't want 266267.  I warned you that it's a long story.  But, you asked.  So, here it is.

The reason that I don't want 266267 is because the attached query is a part of a larger query containing select statements joined by "UNION".

The figures that the attached query derives for 266267 are already being pulled by another select statement within the "larger query".

If I don't get rid of the 266267 figures that the attached query is displaying, my total figures from my "larger query" will be "doubled-up".

So, if I don't specify 266267 as a part of the attached query in my testing, how am I going to prove that it will not return values for 266267?

And, unfortunately, the information that we discussed in that prior case is nothing that I have been able to use, here.  I have tried a billion ways to Sunday.  But, no luck.

John
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41817172
The inputs to your query are ALL the tables the query references.

The best and most well proven technique for solving  sql questions involves
a: "sample data" (for all tables involved in the query), and
b: expected result
note the term "sample" indicates:
small
clean (does not contain anything private, e.g. names can be omitted or changed)

This can be require some effort to provide, and the sample data does need to contain the problem to be solved.

If I have done this correctly I think your query requires these:
CN00500.CRDTMGR

RM00101.COMMENT1
RM00101.COMMENT2
RM00101.CUSTNAME
RM00101.CUSTNMBR
RM00101.PYMTRMID

RM20101.CURTRXAM
RM20101.CUSTNMBR
RM20101.DOCDATE
RM20101.DOCNUMBR
RM20101.DUEDATE
RM20101.DUEDATE
RM20101.ORTRXAMT
RM20101.VOIDSTTS0

RM20201.APFRDCDT
RM20201.APFRDCNM
RM20201.APFRMAPLYAMT
RM20201.APPTOAMT
RM20201.APTODCDT
RM20201.APTODCNM
RM20201.CUSTNMBR
RM20201.DATE1
RM20201.ORAPTOAM

Open in new window

so "sample data" would need to cover all of those (RM00101.CUSTNAME could be omitted or scrubbed)

Then your "expected result" should be derived purely from the sample data you provide (so we can develop a query to meet that expectation).
0
 

Accepted Solution

by:
John Ellis earned 0 total points
ID: 41817988
Hello:

I solved this, on my own.  

I used a COUNT(*) clause, as follows:  
COUNT(*) OVER (PARTITION BY RM20101.CUSTNMBR, RM20201.APTODCNM, RM20201.APTODCDT) as Cnt.

Specifically, I embedded this clause within a cte statement as shown in the code below.

John

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

;with cte as 
(
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
COUNT(*) OVER (PARTITION BY RM20101.CUSTNMBR, RM20201.APTODCNM, RM20201.APTODCDT) AS Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT > @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR not in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
and RM20201.APPTOAMT = RM20101.ORTRXAMT 
GROUP BY RM20201.APTODCNM, RM20201.APTODCDT, RM20201.APFRDCNM)
AND
RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT 
AND RM20101.DUEDATE <> ''
THEN RM20201.APFRMAPLYAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN ('266267',  '0100012160'))  
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20101.RMDTYPAL, RM20101.DUEDATE, RM20201.APFRMAPLYAMT, RM20101.DOCNUMBR, RM20201.APFRDCNM, RM20101.DOCDATE,
RM20201.DATE1, RM20201.APTODCDT, RM20201.APPTOAMT, RM20201.APTODCNM, RM20201.APFRDCDT, RM20101.ORTRXAMT, RM20101.CURTRXAM
HAVING 
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT > @AGE AND RM20201.APFRDCDT < @AGE
AND RM20101.DOCNUMBR not in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = RM20101.DOCNUMBR
and RM20201.APPTOAMT = RM20101.ORTRXAMT 
GROUP BY RM20201.APTODCNM, RM20201.APTODCDT, RM20201.APFRDCNM)
and
RM20101.ORTRXAMT <> RM20101.CURTRXAM 
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT and RM20201.APPTOAMT <> RM20101.ORTRXAMT 
AND RM20101.DUEDATE <> '' AND RM20101.DOCNUMBR = RM20201.APFRDCNM
THEN RM20201.APFRMAPLYAMT * -1 ELSE 0 END
<> 0)
select * from cte where Cnt > 1;

Open in new window

0
 

Author Closing Comment

by:John Ellis
ID: 41825238
I figured this out completely on my own.  

With all due respect to the experts who chimed in and of whom I respect very much, the COUNT(*) clause that I researched and tested was not mentioned as an option.
0

Featured Post

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

627 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