Re: Difficult query problem

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sun, 6 Aug 2006 17:06:52 +0200
Message-ID: <44d6058c$0$29894$626a54ce@news.free.fr>

"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)***"

Regards
Michel Cadot Received on Sun Aug 06 2006 - 10:06:52 CDT