Tough Query?

Aug 5, 2006

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

View 9 Replies


ADVERTISEMENT

Tough Query

Feb 13, 2004

Hi,

I have a table with a couple of million rows. Each row as its datetime field spilt between numerous columns (year, month, day, hour... it's a datawarehouse fact table).

Here's what I'd like to do in a query:

If a value is missing (in the column value) it's set to -999. What I'd like to do is to set this column to the value of the hour before this one. For example, here's some data before and after an update:

BEFORE:
MONTH DAY HOUR VALUE
==================
01 31 21 33.5
01 31 22 -999
03 07 24 87.6
03 08 01 -999

AFTER:
MONTH DAY HOUR VALUE
==================
01 31 21 33.5
01 31 22 33.5
03 07 24 87.6
03 08 01 87.6

Here's the complete design of my table
IDENT nvarchar 50
THEDATE datetime
THEDAY int
THEMONTH int
THEYEAR int
THEVALUE real
SOURCE nvarchar 255

How can I do this?

Thanks,

Skip.

View 2 Replies View Related

Tough Sql Query

Feb 18, 2006

I am going mad with this Query. I need to join 3 Tables. Their FormatsareVouchers[VoucherID] [uniqueidentifier] NOT NULL ,[VoucherTypeID] [int] NOT NULL ,[VoucherNo] [int] NULL ,[VoucherDate] [datetime] NOT NULL ,[VoucherNarration] [varchar] (255)CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED([VoucherID]) ON [PRIMARY]Ledgers[LedgerID] [int] IDENTITY (1, 1) NOT NULL ,[LedgerName] [varchar] (50) COLLATECONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED([LedgerID]) ON [PRIMARY]CREATE TABLE [Transactions] ([TransactionID] [uniqueidentifier] NOT NULL ,[VoucherID] [uniqueidentifier] NOT NULL ,[ByTo] [char] (1)[LedgerID] [int] NOT NULL ,[Credit] [money] NOT NULL ,[Debit] [money] NOT NULL ,CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED([TransactionID]) ON [PRIMARY] ,CONSTRAINT [FK_Transactions_Ledgers] FOREIGN KEY([LedgerID]) REFERENCES [Ledgers] ([LedgerID]),CONSTRAINT [FK_Transactions_Vouchers] FOREIGN KEY([VoucherID]) REFERENCES [Vouchers] ([VoucherID])) ON [PRIMARY]GOThe Required Output isID VoucherNo VoucherDate LedgerName Amount1 1 2001-09-03 Bank-1 2400.002 2 2001-09-03 Cash 600.003 3 2001-09-03 TAX A/C 0.004 4 2001-09-03 Bank-1 4000.005 5 2001-09-03 Bank-10.00But, I am getting More than One row from the transactions table. I justneed the first matching rowID VoucherNo VoucherDate LedgerName Amount1 1 2001-09-03 Bank-1 2400.002 2 2001-09-03 Cash 600.003 3 2001-09-03 TAX A/C 0.004 4 2001-09-03 Bank-1 4000.005 4 2001-09-03 Cash 400.006 5 2001-09-03 Bank-1 0.007 5 2001-09-03 Cash 5035.00The Query I am using isSELECTdbo.Vouchers2001.VoucherID,dbo.Vouchers2001.VoucherNo,dbo.Vouchers2001.VoucherDate,dbo.Ledgers.LedgerName,SUM(dbo.Transactions2001.Debit) AS AmountFROM dbo.Vouchers2001 INNER JOINdbo.Transactions2001ON dbo.Vouchers2001.VoucherID =dbo.Transactions2001.VoucherID INNER JOINdbo.Ledgers ON dbo.Transactions2001.LedgerID =dbo.Ledgers.LedgerIDWHERE (dbo.Vouchers2001.VoucherTypeID = 1)GROUP BY dbo.Vouchers2001.VoucherID,dbo.Ledgers.LedgerName,dbo.Vouchers2001.VoucherDate,dbo.Vouchers2001.VoucherNo,dbo.Vouchers2001.VoucherTypeIDORDER BY dbo.Vouchers2001.VoucherID,dbo.Ledgers.LedgerName,dbo.Vouchers2001.VoucherDate,dbo.Vouchers2001.VoucherNoPlz help Out*** Sent via Developersdex http://www.developersdex.com ***

View 12 Replies View Related

Please Help With This Tough SQL Query

Jul 20, 2005

I've been trying this one for 2-3 hours and can't figure it out. I'deappreciate any help or pointers in the right direction. Thanks.QueryI need the query to return me all the lottery names and results thathave the latest date in the database for that particular game and forthe state [AZ]. So the return data from the data below data would be:Result:--------------------------AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18AZ Atlantic PayDay 2004-08-05 15-51-59-75AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6Example Table "Lottery":----------------------------------------------------State|Game | Date | ResultsAZ Atlantic 6/49 2004-08-04 5-16-17-26-38-44 46AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18AZ Atlantic PayDay 2004-07-29 2-23-62-77AZ Atlantic PayDay 2004-08-05 15-51-59-75AZ Atlantic Tag 2004-08-04 5-8-9-1-2-3AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6

View 3 Replies View Related

Tough Query-Please Help!!! URGENT!!!

Nov 21, 2001

Hello,

I have a table with 3 cols--- id1,id2 and id3
Total no.of rows-- 18

id1id2id3
11
25
35
45
51
6100
7100
845
944
1045
11100
126
136
141
152
163
17111
18123


id1 is an identity column.
I have to populate third column which is id3 based on the data in id2.

I have to group similar values in id2 together
and insert 1,2,3 accordingly in id3.
For eg: we have three 100's in id2, so we should have values 1,2,3 for id3.
we have two 45's in id2, we should have values 1,2 for id3.

so final output should be:


id1id2id3
111
251
352
453
512
61001
71002
8451
9441
10452
111003
1261
1362
1413
1521
1631
171111
181231


Is it possible?
Please help!!!!!!!

Thanks,
cheryl

View 3 Replies View Related

Aggregate Function - Tough Query For Me...

Oct 17, 2006

I currently have the following query:

Quote:
select distinct a.memberFirstName, a.memberLastName, c.ChapterName, d.divisionName,
count(f.memberID) as numMembers
FROM Members a
INNER JOIN groupLeaders b
ON a.memberID = b.memberID
Inner JOIN Chapters c
ON c.chapterID = b.chapterID
LEFT JOIN divisions d
ON d.divisionID = c.divisionID
Inner Join groupsOfEight e
ON e.groupLeaderID = b.groupLeaderID
Inner Join groupOfEightMembers f
ON f.groupOfEightID = e.groupOfEightID
Group BY a.memberFirstName, a.memberLastName, c.chapterName, d.divisionName
Order By divisionName, numMembers

This query returns me the names of all of my Group Leaders, their Chapter, Division, and the number of members they have selected to be in their group.

Now, instead of the number of members in each Group I would like to know the total number of Members in each division to appear in the count.

[NOTE: All chapters have a division, linked by a divisionID in the "Chapters" table -- I need to get a count of all the "ChapterMembers" [chaptermembers is a table also] that are in the Division.

Here is the query I started to build before I ran into serious trouble:

Quote:
select a.divisionName, count('c.memberID') as numMembers
From Divisions a
Inner Join Chapters b
On b.divisionID = a.divisionID
Inner Join chapterMembers c
ON c.chapterID = b.chapterID
Left Join Members d
ON d.memberID = c.memberID
LEFT Join groupLeaders e
On e.memberID = d.memberID
Group By a.divisionName

This particular query returns only the DivisonName and the number of Members in the division as expected. However, when I try to select the information for the GroupLeader (first & last name) I am forced to add memberFirstName to the Group By statement which changes my Count...

Have I done an okay job of explaining the problem?

The goal here is to select all of the GroupLeaders first & last name, their chapterName, divisionName, and the total number of members in the division.

Thanks for any advice!

Zoop

View 3 Replies View Related

Express Server - Tough Query

Jun 10, 2008

I have tables [CustomerData] ([CD]) and [MembershipData] ([MD]).

[CD] has
ID - Primary key
FirstName
LastName
Birthdate
Etc

[MD] has
ID - Primary key
Customer - The ID of the customer from [CustomerData]
StartDate - Membership start date
EndDate - Membership end date

Each [CD] can have multiple [MD].

Memberships can be looked at in one of four ways. They are also prioritized (see next paragraph) in this order
Current: Membership started on or before today and ends on or after today.
Future: Membership starts after today.
Past: Membership ended before today and start and end dates do not fall on 1/1/1900
None: Membership starts and ends on 1/1/1900

I have created four Views that return all memberships for each type. I am trying to create a query that selects all [CD] and includes the highest matching membership for each [CD]

I have a working query that will return the highest priority [MD] that a [CD] has when I specify a [CD] in my WHERE.

That query looks like this. (CurrentMembership, FutureMembership, PastMembership and NoMembership are my Views)

IF (EXISTS(SELECT * FROM CurrentMembership WHERE Customer = 452))
(SELECT TOP 1 * FROM CurrentMembership WHERE Customer = 452)
ELSE
IF (EXISTS(SELECT * FROM FutureMembership WHERE Customer = 452))
(SELECT TOP 1 * FROM FutureMembership WHERE Customer = 452)
ELSE
IF (EXISTS(SELECT * FROM PastMembership WHERE Customer = 452))
(SELECT TOP 1 * FROM PastMembership WHERE Customer = 452)
ELSE
(SELECT TOP 1 * FROM NoMembership WHERE Customer = 452)


I have it working for one specified individual. I'm at a loss as to how to extend it to work for all individuals.

Any ideas?

TIA

View 7 Replies View Related

Tough 2 Year Comparison Sales Query

Dec 17, 2007

I'm developing a 2 year comparison rolling 12 month Sales report but am having problems with the query I'm using. Because I need to include every month (whether no sales or not) I have a calendar table named metaDates and each table gets outer joined as a result. (Forgive the long query) Please see below.

SELECT Customer.country, Order_Line_Invoice.prodcatid, MetaDates.[Month], MetaDates.[Year], isNull(SUM(Order_Line_Invoice.Sales),0) AS Sales , SUM(Order_Line_Invoice.Cost) AS Cost,
( isNull(SUM(Order_Line_Invoice.Sales),0) - isNull(SUM(Order_Line_Invoice.Cost),0) )AS GM,
'Current 12 Months' AS yearNum
FROM MetaDates
LEFT OUTER JOIN Order_Line_Invoice ON (MetaDates.Date = Order_Line_Invoice.InvoiceDate AND (Order_Line_Invoice.prodcatid IN (@GroupByFieldFilter)) )
LEFT OUTER JOIN Customer ON (Order_Line_Invoice.CustId = Customer.CustId and Customer.country IN (@country) )
LEFT OUTER JOIN Product ON (Order_Line_Invoice.ProdId = Product.ProdId)
WHERE (MetaDates.Date BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), 0) AND @EndDate)

