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 Go to next message
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 #413272 is a reply to message #413269] Wed, 15 July 2009 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: How to get values of previous record [message #413276 is a reply to message #413272] Wed, 15 July 2009 01:37 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #413320 is a reply to message #413309] Wed, 15 July 2009 04:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Use LAG instead of LEAD

Other than that, I'm afraid I can't help as despite your query and test data, I've got no idea what you're trying to achieve.
Re: How to get values of previous record [message #413329 is a reply to message #413320] Wed, 15 July 2009 04:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4641
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.
Re: How to get values of previous record [message #413394 is a reply to message #413378] Wed, 15 July 2009 08:40 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
@joy_division

Yes good point. I too missed this while posting my reply. It is not needed as createddate is a date field in the table.
Re: How to get values of previous record [message #413705 is a reply to message #413394] Fri, 17 July 2009 03:27 Go to previous message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks to all of you,
I have solved the problem using JrowBottom's query.

Regards,
Mahi
Previous Topic: How to pass the result in refcursor to a table without using LOOP
Next Topic: materialized views
Goto Forum:
  


Current Time: Wed Dec 07 03:11:06 CST 2016

Total time taken to generate the page: 0.05203 seconds