CODEWITHSUNDEEP
sqlsql-server
glenho123
glenho123

Reputation: 577

Get Status value based on timesheet date

I have an Assets table that has an audit log of when a particular status of that Asset changes Status... so look's something similar to this

AssetId     CapexStatus     Date
-------     -----------     -----
AM706       1               2017-02-03
AM706       0               2017-02-07
AM706       1               2017-02-10

I then have a timesheet table which has the AssetID and a transaction date on it. I basically want to pull the Capex Status out of the AssetLog table based on the AssetId and the current Capex Status at the time of the transaction date. eg. If the transaction date is 8th Feb then the Capex Status should be "0".

Timesheet table

TimesheetId     AssetId     TimesheetDate
-----------     -------     -------------
1               AM706       2017-02-01
2               AM706       2017-02-08
3               AM706       2017-02-12

Upvotes: 1

Views: 53

Answers (2)

Horaciux
Horaciux

Reputation: 6477

create view vwMaxCapex
as
select top 1 capexStatus, date, AssetId from AssetsLog
order by date asc
go


select a.AssetId, a.timesheetDate, 
  (select capexstatus 
    from vwMaxCapex 
     where date<=a.timesheetDate and assetId=a.AssetId) capex
from timetable a

Upvotes: 0

Leon Bambrick
Leon Bambrick

Reputation: 26306

I think something like this might do it:

select 
  t.*, 
  a.CapexStatus
from
  TimeSheet t
outer apply (Select top 1 * from AssetLog al
   where 
        al.AssetID = t.AssetID 
   and  al.Date < t.TimesheetDate
   order by al.Date desc) a

Upvotes: 1

Related Questions