GROUP BY MetaDates.[Year], MetaDates.[Month], Customer.country, Order_Line_Invoice.prodcatid

UNION
SELECT Customer.country, Order_Line_Invoice.prodcatid, MetaDates.[Month], MetaDates.[Year], isNull(SUM(Order_Line_Invoice.Sales),0) AS Sales , SUM(Order_Line_Invoice.Cost) AS Cost,
( isNull(SUM(Order_Line_Invoice.Sales),0) - isNull(SUM(Order_Line_Invoice.Cost),0) ) AS GM,
'Previous 12 Months' AS yearNum
FROM MetaDates
LEFT OUTER JOIN Order_Line_Invoice ON (MetaDates.Date = Order_Line_Invoice.InvoiceDate AND (Order_Line_Invoice.prodcatid IN (@GroupByFieldFilter)) )
LEFT OUTER JOIN Customer ON (Order_Line_Invoice.CustId = Customer.CustId and Customer.country IN (@country) )
LEFT OUTER JOIN Product ON (Order_Line_Invoice.ProdId = Product.ProdId)
WHERE (MetaDates.Date BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-23,@EndDate) ), 0) AND dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), -1))

GROUP BY MetaDates.[Year], MetaDates.[Month], Customer.country, Order_Line_Invoice.prodcatid
ORDER BY MetaDates.[Year], MetaDates.[Month]


