Solved

Group By Query not returning expected resiults

Posted on 2009-05-15
4
294 Views
Last Modified: 2012-05-07
Hello,

I am running a Query to return records where two items Item where both selected on thesame date.

Field names are: CPTCOD - values are '94002' and '94003' (these are teh item codes)
                           SCVDAT - is the date Field

The current Query is returning the following results:
SVCDAT                     SVCCOD
02/20/2009      94002
05/06/2009      94002
05/06/2009      94002
05/10/2009      94003
05/01/2009      94003
05/02/2009      94003

When it should be returning NO records as there are no instances where both 94002 and 94003 where selected on the same date.

Thank you!
PROC SQL;
Create table main as
Select distinct l.*
FROM ctemp.Proc20090513 l
 
Where l.CPTCOD in ('94002', '94003')
AND l.SVCDAT in (SELECT l.SVCDAT From ctemp.Proc20090513 l
		WHERE l.CPTCOD in ('94002', '94003')
                  	GROUP BY l.SVCDAT HAVING COUNT(DISTINCT l.CPTCOD) > 1)

Open in new window

0
Comment
Question by:Delirious
[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
  • 2
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
joeyw earned 250 total points
ID: 24394875
so you are looking for records that have both svccode selected on the same day?

the in statement is really an "or" check.

try changing your where clause like this:

Where l.CPTCOD = '94002')
AND l.SVCDAT in (SELECT l.SVCDAT From ctemp.Proc20090513 l
                WHERE l.CPTCOD = '94003' )

the first part will pull any 94002 rows, the second part will pull any 94003 rows and the in will only match when the dates are the same.
                       
0
 

Author Comment

by:Delirious
ID: 24395272
Would I get rid of the last statement - GROUP BY l.SVCDAT HAVING COUNT(DISTINCT l.CPTCOD) > 1) ?

-thanks
0
 

Author Comment

by:Delirious
ID: 24395453
I am assumming yes, after trying it out.

However I still don't think it is correct...but closer.

I am now getting
02/20/2009      94002
05/06/2009      94002
05/06/2009      94002

I think there should be no results found since these records dont have matcing '94003' codes selected on the same day.

if there where records (which there aren't)I would like to see...
02/20/2009      94002
02/20/2009      94003
05/06/2009      94002
05/06/2009      94003

I am testing against this scenerio as it indicates customers are being double charged....

Thanks,
0
 
LVL 4

Expert Comment

by:joeyw
ID: 24395499
Is there also a customer number field?  In sql statement, if one customer has 94002 and a different customer has 94003, then you will get results.  If you have a customer identifier change the statment this way:

Where l.CPTCOD = '94002')
AND l.SVCDAT in (SELECT l.SVCDAT From ctemp.Proc20090513 l
                WHERE l.CPTCOD = '94003'  and l.customerid=customerid)

and yes leave off the group by distinct.
0

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.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

688 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