Tough query? -- sql server (alternate)

DFS
8/4/2006 11:51:03 PM

The following data set is building inspection visits. It consists of
multiple visits (2+) made to the same building on the same day.

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 (eg records 5-6,
or 9-11)

Here's the table
=====================================
CREATE TABLE VISITS
(
VISITID NUMBER(5,0) NOT NULL ,
BLDGCODE VARCHAR2(10) NOT NULL ,
VISITDATE DATE NOT NULL ,
EMPID NUMBER(5,0) NOT NULL ,
VISITCODE VARCHAR2(5) NOT NULL
);

ALTER TABLE VISITS
ADD CONSTRAINT PK_VISITS PRIMARY KEY
(
VISITID
);

CREATE UNIQUE INDEX UIDX_VISITS ON VISITS
(
BLDGCODE,
VISITDATE,
EMPID,
VISITCODE
);
=====================================

And here's the data:
=====================================
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
6, BLDG3, 2/15/2006, 143, V8
7, BLDG4, 8/1/2006, 319, V9
8, BLDG4, 8/1/2006, 390, V9
9, BLDG4, 8/2/2006, 319, V9
10, BLDG4, 8/2/2006, 390, V9
11, BLDG4, 8/2/2006, 390, V8
12, BLDG5, 8/28/2006, 318, V11
13, BLDG5, 8/28/2006, 376, V11
14, BLDG5, 8/29/2006, 318, V11
15, BLDG5, 8/29/2006, 334, V11
16, BLDG5, 8/29/2006, 376, V11
17, BLDG5, 8/30/2006, 318, V11
18, BLDG5, 8/30/2006, 376, V11
19, BLDG5, 8/30/2006, 334, V11
20, BLDG5, 8/31/2006, 318, V11
21, BLDG5, 8/31/2006, 376, V11
22, BLDG5, 8/31/2006, 334, V11
23, BLDG6, 10/11/2005, 323, V19
24, BLDG6, 10/11/2005, 323, V27
25, BLDG6, 11/8/2005, 323, V8
26, BLDG6, 11/8/2005, 323, V27
27, BLDG7, 10/18/2005, 323, V3
28, BLDG7, 10/18/2005, 323, V27
29, BLDG7, 11/14/2005, 14, V3
30, BLDG7, 11/14/2005, 323, V3
31, BLDG7, 11/14/2005, 143, V3
32, BLDG7, 12/15/2005, 143, V3
33, BLDG7, 12/15/2005, 323, V3
34, BLDG8, 3/8/2006, 15, V23
35, BLDG8, 3/8/2006, 120, V23
36, BLDG9, 5/22/2006, 25, V2
37, BLDG9, 5/22/2006, 391, V14
38, BLDG10, 11/3/2005, 310, V6
39, BLDG10, 11/3/2005, 310, V8
40, BLDG10, 3/15/2006, 139, V28
41, BLDG10, 3/15/2006, 310, V28
42, BLDG10, 3/16/2006, 139, V28
43, BLDG10, 3/16/2006, 310, V28
44, BLDG11, 11/3/2005, 323, V22
45, BLDG11, 11/3/2005, 323, V27
46, BLDG12, 4/18/2006, 71, V2
47, BLDG12, 4/18/2006, 337, V13
48, BLDG12, 4/19/2006, 71, V2
49, BLDG12, 4/19/2006, 337, V13
50, BLDG13, 10/3/2005, 142, V22
51, BLDG13, 10/3/2005, 142, V27
52, BLDG14, 5/23/2006, 32, V23
53, BLDG14, 5/23/2006, 139, V23
54, BLDG14, 5/24/2006, 32, V23
55, BLDG14, 5/24/2006, 139, V23
56, BLDG15, 5/30/2006, 141, V17
57, BLDG15, 5/30/2006, 141, V22
58, BLDG16, 6/1/2006, 71, V18
59, BLDG16, 6/1/2006, 336, V18
60, BLDG16, 6/1/2006, 123, V18
61, BLDG17, 2/21/2006, 34, V8
62, BLDG17, 2/21/2006, 34, V19
63, BLDG18, 12/14/2005, 141, V7
64, BLDG18, 12/14/2005, 141, V17
65, BLDG19, 10/18/2005, 320, V14
66, BLDG19, 10/18/2005, 320, V16
67, BLDG20, 3/6/2006, 141, V8
68, BLDG20, 3/6/2006, 141, V22
69, BLDG21, 10/11/2005, 324, V6
70, BLDG21, 10/11/2005, 324, V7
71, BLDG22, 7/10/2006, 38, V23
72, BLDG22, 7/10/2006, 252, V11
73, BLDG22, 7/11/2006, 38, V23
74, BLDG22, 7/11/2006, 252, V11
75, BLDG22, 7/11/2006, 142, V22
76, BLDG23, 11/10/2005, 308, V7
77, BLDG23, 11/10/2005, 308, V8
78, BLDG23, 5/11/2006, 308, V8
79, BLDG23, 5/11/2006, 391, V2
80, BLDG24, 3/23/2006, 143, V24
81, BLDG24, 3/23/2006, 155, V2
82, BLDG24, 3/24/2006, 143, V24
83, BLDG24, 3/24/2006, 155, V25
84, BLDG25, 10/3/2005, 31, V14
85, BLDG25, 10/3/2005, 31, V19
86, BLDG26, 2/20/2006, 31, V14
87, BLDG26, 2/20/2006, 31, V22
88, BLDG27, 2/15/2006, 13, V14
89, BLDG27, 2/15/2006, 143, V8
90, BLDG28, 10/12/2005, 141, V8
91, BLDG28, 10/12/2005, 141, V17
92, BLDG29, 10/4/2005, 32, V22
93, BLDG29, 10/4/2005, 310, V2
94, BLDG30, 9/12/2005, 53, V23
95, BLDG30, 9/12/2005, 123, V21
96, BLDG30, 9/12/2005, 141, V23
97, BLDG30, 9/13/2005, 53, V23
98, BLDG30, 9/13/2005, 141, V23
99, BLDG30, 9/13/2005, 123, V21
100, BLDG30, 9/14/2005, 53, V23
101, BLDG30, 9/14/2005, 141, V23
102, BLDG30, 9/14/2005, 123, V21
103, BLDG31, 2/14/2006, 13, V14
104, BLDG31, 2/14/2006, 143, V8
105, BLDG32, 11/1/2005, 320, V3
106, BLDG32, 11/1/2005, 320, V27
107, BLDG33, 11/3/2005, 34, V7
108, BLDG33, 11/3/2005, 34, V19
109, BLDG34, 7/10/2006, 37, V23
110, BLDG34, 7/10/2006, 62, V23
111, BLDG34, 7/11/2006, 37, V23
112, BLDG34, 7/11/2006, 62, V23
113, BLDG34, 7/12/2006, 37, V23
114, BLDG34, 7/12/2006, 62, V23
115, BLDG35, 11/21/2005, 78, V27
116, BLDG35, 11/21/2005, 334, V8
117, BLDG36, 7/10/2006, 358, V1
118, BLDG36, 7/10/2006, 358, V8
119, BLDG37, 8/14/2006, 50, V14
120, BLDG37, 8/14/2006, 71, V11
121, BLDG37, 8/15/2006, 50, V14
122, BLDG37, 8/15/2006, 71, V11
123, BLDG38, 9/13/2005, 130, V6
124, BLDG38, 9/13/2005, 130, V8
125, BLDG39, 2/22/2006, 34, V8
126, BLDG39, 2/22/2006, 34, V14
127, BLDG40, 2/14/2006, 13, V14
128, BLDG40, 2/14/2006, 143, V8
129, BLDG41, 5/22/2006, 252, V17
130, BLDG41, 5/22/2006, 326, V17
131, BLDG41, 5/23/2006, 252, V17
132, BLDG41, 5/23/2006, 326, V17
133, BLDG42, 7/10/2006, 309, V2
134, BLDG42, 7/10/2006, 318, V23
135, BLDG42, 7/11/2006, 309, V2
136, BLDG42, 7/11/2006, 318, V23
137, BLDG42, 7/12/2006, 309, V2
138, BLDG42, 7/12/2006, 318, V23
139, BLDG43, 10/18/2005, 206, V8
140, BLDG43, 10/18/2005, 206, V14
141, BLDG44, 3/9/2006, 142, V24
142, BLDG44, 3/9/2006, 233, V23
143, BLDG44, 3/9/2006, 319, V24
144, BLDG44, 3/10/2006, 142, V24
145, BLDG44, 3/10/2006, 319, V24
146, BLDG44, 3/10/2006, 233, V23
147, BLDG45, 9/15/2005, 128, V6
148, BLDG45, 9/15/2005, 128, V9
149, BLDG46, 5/24/2006, 25, V2
150, BLDG46, 5/24/2006, 391, V8
151, BLDG47, 1/17/2006, 321, V6
152, BLDG47, 1/17/2006, 321, V22
153, BLDG48, 7/13/2006, 38, V18
154, BLDG48, 7/13/2006, 318, V11
155, BLDG49, 7/12/2006, 142, V23
156, BLDG49, 7/12/2006, 263, V23
157, BLDG50, 4/11/2006, 62, V24
158, BLDG50, 4/11/2006, 142, V24
159, BLDG50, 4/12/2006, 62, V24
160, BLDG50, 4/12/2006, 142, V24
161, BLDG51, 10/13/2005, 78, V13
162, BLDG51, 10/13/2005, 325, V13
163, BLDG52, 5/2/2006, 145, V9
164, BLDG52, 5/2/2006, 390, V12
165, BLDG52, 5/2/2006, 390, V9
166, BLDG52, 5/3/2006, 145, V8
167, BLDG52, 5/3/2006, 390, V9
168, BLDG52, 5/3/2006, 390, V12
169, BLDG53, 12/14/2005, 76, V9
170, BLDG53, 12/14/2005, 322, V9
171, BLDG53, 12/15/2005, 76, V9
172, BLDG53, 12/15/2005, 322, V9
173, BLDG53, 12/15/2005, 322, V22
174, BLDG54, 9/6/2005, 323, V3
175, BLDG54, 9/6/2005, 323, V27
176, BLDG54, 12/13/2005, 323, V22
177, BLDG54, 12/13/2005, 323, V27
178, BLDG55, 9/6/2005, 129, V21
179, BLDG55, 9/6/2005, 233, V23
180, BLDG55, 9/7/2005, 38, V23
181, BLDG55, 9/7/2005, 233, V23
182, BLDG55, 9/7/2005, 142, V23
183, BLDG55, 9/7/2005, 129, V21
184, BLDG55, 9/8/2005, 38, V23
185, BLDG55, 9/8/2005, 233, V23
186, BLDG55, 9/8/2005, 142, V23
187, BLDG55, 9/8/2005, 129, V21
188, BLDG55, 9/9/2005, 129, V21