That said the second outer join (Customer.country IN (@country)) never gets executed. It's really a simple concept. "Give me last 2 year sales by month (every previous and last month even if no sales) but only for the country the user is in"

Can someone help me out with this sql statement. Any help would be much appreciated.

View 11 Replies View Related

This Is A Tough One

Jun 27, 2007

The following table is counts of the patients that are currently in these statuses. For example, there are 4 people in Triage/Greenhttp://www.helixpoint.com/sql/TriageStatusGrid.jpgIf you look at a screen shot of the tables below, Red/Yellow/Green... These are in the Priority table. Red Being "Immediate or ID of 1http://www.helixpoint.com/sql/db.gifNow the grid and the db do not match...but here is a scenario from the db screen shot.. Patient 1, 2, 3, and 4 are currently in Sector 2 (transport)Patient 1, 3, and 4 are currently in Priority 1(Immediate)Here is a kicker. Patient 4 can not be counted because he has a dischargeDateTime in the Patient table.So here are the counts I need to get:So there are 3 patients in the transport column ( Patient 1, 2, 3) “4 has been discharged�2 patients in the immediate/Red column ( Patient 1, 3) “4 has been discharged�I would need to return a number 2 for the Transport/Red columnHow can I do this sql? Would I do this in multiple sql calls? Can you give me an example?This is what I triedIt does not seem to use the current status SELECT COUNT(*) AS Expr1FROM Patient LEFT OUTER JOINSectorHistory ON Patient.PatientID = SectorHistory.PatientID LEFT OUTER JOINPriorityHistory ON Patient.PatientID = PriorityHistory.PatientIDWHERE (Patient.DischargeDateTime IS NULL) AND (PriorityHistory.PriorityID = 1) AND (SectorHistory.SectorID = 2)

