Re: Difficult query problem

From: DFS <nospam_at_dfs_.com>
Date: Sun, 6 Aug 2006 12:57:59 -0400
Message-ID: <AbpBg.26$Nx4.9@bignews8.bellsouth.net>

Michel Cadot wrote:

> "DFS" <nospam_at_dfs_.com> a écrit dans le message de news:
> C5nBg.15880$WM.13984_at_bignews1.bellsouth.net...
>> Michel Cadot wrote:

>>> "DFS" <nospam_at_dfs_.com> a écrit dans le message de news:
>>> 9FTAg.46060$Nt.22378_at_bignews8.bellsouth.net...
>>>> Tough one here, I think. The following data set is building
>>>> inspection visits. It consists of multiple visits (2+) made to the
>>>> same building on the same day.
>>>>
>>>> There are 3 "conditions" I want to reveal using only SQL
>>>> statements.
>>>>
>>>> 1) visits made by the same employee, for different visit codes (eg
>>>> records 1 and 2)
>>>> 2) visits made by different employees, for the same visit codes (eg
>>>> records 7 and 8)
>>>> 3) visits made by the different employees, for different visit
>>>> codes (eg records 36 and 37)
>>>>
>>>
>>> Given the algorithm you use in your VB code I think the following
>>> query will do the work.

>>
>> Very nice!  I like how it displays which conditions the row meets.
>>
>> For conditions 1 and 2 it gives the correct rowcounts (206 and 259
>> respectively), but not for condition 3 (it returns 127 rows but
>> should bring back 177).  Example: it returns only one row for
>> BLDG130 on 10-NOV-05, but 3 visits were made to that building on
>> that day.
>
> Yes but aming these 3 visits, 2 were with visitcode 'V9' and meet
> condition 2
> and so are eliminated according to your VB algorithm:
> "marking the rows that met conditions 1 or 2, then
> looking for ***those that met neither or both conditions 1 and 2 (and
> marked
> those as condition3)***"

My VB algorithm didn't eliminate them - it also marked them as condition 3.

I updated your initial response:

replace "and cntcode <= 1 and cntemp <= 1" with "and cntcode >= 1 and cntemp >= 1"

and I get 177 rows. Cool. Great query!

I do most of my querying in Access (ODBC links to Oracle tables), but I could create this one as a view or pass-thru.

Thanks Michael. Received on Sun Aug 06 2006 - 11:57:59 CDT