Tough query?

**DFS** · Aug 5 '06, 03:55 AM

Re: Tough query?

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.

DFS wrote:

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,BLDGCOD E,VISITDATE,EMP ID,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
189, BLDG55, 9/9/2005, 233, V23
190, BLDG55, 6/20/2006, 142, V8
191, BLDG55, 6/20/2006, 142, V29
192, BLDG56, 6/28/2006, 131, V13
193, BLDG56, 6/28/2006, 319, V13
194, BLDG56, 6/29/2006, 131, V13
195, BLDG56, 6/29/2006, 319, V13
196, BLDG57, 11/8/2005, 320, V22
197, BLDG57, 11/8/2005, 320, V27
198, BLDG58, 1/25/2006, 13, V3
199, BLDG58, 1/25/2006, 14, V13
200, BLDG59, 11/29/2005, 233, V9
201, BLDG59, 11/29/2005, 233, V14
202, BLDG60, 2/8/2006, 323, V22
203, BLDG60, 2/8/2006, 323, V27
204, BLDG61, 1/17/2006, 166, V3
205, BLDG61, 1/17/2006, 166, V22
206, BLDG62, 9/27/2005, 320, V3
207, BLDG62, 9/27/2005, 320, V22
208, BLDG62, 2/21/2006, 115, V9
209, BLDG62, 2/21/2006, 320, V9
210, BLDG62, 2/22/2006, 115, V9
211, BLDG62, 2/22/2006, 320, V9
212, BLDG63, 11/14/2005, 87, V11
213, BLDG63, 11/14/2005, 129, V27
214, BLDG63, 11/14/2005, 323, V27
215, BLDG63, 11/15/2005, 129, V11
216, BLDG63, 11/15/2005, 143, V11
217, BLDG63, 11/16/2005, 129, V11
218, BLDG63, 11/16/2005, 143, V11
219, BLDG63, 11/17/2005, 129, V11
220, BLDG63, 11/17/2005, 143, V11
221, BLDG63, 11/18/2005, 129, V27
222, BLDG63, 11/18/2005, 143, V11
223, BLDG64, 6/7/2006, 253, V2
224, BLDG64, 6/7/2006, 391, V6
225, BLDG65, 6/7/2006, 253, V2
226, BLDG65, 6/7/2006, 391, V14
227, BLDG66, 1/11/2006, 39, V25
228, BLDG66, 1/11/2006, 141, V25
229, BLDG66, 1/12/2006, 39, V25
230, BLDG66, 1/12/2006, 141, V25
231, BLDG66, 3/20/2006, 39, V23
232, BLDG66, 3/20/2006, 76, V23
233, BLDG66, 3/21/2006, 39, V23
234, BLDG66, 3/21/2006, 115, V23
235, BLDG66, 3/21/2006, 76, V23
236, BLDG66, 3/22/2006, 39, V23
237, BLDG66, 3/22/2006, 115, V23
238, BLDG66, 3/22/2006, 76, V23
239, BLDG67, 5/26/2006, 141, V7
240, BLDG67, 5/26/2006, 141, V17
241, BLDG68, 12/21/2005, 141, V8
242, BLDG68, 12/21/2005, 141, V17
243, BLDG69, 5/23/2006, 50, V3
244, BLDG69, 5/23/2006, 50, V8
245, BLDG70, 2/1/2006, 114, V17
246, BLDG70, 2/1/2006, 114, V22
247, BLDG71, 10/11/2005, 131, V8
248, BLDG71, 10/11/2005, 334, V8
249, BLDG71, 3/10/2006, 334, V8
250, BLDG71, 3/10/2006, 334, V22
251, BLDG72, 7/31/2006, 398, V2
252, BLDG72, 7/31/2006, 398, V22
253, BLDG73, 11/30/2005, 129, V23
254, BLDG73, 11/30/2005, 326, V24
255, BLDG74, 11/29/2005, 143, V22
256, BLDG74, 11/29/2005, 143, V23
257, BLDG75, 4/26/2006, 12, V18
258, BLDG75, 4/26/2006, 17, V18
259, BLDG76, 6/6/2006, 320, V14
260, BLDG76, 6/6/2006, 320, V15
261, BLDG77, 1/10/2006, 78, V2
262, BLDG77, 1/10/2006, 325, V9
263, BLDG77, 1/11/2006, 78, V2
264, BLDG77, 1/11/2006, 325, V8
265, BLDG77, 1/11/2006, 325, V6
266, BLDG78, 5/17/2006, 141, V17
267, BLDG78, 5/17/2006, 141, V22
268, BLDG79, 9/13/2005, 37, V19
269, BLDG79, 9/13/2005, 318, V19
270, BLDG80, 12/20/2005, 34, V13
271, BLDG80, 12/20/2005, 250, V13
272, BLDG81, 4/19/2006, 25, V22
273, BLDG81, 4/19/2006, 391, V2
274, BLDG82, 5/3/2006, 108, V14
275, BLDG82, 5/3/2006, 391, V2
276, BLDG83, 6/19/2006, 36, V8
277, BLDG83, 6/19/2006, 393, V8
278, BLDG84, 2/13/2006, 13, V14
279, BLDG84, 2/13/2006, 143, V8
280, BLDG85, 5/8/2006, 308, V8
281, BLDG85, 5/8/2006, 391, V2
282, BLDG86, 4/25/2006, 322, V8
283, BLDG86, 4/25/2006, 322, V22
284, BLDG87, 7/14/2006, 322, V13
285, BLDG87, 7/14/2006, 322, V15
286, BLDG88, 2/2/2006, 322, V8
287, BLDG88, 2/2/2006, 322, V22
288, BLDG89, 4/13/2006, 390, V2
289, BLDG89, 4/13/2006, 390, V8
290, BLDG90, 10/12/2005, 131, V8
291, BLDG90, 10/12/2005, 334, V8
292, BLDG91, 6/26/2006, 131, V22
293, BLDG91, 6/26/2006, 319, V22
294, BLDG91, 6/27/2006, 131, V22
295, BLDG91, 6/27/2006, 319, V22
296, BLDG92, 3/1/2006, 39, V23
297, BLDG92, 3/1/2006, 141, V23
298, BLDG92, 3/2/2006, 39, V23
299, BLDG92, 3/2/2006, 115, V23
300, BLDG92, 3/2/2006, 141, V23
301, BLDG92, 3/3/2006, 39, V23
302, BLDG92, 3/3/2006, 141, V23
303, BLDG92, 3/3/2006, 115, V23
304, BLDG92, 7/20/2006, 115, V23
305, BLDG92, 7/20/2006, 141, V25
306, BLDG92, 7/21/2006, 115, V23
307, BLDG92, 7/21/2006, 141, V25
308, BLDG93, 5/8/2006, 78, V2
309, BLDG93, 5/8/2006, 325, V9
310, BLDG93, 5/9/2006, 78, V2
311, BLDG93, 5/9/2006, 78, V9
312, BLDG93, 5/9/2006, 325, V9
313, BLDG94, 6/19/2006, 128, V9
314, BLDG94, 6/19/2006, 358, V9
315, BLDG94, 6/20/2006, 128, V9
316, BLDG94, 6/20/2006, 358, V9
317, BLDG95, 6/6/2006, 253, V2
318, BLDG95, 6/6/2006, 391, V14
319, BLDG96, 6/6/2006, 253, V2
320, BLDG96, 6/6/2006, 391, V14
321, BLDG97, 6/5/2006, 253, V7
322, BLDG97, 6/5/2006, 391, V7
323, BLDG98, 1/24/2006, 322, V9
324, BLDG98, 1/24/2006, 322, V22
325, BLDG99, 10/12/2005, 323, V7
326, BLDG99, 10/12/2005, 323, V15
327, BLDG100, 12/21/2005, 320, V14
328, BLDG100, 12/21/2005, 320, V22
329, BLDG100, 2/23/2006, 115, V9
330, BLDG100, 2/23/2006, 320, V9
331, BLDG100, 2/24/2006, 115, V9
332, BLDG100, 2/24/2006, 320, V9
333, BLDG101, 2/22/2006, 115, V9
334, BLDG101, 2/22/2006, 320, V9
335, BLDG101, 2/23/2006, 115, V9
336, BLDG101, 2/23/2006, 320, V9
337, BLDG102, 10/13/2005, 131, V8
338, BLDG102, 10/13/2005, 334, V8
339, BLDG103, 1/12/2006, 119, V7
340, BLDG103, 1/12/2006, 119, V22
341, BLDG104, 5/17/2006, 233, V23
342, BLDG104, 5/17/2006, 243, V23
343, BLDG104, 5/18/2006, 233, V23
344, BLDG104, 5/18/2006, 243, V23
345, BLDG105, 11/22/2005, 309, V6
346, BLDG105, 11/22/2005, 309, V22
347, BLDG106, 1/12/2006, 166, V8
348, BLDG106, 1/12/2006, 166, V22
349, BLDG107, 9/27/2005, 206, V7
350, BLDG107, 9/27/2005, 206, V20
351, BLDG108, 4/12/2006, 322, V14
352, BLDG108, 4/12/2006, 322, V22
353, BLDG109, 3/27/2006, 17, V11
354, BLDG109, 3/27/2006, 358, V11
355, BLDG109, 3/27/2006, 127, V11
356, BLDG109, 3/27/2006, 142, V11
357, BLDG109, 3/27/2006, 144, V11
358, BLDG109, 3/27/2006, 318, V11
359, BLDG109, 3/27/2006, 129, V11
360, BLDG109, 3/28/2006, 17, V11
361, BLDG109, 3/28/2006, 115, V11
362, BLDG109, 3/28/2006, 358, V11
363, BLDG109, 3/28/2006, 334, V11
364, BLDG109, 3/28/2006, 323, V11
365, BLDG109, 3/28/2006, 318, V11
366, BLDG109, 3/28/2006, 144, V11
367, BLDG109, 3/28/2006, 142, V11
368, BLDG109, 3/28/2006, 129, V11
369, BLDG109, 3/28/2006, 127, V11
370, BLDG109, 3/29/2006, 17, V11
371, BLDG109, 3/29/2006, 323, V11
372, BLDG109, 3/29/2006, 358, V11
373, BLDG109, 3/29/2006, 334, V11
374, BLDG109, 3/29/2006, 318, V11
375, BLDG109, 3/29/2006, 144, V11
376, BLDG109, 3/29/2006, 142, V11
377, BLDG109, 3/29/2006, 129, V11
378, BLDG109, 3/29/2006, 127, V11
379, BLDG109, 3/29/2006, 115, V11
380, BLDG109, 3/30/2006, 17, V11
381, BLDG109, 3/30/2006, 129, V11
382, BLDG109, 3/30/2006, 358, V11
383, BLDG109, 3/30/2006, 334, V11
384, BLDG109, 3/30/2006, 323, V11
385, BLDG109, 3/30/2006, 318, V11
386, BLDG109, 3/30/2006, 144, V11
387, BLDG109, 3/30/2006, 142, V11
388, BLDG109, 3/30/2006, 127, V11
389, BLDG109, 3/30/2006, 115, V11
390, BLDG109, 3/31/2006, 17, V11
391, BLDG109, 3/31/2006, 318, V11
392, BLDG109, 3/31/2006, 358, V11
393, BLDG109, 3/31/2006, 144, V11
394, BLDG109, 3/31/2006, 142, V11
395, BLDG109, 3/31/2006, 129, V11
396, BLDG109, 3/31/2006, 127, V11
397, BLDG109, 3/31/2006, 115, V11
398, BLDG110, 11/7/2005, 320, V22
399, BLDG110, 11/7/2005, 320, V27
400, BLDG111, 10/12/2005, 146, V8
401, BLDG111, 10/12/2005, 146, V22
402, BLDG112, 2/14/2006, 141, V8
403, BLDG112, 2/14/2006, 141, V22
404, BLDG113, 2/15/2006, 145, V9
405, BLDG113, 2/15/2006, 233, V9
406, BLDG113, 2/16/2006, 145, V8
407, BLDG113, 2/16/2006, 233, V9
408, BLDG114, 1/26/2006, 310, V6
409, BLDG114, 1/26/2006, 310, V8
410, BLDG115, 5/5/2006, 36, V9
411, BLDG115, 5/5/2006, 376, V9
412, BLDG115, 7/5/2006, 36, V9
413, BLDG115, 7/5/2006, 376, V9
414, BLDG115, 7/6/2006, 36, V9
415, BLDG115, 7/6/2006, 376, V9
416, BLDG116, 4/12/2006, 34, V4
417, BLDG116, 4/12/2006, 34, V13
418, BLDG117, 12/27/2005, 323, V13
419, BLDG117, 12/27/2005, 323, V27
420, BLDG117, 2/17/2006, 14, V3
421, BLDG117, 2/17/2006, 323, V3
422, BLDG118, 1/31/2006, 308, V17
423, BLDG118, 1/31/2006, 308, V22
424, BLDG119, 5/9/2006, 308, V8
425, BLDG119, 5/9/2006, 391, V2
426, BLDG120, 11/8/2005, 233, V28
427, BLDG120, 11/8/2005, 233, V29
428, BLDG121, 12/15/2005, 141, V8
429, BLDG121, 12/15/2005, 141, V17
430, BLDG122, 5/23/2006, 25, V2
431, BLDG122, 5/23/2006, 391, V22
432, BLDG123, 4/20/2006, 25, V14
433, BLDG123, 4/20/2006, 391, V2
434, BLDG124, 4/17/2006, 25, V22
435, BLDG124, 4/17/2006, 391, V2
436, BLDG125, 4/18/2006, 25, V22
437, BLDG125, 4/18/2006, 391, V2
438, BLDG126, 10/18/2005, 13, V19
439, BLDG126, 10/18/2005, 13, V22
440, BLDG127, 5/10/2006, 308, V8
441, BLDG127, 5/10/2006, 391, V2
442, BLDG128, 1/10/2006, 78, V11
443, BLDG128, 1/10/2006, 233, V23
444, BLDG129, 11/8/2005, 78, V9
445, BLDG129, 11/8/2005, 325, V9
446, BLDG129, 11/9/2005, 78, V9
447, BLDG129, 11/9/2005, 325, V9
448, BLDG130, 10/18/2005, 325, V28
449, BLDG130, 10/18/2005, 334, V9
450, BLDG130, 11/9/2005, 78, V9
451, BLDG130, 11/9/2005, 334, V9
452, BLDG130, 11/9/2005, 325, V9
453, BLDG130, 11/10/2005, 78, V9
454, BLDG130, 11/10/2005, 325, V9
455, BLDG130, 11/10/2005, 334, V6
456, BLDG131, 5/30/2006, 50, V3
457, BLDG131, 5/30/2006, 50, V8
458, BLDG132, 5/24/2006, 31, V6
459, BLDG132, 5/24/2006, 31, V22
460, BLDG133, 2/7/2006, 114, V17
461, BLDG133, 2/7/2006, 114, V22
462, BLDG134, 6/21/2006, 62, V9
463, BLDG134, 6/21/2006, 309, V28
464, BLDG135, 5/10/2006, 78, V9
465, BLDG135, 5/10/2006, 325, V9
466, BLDG135, 5/11/2006, 78, V9
467, BLDG135, 5/11/2006, 325, V9
468, BLDG136, 12/15/2005, 129, V23
469, BLDG136, 12/15/2005, 233, V23
470, BLDG137, 5/17/2006, 129, V6
471, BLDG137, 5/17/2006, 129, V8
472, BLDG138, 3/8/2006, 336, V8
473, BLDG138, 3/8/2006, 336, V22
474, BLDG139, 3/20/2006, 129, V14
475, BLDG139, 3/20/2006, 129, V19
476, BLDG140, 3/22/2006, 52, V29
477, BLDG140, 3/22/2006, 334, V29
478, BLDG140, 3/23/2006, 52, V29
479, BLDG140, 3/23/2006, 334, V29
480, BLDG141, 1/4/2006, 78, V22
481, BLDG141, 1/4/2006, 334, V22
482, BLDG142, 9/7/2005, 52, V8
483, BLDG142, 9/7/2005, 52, V10
484, BLDG143, 5/4/2006, 78, V28
485, BLDG143, 5/4/2006, 334, V29
486, BLDG144, 5/11/2006, 50, V3
487, BLDG144, 5/11/2006, 50, V8
488, BLDG144, 5/12/2006, 50, V7
489, BLDG144, 5/12/2006, 50, V8
490, BLDG145, 10/4/2005, 119, V16
491, BLDG145, 10/4/2005, 119, V19
492, BLDG146, 6/6/2006, 34, V7
493, BLDG146, 6/6/2006, 34, V8
494, BLDG147, 9/14/2005, 36, V28
495, BLDG147, 9/14/2005, 324, V5
496, BLDG148, 9/14/2005, 322, V14
497, BLDG148, 9/14/2005, 322, V15
498, BLDG149, 6/20/2006, 123, V8
499, BLDG149, 6/20/2006, 123, V22
500, BLDG150, 5/16/2006, 12, V18
501, BLDG150, 5/16/2006, 318, V18
502, BLDG151, 4/17/2006, 62, V23
503, BLDG151, 4/17/2006, 142, V23
504, BLDG151, 4/17/2006, 318, V23
505, BLDG151, 4/17/2006, 154, V2
506, BLDG151, 4/18/2006, 62, V23
507, BLDG151, 4/18/2006, 318, V23
508, BLDG151, 4/18/2006, 142, V23
509, BLDG151, 4/18/2006, 154, V2
510, BLDG151, 4/19/2006, 62, V22
511, BLDG151, 4/19/2006, 318, V23
512, BLDG151, 4/19/2006, 154, V2
513, BLDG151, 4/19/2006, 142, V23
514, BLDG152, 10/20/2005, 320, V14
515, BLDG152, 10/20/2005, 320, V15
516, BLDG153, 7/11/2006, 334, V8
517, BLDG153, 7/11/2006, 334, V9
518, BLDG154, 2/1/2006, 53, V23
519, BLDG154, 2/1/2006, 323, V23
520, BLDG154, 2/2/2006, 53, V23
521, BLDG154, 2/2/2006, 323, V23
522, BLDG154, 2/3/2006, 53, V23
523, BLDG154, 2/3/2006, 323, V27
524, BLDG154, 2/3/2006, 323, V23
525, BLDG154, 7/12/2006, 53, V24
526, BLDG154, 7/12/2006, 139, V26
527, BLDG154, 7/12/2006, 141, V25
528, BLDG154, 7/13/2006, 53, V24
529, BLDG154, 7/13/2006, 139, V26
530, BLDG154, 7/13/2006, 141, V25
531, BLDG154, 7/14/2006, 53, V24
532, BLDG154, 7/14/2006, 141, V25
533, BLDG154, 7/14/2006, 139, V26
534, BLDG155, 9/14/2005, 323, V7
535, BLDG155, 9/14/2005, 323, V27
536, BLDG156, 9/12/2005, 165, V6
537, BLDG156, 9/12/2005, 165, V8
538, BLDG157, 10/27/2005, 32, V13
539, BLDG157, 10/27/2005, 310, V13
540, BLDG158, 11/2/2005, 320, V14
541, BLDG158, 11/2/2005, 320, V15
542, BLDG158, 2/20/2006, 115, V9
543, BLDG158, 2/20/2006, 320, V9
544, BLDG158, 2/21/2006, 115, V9
545, BLDG158, 2/21/2006, 320, V9
546, BLDG158, 4/26/2006, 320, V3
547, BLDG158, 4/26/2006, 320, V17
548, BLDG159, 3/14/2006, 336, V14
549, BLDG159, 3/14/2006, 336, V22
550, BLDG160, 3/15/2006, 336, V8
551, BLDG160, 3/15/2006, 336, V14
552, BLDG160, 8/24/2006, 336, V8
553, BLDG160, 8/24/2006, 336, V27
554, BLDG161, 1/5/2006, 253, V6
555, BLDG161, 1/5/2006, 253, V17
556, BLDG162, 3/13/2006, 141, V9
557, BLDG162, 3/13/2006, 141, V22
558, BLDG163, 6/29/2006, 78, V6
559, BLDG163, 6/29/2006, 78, V9
560, BLDG164, 12/12/2005, 76, V9
561, BLDG164, 12/12/2005, 322, V9
562, BLDG164, 12/13/2005, 76, V9
563, BLDG164, 12/13/2005, 322, V22
564, BLDG164, 12/13/2005, 322, V9
565, BLDG165, 1/11/2006, 166, V8
566, BLDG165, 1/11/2006, 166, V22
567, BLDG166, 5/24/2006, 141, V17
568, BLDG166, 5/24/2006, 141, V22
569, BLDG167, 1/11/2006, 165, V8
570, BLDG167, 1/11/2006, 165, V22
571, BLDG168, 10/18/2005, 244, V14
572, BLDG168, 10/18/2005, 309, V14
573, BLDG169, 1/31/2006, 144, V8
574, BLDG169, 1/31/2006, 144, V22
575, BLDG170, 6/6/2006, 123, V18
576, BLDG170, 6/6/2006, 383, V18
577, BLDG171, 1/17/2006, 263, V6
578, BLDG171, 1/17/2006, 263, V9
579, BLDG172, 8/10/2006, 233, V9
580, BLDG172, 8/10/2006, 376, V9
581, BLDG172, 8/11/2006, 233, V9
582, BLDG172, 8/11/2006, 376, V9
583, BLDG173, 4/4/2006, 131, V13
584, BLDG173, 4/4/2006, 144, V13
585, BLDG174, 7/4/2006, 383, V4
586, BLDG174, 7/4/2006, 383, V8
>
=============== =============== =======
>
Thanks