View 1 Replies View Related

Tough Question

Jun 25, 2004

I have an employee table, which i have to join with itself because I have to findout someones primary boss. An employee can have several bosses...


This is an example of the table.

Emp_Id 1
Emp_name John
Boss 'n/a'

Emp_Id 2
Emp_name Peter
Boss 1

Emp_Id 3
Emp_name Mary
Boss_Id 1

Emp_Id 3
Emp_name Mary
Boss_Id 2

I know this isnt a normalized table, but anyway... for each boss that an employee has there is one record depicting the employes boss.

In this case Mary has 2 bosses, John and Peter.


What I need is a query that returns the employee information and the primary boss( in this case the boss with the lowest id)

So for mary the query would return

Emp_iD, Emp_name, Boss_Id
3 Mary 1

Anyone know how could i do this?

View 2 Replies View Related

Tough Problem...

Jun 29, 2004

I need a query that will return data in the following format.

Col1 Col2 Count
---------------
A | B | 1
A | C | 2
A | D | 3
A | E | 4
B | A | 1
B | B | 2
B | C | 3
--------------

In other words I want to group the results by col1 and when col1 one changes I want to restart my rowcount. I also want to return the row count for each record.

I have tried many different methods, but I am starting to think that this is not even possible.

Any ideas?

Thanks...

View 13 Replies View Related

Tough Question

Apr 21, 2008

Ok, I'm needing to write a view for a 3rd party scripting tool to call. Apparently this scrpting tool cannot call a function or SP.

Here is the problem. The view needs to take a notes col and a date col
and concat them together as one col and the cus acct # as 2nd col.
There will be several rows of notes to one cus acct #.

So if a normal select against this returns

note date acct
================================
txt1 1/1/00 1
txt2 1/2/00 2
txt3 1/3/00 1

I need to return this through a view

notes acct
===========================
txt1 1/1/00, txt3 1/3/00 1
txt2 1/2/00 2

I realize there are several ways to do this in a SP or function
but through a view?

View 2 Replies View Related

Tough Little Trigger

Jul 23, 2005

