Solved

Group By Query not returning expected resiults

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

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Join multiple pivot queries 2 18
Character matching different date formats for dates between 6 48
SQL Sub-Query Help 22 62
Select only the top record in a left join 13 37
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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.
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…

821 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