RE: Help me compare fields in two distinct records

From: Jain, Akshay <Akshay.Jain_at_cit.com>
Date: Mon, 14 Aug 2000 16:35:48 -0400
Message-Id: <10589.114542@fatcity.com>

I would suggest a PL/SQL type programmatic solution with a linear pass through the table.

e.g. (Not at all tested, just an exposition of the idea):

DECLARE

	CURSOR crsr_patients IS
	select  icons,
	        admdatetime,
	        disdate,
	        inptoutcome
	from       inpatient
	where icons IN (602, 610);
	
	RECORD prev_patient IS crsr_patients%ROWTYPE;
	RECORD next_patient IS crsr_patients%ROWTYPE;

BEGIN

	OPEN crsr_patients;
	FETCH crsr_patients into prev_patient;
	FETCH crsr_patients into next_patient;		

	WHILE (crsr_patients%FOUND) LOOP
		diff_time := prev_patients.disdate -
next_patient.admdatetime;
		dbms_output ('Patient ' || prev_patient.ICONS || ' returned
in ' diff_time);
		prev_patient := next_patient;
		FETCH crsr_patients into next_patient;		
	END LOOP:

	CLOSE crsr_patients;

END;
/

Akshay Jain

---

Newcourt-CIT
Tel. (416) 507-5385
mailto:Akshay.Jain_at_cit.com

---

-----Original Message-----
From: Peter Hazelton [mailto:peterhazelton_at_hotmail.com] Sent: Monday, August 14, 2000 2:07 PM
To: Multiple recipients of list ORACLE-L Subject: Help me compare fields in two distinct records

Consider the following record set that is created by this SQL statement:

select icons,

        to_char(admdatetime,'DD-MM-YYYY HH:MI:SS') ADMN,
        to_char(disdate,'DD-MM-YYYY HH:MI:SS') DIS,
        inptoutcome
from       inpatient

where icons IN (602, 610)

    ICONS ADMN                DIS                 INPTOUT
--------- ------------------- ------------------- -------
      610 30-11-1997 10:19:00 08-12-1997 12:00:00 Home
      610 08-04-1998 11:58:00 16-04-1998 12:00:00 Home
      610 17-04-1998 07:04:00 29-04-1998 12:00:00 Acute
      610 27-04-1998 03:26:00 30-04-1998 12:00:00 Home
      602 25-03-1998 01:23:00 02-04-1998 12:00:00 Home
      602 28-10-1998 07:58:00 29-10-1998 12:00:00 Home

I will be selecting a much larger record set when I run the query on my whole table so consider this a small test case.

What I need to do is compare the DIS time of one line to the ADMN time of the NEXT record when the ICONS numbers are the same.

i.e.
Consider the first two records in the data set. I need to see the difference

between line 2 ADMN of 08-04-1998 11:58:00 and line one DIS of 08-12-1997 12:00:00. These are obviously two different records so you can not do a reqular SQL query on the two to find the difference.

So my question is: What is the best way to do this comparison? Array? Function? Proceedure? All or none of the above? If anyone can shed some light on this for me I would really appreciate it. My ultimate goal is to look at the data set and only select those records that have the same icons number and the difference between the DIS of line one and the ADM of line two is 24 hours or less.

Help me if you can, this list was tremendous for me last week in helping me with another problem so I anxiously await your response.

Peter Hazelton

---

Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

-- 
Author: Peter Hazelton
  INET: peterhazelton_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Mon Aug 14 2000 - 15:35:48 CDT