OraFAQ Forum: SQL & PL/SQL » Looping or SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Looping or SQL (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Looping or SQL [message #616506]

Tue, 17 June 2014 14:13

rajivn786
Messages: 161
Registered: January 2010

Senior Member

Hi,

Not sure how to code this...

Here are the SQL statements..

create table X
(
  medical_record_number VARCHAR2(20),
  result_date           DATE,
  peak_val              NUMBER,
  creat_date            DATE,
  creat_val             NUMBER
)

Insert into X (MEDICAL_RECORD_NUMBER,RESULT_DATE,PEAK_VAL,CREAT_DATE,CREAT_VAL) values ('2082372',to_date('09-09-2005 07:00:00','DD-MM-YYYY HH24:MI:SS'),4.5,to_date('14-09-2005 02:43:00','DD-MM-YYYY HH24:MI:SS'),4.1);
Insert into X (MEDICAL_RECORD_NUMBER,RESULT_DATE,PEAK_VAL,CREAT_DATE,CREAT_VAL) values ('2082372',to_date('09-09-2005 07:00:00','DD-MM-YYYY HH24:MI:SS'),4.5,to_date('14-09-2005 14:24:00','DD-MM-YYYY HH24:MI:SS'),3.7);
Insert into X (MEDICAL_RECORD_NUMBER,RESULT_DATE,PEAK_VAL,CREAT_DATE,CREAT_VAL) values ('2082372',to_date('09-09-2005 07:00:00','DD-MM-YYYY HH24:MI:SS'),4.5,to_date('15-09-2005 02:25:00','DD-MM-YYYY HH24:MI:SS'),3.5);
Insert into X (MEDICAL_RECORD_NUMBER,RESULT_DATE,PEAK_VAL,CREAT_DATE,CREAT_VAL) values ('2082372',to_date('09-09-2005 07:00:00','DD-MM-YYYY HH24:MI:SS'),4.5,to_date('15-09-2005 15:27:00','DD-MM-YYYY HH24:MI:SS'),2.7);
Insert into X (MEDICAL_RECORD_NUMBER,RESULT_DATE,PEAK_VAL,CREAT_DATE,CREAT_VAL) values ('2082372',to_date('09-09-2005 07:00:00','DD-MM-YYYY HH24:MI:SS'),4.5,to_date('17-09-2005 05:07:00','DD-MM-YYYY HH24:MI:SS'),2.2);
Insert into X (MEDICAL_RECORD_NUMBER,RESULT_DATE,PEAK_VAL,CREAT_DATE,CREAT_VAL) values ('2082372',to_date('09-09-2005 07:00:00','DD-MM-YYYY HH24:MI:SS'),4.5,to_date('19-09-2005 01:54:00','DD-MM-YYYY HH24:MI:SS'),1.3);
Insert into X (MEDICAL_RECORD_NUMBER,RESULT_DATE,PEAK_VAL,CREAT_DATE,CREAT_VAL) values ('2082372',to_date('09-09-2005 07:00:00','DD-MM-YYYY HH24:MI:SS'),4.5,to_date('20-09-2005 01:20:00','DD-MM-YYYY HH24:MI:SS'),1.3);
Insert into X (MEDICAL_RECORD_NUMBER,RESULT_DATE,PEAK_VAL,CREAT_DATE,CREAT_VAL) values ('2082372',to_date('09-09-2005 07:00:00','DD-MM-YYYY HH24:MI:SS'),4.5,to_date('21-09-2005 03:44:00','DD-MM-YYYY HH24:MI:SS'),1.1);
Insert into X (MEDICAL_RECORD_NUMBER,RESULT_DATE,PEAK_VAL,CREAT_DATE,CREAT_VAL) values ('2082372',to_date('09-09-2005 07:00:00','DD-MM-YYYY HH24:MI:SS'),4.5,to_date('22-09-2005 03:11:00','DD-MM-YYYY HH24:MI:SS'),1);
Insert into X (MEDICAL_RECORD_NUMBER,RESULT_DATE,PEAK_VAL,CREAT_DATE,CREAT_VAL) values ('2082372',to_date('09-09-2005 07:00:00','DD-MM-YYYY HH24:MI:SS'),4.5,to_date('23-09-2005 03:04:00','DD-MM-YYYY HH24:MI:SS'),1.1);
Insert into X (MEDICAL_RECORD_NUMBER,RESULT_DATE,PEAK_VAL,CREAT_DATE,CREAT_VAL) values ('2082372',to_date('09-09-2005 07:00:00','DD-MM-YYYY HH24:MI:SS'),4.5,to_date('24-09-2005 05:45:00','DD-MM-YYYY HH24:MI:SS'),1.1);
Insert into X (MEDICAL_RECORD_NUMBER,RESULT_DATE,PEAK_VAL,CREAT_DATE,CREAT_VAL) values ('2082372',to_date('09-09-2005 07:00:00','DD-MM-YYYY HH24:MI:SS'),4.5,to_date('25-09-2005 04:43:00','DD-MM-YYYY HH24:MI:SS'),1.1);

O/P :

2082372  09-09-2005 07:00:00  17-09-2005 05:07:00

The output : Find the 3rd record  date of 3 consecutive records which are on different days & all 3 records  peak_val - creat_val >= 0.3

Here is the procedure...

1) I need to go through all the records in the ascending order.
2) Find the 1st 3 consecutive records (different days) where the PEAK_VAL - CREAT_VAL >= 0.3.
3) If there is more than 1 record for a day, we may need to consider all the records if a record for that day does not satisfy the above criterion
I mean...

