Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

Syntax for Count function as a subquery

Hello all.  I have been researching this for quite some time, however, cannot seem to find anything that pertains to my specific question.  I need to write a subquery within the following to only pull records where the account number (customeraccountnumber) appears more than once based on the attached query criteria sample.  Any and all assistance/feeback is greatly appreciated!

Sincerely,
Nikki
SELECT i.[createdbyname],
              ,i.[createdon]
              ,i.[accountidname]
             ,i.[contacttypeidname]
             ,i.[customeraccountnumber]
            ,i.[customercategoryname]
            ,i.[customerstatusname]
            ,i.[primaryreasondetailname]
            ,i.[primaryreasonforcallname]
           ,i.[recordtypename]
           ,i.[secondaryreasondetailname]
          ,i.[secondaryreasonforcallname]            
FROM [Filteredcustomerinteraction] i
where name not like '%no call%' 
and i.name not like '%ASA%' 
and i.name not like '%Wrong department%'
and i.contacttypeidname = 'Call - Inbound'
and i.primaryreasondetailname not like '%transferred call%' 
and i.createdon >= '03/21/2011'
and i.createdon < '03/23/2011'
and i.siteid = '14'

Open in new window

0
Nikki28838
Asked:
Nikki28838
1 Solution
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
Maybe give this a try... (add to your query)

and 1 < (SELECT COUNT(*) FROM FilteredCustomerinteraction x WHERE x.customeraccountnumber = i.customeraccountnumber )
0
 
SharathData EngineerCommented:
try this.
SELECT i.[createdbyname], 
       i.[createdon], 
       i.[accountidname], 
       i.[contacttypeidname], 
       i.[customeraccountnumber], 
       i.[customercategoryname], 
       i.[customerstatusname], 
       i.[primaryreasondetailname], 
       i.[primaryreasonforcallname], 
       i.[recordtypename], 
       i.[secondaryreasondetailname], 
       i.[secondaryreasonforcallname] 
  FROM [Filteredcustomerinteraction] i 
 WHERE i.name NOT LIKE '%no call%' 
       AND i.name NOT LIKE '%ASA%' 
       AND i.name NOT LIKE '%Wrong department%' 
       AND i.contacttypeidname = 'Call - Inbound' 
       AND i.primaryreasondetailname NOT LIKE '%transferred call%' 
       AND i.createdon >= '03/21/2011' 
       AND i.createdon < '03/23/2011' 
       AND i.siteid = '14' 
       AND i.[customeraccountnumber] IN (  SELECT i1.[customeraccountnumber] 
                                             FROM [Filteredcustomerinteraction] i1 
                                            WHERE i1.name NOT LIKE '%no call%' 
                                                  AND i1.name NOT LIKE '%ASA%' 
                                                  AND i1.name NOT LIKE '%Wrong department%' 
                                                  AND i1.contacttypeidname = 'Call - Inbound'
                                                  AND i1.primaryreasondetailname NOT LIKE '%transferred call%'
                                                  AND i1.createdon >= '03/21/2011' 
                                                  AND i1.createdon < '03/23/2011' 
                                                  AND i1.siteid = '14' 
                                         GROUP BY i1.[customeraccountnumber] 
                                           HAVING COUNT(* ) > 1)

Open in new window

0
 
Nikki28838Author Commented:
Thank you so much!  Looking at it, it all makes sense!  I will be able to use this logic over and over!  You are a lifesaver!  

Sincerely,
Nikki
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now