Given the following 3 Tables:CREATE TABLE [Company] ([CompanyID] [int] NOT NULL ,[DateTimeCreated] [datetime] NOT NULL CONSTRAINT[DF_Company_DateTimeCreated] DEFAULT (getdate()),[DateTimeModified] [datetime] NULL ,CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED([CompanyID]) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [CompanyOffice] ([CompanyID] [int] NOT NULL ,[OfficeID] [int] NOT NULL ,[IsActive] [bit] NOT NULL ,CONSTRAINT [PK_CompanyOffice] PRIMARY KEY CLUSTERED([CompanyID],[OfficeID]) ON [PRIMARY] ,CONSTRAINT [FK_CompanyOffice_Company] FOREIGN KEY([CompanyID]) REFERENCES [Company] ([CompanyID]),CONSTRAINT [FK_CompanyOffice_Office] FOREIGN KEY([OfficeID]) REFERENCES [Office] ([OfficeID])) ON [PRIMARY]GOCREATE TABLE [Office] ([OfficeID] [int] NOT NULL ,[DateTimeCreated] [datetime] NOT NULL CONSTRAINT[DF_Office_DateTimeCreated] DEFAULT (getdate()),[DateTimeModified] [datetime] NULL ,[FullOfficeName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,CONSTRAINT [PK_Office] PRIMARY KEY CLUSTERED([OfficeID]) ON [PRIMARY]) ON [PRIMARY]GOThe CompanyOffice.dbo.IsActive bit field is supposed to be marked"true" for 1 record per a given Office (i.e. there can only be a single"Active" Company for any given Office). I decided the best way toenforce is through a trigger...My initial thoughts were a toggling effect (similar to the behaviorthat a radio button exhibits)... which would work like a champ for aSingle Row Insert or Update but for a Multi Row Insert/Update not thatstaight forward... I fooled around a little with some complicatedsub-queries that did not pan out. The only other way to do this is toutilize a cursor (at least that I can think of). Because of theoverhead with a cursor, I find this incredibly undesirable.My secondary thought was to just restrict an Insert or Update Statementthat leaves the Table in an "error" state (2 or 0 Active Companies peran Office). Then I realized that if the "Toggling Trigger" did notexist from above, it will often be the case that the Table would haveto be left in an "error" state for a short while, until a second updatestatement is run. (example, I insert a new active Company in theCompanyOffice table for an Office, then I go to the other activeCompany record for this Office and set the IsActive flag to false...for that short period of time between the 2 statement the DB is an"error" state, because there are 2 Active Companies for that singleOffice.) That makes this solution very undesirable.Any suggestions?Thanks in Advance --Rich

View 7 Replies View Related

Tough Problem, Need Help

Jul 20, 2005

I have a very strange database with a very strange problem.Consider 4 tables:Table1:----------------Table1ID INT PKTable2ID INT FKTable3ID INT FKOrderNo VARCHAR(50)Table2----------------Table2ID INT PKTable4ID INT FKTable3----------------Table3ID INT PKTable2ID INT FKTable4----------------Table4ID INT PKOrderTotal VARCHAR(50)With Data:Table1:------------1 1 NULL 900012 2 NULL 900023 NULL 1 900034 NULL 2 90004Table2:------------1 12 1Table3:------------1 12 2Table4:------------1 5002 1000Table1 can have either a Table2ID OR a Table3ID but not both.This is the query I'm attempting:---------------------------SELECT dbo.Table1.OrderNo, dbo.Table4.OrderTotalFROM dbo.Table1 LEFT OUTER JOINdbo.Table4 INNER JOINdbo.Table2 ON dbo.Table4.Table4ID =dbo.Table2.Table4ID INNER JOINdbo.Table3 ON dbo.Table2.Table2ID =dbo.Table3.Table3ID ON dbo.Table1.Table2ID = dbo.Table2.Table2ID ANDdbo.Table1.Table3ID = dbo.Table3.Table3IDWhich gives me:---------------------------90001 NULL90002 NULL90003 NULL90004 NULLWhen I really want:----------------------------90001 50090002 50090003 50090003 1000 (NOT 500)I don't know how to do this. Are any of you sql guru's up to thechallenge?Thanks in advance-Mattp.s. sql to recreate tables includedCREATE TABLE [dbo].[Table1] ([Table1ID] [int] IDENTITY (1, 1) NOT NULL ,[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Table2ID] [int] NULL ,[Table3ID] [int] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Table2] ([Table2ID] [int] IDENTITY (1, 1) NOT NULL ,[Table4ID] [int] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Table3] ([Table3ID] [int] IDENTITY (1, 1) NOT NULL ,[Table2ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Table4] ([Table4ID] [int] IDENTITY (1, 1) NOT NULL ,[OrderTotal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOALTER TABLE [dbo].[Table1] WITH NOCHECK ADDCONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED([Table1ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table2] WITH NOCHECK ADDCONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED([Table2ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table3] WITH NOCHECK ADDCONSTRAINT [PK_Table3] PRIMARY KEY CLUSTERED([Table3ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table4] WITH NOCHECK ADDCONSTRAINT [PK_Table4] PRIMARY KEY CLUSTERED([Table4ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table1] ADDCONSTRAINT [FK_Table1_Table2] FOREIGN KEY([Table2ID]) REFERENCES [dbo].[Table2] ([Table2ID]),CONSTRAINT [FK_Table1_Table3] FOREIGN KEY([Table3ID]) REFERENCES [dbo].[Table3] ([Table3ID])GOALTER TABLE [dbo].[Table3] ADDCONSTRAINT [FK_Table3_Table2] FOREIGN KEY([Table3ID]) REFERENCES [dbo].[Table2] ([Table2ID])GO

View 6 Replies View Related

A Tough Situation To Get ID....

Apr 17, 2008



Hello All

I have a table called Tax Act

In that I have Tax Act ID. Which is supposed to be NOT NULL.

I have to create IDs by my self using some MAX and MAX+1 incrementing function.

Please guide me on how to

View 3 Replies View Related

Connection Timeouts - A Tough One!

Dec 16, 2003

I'm having major problems with SqlClient Data provider connections in my application. I'm using Win 2003 Server with SQL Server 2000 (both on the same machine), and my application is developed in VB.NET.

I'm using the following db connection string:
Data Source=(local);Initial Catalog=database;Uid=user;Pwd=password;max pool size=100;

As you can see max pool size is set to 100. The SQL Database has it's MAX_CONNECTIONS property set to >32000.

The symptoms are that at seemingly random times, not nessecarily during peak hours, the application becomes extremely slow and stops responding. The error message that is beeing generated is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

After monitoring and debugging, I've found two things.

1) At random times, zero-three times a day, the number of database connections (according to performance monitor) rises from normally 2-8 up to 20-25. After a while, usually 10-60 minutes, it drops back to normal. This is directly related to when the timeout errors occur. It doesn't seem to happen more often during peak load, for some reason. I suspect there is connection leakage in the application, but I've done extensive debugging, and I just can't find any. Also, since the peaks occur randomly and I can't reproduce the error in my development environment, I have no solution to this problem. A few weeks ago, the application ran during heavy load without this problem for 7 days. Before and after that, there have been errors at least every second day. That confuses me even more.

2) The number of connections (monitored in perf mon) never reach above 25. Why? I'm using SQL Server enterprise edition, it's connection limit is over 32000. ADO pool size is 100, and I can't find anything else that would limit the amount of concurrent connections. I'm totally confused. Why does number of connections never rise above 25? Is there a setting hidden somewhere that I just can't find? Also, according to Performance Monitor, I don't get any failed connects, I just get failed commands. Maybe that's a clue? Could the problem some kind of deadlocks in the database that causes timeout?

