Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

T-SQL:  Syntax for "HAVING COUNT"

Posted on 2016-09-25
7
Medium Priority
?
74 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 30

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

660 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