Home » SQL & PL/SQL » SQL & PL/SQL » How to get values of previous record (Oracle 10g)
How to get values of previous record [message #413269] |
Wed, 15 July 2009 01:19  |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi,
I have to find the status of MECODE based on date of examination. The status of a Medical Examiner that is ME can have:
1-Active, 2-Inactive, 3-Inactive, 4- Reactive and 5- Deblacklist.
If on the date of examination, the status of mecode is 2 or 3 then it will be in effect from within 8 days afer the
previous status. For example, If ME is Re-Active on 20-mar-09 and is blacklisted on 25th March'09, then he is still
in re-active status for the date of examination done on 25th march'09 to 02nd April'09.
I am giving create and inserts scripts for the table from where the correct status needs to be fetched from:
CREATE TABLE MEMS_MESTATUS_HIST1
(
SLNO NUMBER(10) NOT NULL,
MECODE VARCHAR2(15 BYTE),
STATUS NUMBER(1) NOT NULL,
FLG NUMBER(1) NOT NULL,
CREATEDDATE DATE NOT NULL
)
INSERT INTO MEMS_MESTATUS_HIST1 ( SLNO, MECODE, STATUS, FLG, CREATEDDATE) VALUES (
20001, '00000278', 1, 2, TO_Date( '01/01/2000 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO MEMS_MESTATUS_HIST1 ( SLNO, MECODE, STATUS, FLG, CREATEDDATE) VALUES (
20002, '00000278', 2, 2, TO_Date( '01/10/2000 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO MEMS_MESTATUS_HIST1 ( SLNO, MECODE, STATUS, FLG, CREATEDDATE) VALUES (
20003, '00000278', 4, 2, TO_Date( '03/20/2009 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO MEMS_MESTATUS_HIST1 ( SLNO, MECODE, STATUS, FLG, CREATEDDATE) VALUES (
20004, '00000278', 3, 2, TO_Date( '03/25/2009 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO MEMS_MESTATUS_HIST1 ( SLNO, MECODE, STATUS, FLG, CREATEDDATE) VALUES (
20005, '00000278', 5, 2, TO_Date( '04/20/2009 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO MEMS_MESTATUS_HIST1 ( SLNO, MECODE, STATUS, FLG, CREATEDDATE) VALUES (
20006, '00000278', 3, 2, TO_Date( '04/29/2009 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO MEMS_MESTATUS_HIST1 ( SLNO, MECODE, STATUS, FLG, CREATEDDATE) VALUES (
20007, '00000278', 5, 2, TO_Date( '05/05/2009 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO MEMS_MESTATUS_HIST1 ( SLNO, MECODE, STATUS, FLG, CREATEDDATE) VALUES (
20008, '00000278', 3, 2, TO_Date( '05/10/2009 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO MEMS_MESTATUS_HIST1 ( SLNO, MECODE, STATUS, FLG, CREATEDDATE) VALUES (
20009, '00000278', 2, 2, TO_Date( '05/15/2009 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO MEMS_MESTATUS_HIST1 ( SLNO, MECODE, STATUS, FLG, CREATEDDATE) VALUES (
20010, '00000278', 1, 1, TO_Date( '05/28/2009 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));
I am testing for a particular date of exam hard coded in select staement. This date needs to be checked with the createddate to find the correct status of the MECODE before moving to billing.
I am testing this using a select statement, but this not working on the 8 days plus scenario to the last status. It is giving the current status as per date. But only in case of status 2 and 3, it get effective after 8 days from date of createddate. Before that it should be effective for the last status.
select distinct nvl(status,0) mMEstatus from mems_mestatus_hist1 where createddate = (select max(createddate) from mems_mestatus_hist1
where to_date(createddate)<=TO_Date( '03/01/2001 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM') and mecode='00000278') and mecode='00000278';
This statement gives correct result as 1.
But the below select gives wrong result for date 03/25/09. It gives status 3. It should be 4 as The ME was reactivated on 20-mar-09 , so its only 5 days he got blacklisted on 25th so examinations done by him on 25th should carry a status of 4 only.
Don't know how to get the status of previous record.
Please help me in this as how to implement the plus 8 day logic to the below select statement.
select distinct nvl(status,0) mMEstatus from mems_mestatus_hist1 where createddate = (select max(createddate) from mems_mestatus_hist1
where to_date(createddate)<=TO_Date( '03/25/2009 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM') and mecode='00000278') and mecode='00000278';
Regards,
Mahi
[Updated on: Wed, 15 July 2009 01:29] Report message to a moderator
|
|
|
|
Re: How to get values of previous record [message #413276 is a reply to message #413272] |
Wed, 15 July 2009 01:37   |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Please find the two formatted sql select statements which are not formatted in the original post:
SELECT DISTINCT Nvl(status,0) mmestatus
FROM mems_mestatus_hist1
WHERE createddate = (SELECT Max(createddate)
FROM mems_mestatus_hist1
WHERE To_date(createddate) <= To_date('03/01/2001 12:00:00 PM','MM/DD/YYYY HH:MI:SS AM')
AND mecode = '00000278')
AND mecode = '00000278';
This s giving correct result that status = 1
SELECT DISTINCT Nvl(status,0) mmestatus
FROM mems_mestatus_hist1
WHERE createddate = (SELECT Max(createddate)
FROM mems_mestatus_hist1
WHERE To_date(createddate) <= To_date(''03/25/2009 12:00:00 PM','MM/DD/YYYY HH:MI:SS AM')
AND mecode = '00000278')
AND mecode = '00000278';
This is giving wrong result as 3. The status should come as 4 because its not more than 8 days so staus should not change and in this case on 25th march'09 the staus is 3(blacklist).
So the 8 days scenario is not working and it should work only when status is 2 or 3.
So if the output of the above select statement comes out as 2 or 3 then I need to write another Select statement which should tell me the status of the previous status and whether the difference between the two status is more or less than 8 days. And based on the difference we can find the correct status.
So I need to find a way to select the previous record than the one fetched for.
Regards,
Mahi
[Updated on: Wed, 15 July 2009 01:52] Report message to a moderator
|
|
|
Re: How to get values of previous record [message #413309 is a reply to message #413276] |
Wed, 15 July 2009 03:46   |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Anyone please look into this....I only want to know as how to get the preceding value of a column.
To get the value of next row, we an use LEAD function. What to do in case I need the one lower value of a column.
Please help,
Mahi
|
|
|
|
Re: How to get values of previous record [message #413329 is a reply to message #413320] |
Wed, 15 July 2009 04:32   |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi Jrowbottom,
If the table is created is created and rows inserted and you run the select query .....
The select query SELECT DISTINCT Nvl(status,0) mmestatus, createddate
FROM mems_mestatus_hist1
WHERE createddate = (SELECT Max(createddate)
FROM mems_mestatus_hist1
WHERE To_date(createddate) <= To_date('03/25/2009 12:00:00 PM','MM/DD/YYYY HH:MI:SS AM')
AND mecode = '00000278')
AND mecode = '00000278';
You get the value as :- 3 and 3/25/2009 12:00:00 PM
If you see the table then prior this recod in table, there is an entry done for createddate = 3/20/2009 12:00:00 PM
with status = 4
So as I said that if the status is 3 or 4, then this status gets effective after 8 days on createddate. So our record for 3/25/2009 is less than 8 days from its previous record value which is 03/20/2009.
So in this the status should be fetched as 4.
So I am trying to write a select statement where I can get the Status = 4 which is the previous record using :-
SELECT LAG (status, 1) OVER (ORDER BY createddate) AS status2
FROM mems_mestatus_hist
WHERE TO_DATE (createddate) <=
TO_DATE ('03/25/2009 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM')
AND mecode = '00000278'
I do not know how to get the only one value 4.
Please advice.
Regards,
Mahi
|
|
|
Re: How to get values of previous record [message #413342 is a reply to message #413329] |
Wed, 15 July 2009 05:57   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You have to understand - we know none of your business rules.
You keep mentioning an 8 day cut off, but none of your queries seem to reflect anything to do with this.
Your queries only look for the most recent record before a certain date.
If you want to get the previous record before that one, this shows one way of doing it:SELECT Nvl(status,0) mmestatus, createddate, row_number() over (order by createddate desc) rnum
FROM mems_mestatus_hist1
WHERE createddate <= (SELECT Max(createddate)
FROM mems_mestatus_hist1
WHERE To_date(createddate) <= To_date('03/25/2009 12:00:00 PM','MM/DD/YYYY HH:MI:SS AM')
AND mecode = '00000278')
AND mecode = '00000278');
|
|
|
Re: How to get values of previous record [message #413343 is a reply to message #413329] |
Wed, 15 July 2009 05:57   |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
In one of your reply you said
Quote: | So as I said that if the status is 3 or 4, then this status gets effective after 8 days on createddate
|
and before that you mentioned
Quote: | So the 8 days scenario is not working and it should work only when status is 2 or 3
|
So I am not sure which one is correct in above. However if I assume the condition applies for 2 or 3 then you can do something following
SQL> select * from (
2 SELECT Nvl(status,0) mmestatus,
3 max(createddate) over () mdate,
4 createddate
5 FROM mems_mestatus_hist1
6 WHERE case when status in (2,3)
7 then createddate+8
8 else createddate end <=
9 (
10 SELECT Max(createddate)
11 FROM mems_mestatus_hist1
12 WHERE
13 To_date(createddate) <=
14 To_date('03/25/2009 12:00:00 PM','MM/DD/YYYY HH:MI:SS AM')
15 AND mecode = '00000278'
16 )
17 and mecode = '00000278'
18 )
19 where mdate = createddate
20 /
MMESTATUS MDATE CREATEDDATE
---------- -------------------- --------------------
4 20-Mar-2009 12:00:00 20-Mar-2009 12:00:00
1 row selected.
SQL>
I have not tested it fully you may have to do that.
[Update] Minor typo corrected.
[Updated on: Wed, 15 July 2009 06:02] Report message to a moderator
|
|
|
Re: How to get values of previous record [message #413378 is a reply to message #413269] |
Wed, 15 July 2009 07:56   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
virmahi wrote on Wed, 15 July 2009 02:19 |
where to_date(createddate)<=TO_Date( '03/01/2001 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM') and mecode='00000278') and mecode='00000278';
|
TO_DATE-ing a DATE is incorrect. If it's a VARCHAR2, then you'll need a format mask.
|
|
|
|
|
Goto Forum:
Current Time: Sat Jul 12 14:25:17 CDT 2025
|