**--CELKO--** · Aug 5 '06, 01:35 PM

Re: Tough query?

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_TIMESTA MP 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** · Aug 5 '06, 02:35 PM

Re: Tough query?

--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_TIMESTA MP 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(VISITCOD E)
) 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

**Dan Guzman** · Aug 5 '06, 06:15 PM

Re: Tough query?

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

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

"DFS" <nospam@dfs_.co mwrote in message
news:G72Bg.4267 6$Bd.9890@bigne ws6.bellsouth.n et...

--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_TIMESTA MP 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(VISITCOD E)
) 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--** · Aug 5 '06, 07:35 PM

Re: Tough query?

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

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.

>I bought SQL for Smarties 2nd Ed. Good stuff. <<

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

**Erland Sommarskog** · Aug 5 '06, 10:05 PM

Re: Tough query?

DFS (nospam@dfs_.co m) writes:

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

It may have. Try this:

SET LANGAUGE German
go
SELECT convert(datetim e, '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@sommarsk og.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

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**DFS** · Aug 6 '06, 01:55 AM

Re: Tough query?

--CELKO-- wrote:

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

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

Hardly.

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

>>I bought SQL for Smarties 2nd Ed. Good stuff. <<

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

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.

**jsfromynr** · Aug 7 '06, 08:15 AM

Re: Tough query?

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,BLDGCOD E,VISITDATE,EMP ID,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,VISITD ATE ,Count(Distinct EMPID)
From
YourTable
Group By BLDGCODE,VISITD ATE

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

Programming Discussions

http://jatindersingh.blogspot.com

Programming Discussions

Create a Website | Tripod Web Hosting

http://sqloracle.tripod.com

**DFS** · Aug 7 '06, 12:15 PM

Re: Tough query?

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,BLDGCOD E,VISITDATE,EMP ID,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,VISITD ATE ,Count(Distinct EMPID)
From
YourTable
Group By BLDGCODE,VISITD ATE
>
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(VISITCOD E)
) V2
WHERE (V.BLDGCODE = V2.BLDGCODE)
AND (V.VISITDATE = V2.VISITDATE);

With Warm regards
Jatinder Singh

Programming Discussions

http://jatindersingh.blogspot.com

Programming Discussions

http://sqloracle.tripod.com

Tough query?

Tough query?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment