Solved

Group By Query not returning expected resiults

Posted on 2009-05-15
4
258 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
  • 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now