I realize this entire post is rather confusing. It reflects the state of my mind, I guess, after working around the clock on this issue for a month :)

Any help is deeply appreciated!

View 2 Replies View Related

Tough SQL Server/ASP Problem

Jun 28, 2000

This one has me stumped!!!

I have a stored procedure that moves data from three tables to three other tables that are exactly like the first three. When I call the SP from the Query Analyzer, it works great and moves all the records over. When I call it from ASP, the SP inserts two identical records in each table. Has anyone seen this kind of behavior? The ASP code is not in a loop my the way. I placed a response.write after I executed the SP and it only printed to the screen once. SO I know that the SP is only being called once from ASP.
I don't have any loops within the SP either.

Thanks in advance

View 3 Replies View Related

This May Be A Tough One: Contains With Inflectional And NOT Logic...Can It Be Done?

Aug 10, 2007

Thank you in advance!

I know from other posts that this works:
CONTAINS(column, 'formsof(inflectional,sport) AND formsof(inflectional,award)'

But I have an advanced googlesque search that includes "Without the Words:". I want to include inflectionals in the list of words to be omitted. Can this be done?

So far I have not had any success with:
'NOT(formsof(inflectional,sport)) AND NOT(formsof(inflectional, award))'

Any help would be greatly appreciated!

View 1 Replies View Related

Some Tough SQL Server Queries

Oct 4, 2006

1) Given the name of an index how to find the columns associated with the particular index in SQL Server?

2) Given the name of a constraint, how to find whether it is enabled or disabled?

3) Is there any equivalent of the Oracle 'valid' state in SQL Server? e.g. In Oracle 'valid' state is defined for views, indexes. Are there equivalent concepts in SQL Server and if there are, how to find if a particular index/view is 'valid' or not?

View 2 Replies View Related

Tough Date Manipulation Issue.

Jul 23, 2005

Greetings All, I was hoping that someone might be able to help me withthe following issue:table ddl:create table exchange(exchangefrom varchar(6),exchangeto varchar(6),exchangecode varchar(6),datemfrom datetime,dateto datetime,exchangerate decimal(28,10))The data in this table under normal conditons will look like:select *from exchangeUSD EURO GL 01/01/2004 01/31/2004 .7523453111USD GBP GL 01/01/2004 01/31/2004 .5384966123USD EURO GL 02/01/2004 02/29/2004 .7523492111USD GBP GL 02/01/2004 02/29/2004 .6004972023My task is to calculate the days delimeted by the start and end date ofthe period which is simple enough:select exchangefrom, exchangeto, exchangecode, datemfrom, dateto,datediff(d, datemfrom, dateto)from exchangeHowever due to circumstances beyond my control the dateto field maycontain a null instead of a valid end date!! YIKES:select *from exchangeUSD EURO GL 01/01/2004 01/31/2004 .7523453111USD GBP GL 01/01/2004 NULL .5384966123USD EURO GL 02/01/2004 02/29/2004 .7523492111USD GBP GL 02/01/2004 02/29/2004 .6004972023My solution to correct the data is to populate the missing end datewith the (start date -1 day) of the next period. However, I am notsure how to do this with SQL? E.g) from the example directly above therow: USD GBP GL 01/01/2004 NULL needs to be updated to:USD GBP GL 01/01/2004 01/31/2004 and this can be done bylooking for the next period (USD GBP GL 02/01/200402/29/2004) that follows and subtracting from its start date 1 day (02/01/2004 - 1 day = 01/31/2004) and that will give me the appropriateend date.If anyone has any insight into solving this problem I would be verythankful.Regards, TFD.

