Select previous date 1

[jgurgen]

I have a view i have created that im trying to select into a coloumn the previous TransactionDate of a loan. How can i do this all in the same view.

EXAMPLE
LOAN TranDate PrevTranDate
0001 1/1/07 null
0001 1/2/07 1/1/07
0002 1/4/07 null
0001 1/5/07 1/2/07

SELECT  
CC.AutoID, 
H.LoanID,
H.HistoryCounter, 
H.SysDateTime,
IsNUll( CC_Orig.DateAdded,CC.DateAdded ) As DateAdded,
IsNULL(CC_Orig.EndDate, CC.EndDate) As EndDate, 
IsNULL( CC_Orig.CollectorCode, CC.CollectorCode) As CollectorCode,
IsNULL(CC_Orig.CollectorName,CC.CollectorName) As CollectorName,
H.TransactionCode,
H.TransactionAmt,
TransactionCode.Description AS TranCodeDesc,
H.TransactionDate, 
H.ReversalCode, 
H.ReversalFlag, 
Status.Legal,
S_ReversalCode.Description AS ReversalCodeDesc, 
S_ReversalFlag.Description AS ReversalFlagDesc,
H.MoneyType,
Company.Name1, 
S_MoneyType.Description AS MoneyTypeDesc,
Status.PrimStat


/*
CC.DateAdded As DateAdded1,
CC.EndDate As EndDate1, 
CC.CollectorCode As CollectorCode1,
CC.CollectorName As CollectorName1
*/

FROM Company,
CollectorCodeHistory  CC
INNER JOIN History H
ON ( CC.LoanID = H.LoanID 
     AND CC.DateAdded <= H.SysDateTime
     AND ISNULL(CC.EndDate, GetDate()) >= H.SysDateTime
     AND H.TransactionCode BETWEEN 100 and 340 AND TransactionCode <> 140 AND TransactionAmt <> 0  )
LEFT JOIN CorrectedHistory CH ON ( H.HistoryCounter = CH.HistoryCounterReversal AND H.LoanID = CH.LoanID )
LEFT JOIN CollectorCodeHistory  CC_Orig
ON ( CC_Orig.LoanID = CH.LoanID 
     AND CC_Orig.DateAdded <= CH.SysDateTime
     AND ISNULL(CC_Orig.EndDate, GetDate()) >= CH.SysDateTime )
INNER JOIN Status ON H.LoanID = Status.LoanID
INNER JOIN TransactionCode ON H.TransactionCode = TransactionCode.Code
INNER JOIN S_ReversalFlag ON H.ReversalFlag = S_ReversalFlag.Code
INNER JOIN S_ReversalCode ON H.ReversalCode = S_ReversalCode.Code
INNER JOIN S_MoneyType ON H.MoneyType = S_MoneyType.Code

Where H.MoneyType <> 0

 

[AlexCuse]

Here is an example that should help. You need to join the table to itself, to get the maximum transaction date (for each loan) that is less than the transaction date on the current row. Makes sense?

select a.loan, a.TranDate, Max(b.TranDate) as PrevTranDate
from LoanTbl a
left join LoanTbl b
on a.loan = b.loan
and a.TranDate > b.TranDate

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jgurgen]

that makes sense let me give that a try

 

[jgurgen]

ok i did it but does everything have to go in the group by?

 

[AlexCuse]

Yeah you would need to. Since the grouping is not doing much but making sure you get the max (prvious date) for each row, I don't think this would cause a problem though. Are you seeing strange results?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jgurgen]

nope worked perfectly
thanks

 

[AlexCuse]

Glad it worked for you

A good rule of thumb, when using aggregate functions (max, min, avg, sum, etc...) is that anything that is included in your select list (but no aggregate function is applied to) should also be included in your group by.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom