Re: Difficult query problem

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 5 Aug 2006 15:28:23 -0700
Message-ID: <1154816903.766011.248600@75g2000cwc.googlegroups.com>

DFS wrote:
> 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)
>
>
> Here's the table
> =====================================
> CREATE TABLE VISITS
> (
> VISITID NUMBER(3,0) NOT NULL ,
> BLDGCODE VARCHAR2(10) NOT NULL ,
> VISITDATE DATE NOT NULL ,
> EMPID NUMBER(3,0) NOT NULL ,
> VISITCODE VARCHAR2(5) NOT NULL
> );
> 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
> INSERTS
>
> INSERT INTO VISITS (VISITID, BLDGCODE, VISITDATE, EMPID, VISITCODE)
> VALUES(1, 'BLDG1', #10/18/2005#, 128, 'V6');
> INSERT INTO VISITS (VISITID, BLDGCODE, VISITDATE, EMPID, VISITCODE)
> VALUES(2, 'BLDG1', #10/18/2005#, 128, 'V9');
> INSERT INTO VISITS (VISITID, BLDGCODE, VISITDATE, EMPID, VISITCODE)
> VALUES(3, 'BLDG2', #1/24/2006#, 128, 'V8');
> =====================================
>
> Your help is appreciated.

Sybrand Bakker provides you with excellent tips to help you find a solution.

Your insert statements should look like this:

INSERT INTO VISITS (VISITID, BLDGCODE, VISITDATE, EMPID, VISITCODE)
VALUES(1, 'BLDG1', '18-OCT-2005', 128, 'V6');
INSERT INTO VISITS (VISITID, BLDGCODE, VISITDATE, EMPID, VISITCODE)
VALUES(2, 'BLDG1', '18-OCT-2005', 128, 'V9');
INSERT INTO VISITS (VISITID, BLDGCODE, VISITDATE, EMPID, VISITCODE)
VALUES(3, 'BLDG2', '24-JAN-2006', 128, 'V8');

...

#7/10/2006# may mean July 10, 2006 or October 7, 2006, depending on your location in the world. '10-JUL-2006' is less likely to be misunderstood, and is in the correct format to be understood by Oracle.

To me, it looks like you are trying too hard to find the answer. The answer to problem #1 may be as simple as this: SELECT

  V1.EMPID,
  V1.BLDGCODE,
  V1.VISITDATE

FROM
  VISITS V1,
  VISITS V2
WHERE
  V1.EMPID=V2.EMPID

  AND V1.BLDGCODE=V2.BLDGCODE
  AND V1.VISITDATE=V2.VISITDATE
  AND V1.VISITID<>V2.VISITID;

By changing the = and <> signs between the columns, you can find answers #2 and #3.

For #1, assume that you need to list the VISITIDs for each of the records. This can very easily be accomplished in your programming language - hint: SELECT DISTINCT, include V1.VISITID in the SELECT and sort by all columns.

For #1, assume that you need to list the VISITIDs for each of the records, and you do not have access to a programming language, like VB, so everything must be done using SQL code. What would that code look like?
SELECT

  V2.EMPID,
  V2.BLDGCODE,
  V2.VISITDATE,
  MAX(DECODE(V2.RN-V1.MINRN,0,V2.VISITCODE,'')) ||
MAX(DECODE(V2.RN-V1.MINRN,1,', '||V2.VISITCODE,'')) ||
MAX(DECODE(V2.RN-V1.MINRN,2,', '||V2.VISITCODE,''))  ||
MAX(DECODE(V2.RN-V1.MINRN,3,', '||V2.VISITCODE,'')) ||
MAX(DECODE(V2.RN-V1.MINRN,4,', '||V2.VISITCODE,'')) ||
MAX(DECODE(V2.RN-V1.MINRN,5,', '||V2.VISITCODE,''))  ||
MAX(DECODE(V2.RN-V1.MINRN,6,', '||V2.VISITCODE,'')) ||
MAX(DECODE(V2.RN-V1.MINRN,7,', '||V2.VISITCODE,'')) ||
MAX(DECODE(V2.RN-V1.MINRN,8,', '||V2.VISITCODE,''))  ||

MAX(DECODE(V2.RN-V1.MINRN,9,', '||V2.VISITCODE,'')) || MAX(DECODE(V2.RN-V1.MINRN,10,', '||V2.VISITCODE,'')) VISIT_CODES FROM
  (SELECT
    EMPID,
    BLDGCODE,
    VISITDATE,
    MIN(ROWNUM) MINRN,
    MAX(ROWNUM) MAXRN
  FROM
    (SELECT DISTINCT

      V1.EMPID,
      V1.BLDGCODE,
      V1.VISITDATE,
      V1.VISITCODE
    FROM
      VISITS V1,
      VISITS V2
    WHERE
      V1.EMPID=V2.EMPID
      AND V1.BLDGCODE=V2.BLDGCODE
      AND V1.VISITDATE=V2.VISITDATE
      AND V1.VISITID<>V2.VISITID
    ORDER BY
      V1.EMPID,
      V1.BLDGCODE,
      V1.VISITDATE,
      V1.VISITCODE)

  GROUP BY
    EMPID,
    BLDGCODE,
    VISITDATE) V1,
  (SELECT
    EMPID,
    BLDGCODE,
    VISITDATE,
    VISITCODE,
    ROWNUM RN
  FROM
    (SELECT DISTINCT

      V1.EMPID,
      V1.BLDGCODE,
      V1.VISITDATE,
      V1.VISITCODE
    FROM
      VISITS V1,
      VISITS V2
    WHERE
      V1.EMPID=V2.EMPID
      AND V1.BLDGCODE=V2.BLDGCODE
      AND V1.VISITDATE=V2.VISITDATE
      AND V1.VISITID<>V2.VISITID
    ORDER BY
      V1.EMPID,
      V1.BLDGCODE,
      V1.VISITDATE,
      V1.VISITCODE)) V2

WHERE
V2.EMPID=V1.EMPID

  AND V2.BLDGCODE=V1.BLDGCODE
  AND V2.VISITDATE=V1.VISITDATE
  AND V2.RN BETWEEN V1.MINRN AND V1.MAXRN

GROUP BY

  V2.EMPID,
  V2.BLDGCODE,
  V2.VISITDATE;

This SQL code shows examples of nested inline views, self joins, range joins, ROWNUM and DECODE to collapse the result set into a single result row per EMPID, BLDGCODE, VISITDATE. If you look hard enough, you will find the original short SQL statement embedded in this larger SQL statement. As written, the SQL statement will return the first 10 VISITCODEs for each EMPID, BLDGCODE, VISITDATE combination.

The answers for the other two problems follow the same pattern.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Aug 05 2006 - 17:28:23 CDT