Posted by --CELKO-- on 08/05/06 13:46

I want to get a list of visits made to the same building on the same
day, but by different employees, and for different visit codes (e.g.
records [sic] 5-6 or 9-11)

Why are you posting Oracle dialect in a SQL Server newsgroup? Why are
you using an improper date format? Why are you avoiding the natural key
with a fake "record number" - you even use the word record,
apparently not understanding what a row is and how it is not like a
record at all.

First, translate the dialect into Standard SQL and remove the redundant
non-key:

CREATE TABLE Visits
(bldg_code VARCHAR(10) NOT NULL,
visit_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
emp_id NUMERIC(5,0) NOT NULL,
visit_code VARCHAR(5) NOT NULL,
PRIMARY KEY (bldg_code, visit_date, emp_id, visit_code));

Now, thinking in sets instead of VB or other procedural languages that
have records, fields and files, think in sets. We want groups
(subsets) of rows based on (bldg_code, visit_date) with elements that
are unlike on the other two attributes. Once you say it that way the
query writes itself.

SELECT bldg_code, visit_date, COUNT(*)
FROM Visits AS V
GROUP BY bldg_code, visit_date
HAVING MIN(emp_id) <> MAX(emp_id)
AND MIN(visit_code) <> MAX(visit_code);

[Back to original message]