View 7 Replies View Related

Tough Correlated Subquery Issue

Aug 29, 2006

I am running 2 versions of a correlated subquery. The two versiondiffer slightly in design but differ tremendously in performance....ifanyone can answer this, you would be awesome.The "bad" query attempts to build a result set using a correlatedsubquery. The part causing the error is that the correlated subqueryis part of a derived table (joining 3 tables). Trying to run the querytakes a long time and the more records in the source table, theperformance is exponentially worse. When i change the derived table toa fixed table, the query runs fast.I look at the Execution Plan in Query Analyzer and the majority of timeis taken by the Clustered Index Seek and by the Nested Loops/InnerJoin.************************************************** ************************************************** ******here is the "bad" query:************************************************** ************************************************** ******SELECT licenseKey, (SELECT TOP 1 mi.id FROM messages miINNER JOIN identities i ON i.id=mi.identityidINNER JOIN licenses l on i.licenseid=l.idWHERE l.licenseKey = t1.licenseKey AND category = 'usage'ORDER BY mi.created DESC) as messageidFROM licenses T1************************************************** ************************************************** ******here is the "good" query************************************************** ************************************************** ******SELECT licenseKey, (SELECT TOP 1 t2.id FROM temptable T2WHERE t2.licenseKey = t1.licenseKeyORDER BY t2.created DESC) as messageidFROM licenses T1************************************************** ************************************************** ******Thank you in advance

View 5 Replies View Related

Trace And 6.5---simple Question--Tough Answers

Mar 23, 2004

I want to trace just sql statements, ie insert delete and update
On d drive the file should be l dropped

and have a fixed size say 1 mg

How do i set up

View 7 Replies View Related

Tough One - 28000 (18452) Not Associated With A Trusted Connection

Sep 6, 2007

Here's a real puzzler. I have a remote SQL Server running on a different domain than the one I am logged in to. From my own PC, logged in as myself, I can create an ODBC connection to this remote server (using integrated authentication) with no problem. When I log in to any other PC as myself, I get the "28000 (18452) Not associated with a trusted connection" error. This would therefore seem to be a PC-specific problem, but I can't see any difference between the PCs. All PC's are running the same version of MDAC.

Firewall is down, no proxy is involved, and I have given myself Administrative privelege on the PCs.

Can anyone explain this? Thanks in advance.

View 9 Replies View Related

Final Attemp -Sql Stored Procedure Tough Question

Mar 17, 2004

I have a procedure I need to get the values out of..I am using outputs...I have no idea why it wont work......I need all values listed in the select part of procedure....



CREATE procedure dbo.Appt_Login_NET
(
@LoginName nvarchar(15),
@Password NvarChar(15),
@UserName nvarchar(15)Output,
@UserPassword nvarchar(15)Output,
@UserClinic nvarchar(3)Output,
@UserTester bit Output
)
as
select
UserName,
UserPassword,
UserClinic,
UserTester
from
Clinic_users
where
UserName = @LoginName
and
UserPassword = @Password

GO



my vb.net code to retrive this info is

