Delirious
asked on
Group By Query not returning expected resiults
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!
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,
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,
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.
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.
ASKER
-thanks