Solved

Group By Query not returning expected resiults

Posted on 2009-05-15
4
290 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Title # Comments Views Activity
Updating a field based from a if exist.... 2 42
SQL Pivot table 2 52
SQL Syntax 6 56
Access 2010 Only Including Numbers After Certain Limit 3 39
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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