a) Consider the 1st record. 4.5 - 4.1 is >= 0.3
b) Go to the 2nd record which is same date as 1st.Ignore this as we had already the same truncated date which satisfied this criterion
(If the 1st record (point a) didn not have a diference >= 0.3 then we should consider this record)
c) That brings us to 3rd row.. 4.5 - 3.5 is >= 0.3.
d) GO to 4th record...Ignore this -- same reason as point b.
e) go to 5th record.4.5 - 2.2 is >= 0.3.
f) So we have 3 consecutive records which are different days and difference is >= 0.3.
g) So the output is 17-09-2005 05:07:00.
h) If for example for 17-09-2005 05:07:00 if we didn't have value >= 0.3...
then we should restart the whole process from the record starting from 19-09-2005 01:54:00 as the chain will be broken...because we wouldn't have 3 0.3 values for 3 days consecutively

Let me know if any issues..

Report message to a moderator

Re: Looping or SQL [message #616510 is a reply to message #616506]

Tue, 17 June 2014 14:23

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

rajivn786 wrote on Wed, 18 June 2014 00:43

a) Consider the 1st record. 4.5 - 4.1 is >= 0.3
b) Go to the 2nd record which is same date as 1st.Ignore this as we had already the same truncated date which satisfied this criterion
(If the 1st record (point a) didn not have a diference >= 0.3 then we should consider this record)
c) That brings us to 3rd row.. 4.5 - 3.5 is >= 0.3.
d) GO to 4th record...Ignore this -- same reason as point b.
e) go to 5th record.4.5 - 2.2 is >= 0.3.
f) So we have 3 consecutive records which are different days and difference is >= 0.3.
g) So the output is 17-09-2005 05:07:00.
h) If for example for 17-09-2005 05:07:00 if we didn't have value >= 0.3...
then we should restart the whole process from the record starting from 19-09-2005 01:54:00 as the chain will be broken...because we wouldn't have 3 0.3 values for 3 days consecutively

Reply for each rule :

a) OK, this is a filter predicate.
b) Need to use ANALYTIC function over date column, such that similar dates are grouped in a partition.
c) So in each partition as stated in point (b), you need only the 1st record?
d) OK, ignored as you mentioned.
e) Alright, filter predicate still applies.
f) Agreed.
g) How 3?, You have 4 consecutive records. Please clarify the rule.
h) You need to clarify point (g) to proceed further.

Report message to a moderator

Re: Looping or SQL [message #616522 is a reply to message #616510]

Tue, 17 June 2014 16:15

rajivn786
Messages: 161
Registered: January 2010

Senior Member

Hi Lalit,

Thanks for replying back...

Reply for each rule :

a) OK, this is a filter predicate.
b) Need to use ANALYTIC function over date column, such that similar dates are grouped in a partition.
c) So in each partition as stated in point (b), you need only the 1st record?
-- If there are 2 records for the same day...then we need to pick up 1st date which satisfies the criterion of 3 values
<= 0.3...

d) OK, ignored as you mentioned.
e) Alright, filter predicate still applies.
f) Agreed.
g) How 3?, You have 4 consecutive records. Please clarify the rule.

If you consider it based on truncated date
(2 records for 14/9 -- consider 1st record which satisfies 0.3 criterion
2 records for 15/9 -- consider 1st record which satisfies 0.3 criterion
1 record for 17/9 -)

So, effectively there are 3 consecutive dates which satisfied the criterion

So , there are 3 dates only if you consider truncated date..
h) You need to clarify point (g) to proceed further.

[Updated on: Tue, 17 June 2014 16:26]

Report message to a moderator

Re: Looping or SQL [message #616526 is a reply to message #616522]

Tue, 17 June 2014 19:24

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

https://community.oracle.com/message/12492371#12492371

SY.

Report message to a moderator

Previous Topic:	Reqular expression
Next Topic:	create separate record for each column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 01:58:53 CDT 2024