SQL View to pull last activity

[MDA]

Hi all,

I am stuck and need some new ideas.

I have a SQL table that is updated/appended nightly with the status of contracts. The records are appended on the date that they are changed.

Example: Contract | Status | Date
100 Pending 1/1/2004
200 Closed 12/20/2003
100 Closed 1/10/2004
300 Out 1/5/2004

I need to create a view that gives me a transaction status as of a certain date. So if the as of date is 1/5/2004 than the status of Contract 100 should be 'Pending.' The problem I have is that if I report on say 1/3/2004, there are no transactions for this date, so nothing is returned. The last activity is possible with Access and MDX(OLAP) but I can't seem to figure out how to do this with a SQL view! Is there any way to look back at transactions based on a period (Date)?

Please let me know if you have any ideas

Thanks in advance,
MDA

 

[filmr]

Not sure on a view (how to pass the view the date in question) but this procedure should do what you want.

Create Procedure GetStatus
@StatusDate smalldatetime
As
Select b.Contract, b.Status
From TestStatus b Join
(Select Contract, max(Date) as MaxDate
from TestStatus
Where Date <= @StatusDate
Group by Contract) a
On b.Contract = a.Contract and b.Date = a.MaxDate
Return

Hope that helps.

Rick