Private Sub Button1_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.ServerClick
Dim con As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmd As New SqlCommand
Dim parmuser As SqlParameter
Dim parmus As SqlParameter
Dim parmpass As SqlParameter
Dim parmtest As SqlParameter
Dim struser As String
Dim strpass As String
Dim strclinic As String
Dim strnames As String
Dim tester As String
strpass = txtPass.Value
struser = txtUser.Value
cmd = New SqlCommand("Appt_Login_NET", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@LoginName", struser)
cmd.Parameters.Add("@Password", strpass)
parmus = cmd.Parameters.Add("@UserName", SqlDbType.NVarChar)
parmus.Size = 15
parmus.Direction = ParameterDirection.Output
parmuser = cmd.Parameters.Add("@UserClinic", SqlDbType.NVarChar)
parmuser.Size = 3
parmuser.Direction = ParameterDirection.Output
parmpass = cmd.Parameters.Add("@UserPassword", SqlDbType.NVarChar)
parmpass.Size = 15
parmpass.Direction = ParameterDirection.Output
parmtest = cmd.Parameters.Add("@UserTester", SqlDbType.Bit)
parmtest.Size = 1
parmtest.Direction = ParameterDirection.Output

con.Open()
cmd.ExecuteNonQuery()
If Not IsDBNull(cmd.Parameters("@UserName").Value) Then
Label1.Text = cmd.Parameters("@UserName").Value()
Else
Label1.Text = "No Results Found"
End If

con.Close()
End Sub

Why does this always show as "DBNUll" I get nothing when I debug any of my parm variables.I searched the SQl Server and in Query analyzer instead of the output variables in the procedure being just outputs they are input/outputs...................What does it take to get this working??? Do I need a conversion datatype I would prefer I gain the values and store them in variables......

View 7 Replies View Related

TOUGH INSERT: Copy Sale Record/Line Items For Duplicate Record

Jul 20, 2005

I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria

View 6 Replies View Related

Query Runs Fine In Query Analyser But Not The Query Debugger

Dec 19, 2003

I'm running a query, actually its an insert that works when using the TSQL below.

However when I try to use the debugger to step through and using the exact same values as those below I get the following error:

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

Its Killing me because everything else works, but this. Can somebody help.


DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,

@BeenRead NVARCHAR(10),-- = NULL

@FK_UserIDList NVARCHAR(4000)-- = NULL


--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'

SET @FK_UserIDList = '1,2,3'


--AS

--SET NOCOUNT ON


SET NOCOUNT ON

SET XACT_ABORT ON

BEGIN TRANSACTION

SET @Date = GETDATE()

-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)

SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,

@Job_Date,
@Start,
@Finish

-- Get the new Customer Identifier, return as OUTPUT param
SELECT @NoteID = @@IDENTITY


-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned
-- users.
IF @FK_UserIDList IS NOT NULL
EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList

-- Insert New Address record
-- Retrieve Address reference into @AddressId
-- EXEC spInsertForUserNote
-- @FK_UserID,
--@NoteID,
-- @BeenRead
-- @Fax,
-- @PKId,
-- @AddressId OUTPUT

COMMIT TRANSACTION

--------------------------------------------------
GO

View 1 Replies View Related

Query Diff Results From Ent Manager Query And Query Analizer

May 28, 2008

ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)

SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007

but in query analizer I get the result of

12/31/2006

Why the different dates

View 4 Replies View Related

[Query] - Query Designer Encountered A Query Error:Unspecified Error

Jan 22, 2001

Hi,

I get this error dialog when I try to open all the rows of any table from Enterprise manager..

Any help would be really appreciated..

Thanks,
-Srini.

View 1 Replies View Related

Error: 8624 Internal Query Processor Error: The Query Processor Could Not Produce A Query Plan.

May 24, 2007

SQL Server 2005 9.0.3161 on Win 2k3 R2



I receive the following error:



"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."



I have traced this to an insert statement that executes as part of a stored procedure.



INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)

VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)



There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.



Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).

View 5 Replies View Related

Query Works In 'test Query' But Refuses To Show Up In The Datagrid On A Web Page - Urgent!

Mar 28, 2007

Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
 SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC

 Here is the page source
 
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="&#9;SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches&#13;&#10;&#9;FROM dbo.MAKES INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID&#13;&#10;&#9;WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )&#13;&#10;&#9;GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID&#13;&#10;&#9;HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2&#13;&#10;&#9;ORDER BY count(*) DESC&#13;&#10;">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
 AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
 Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
 Please help!
 
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
 

View 4 Replies View Related

SQL Server 2012 :: Adding Count To Query Without Duplicating Original Select Query

Aug 5, 2014

I have the following code.

SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,

[code]...

However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.

View 2 Replies View Related

Opening Up Odbc Data Source In The Query Query Inside Of The Server Manager

Jun 15, 2007

I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.



Thanks in advance



Daniel Buchanan.



If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.

View 1 Replies View Related

Master Data Services :: Error - Query Processor Could Not Produce A Query Plan

Jul 19, 2015

We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T

• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues

• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"

• We are using the last version of Excel Add-in

• We try to reinstall the MDS feature

• If I backup/restore MDS database to other server it works

• We updated to SQL 2012 SP2 + CU4 but the error persisted ...

Looking at the MDSTraceLog we are routed to the this msg

SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28

At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped

** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
   at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
   at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)

[code]....

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved