Posted by DFS on 08/05/06 14:43

--CELKO-- wrote:

>DFS wrote
> 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?

Too lazy to change it from the similar post I made to an Oracle newsgroup.

NUMBER to NUMERIC
DATE to DATETIME
VARCHAR2 to VARCHAR

and that DDL runs fine in SQL Server.




> Why are you using an improper date format?

Does SQL Server have problems recognizing m/d/yyyy? I hadn't heard.



> Why are you avoiding the natural
> key with a fake "record number"

I have comments and other info attached to those visits. It makes life much
easier to use a unique ID like that. It wasn't necessary for this exercise,
true.



> you even use the word record, apparently not understanding
> what a row is and how it is not like a record at all.

The difference is academic (and I'm sure you'll tell me why it's not).



> 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);


Very nice! Thanks for the good response. I like the 'thinking in sets'
approach. That's my mindset, too. (well, recordsets anyway :)

I didn't quite correctly specify the results I wanted. Besides the "visits
by different employees, for different visit codes" I wanted to see all other
rows for buildings in that subset. So a quick join of the table to your
query, ala

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

and I got exactly what I needed (takes it to 177 rows versus 74. 177 is
also the row count I got from my kludgey VB approach, so it's a nice
confirmation I'm only partially dense).

ps I bought SQL for Smarties 2nd Ed. Good stuff.

Thanks

[Back to original message]