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
Solved

Group By Query not returning expected resiults

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
export sql results to csv 6 39
sql server concatenate fields 10 38
Comparison query - 4 columns 9 24
add criteria to query in VB, Access 2003 2 8
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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

856 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