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:

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.

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.

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.

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.

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

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: