Resolution

To capture the previous work odometer read you must find the pervious work order of an asset. This script looks at each odometer read of an asset at a specific time then joins it to the next work order.

IF OBJECT_ID('tempdb..#wo') IS NOT NULL

DROP TABLE #wo

IF OBJECT_ID('tempdb..#assets') IS NOT NULL

DROP TABLE #assets

select distinct ah_asset_code , a_work_order_no , hd_create_date into #wo from wm_master

select distinct a_code , at_odom_read , at_date into #assets FROM wm_assets

select * , (select at_odom_read as prevread from (select * , ROW_NUMBER() OVER(PARTITION BY a_code ORDER BY at_date DESC) as rt FROM #assets where a_code=ah_asset_code and at_date <= hd_create_date ) as t where rt=1) as previousread from #wo order by 1

Browser not supported