How to return the previous record?

Select lid,

datelastmodified,

datecompleted,

statusid,

statusrecordid

from loan_status

where statusrecordid -1 and statusid = 11

Do you guys have any idea, if stating -1 gives you the previous record?

No, won't work, but it would help if you explained what you were trying to do.

Well, now I think I would need a cursor, but I have a user is pulling the following information from a table:

Select lnid, statusid, statusrecordid, date from loan_status where statusid = 11 and year(datecompleted)= 1800 or
statusid = 12 and year(datecompleted)= 1800

He wants to add another condition to pull or display the previous record, for instance if the statusrecordid is 10, he wants it to display statusrecordid 9 and its associated information (data).

However, I really don't want to use cursors.

Well I am not sure exactly what you are trying to do, but maybe if you have a variable store the ID number of the "current" record.

If it is always true that the previous record is one less than the current you could then execute a query where the id number is current - 1.

I have not tried anything like this, but it might work.

The only issue you may run into if something like this.

you have records 1,2,3,4,5 in the database.
Someone deletes record 3.

Someone views record 4, then wants to view the previous record, but 3 has been deleted.
Now the issue is, there is no way to determine the previous record, because record 3 doesn't exist anymore.

I see what you are saying regarding the issue. I am still working on it, when I get a chance I will post what I have then if you have any ideas, I would love to get your opinion(s).

You could do something like this:

The TOP 1 will ensure that only one record is selected and the "AND statusrecordid < 11 ORDER BY statusrecordid DESC" part will ensure that only the previous record is selected. The nice part about this is if someone has deleted the record where statusrecordid = 10, this will pick the one previous to that (i.e. 9).

Thanks that was helpful and started more ideas and error-checking. Here's the thing,

I'm finding that knowing the statusrecordid is not important because that is dynamic. Meaning, I don't hardcode the statusrecordid in the script to 11 or 12 because it can be 6 or 7, etc. Another thing, I think I will have to do a self join because I need to compare lnids.

So ideally, where statusid is 11 or 12, compare lnids and check the statusrecordid of the lnids.
For instance, and I know this sounds confusing but please bare with me.

Statusid is the final status 11 or 12 of a record. What I want to know what was the status (statusid) of the record before going to 11 or 12. I hope this makes sense.

lnid datelastmodified datecompleted statusid statusrecordid Status description
000286 2004-06-28 15:47:17.000 2004-06-28 15:51:20.000 12 6 Closed
000286 2004-08-10 09:59:51.000 1800-01-01 00:00:00.000 12 8 Closed

Select /*top 1*/ lnd, datelastmodified,datecompleted,statusid, statusrecordid
From ln_status
where statusid = 11 and year(datecompleted)= 1800 or
statusid = 12 and year(datecompleted)= 1800
/*** I would need to modify this to address the change from record to record***/
and statusrecordid < 11 or statusrecordid < 12
ORDER BY statusrecordid DESC


Does this make better sense? Any insights?

Don't know what language you are using, but just about every language I've used (ASP, VB, Delphi, Python etc.) allow you to create parameters with your SQL statement. Briefly, you could do something like this (pseudocode):

This is what I have so far: (tsql)

Select ls1.loanrecordid, ls1.datelastmodified,ls1.datecompleted, ls1.statusid,
ls1.statusrecordid, setups_loanstatus.statusdescription
from loan_status ls1 INNER Join
(select loanrecordid, statusrecordid from loan_status) ls2
ON ls1.loanrecordid = ls2.loanrecordid and ls1.statusrecordid = ls2.statusrecordid
join setups_loanstatus ON
ls1.statusid = setups_loanstatus.statusid
join loan_main on ls1.loanrecordid = loan_main.loanrecordid
where
ls1.statusid = 11 or ls1.statusid = 12 and ls1.loanrecordid = ls2.loanrecordid
Order by ls1.loanrecordid

This gives me:

loanrecordid datelastmodified datecompleted statusid statusrecordid Status description

1000161 2004-04-20 00:00:00.000 2004-04-20 00:00:00.000 12 34 Withdrawn
1000161 2005-01-12 10:55:10.000 1800-01-01 00:00:00.000 11 36 Declined

What I want is the above and the previous statusid and statusrecordid for loanrecordid 1000161. Wouldn't it be statusrecordid = statusrecordid - 1 or something like that to give me the previous record(s) for 1000161? Or would I need to create a function and cursor?

Do you have an example?

I'm back working on this and so far I haven't been able to illustrate this suggestion.

Set nocount on
GO
Declare @storecurrent int
set @storecurrent = ls1.statusrecordid

Select ls1.loanrecordid, ls1.datelastmodified,ls1.datecompleted, ls1.statusid,
ls1.statusrecordid, setups_loanstatus.statusdescription
from loan_status ls1 INNER Join
(select loanrecordid, statusrecordid from loan_status) ls2
ON ls1.loanrecordid = ls2.loanrecordid and ls1.statusrecordid = ls2.statusrecordid

join setups_loanstatus ON
ls1.statusid = setups_loanstatus.statusid
join loan_main on ls1.loanrecordid = loan_main.loanrecordid

where
ls1.statusid = 11 or ls1.statusid = 12 and ls1.loanrecordid = ls2.loanrecordid and ls1.statusrecordid = @storecurrent - 1
Order by ls1.loanrecordid

Syntactially, how can I set @storecurrent to the select stmt pulling the statusrecordid? Since I am setting the variable to ls1.statusrecordid, it does not recognize ls1 because I haven't establish my join yet. Any ideas?

I made some additional changes but no luck. I think I am grasping at straws. Would you suggest creating a temp table to store information?


Declare @storecurrent int, @counter int
set @storecurrent = (select statusrecordid from loan_status)
select @counter = 1

While @counter <= @@rowcount

Begin
Select ls1.loanrecordid, ls1.datelastmodified,ls1.datecompleted, ls1.statusid,
ls1.statusrecordid, setups_loanstatus.statusdescription
from loan_status ls1 INNER Join
(select loanrecordid, statusrecordid from loan_status) ls2
ON ls1.loanrecordid = ls2.loanrecordid and ls1.statusrecordid = ls2.statusrecordid
join setups_loanstatus ON ls1.statusid = setups_loanstatus.statusid
join loan_main on ls1.loanrecordid = loan_main.loanrecordid
where ls1.statusid = 11 or ls1.statusid = 12 and ls1.loanrecordid = ls2.loanrecordid
set @counter = @counter - 1
end
--Order by ls1.loanrecordid
--@storecurrent = @storecurrent - 1