Retrieving previous odometer read per work order

Client wants to add the previous odometer read field from the Work Order Management screen to a report

• All versions of Munis

• SSRS reports

• Crystal reports

• SaaS, Tyler-hosted clients

• OnPrem, Self-hosted clients

• Work Order Management

The view database column overlay shows previous read as derived.

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