Re: Difficult query problem

Charles Hooper wrote:

> 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');
Yes. I work in VB a lot - force of habit.

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

I already had the right rowcount results, but it was a kludgey mixture of queries and VB code. I got #1 and #2 implemented in all SQL right after I posted the first time. #3 was trickier.

#3. DIFFERENT EMPLOYEES, DIFFERENT VISITCODE SELECT V.* FROM VISITS V INNER JOIN
(
SELECT BLDGCODE, SCHEDULEDATE
FROM VISITS
GROUP BY BLDGCODE, VISITDATE
HAVING MIN(EMPID) <> MAX(EMPID)
AND MIN(VISITCODE) <> MAX(VISITCODE)
) V2
ON (V.BLDGCODE= V2.BLDGCODE)
AND (V.VISITDATE= V2.VISITDATE) (middle section V2 thanks to Joe Celko on comp.databases.ms-sqlserver)

This SQL returns 177 rows, which matches my kludgey solution.

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

Looks good. It does look a little simpler than mine.

Couldn't get this to work right for #3, but I'll have to play around with it more.

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

Interesting.

Thanks for the good SQL code and analysis. Received on Sat Aug 05 2006 - 20:52:55 CDT