Re: Difficult query problem

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

"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. Just uncomment the where part for the case you want to display:

with
  rawdata as (
    select visitid, bldgcode, visitdate, empid, visitcode,

           count(distinct visitcode) over (partition by visitdate, bldgcode, empid) cntcode,
           count(distinct empid) over (partition by visitdate, bldgcode, visitcode) cntemp,
           min(empid) over (partition by visitdate, bldgcode) minemp,
           max(empid) over (partition by visitdate, bldgcode) maxemp,
           min(visitcode) over (partition by visitdate, bldgcode) mincode,
           max(visitcode) over (partition by visitdate, bldgcode) maxcode

    from visits
  ),
  data as (
    select visitid, bldgcode, visitdate, empid, visitcode,

           case when cntcode > 1 then 'Y' end case1,
           case when cntemp > 1 then 'Y' end case2,
           case when minemp != maxemp and mincode != maxcode
                     and cntcode <= 1 and cntemp <=1
                then 'Y'
          end case3

    from rawdata
  )
select visitid, bldgcode, visitdate, empid, visitcode,

       case1 "1", case2 "2", case3 "3"
from data

--where case1 = 'Y'
--where case2 = 'Y'
--where case3 = 'Y'
--where case1 = 'Y' or case2 = 'Y' or case3 = 'Y'

/

Regards
Michel Cadot Received on Sun Aug 06 2006 - 04:43:06 CDT