DFS
8/5/2006 12:00:23 AM

ps If your query returns 177 records (from the population of 586 below), you
probably have it. That's my result with some queries and VB code, but I
think it can be done with just SQL.

[quoted text, click to view]

--CELKO--
8/5/2006 6:46:20 AM

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

DFS
8/5/2006 10:43:40 AM

[quoted text, click to view]

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.

[quoted text, click to view]

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

[quoted text, click to view]

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.

[quoted text, click to view]

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

[quoted text, click to view]

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

--CELKO--
8/5/2006 12:38:12 PM

[quoted text, click to view]

Nah, just Google one of my "rants to newbies"; but how can you say that
you like the "think in Sets" approach and use the terms wrong, thus
destroying your ability to think in sets and SQL? The words are very
important because they are the tools of thought.

[quoted text, click to view]

Get the third edition. My publisher was expecting it to be size of the
second and priced it in advance based on that; it is a few hundred
pages bigger. My little gift to the geek book buyer :)

Dan Guzman
8/5/2006 6:22:32 PM

[quoted text, click to view]

When you pass a date string so SQL Server, the interpretation depends on
your DATAFORMAT setting. I suggest you use 'yyyymmdd' so that the value is
understood correctly regardless of the DATAFORMAT setting.

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

DFS
8/5/2006 10:03:11 PM

[quoted text, click to view]

Hardly.

tuples vs rows vs records is a semantic exercise for "sophisticates" and
theorists like yourself. In the real world, they're used interchangeably;
see the SQL Server BOL documentation for instance.

[quoted text, click to view]

On Amazon, I see that beast is just over 800 pages total (with indexes,
etc)!

I'll check it out next time I'm at B&N.

Erland Sommarskog
8/5/2006 10:12:32 PM

DFS (nospam@dfs_.com) writes:
[quoted text, click to view]

It may have. Try this:

SET LANGAUGE German
go
SELECT convert(datetime, '9/5/2003')

Not talking about that many readers in this newsgroup find strings like
8/29/2006 as pure garbage if it supposed to be a date. There are only
12 months per year where I live.

There are three safe formats in SQL Server:

YYYYMMDD
YYYY-MM-DDTHH:MM:SS[.fff]
YYYY-MM-DDZ

T and Z here represent themselves. The last format is only in SQL 2005.
All other formats are ambiguous and can be interpreted differently
depending on the langugae setting.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

jsfromynr
8/7/2006 1:17:34 AM

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.

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

DFS
8/7/2006 8:21:23 AM

[quoted text, click to view]

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : Tough query?