Posted by DFS on 08/07/06 12:21

jsfromynr wrote:
> Hi There,
> I think IF out of this sample data you are gathering info of total how
> many visits have been made (excluding duplicate visits by emp)
>
> VISITID,BLDGCODE,VISITDATE,EMPID,VISITCODE
> 1, BLDG1, 10/18/2005, 128, V6
> 2, BLDG1, 10/18/2005, 128, V9
> 3, BLDG2, 1/24/2006, 128, V8
> 4, BLDG2, 1/24/2006, 165, V22
> 5, BLDG3, 2/15/2006, 13, V14
>
> Select BLDGCODE,VISITDATE ,Count(Distinct EMPID)
> From
> YourTable
> Group By BLDGCODE,VISITDATE
>
> Result :
> BLDG1 10/18/2005 1
> BLDG2 1/24/2006 2
> BLDG3 2/15/2006 1
>
>
> I hope this helps.

Thanks Jatinder, but that's not what I was looking for. The answer CELKO
provided (the V2 section) formed the basis for this:

SELECT V.*
FROM VISITS V,
(
SELECT BLDGCODE, VISITDATE
FROM VISITS
GROUP BY BLDGCODE, VISITDATE
HAVING MIN(EMPID) <> MAX(EMPID)
AND MIN(VISITCODE) <> MAX(VISITCODE)
) V2
WHERE (V.BLDGCODE = V2.BLDGCODE)
AND (V.VISITDATE = V2.VISITDATE);




> With Warm regards
> Jatinder Singh
> http://jatindersingh.blogspot.com
> http://sqloracle.tripod.com

[Back to original message]