Solved

T-SQL:  Syntax for "HAVING COUNT"

Posted on 2016-09-25
7
44 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
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 17

Expert Comment

by:Pawan Kumar Khowal
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 48

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 48

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 27
t-sql splitting string column 5 25
Convert int to military time 8 20
Change part of a string 2 16
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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

12 Experts available now in Live!

Get 1:1 Help Now