Solved

T-SQL:  Syntax for "HAVING COUNT"

Posted on 2016-09-25
7
55 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 28

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 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to share SSIS Package? 6 37
Query group by data in SQL Server - cursor? 3 34
sqlserver get datetime field and create a string 5 18
awk and Pythagoras? 5 8
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

809 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