{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Live Metric Library","ONRC Change Register","AM Change Register"],"not_rendered":{"charts":0,"images":3,"macros":1,"external_links":0,"defined_names":7,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Live Metric Library","index":0,"n_rows":73,"n_cols":19,"rows_kept":73,"cut":false,"hidden":false,"merged":[]}
[null,null,null,"Metric Library"]
[null,null,null,"Data Quality Project"]
[]
["DQ Report","Category","Sub-Category","Metric Reference","Status","Metric Description","RAMM Tables Used","Specific Exceptions","Primary Dimension","Asset Management Importance","Grade 3 (Major Issues)","Grade 2 (Minor Issues)","Grade 1 (Expected Standard)","Metric Purpose","Consequence of Poor Quality Data","Potential reason(s) for not being at the expected standard unique to this metric","Specific comments on the metric","Data Quality Metric RAMM SQL","Record by Record RAMM SQL"]
["ONRC","Network","Carriageway","Ca1a","Active","Rural number of lanes matches carriageway width\nThe percentage of the Rural sealed network length, based on carriageways, with an alignment between the recorded carriageway width and number of lanes. Alignments are as follows:\n- Number of lanes = 1 and width <6m, \n- Number of lanes = 2 and width >4m or <17m, \n- Number of lanes > 2 and width >9m","carr_way\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'","Accuracy","Low","<80%",">=80%",">=95%","Confirm that the number of lanes recorded for each rural carriageway relates to the recorded carriageway width","ONRC: Incorrect reporting of achieved lengths by lane-km in the Cost Efficiency Performance Measures\nAM: Reduced understanding of traffic demand and loading on the network, and subsequently levels of service provided, network resilience, pavement and surfacing renewals forward works programming, etc","Lack of process for defining the number of lanes per carriageway section","The width ranges were chosen by the project team to allow for the varying nature of the networks across NZ.  They are not necessary in line with any design standards or best practice","--------------------------------------------------------------------------------\n--Rural number of lanes matches carriageway width:\n--Percentage of Rural sealed network length, based on carriageways, with alignment between the recorded carriageway \n--width and no. of lanes (No. lanes=1 & width<6m, No. lanes=2 & width>4m or <17m\n--No lanes>2 & width>9m)\n\n--Ref - Ca1a\n--------------------------------------------------------------------------------\n\n{No. lanes = 1 & width <6m}\nselect sum(carrway_end_m - carrway_start_m) lane1\ninto #temp_lane1_r\nfrom carr_way c \nwhere cway_width < 6    \nand lanes = 1   \nand owner_type = 'L'    \nand pavement_type in ('T', 'S', 'C') \nand urban_rural = 'R'\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L';     \n\n{No. lanes = 2 & width >4m or <17m}   \nselect sum(carrway_end_m - carrway_start_m) lane2\ninto #temp_lane2_r\nfrom carr_way c \nwhere (cway_width > 4 and cway_width < 17)\nand lanes = 2   \nand owner_type = 'L'    \nand pavement_type in ('T', 'S', 'C') \nand urban_rural = 'R'\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L';       \n\n{No lanes >2 & width > 9m}\nselect sum(carrway_end_m - carrway_start_m) lanegr2\ninto #temp_lanegr2_r\nfrom carr_way c \nwhere cway_width > 9\nand lanes > 2\nand owner_type = 'L'    \nand pavement_type in ('T', 'S', 'C') \nand urban_rural = 'R'\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L';       \n\nupdate #temp_lane1_r\nset lane1 = 0 \nwhere lane1 is null;\n\nupdate #temp_lane2_r\nset lane2 = 0 \nwhere lane2 is null;\n\nupdate #temp_lanegr2_r\nset lanegr2 = 0 \nwhere lanegr2 is null;\n\n{Select total rural carriageway length}\nselect sum(carrway_end_m - carrway_start_m) as tot_rural_lgth\ninto #temp_tot_rural_lgth\nfrom carr_way c\n    where c.pavement_type in ('T', 'S', 'C')\n    and urban_rural = 'R'\n    and c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n        and owner_type = 'L';\n\n{Express as % sealed network length with alignment between carriageway width and no. of lanes} \nselect (cast(lane1 + lane2 + lanegr2 as decimal))/(cast(tot_rural_lgth as decimal)) *100 as Ca1a_rural_align\nfrom #temp_lane1_r, #temp_lane2_r, #temp_lanegr2_r, #temp_tot_rural_lgth;   \n\ndrop table #temp_lane1_r, #temp_lane2_r, #temp_lanegr2_r, #temp_tot_rural_lgth;","--------------------------------------------------------------------------------\n--Rural number of lanes matches width:\n--Percentage of Rural sealed network length with alignment between carriageway \n--width and no. of lanes (No. lanes=1 & width<6m, No. lanes=2 & width>4m or <17m\n--No lanes>2 & width>9m)\n\n--Ref - Ca1a\n--------------------------------------------------------------------------------\n\n\nselect c.road_id, carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m, c.cway_width, c.lanes, c.urban_rural\nfrom carr_way\tc\t\nwhere (cway_width >= 6 or cway_width is null)\nand lanes = 1\t\t\nand owner_type = 'L'\t\t\nand pavement_type in ('T', 'S', 'C') \nand urban_rural = 'R'\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L'\t\t\n\nunion\n\t\nselect c.road_id, carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m, c.cway_width, c.lanes, c.urban_rural\nfrom carr_way\tc\t\nwhere (cway_width <= 4 or cway_width >= 17 or cway_width is null)\nand lanes = 2\t\t\nand owner_type = 'L'\t\t\nand pavement_type in ('T', 'S', 'C') \nand urban_rural = 'R'\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L'\t\n\nunion\n\nselect c.road_id, carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m, c.cway_width, c.lanes, c.urban_rural\nfrom carr_way\tc\t\nwhere (cway_width <= 9 or cway_width is null)\nand lanes > 2\nand owner_type = 'L'\t\t\nand pavement_type in ('T', 'S', 'C') \nand urban_rural = 'R'\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L';\t"]
["ONRC","Network","Carriageway","Ca1b","Active","Urban number of lanes matches carriageway width\nThe percentage of the Urban sealed network length, based on carriageways, with an alignment between the recorded carriageway width and number of lanes. Alignments are as follows:\n- Number of lanes = 1 and width <6m, \n- Number of lanes = 2 and width >4m or <17m, \n- Number of lanes > 2 and width >9m","carr_way\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'","Accuracy","Low","<80%",">=80%",">=95%","Confirm that the number of lanes recorded for each rural carriageway relates to the recorded carriageway width","ONRC: Incorrect reporting of achieved lengths by lane-km in the Cost Efficiency Performance Measures\nAM: Reduced understanding of traffic demand and loading on the network, and subsequently levels of service provided, network resilience, pavement and surfacing renewals forward works programming, etc","Lack of process for defining the number of lanes per carriageway section","The width ranges were chosen by the project team to allow for the varying nature of the networks across NZ.  They are not necessary in line with any design standards or best practice","--------------------------------------------------------------------------------\n--Urban number of lanes matches carriageway width:\n--Percentage of Urban sealed network length, based on carriageways, with alignment between the recorded carriageway \n--width and no. of lanes (No. lanes=1 & width<6m, No. lanes=2 & width>4m or <17m,\n--No lanes>2 & width>9m)\n\n--Ref - Ca1b\n--------------------------------------------------------------------------------\n\n{No. lanes = 1 & width <6m}\nselect sum(carrway_end_m - carrway_start_m) lane1\ninto #temp_lane1_u\nfrom carr_way c \nwhere cway_width < 6    \nand lanes = 1   \nand owner_type = 'L'    \nand pavement_type in ('T', 'S', 'C') \nand urban_rural = 'U'\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L';     \n\n{No. lanes = 2 & width >4m or <17m}   \nselect sum(carrway_end_m - carrway_start_m) lane2\ninto #temp_lane2_u\nfrom carr_way c \nwhere (cway_width > 4 and cway_width < 17)\nand lanes = 2   \nand owner_type = 'L'    \nand pavement_type in ('T', 'S', 'C') \nand urban_rural = 'U'\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L';       \n\n{No lanes >2 & width < 9m}\nselect sum(carrway_end_m - carrway_start_m) lanegr2\ninto #temp_lanegr2_u\nfrom carr_way c \nwhere cway_width > 9\nand lanes > 2   \nand owner_type = 'L'    \nand pavement_type in ('T', 'S', 'C')\nand urban_rural = 'U'\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L';       \n\nupdate #temp_lane1_u\nset lane1 = 0 \nwhere lane1 is null;\n\nupdate #temp_lane2_u\nset lane2 = 0 \nwhere lane2 is null;\n\nupdate #temp_lanegr2_u\nset lanegr2 = 0 \nwhere lanegr2 is null;\n\n\n{Select total urban carriageway length}\nselect sum(carrway_end_m - carrway_start_m) as tot_urban_lgth\ninto #temp_tot_urban_lgth\nfrom carr_way c\n    where c.pavement_type in ('T', 'S', 'C')\n    and urban_rural = 'U'\n    and c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n        and owner_type = 'L';\n\n\n{Express as % sealed network length with alignment between carriageway width and no. of lanes} \nselect (cast(lane1 + lane2 + lanegr2 as decimal))/(cast(tot_urban_lgth as decimal)) *100 as Ca1b_urban_align\nfrom #temp_lane1_u, #temp_lane2_u, #temp_lanegr2_u, #temp_tot_urban_lgth;  \n\ndrop table #temp_lane1_u, #temp_lane2_u, #temp_lanegr2_u, #temp_tot_urban_lgth;","--------------------------------------------------------------------------------\n--Urban number of lanes matches width:\n--Percentage of Urban sealed network length with alignment between carriageway \n--width and no. of lanes (No. lanes=1 & width<6m, No. lanes=2 & width>4m or <17m,\n--No lanes>2 & width>9m)\n\n--Ref - Ca1b\n--------------------------------------------------------------------------------\n\n\nselect c.road_id, carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m, c.cway_width, c.lanes, c.urban_rural\nfrom carr_way\tc\t\nwhere (cway_width >= 6 or cway_width is null)\nand lanes = 1\t\t\nand owner_type = 'L'\t\t\nand pavement_type in ('T', 'S', 'C') \nand urban_rural = 'U'\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L'\n  \nunion\t\n\nselect c.road_id, carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m, c.cway_width, c.lanes, c.urban_rural\nfrom carr_way\tc\t\nwhere (cway_width <= 4 or cway_width >= 17 or cway_width is null)\nand lanes = 2\t\t\nand owner_type = 'L'\t\t\nand pavement_type in ('T', 'S', 'C') \nand urban_rural = 'U'\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L'\n  \nunion\n\nselect c.road_id, carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m, c.cway_width, c.lanes, c.urban_rural\nfrom carr_way\tc\t\nwhere (cway_width <= 9 or cway_width is null)\nand lanes > 2\nand owner_type = 'L'\t\t\nand pavement_type in ('T', 'S', 'C') \nand urban_rural = 'U'\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L';\t"]
["ONRC","Network","Carriageway","Ca2","Active","ONRC categories are assigned\nThe proportion of the number of carriageway section records in the carriageway table with an assigned ONRC category ","carr_way\nonrc_cway_view\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'","Completeness","High","<95%",">=95%",">=98%","Confirm that an ONRC category is recorded for all 'road' carriageway sections","ONRC: Incorrect reporting of all Performance Measures results as parts of network will not be included\nAM: Parts of road network with no category are potentially excluded, or incorrectly defined, in planning, executing and reporting of maintenance and renewal activity","Lack of process or resources for the ongoing maintenance of this dataset\nLack of clarity or understanding that RCAs are expected to maintain their ONRC","This metrics only interrogates carriageways where the road is recorded with a road_type of \"L\" in the Roadnames table.  Car parks recorded with an road_type of \"L\" are expected to 'fail' the test.  An allowance has been made for this in the grading ranges.\nPaths, walkways and other parts of the network with a different road_type are excluded","--------------------------------------------------------------------------------  \n--ONRC categories are assigned:\n--Proportion of carriageway section records with an assigned ONRC category \n--(where road type = \"L\" and owner type \"L\")\n\n--Ref - Ca2\n--------------------------------------------------------------------------------  \n\n{Select total sealed carriageway sections}\nselect count(*) as cway_count \ninto #temp_carway_no        \nfrom carr_way c\nwhere pavement_type in ('T', 'S', 'C')\nand c.road_id in (select road_id from roadnames\n    where road_type = 'L')\nand owner_type = 'L'; \n\n\n{Select carriageway sections with ONRC} \nselect count(*) as onrc_count\ninto #temp_onrc_no  \nfrom carr_way c\nleft join onrc_cway_view o\non c.carr_way_no = o.carr_way_no\nwhere pavement_type in ('T', 'S', 'C') \nand o.category_id is not null\nand c.road_id in (select road_id from roadnames\n    where road_type = 'L')\nand owner_type = 'L'; \n\n{Express as % with ONRC}  \nselect (cast(onrc_count as decimal))/(cast(cway_count as decimal)) *100 as Ca2_onrc\nfrom #temp_carway_no, #temp_onrc_no;\n\ndrop table #temp_carway_no, #temp_onrc_no;","--------------------------------------------------------------------------------\t\n--ONRC categories are assigned:\n--Proportion of carriageway section records with an assigned ONRC category \n--(where road type = \"L\" and owner type \"L\")\n\n--Ref - Ca2\n--------------------------------------------------------------------------------\t\n\n\nselect c.road_id, c.carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m, o.category_id\nfrom carr_way c\nleft join onrc_cway_view o\non c.carr_way_no = o.carr_way_no\nwhere pavement_type in ('T', 'S', 'C') \nand o.category_id is null\nand c.road_id in (select road_id from roadnames\n    where road_type = 'L')\nand owner_type = 'L';\t"]
["ONRC","Network","Carriageway","Ca3a","Active","Rural carriageways are generally not short\nThe proportion of the number of Rural sealed carriageway records with a length greater than 50m (i.e.. not short)","carr_way\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'","Accuracy","Moderate","<70%",">=70%",">=90%","Confirm that rural carriageway sectioning length is appropriate","ONRC: Short carriageway sections has the potential to distort the reported results for the Amenity Performance Measures\nAM: Short carriageway sections can be difficult, and time consuming to manage and maintain","Lack of, or non-implementation of Network Definition manual\nLack of understanding of the consequences associated with many short sections",null,"--------------------------------------------------------------------------------  \n--Rural carriageways are generally not short:\n--Proportion of Rural sealed carriageway records greater than 50m in length \n--(ie. not short)\n\n--Ref - Ca3a\n--------------------------------------------------------------------------------  \n\n{Select total sealed rural carriageway sections}\nselect count(*) as cway_rur_count \ninto #temp_carway_no_rur        \nfrom carr_way c\nwhere pavement_type in ('T', 'S', 'C')\nand urban_rural = 'R'\nand c.road_id in (select road_id from roadnames\n    where road_type = 'L')\nand owner_type = 'L';\n\n{Select rural carriageway sections with length >= 50m}\nselect count(*) rural_50\ninto #temp_rural_50\nfrom carr_way c   \nwhere owner_type = 'L'    \nand pavement_type in ('T', 'S', 'C') \nand urban_rural = 'R'\nand (carrway_end_m - carrway_start_m) >= 50\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L'; \n\n\n{Express as % rural sealed network length with length >= 50m} \nselect (cast(rural_50 as decimal))/(cast(cway_rur_count as decimal)) *100 as Ca3a_rural_50\nfrom #temp_carway_no_rur, #temp_rural_50;\n\ndrop table #temp_carway_no_rur, #temp_rural_50;","--------------------------------------------------------------------------------\t\n--Rural carriageways are generally not short:\n--Proportion of Rural sealed carriageway records greater than 50m in length \n--(ie. not short)\n\n--Ref - Ca3a\n--------------------------------------------------------------------------------\t\n\n\nselect c.road_id, c.carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m, urban_rural\nfrom carr_way\tc\t\t\nwhere owner_type = 'L'\t\t\nand pavement_type in ('T', 'S', 'C') \nand urban_rural = 'R'\nand (carrway_end_m - carrway_start_m) < 50\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L';\t"]
["ONRC","Network","Carriageway","Ca3b","Active","Urban carriageways are generally not short\nThe proportion of the number of Urban sealed carriageway records with a length greater than 20m (i.e.. not short)","carr_way\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'","Accuracy","Moderate","<70%",">=70%",">=90%","Confirm that rural carriageway sectioning length is appropriate","ONRC: Short carriageway sections has the potential to distort the reported results of the Amenity Performance Measures\nAM: Short carriageway sections can be difficult, and time consuming to manage and maintain","Lack of, or non-implementation of Network Definition manual\nLack of understanding of the consequences associated with many short sections",null,"-------------------------------------------------------------------------------  \n--Urban carriageways are generally not short:\n--Proportion of Urban sealed carriageway records greater than 20m in length \n--(ie not short)\n\n--Ref - Ca3b\n--------------------------------------------------------------------------------  \n\n{Select total sealed urban carriageway sections}\nselect count(*) as cway_urb_count \ninto #temp_carway_no_urb        \nfrom carr_way c\nwhere pavement_type in ('T', 'S', 'C')\nand urban_rural = 'U'\nand c.road_id in (select road_id from roadnames\n    where road_type = 'L')\nand owner_type = 'L';\n\n{Select urban carriageway sections with length >= 20}\nselect count(*) urban_20\ninto #temp_urban_20\nfrom carr_way c   \nwhere owner_type = 'L'    \nand pavement_type in ('T', 'S', 'C') \nand urban_rural = 'U'\nand (carrway_end_m - carrway_start_m) >= 20\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L';   \n\n{Express as % urban sealed network length with length >= 20m}\nselect (cast(urban_20 as decimal))/(cast(cway_urb_count as decimal)) *100 as Ca3b_urban_20\nfrom #temp_carway_no_urb, #temp_urban_20;\n\ndrop table #temp_carway_no_urb, #temp_urban_20;","--------------------------------------------------------------------------------\t\n--Urban carriageways are generally not short:\n--Proportion of Urban sealed carriageway records greater than 20m in length \n--(ie not short)\n\n--Ref - Ca3b\n--------------------------------------------------------------------------------\t\n\n\n\nselect c.road_id, c.carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m, urban_rural\n\nfrom carr_way\tc\t\t\nwhere\towner_type = 'L'\t\t\nand pavement_type in ('T', 'S', 'C') \nand urban_rural = 'U'\nand (carrway_end_m - carrway_start_m) < 20\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L';\t"]
["ONRC","Network","Carriageway","Ca4","Active","Sealed/unsealed network correctly defined\nThe percentage of the network length, based on carriageways, with a sealed pavement type and a sealed material surface record, or an unsealed pavement type with either no surface record or an unsealed material surface record.","carr_way\nsurface_structure\nsurface_material","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'","Accuracy","High","<80%",">=80%",">=95%","Confirm that the sealed and unsealed networks are correctly defined.","ONRC: Incorrect reporting of Cost Efficiency Performance Measures as a result of incorrect sealed network length\nAM: Impacts on setting levels of service, network analysis, renewal and maintenance programme development, condition surveys and achievement and other types of reporting","Poor network definition through carriageway sectioning\nPoor management of carriageway sections following seal extension works\nIncorrect recording of sealed surface inventory data","Metric allows for two scenarios for the unsealed network.  Either there is no record in the carriageway surface table, or there is a record with a material type of \"METAL\".","--------------------------------------------------------------------------------  \n--Sealed/unsealed network correctly defined:\n--Percentage of sealed network length with a surface record, or unsealed network\n--with no surface record. Excludes pavement type of bridge.\n\n--Ref - Ca4\n--------------------------------------------------------------------------------  \n\n{Select total carriageway length}\nselect sum(carrway_end_m - carrway_start_m) as cway_total\ninto #temp_cway_total\nfrom carr_way c\n    where c.pavement_type not in ('B')\n    and c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L';\n\n\n{Select sealed length with surface record}\nselect sum(carr_way.carrway_end_m - carr_way.carrway_start_m) as surf_sealed\ninto #temp_surf_sealed  \nfrom carr_way\nwhere carr_way_no in (select distinct carr_way_no\n                      from surface_structure\n                      join carr_way \n                      on (carr_way.road_id = surface_structure.road_id) \n                      left join surf_material \n                      on surface_structure.surf_material = surf_material.surf_material\n                      where surf_sectioning = 'C' \n                          and surf_structure_set = 'T' \n                          and major_surface = 'Y'   \n                          and surf_width > 3  \n                          and (start_m >= carr_way.carrway_start_m        \n                          and start_m < carr_way.carrway_end_m)       \n                          and carr_way.pavement_type in ('T', 'S', 'C')\n                          and carr_way.road_id in (select road_id from roadnames\n                              where road_type = 'L')\n                          and owner_type = 'L'\n                          and surf_material.surf_category <> 'METAL');\n\n\n{Select unsealed length with no surface record}\nselect sum(carr_way.carrway_end_m - carr_way.carrway_start_m) as no_surf_unsealed\ninto #temp_no_surf_unsealed \nfrom carr_way\nwhere carr_way_no in (select distinct carr_way_no\n                      from carr_way\nleft join surface_structure on (carr_way.road_id = surface_structure.road_id  \n    and surf_sectioning = 'C' \n    and surf_structure_set = 'T' \n    and major_surface = 'Y'   \n    and surf_width > 3  \n    and (start_m >= carr_way.carrway_start_m        \n    and start_m < carr_way.carrway_end_m))\nleft join surf_material \non surface_structure.surf_material = surf_material.surf_material      \nwhere carr_way.pavement_type in ('U')\nand carr_way.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand owner_type = 'L'\nand (surf_structure_id is null or surf_material.surf_category = 'METAL'));\n\n\n\nupdate #temp_no_surf_unsealed \nset no_surf_unsealed = 0  \nwhere no_surf_unsealed is null;\n\n\n{Express as % alignment between total carriageway and surface length}\nselect ((cast(surf_sealed + no_surf_unsealed   as decimal))/(cast(cway_total as decimal))) * 100 as Ca4_correctly_defined\nfrom #temp_surf_sealed, #temp_no_surf_unsealed  , #temp_cway_total;\n\ndrop table #temp_surf_sealed, #temp_no_surf_unsealed, #temp_cway_total;","--------------------------------------------------------------------------------\t\n--Sealed/unsealed network correctly defined:\n--Percentage of sealed network length with a surface record, or unsealed network\n--with no surface record. Excludes pavement type of bridge.\n\n--Ref - Ca4\n--------------------------------------------------------------------------------\t\n\n\nselect carr_way.road_id, carr_way.carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m\nfrom carr_way\nwhere carr_way_no in (select distinct carr_way_no\n                      from surface_structure\n                      join carr_way \n                      on (carr_way.road_id = surface_structure.road_id)\t\n                      left join surf_material \n                      on surface_structure.surf_material = surf_material.surf_material\n                      where surf_sectioning = 'C' \n                          and\tsurf_structure_set = 'T' \n                          and surf_width > 3\t\t\t\t\n                          and (start_m >= carr_way.carrway_start_m \t\t\t\t\n                          and start_m < carr_way.carrway_end_m)\t\t\t\t\n                          and carr_way.pavement_type in ('T', 'S', 'C')\n                          and carr_way.road_id in (select road_id from roadnames\n                              where road_type = 'L')\n                          and owner_type = 'L'\n                          and (surf_structure_id is null or surf_material.surf_category = 'METAL'));\n\n\nselect carr_way.road_id, carr_way_no, carr_way.carrway_end_m, carr_way.carrway_start_m, carr_way.carrway_end_m - carr_way.carrway_start_m as length_m\nfrom carr_way\nleft join surface_structure on (carr_way.road_id = surface_structure.road_id\t\n    and surf_sectioning = 'C' \n    and\tsurf_structure_set = 'T' \n    and surf_width > 3\t\t\t\t\n    and (start_m >= carr_way.carrway_start_m \t\t\t\t\n    and start_m < carr_way.carrway_end_m))\nleft join surf_material \non surface_structure.surf_material = surf_material.surf_material\t\t\t\nwhere carr_way.pavement_type in ('U')\nand carr_way.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand owner_type = 'L'\nand (surf_structure_id is not null and surf_material.surf_category <> 'METAL'); "]
["ONRC","Network","Treatment Length","TL1a","Active","Treatment Lengths are generally not short\nThe proportion of the number of sealed treatment length records that are not very short.  Very short is considered to be:\n- <20m Urban\n- <100m Rural","carr_way\ntreatment_length\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'\nExcludes disabled treatment lengths","Accuracy","High","<75%",">=75%",">=90%","Confirm that treatment length sectioning length is appropriate","ONRC: Very short treatment lengths have the potential to impact the results of the Amenity Performance Measures\nAM: Short lengths can be difficult/impractical to manage and maintain","Lack of process, understanding or resources for the ongoing maintenance of this dataset\nLack of understanding on how to manage treatment length sectioning within the asset management system\nLack of understanding of the consequence of having a large number of short sections","The limit has been set around practical lengths for managing and maintaining the pavement and surfacing assets.  Urban allows for intersections and cul-de-sac heads, etc.  \nIt is expected that some treatment lengths will be legitimately short.  The has been allowed for in the grade ranges.","--------------------------------------------------------------------------------\n--Treatment Lengths are generally not short:\n--Proportion of sealed Treatment Length records (excludes disabled TLs) that \n--are not very short (<20m Urban and 100m Rural)\n\n--Ref - TL1a\n--------------------------------------------------------------------------------    \n\n{Select count of total treatment length sections}\nselect count(*) as tot_tls  \ninto #temp_tot_tls\nfrom treatment_length\njoin carr_way on (carr_way.road_id = treatment_length.road_id)\nwhere(tl_start_m >= carr_way.carrway_start_m  \n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.tl_disabled = 'N'\n      and carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n          where road_type = 'L')\n      and owner_type = 'L'; \n          \n{Select count of treatment length sections that are not short}\nselect count(*) as not_short_tls \ninto #temp_short_tls\nfrom treatment_length\njoin carr_way on (carr_way.road_id = treatment_length.road_id)\nwhere(tl_start_m >= carr_way.carrway_start_m  \n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.tl_disabled = 'N'\n      and carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n          where road_type = 'L')\n          and owner_type = 'L'\n      and ((treatment_length.urban_rural = 'R'  \n      and tl_length_m >= 100) or (treatment_length.urban_rural = 'U'  \n      and tl_length_m >= 20));  \n\n      \n{Total Proportion that are not short or very long TL} \n  \nupdate #temp_short_tls\nset not_short_tls = 0 \nwhere not_short_tls is null;  \n\nselect (cast(not_short_tls as decimal)) / (cast(tot_tls as decimal)) *100 as TL1a_not_short\nfrom #temp_short_tls, #temp_tot_tls;  \n\ndrop table #temp_short_tls, #temp_tot_tls; ","--------------------------------------------------------------------------------\n--Treatment Lengths are generally not short:\n--Proportion of sealed Treatment Length records (excludes disabled TLs) that \n--are not very short (<20m Urban and 100m Rural)\n\n--Ref - TL1a\n--------------------------------------------------------------------------------\t\t\n\n          \nselect treatment_length.road_id, treat_length_id, tl_start_m, tl_end_m, tl_end_m - tl_start_m as length_m, treatment_length.urban_rural\nfrom treatment_length\njoin carr_way\ton (carr_way.road_id = treatment_length.road_id)\nwhere(tl_start_m >= carr_way.carrway_start_m \t\n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.tl_disabled = 'N'\n      and carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n          where road_type = 'L')\n          and owner_type = 'L'\n      and ((treatment_length.urban_rural = 'R'\t\n      and tl_length_m < 100) or (treatment_length.urban_rural = 'U'\t\n      and tl_length_m < 20));\t"]
["ONRC","Network","Treatment Length","TL1b","Active","Treatment Lengths are not too long\nThe proportion of the number of sealed treatment length records that are not exceptionally long.  Exceptionally long is considered to be:\n- >500m Urban\n- >1km Rural","carr_way\ntreatment_length\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'\nExcludes disabled treatment lengths","Accuracy","High","<75%",">=75%",">=90%","Confirm that treatment length sectioning length is appropriate","ONRC: Very long treatment lengths have the potential to impact the results of the Amenity Performance Measures\nAM: Long lengths can be difficult/impractical to manage and maintain.  The uniformity of condition and performance are likely to be more varied over a greater section length","Lack of process, understanding or resources for the ongoing maintenance of this dataset\nLack of understanding on how to manage treatment length sectioning within the asset management system\nLack of understanding of the consequence of having a large number of long sections","The limit has been set around practical lengths for managing and maintaining the pavement and surfacing assets.  Exceptions to this are allowed for with the grade ranges","-------------------------------------------------------------------------------\n--Treatment Lengths are not too long:\n--Proportion of sealed Treatment Length records (excludes disabled TLs) that \n--are not exceptionally long (>500m Urban and 1km Rural)\n\n--Ref - TL1b\n--------------------------------------------------------------------------------    \nselect count(*) as tot_tls  \ninto #temp_tot_tls\nfrom treatment_length\njoin carr_way on (carr_way.road_id = treatment_length.road_id)\nwhere(tl_start_m >= carr_way.carrway_start_m  \n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.tl_disabled = 'N'\n      and carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n          where road_type = 'L')\n      and owner_type = 'L'; \n          \n{Select count of treatment length sections that are not very long}\nselect count(*) as not_long_tls\ninto #temp_not_long_tls\nfrom treatment_length\njoin carr_way on (carr_way.road_id = treatment_length.road_id)\nwhere(tl_start_m >= carr_way.carrway_start_m  \n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.tl_disabled = 'N'\n      and carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n          where road_type = 'L')\n          and owner_type = 'L'\n      and ((treatment_length.urban_rural = 'R'  \n      and tl_length_m <= 1000) or (treatment_length.urban_rural = 'U' \n      and tl_length_m <= 500)); \n\n      \n{Total Proportion that are not short or very long TL} \n  \nupdate #temp_not_long_tls\nset not_long_tls = 0  \nwhere not_long_tls is null; \n\nselect (cast(not_long_tls as decimal)) / (cast(tot_tls as decimal)) *100 as TL1b_not_long\nfrom #temp_not_long_tls, #temp_tot_tls; \n\ndrop table #temp_not_long_tls, #temp_tot_tls;","--------------------------------------------------------------------------------\n--Treatment Lengths are not too long:\n--Proportion of sealed Treatment Length records (excludes disabled TLs) that \n--are not exceptionally long (>500m Urban and 1km Rural)\n\n--Ref - TL1b\n--------------------------------------------------------------------------------\t\t\n\n          \nselect treatment_length.road_id, treat_length_id, tl_start_m, tl_end_m, tl_end_m - tl_start_m as length_m, treatment_length.urban_rural\nfrom treatment_length\njoin carr_way\ton (carr_way.road_id = treatment_length.road_id)\nwhere(tl_start_m >= carr_way.carrway_start_m \t\n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.tl_disabled = 'N'\n      and carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n          where road_type = 'L')\n          and owner_type = 'L'\n      and ((treatment_length.urban_rural = 'R'\t\n      and tl_length_m > 1000) or (treatment_length.urban_rural = 'U'\t\n      and tl_length_m > 500));"]
["ONRC","Network","Treatment Length","TL2","Active","Treatment Lengths match major surfaces\nThe proportion of the number of treatment length records where the major top surface covers at least 80% of the treatment length","carr_way\ntreatment_length\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'\nExcludes disabled treatment lengths","Accuracy","Moderate","<70%",">=70%",">=90%","Confirm that treatment length sectioning alignment with major top surface records.","ONRC: Impacts the results of the Amenity Performance Measures\nAM: Misalignment indicates potential that sectioning does not reflect how pavement and surfacing assets are being managed and maintained.  Decision-making affected by network not clearly understood through summarising of asset inventory and condition data to inappropriate sectioning.  ","Lack of process, understanding or resources for the ongoing maintenance of this dataset\nLack of understanding on how to manage treatment length sectioning within the asset management system","The 80% coverage has been selected as this is an existing extent already reported in RAMM","--------------------------------------------------------------------------------  \n--Treatment Lengths match major surfaces:\n--Proportion of Treatment Length records with >= 80% coverage of the major \n--surfacing (excludes disabled TLs)\n\n--Ref - TL2\n--------------------------------------------------------------------------------  \n\n{Select total count of treatment lengths}\nselect count(*) as tot_sealed_tls\ninto #temp_tot_sealed_tls  \nfrom treatment_length\njoin carr_way on (carr_way.road_id = treatment_length.road_id)\nwhere(tl_start_m >= carr_way.carrway_start_m  \n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.tl_disabled = 'N'\n      and carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n          where road_type = 'L')\n      and owner_type = 'L'; \n\n\n{Select count of treatment lengths with >=80% coverage of major surfacing}\nselect count(*) as PC_count\ninto #temp_PCless80 \nfrom treatment_length\njoin carr_way on (carr_way.road_id = treatment_length.road_id)\nwhere(tl_start_m >= carr_way.carrway_start_m  \n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.tl_disabled = 'N'  \n      and surface_covered >= 0.8\n      and carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n          where road_type = 'L')\n          and owner_type = 'L';\n      \n\n{Express as % treament length with >=80% coverage}\nselect (cast(PC_count as decimal)) / (cast(tot_sealed_tls as decimal)) *100 as TL2_coverage\nfrom #temp_PCless80, #temp_tot_sealed_tls;  \n\ndrop table #temp_PCless80, #temp_tot_sealed_tls;","--------------------------------------------------------------------------------\t\n--Treatment Lengths match major surfaces:\n--Proportion of Treatment Length records with >= 80% coverage of the major \n--surfacing (excludes disabled TLs)\n\n--Ref - TL2\n--------------------------------------------------------------------------------\t\n\n\nselect treatment_length.road_id, treat_length_id, tl_start_m, tl_end_m, tl_end_m - tl_start_m as length_m, surface_covered\nfrom treatment_length\njoin carr_way\ton (carr_way.road_id = treatment_length.road_id)\nwhere(tl_start_m >= carr_way.carrway_start_m \t\n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.tl_disabled = 'N'\t\n      and (surface_covered < 0.8 or surface_covered is null)\n      and carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n          where road_type = 'L')\n          and owner_type = 'L';"]
["ONRC","Network","Treatment Length","TL4","Active","Network coverage with STE reading\nThe proportion of the number sealed treatment length records with a recorded Smooth Travel Exposure (STE) value","carr_way\ntreatment_length\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'\nExcludes disabled treatment lengths","Completeness","Moderate","<90%",">=90%",">=95%","Check on population of smooth travel exposure field in the treatment length table for the sealed network","ONRC: Incomplete data will impact the result of Amenity Performance Measures\nAM: Understanding of performance and levels of service delivered are likely to be reduced","Lack of process, understanding or resources to run the RAMM Manager Status Check processes to summarise the latest roughness survey data into the Treatment Length table\nRoughness survey does not cover the full extents of the sealed network","Currently the ONRC Performance Measures use STE from the STE field in the Treatment Length table","--------------------------------------------------------------------------------  \n--Network coverage with STE reading:\n--The proportion of sealed treatment length records with a \n--Smooth Travel Exposure (STE) value (excludes disabled TLs)\"\n\n--Ref - TL4\n--------------------------------------------------------------------------------  \nselect count(*) as tot_tls  \ninto #temp_tot_tls\nfrom treatment_length\njoin carr_way on (carr_way.road_id = treatment_length.road_id)\nwhere(tl_start_m >= carr_way.carrway_start_m  \n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.tl_disabled = 'N'\n      and carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n          where road_type = 'L')\n      and owner_type = 'L';\n      \n{Select treatment length sections with STE value}\nselect count(*) as tl_ste\ninto #temp_tl_ste\nfrom treatment_length\njoin carr_way on (carr_way.road_id = treatment_length.road_id)\nwhere(tl_start_m >= carr_way.carrway_start_m  \n      and tl_start_m < carr_way.carrway_end_m)\n      and carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n      and owner_type = 'L'\n      and treatment_length.tl_disabled = 'N'  \n      and ste_length is not null;\n\n\nupdate #temp_tl_ste\nset tl_ste = 0  \nwhere tl_ste is null; \n\n{Express as % treament lengths with STE record}      \nselect (cast(tl_ste as decimal)) / (cast(tot_tls as decimal)) * 100 TL4_ste\nfrom #temp_tl_ste, #temp_tot_tls;\n\ndrop table #temp_tl_ste, #temp_tot_tls;","--------------------------------------------------------------------------------\t\n--Network with STE reading:\n--Proportion of sealed Treatment Length records with a \n--Smooth Travel Exposure (STE) value (excludes disabled TLs)\"\n\n--Ref - TL4\n--------------------------------------------------------------------------------\t\n\n\nselect treatment_length.road_id, treat_length_id, tl_start_m, tl_end_m, tl_end_m - tl_start_m as length_m, ste_length\nfrom treatment_length\njoin carr_way\ton (carr_way.road_id = treatment_length.road_id)\nwhere(tl_start_m >= carr_way.carrway_start_m \t\n      and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n      and owner_type = 'L'\n      and treatment_length.tl_disabled = 'N'\t\n      and ste_length is null;"]
["ONRC","Network","Treatment Length","TL5","Retired","Treatment Lengths match renewals\nThe proportion of Treatment Length records updated to match previous years' surface record start and end RPs (excluding disabled TLs)."]
["ONRC","Network","Treatment Length","TL5.1","Active","Treatment Lengths match renewals\nThe proportion of the number of treatment length records where the major top surface covers at least 80% of the treatment length where the surface date in the reported financial year.","carr_way\ntreatment_length\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'\nExcludes disabled treatment lengths","Timeliness","High","<80%",">=80%",">=95%","Confirm that treatment length sectioning is being maintained and updated following renewal activity","ONRC: Impacts the results of the Amenity Performance Measures\nAM: Decision-making affected by network not clearly understood through summarising of asset inventory and condition data to inappropriate sectioning","Poor processes, or lack of resources for the management and maintaining of treatment length sectioning in a timely manner","The 80% coverage has been selected as this is an existing extent already reported in RAMM","--------------------------------------------------------------------------------  \n--Treatment Lengths match renewals:\n--Proportion of Treatment Length records with >= 80% coverage of the major \n--surfacing with a surface date in the reported financial year \n--(excludes disabled TLs)\n\n--Ref - TL5.1\n--------------------------------------------------------------------------------  \ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n{Select total count of treatment lengths in financial year}\nselect count(*) as recent_sealed_tl\ninto #temp_recent_sealed_tl \nfrom treatment_length\njoin carr_way on (carr_way.road_id = treatment_length.road_id)\nwhere(tl_start_m >= carr_way.carrway_start_m  \n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.tl_disabled = 'N'\n      and carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n          where road_type = 'L')\n      and owner_type = 'L'\n      and surface_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\n\n{Select count of treatment lengths with >=80% coverage of major surfacing in financial year}\nselect count(*) as tl_coverage\ninto #temp_tl_coverage\nfrom treatment_length\njoin carr_way on (carr_way.road_id = treatment_length.road_id)\nwhere(tl_start_m >= carr_way.carrway_start_m  \n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.tl_disabled = 'N'  \n      and surface_covered >= 0.8\n      and carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n          where road_type = 'L')\n      and owner_type = 'L'\n      and surface_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);   \n\n{Express as % treament length with >=80% coverage}\nselect (cast(tl_coverage as decimal)) / (cast(recent_sealed_tl as decimal)) *100 as 'TL5.1_renewals'\nfrom #temp_tl_coverage, #temp_recent_sealed_tl; \n\ndrop table #temp_tl_coverage, #temp_recent_sealed_tl, #tempDate;","--------------------------------------------------------------------------------\t\n--Treatment Lengths match renewals:\n--Proportion of Treatment Length records with >= 80% coverage of the major \n--surfacing with a surface date in the reported financial year \n--(excludes disabled TLs)\n\n--Ref - TL5.1\n--------------------------------------------------------------------------------\t\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2017/18 would have a date of 2018-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n\nselect treatment_length.road_id, treat_length_id, tl_start_m, tl_end_m, tl_end_m - tl_start_m as length_m, surface_covered\nfrom treatment_length\njoin carr_way\ton (carr_way.road_id = treatment_length.road_id)\nwhere(tl_start_m >= carr_way.carrway_start_m \t\n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.tl_disabled = 'N'\t\n      and (surface_covered < 0.8 or surface_covered is null)\n      and carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n          where road_type = 'L')\n      and owner_type = 'L'\n      and surface_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate); \t"]
["ONRC","Asset Inventory","Surfacing","Su1","Retired","Last years' renewals as recorded in RAMM\nPercentage of the sealed network length, based on treatment lengths, with a surface recorded entered into RAMM for the reported financial year",null,null,"Timeliness",null,"<4%",">=4%",">=6%","Test that processes exist to capture surface inventory record updates in a timely manner.  A proxy measure of completeness of previous years data.","Incorrect reporting of renewal quantity and achieved life feeding Cost Efficiency 2 & 3 Performance Measures"]
["ONRC","Asset Inventory","Surfacing","Su2","Active","Surface records correctly located\nThe proportion of the number of surface records entered to RAMM with a surface date in the reported financial year that have a recorded start and end displacement within the limits of the road (as defined in the carriageway table), and have a recorded surface width no greater than 2m wider than the carriageway width","carr_way\nc_surface\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Accuracy","High","<80%",">=80%",">=95%","Confirm that the recorded surface inventory records in the financial year align with the network as defined in the carriageway table ","ONRC: Incorrect reporting of renewal quantity and achieved life feeding Cost Efficiency 2 & 3 Performance Measures\nAM: Data used to support decision-making processes potentially inaccurate.  The data summarised into the treatment length table will not reflect the network dimensions","Poor QA process around as-built data collection\nCarriageway sectioning not actively managed/maintained\nTerratrip used for measuring start and end displacements not calibrated","Not greater than 2m wide than the carriageway width was selected based on the guidance in SHDOM that a change in width of >2m should become a new carriageway section.","--------------------------------------------------------------------------------  \n--Surface records correctly located:\n--Proportion of surface records loaded in reported financial year that are \n--within the limits of the road and have a width no more than 2m wider than \n--the carriageway width\n\n--Ref - Su2\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n{Find the minimum start and maximum end for each road in carr_way}\nselect road_id, min(carrway_start_m) min_start, max(carrway_end_m) max_end\ninto #tmp_min_max\nfrom carr_way\nwhere carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n  and carr_way.owner_type = 'L'\ngroup by carr_way.road_id;\n\n\n{Find surfaces with widths <= carriageway widths plus 2m and within road limits}                                    \nselect count(*) as within_road\ninto #temp_within_road  \nfrom c_surface c\njoin carr_way cw on (c.road_id = cw.road_id)\njoin #tmp_min_max m on (c.road_id = m.road_id and (c.start_m >= min_start\n    and c.end_m <= max_end))\nwhere (c.start_m >= cw.carrway_start_m\n    and c.start_m < cw.carrway_end_m)\n    and cw.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and cw.owner_type = 'L'\n    and surface_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate)\n    and (surf_width <= (cway_width + 2) or surf_width is null);\n    \n{Count all surface records}\nselect count(*) as surf_count\ninto #temp_surf_count \nfrom c_surface c\njoin roadnames r on r.road_id = c.road_id\nwhere road_type = 'L'\nand surface_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n    \n\n{Express as % surfaces within limits of road and not more than 2m wider than carr_way width}\nselect (cast(within_road as decimal)) / (cast(surf_count as decimal)) *100 as Su2_location\nfrom #temp_surf_count, #temp_within_road;\n\ndrop table #tmp_min_max, #temp_surf_count, #temp_within_road, #tempDate;","--------------------------------------------------------------------------------\t\n--Surface records correctly located:\n--Proportion of surface records loaded in reported financial year that are \n--within the limits of the road and have a width no more than 2m wider than \n--the carriageway width\n\n--Ref - Su2\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2017/18 would have a date of 2018-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n\n--Find the minimum start and maximum end for each road in carr_way\nselect road_id, min(carrway_start_m) min_cway_start, max(carrway_end_m) max_cway_end\ninto #temp_min_max\nfrom carr_way\nwhere carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand carr_way.owner_type = 'L'\ngroup by carr_way.road_id;\n\n                                \nselect s.c_surface_id, s.road_id, s.start_m, s.end_m, s.surf_material, s.surf_function, s.chip_size, s.chip_2nd_size, s.surf_width, cway_width, min_cway_start, max_cway_end\nfrom c_surface s\njoin carr_way cw on (s.road_id = cw.road_id)\njoin #temp_min_max m on s.road_id = m.road_id \nwhere (s.start_m >= cw.carrway_start_m\n    and s.start_m < cw.carrway_end_m)\n    and cw.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and cw.owner_type = 'L'\n    and surface_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate)\n    and (s.start_m < min_cway_start\n    or s.end_m > max_cway_end\n    or surf_width > (cway_width + 2));\n    \n    \ndrop table #temp_min_max;"]
["ONRC","Asset Inventory","Surfacing","Su3","Active","Surface records with original cost\nThe proportion of surface records entered to RAMM with a surface date greater than 30 June 2016 and an original cost recorded where the recorded works category is 212 or 214","carr_way\nc_surface\nroadnames\nfund_work_origin\nfund_work_category","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'","Completeness","High","<80%",">=80%",">=95%","Confirm that the original cost is recorded against the surface inventory record for work category 212 and 214 activity since the start of the 2016/17 financial year","ONRC: Incorrect reporting of renewal quantity and achieved life feeding Cost Efficiency 2 & 3 Performance Measures\nAM: Reduced understanding of investment, and effectiveness of investment","Poor or lack of process for the population of this attribute field\nLack of understanding of what costs are to be recorded","30 June 2016 was selected as population of this field was requested of the sector from the 2016/17 financial year onwards.","--------------------------------------------------------------------------------  \n--Surface records with original cost:\n--Proportion of surface records with a surface date greater than\n--30 June 2016 with a cost recorded\n\n--Ref - Su3\n--------------------------------------------------------------------------------\n\n{Count total surfaces with work category (212, 214) since 30 June 2016}\nselect count(*) surf_since_june\ninto #temp_surf_june16\nfrom c_surface s\njoin carr_way on (carr_way.road_id = s.road_id) \n    and (s.start_m >= carr_way.carrway_start_m        \n    and s.start_m < carr_way.carrway_end_m)       \n    and carr_way.pavement_type in ('T', 'S', 'C')\n    and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and surface_date > '2016-06-30'\njoin fund_work_origin fw\non s.work_origin_id = fw.id\njoin fund_work_category fc\non fw.work_category_id = fc.id\nwhere fc.work_category_code in ('212', '214');\n\n{Count surfaces with cost recorded}\nselect count(*) as surf_cost_recorded\ninto #temp_surf_cost\nfrom c_surface s\njoin carr_way c\non (c.road_id = s.road_id)  \n    and (s.start_m >= c.carrway_start_m         \n    and s.start_m < c.carrway_end_m)        \n    and c.pavement_type in ('T', 'S', 'C')\n    and c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and original_cost > 0\n    and surface_date > '2016-06-30'\njoin fund_work_origin fw\non s.work_origin_id = fw.id\njoin fund_work_category fc\non fw.work_category_id = fc.id\nwhere fc.work_category_code in ('212', '214');\n\n\nupdate #temp_surf_cost    \nset surf_cost_recorded = 0    \nwhere surf_cost_recorded is null; \n\n{Express as % surfaces with cost recorded}\nselect (cast(surf_cost_recorded as decimal)) / (cast(surf_since_june as decimal)) *100 as Su3_cost_recorded \nfrom #temp_surf_june16, #temp_surf_cost;\n\ndrop table #temp_surf_june16, #temp_surf_cost;","--------------------------------------------------------------------------------\t\n--Surface records with original cost:\n--Proportion of surface records with a surface date greater than\n--30 June 2016 with a cost recorded\n\n--Ref - Su3\n--------------------------------------------------------------------------------\n\n\n{Count surfaces with no cost recorded}\nselect s.c_surface_id, s.road_id, s.start_m, s.end_m, s.surf_material, s.surf_function, s.chip_size, s.chip_2nd_size, s.work_origin_id, s.original_cost\nfrom c_surface s\njoin carr_way c\non (c.road_id = s.road_id)\t\n    and (s.start_m >= c.carrway_start_m \t\t\t\t\n    and s.start_m < c.carrway_end_m)\t\t\t\t\n    and c.pavement_type in ('T', 'S', 'C')\n    and c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and (original_cost = 0 or original_cost is null)\n    and surface_date > '2016-06-30'\njoin fund_work_origin fw\non s.work_origin_id = fw.id\njoin fund_work_category fc\non fw.work_category_id = fc.id\nwhere fc.work_category_code in ('212', '214');"]
["ONRC","Asset Inventory","Surfacing","Su4","Active","Surface records with work origin\nThe proportion of surface records entered to RAMM with a surface date greater than 30 June 2016 and a work origin and category recorded","carr_way\nc_surface\nroadnames\nfund_work_origin\nfund_work_category","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'","Completeness","High","<80%",">=80%",">=95%","Confirm that surface records associated with renewal activities can be differentiated from those that are not since the start of the 2016/17 financial year","ONRC: Incorrect reporting of renewal quantity and achieved life feeding Cost Efficiency 2 & 3 Performance Measures\nAM: Reduced understanding of pavement and surfacing performance through not screening out activity not associated with renewals","Poor or lack of process for the population of this attribute field\nLack of understanding of how to populate this attribute field in the asset inventory database","30 June 2016 was selected as population of this field was requested of the sector from the 2016/17 financial year onwards.","--------------------------------------------------------------------------------  \n--Surface records with works origin:\n--Proportion of surface records with a surface date greater than \n--30 June 2016 with a works origin/category recorded\n\n--Ref - Su4\n--------------------------------------------------------------------------------\n\n{Select current top surfaces with works origin/category}\nselect count(*) as all_surf_since_june\ninto #temp_all_surf_june16\nfrom c_surface s\njoin carr_way on (carr_way.road_id = s.road_id) \n    and (s.start_m >= carr_way.carrway_start_m        \n    and s.start_m < carr_way.carrway_end_m)       \n    and carr_way.pavement_type in ('T', 'S', 'C')\n    and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and surface_date > '2016-06-30';\n\n\n{Select current top surfaces with works origin/category}\nselect count(*) as works_origin_recorded\ninto #temp_works_origin\nfrom c_surface s\njoin carr_way on (carr_way.road_id = s.road_id) \n    and (s.start_m >= carr_way.carrway_start_m        \n    and s.start_m < carr_way.carrway_end_m)       \n    and carr_way.pavement_type in ('T', 'S', 'C')\n    and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and work_origin_id is not null\n    and surface_date > '2016-06-30';\n\n\nupdate #temp_works_origin   \nset works_origin_recorded = 0   \nwhere works_origin_recorded is null;  \n\n{Express as % surfaces with works origin/category}\nselect (cast(works_origin_recorded as decimal)) / (cast(all_surf_since_june as decimal)) *100 as Su4_works_origin\nfrom #temp_all_surf_june16, #temp_works_origin;\n\ndrop table #temp_all_surf_june16, #temp_works_origin;","--------------------------------------------------------------------------------\t\n--Surface records with works origin:\n--Proportion of surface records with a surface date greater than \n--30 June 2016 with a works origin/category recorded\n\n--Ref - Su4\n--------------------------------------------------------------------------------\n\n{Select current top surfaces with no works origin/category}\nselect s.c_surface_id, s.road_id, s.start_m, s.end_m, s.surf_material, s.surf_function, s.chip_size, s.chip_2nd_size, s.work_origin_id\nfrom c_surface s\njoin carr_way on (carr_way.road_id = s.road_id)\t\n    and (s.start_m >= carr_way.carrway_start_m \t\t\t\t\n    and s.start_m < carr_way.carrway_end_m)\t\t\t\t\n    and carr_way.pavement_type in ('T', 'S', 'C')\n    and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and work_origin_id is null\n    and surface_date > '2016-06-30';"]
["ONRC","Asset Inventory","Surfacing","Su5","Active","Surface records newer than pavement\nThe percentage of the length, based on treatment lengths, where the major top surface is newer than underlying major pavement layer and the recorded pavement layer date is less than 3.5 years old at the end of the reported financial year.","treatment_length\ncarr_way\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'\nExcludes disabled treatment lengths","Accuracy","Moderate","<80%",">=80%",">=95%","Confirm that a surface record exists for all  pavement layer records added to the database in the last 3.5 years.  ","ONRC: Incorrect reporting of renewal quantity and achieved life feeding Cost Efficiency 2 & 3 Performance Measures\nAM: Impacts on future renewal need analysis,  forecasting and programming","No pavement layer data covering the majority of the treatment length section (i.e. major pavement layer and major surface records are for different portions of the treatment length)\nLack of use of pavement builder for completing inventory record updates in RAMM\nPoor, or lack of, process for managing and maintaining pavement and surfacing asset data","The grade ranges make an allowance for treatment lengths which have little pavement layer data and the major pavement layer is associated with a shoulder widening, or similar.","--------------------------------------------------------------------------------       \n--Surface records newer than pavement:\n--Percentage of top surface records newer than underlying pavement layers \n--in the last 3.5 years\n\n--Ref - Su5\n--------------------------------------------------------------------------------      \ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n{Count surface records in the last 3.5 years}\nselect count(*) no_layer \ninto #temp_no_layer    \nfrom treatment_length\njoin carr_way on (carr_way.road_id = treatment_length.road_id)\n          where (tl_start_m >= carr_way.carrway_start_m      \n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.pavement_type in ('T', 'S', 'C')    \n      and treatment_length.tl_disabled = 'N'\n      and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n        and owner_type = 'L'              \n      and layer_date between ((select targetDate from #tempDate) - 1277) and (select targetDate from #tempDate);                    \n     \n{Count top surface records newer than underlying pavement layers in last 3.5 years}               \nselect count(*) no_1stcoat \ninto #temp_first_coat\nfrom treatment_length\njoin carr_way on (carr_way.road_id = treatment_length.road_id)\n          where (tl_start_m >= carr_way.carrway_start_m      \n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.pavement_type in ('T', 'S', 'C')    \n      and treatment_length.tl_disabled = 'N'\n      and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n      and carr_way.owner_type = 'L'              \n      and surface_date >= layer_date              \n      and layer_date between ((select targetDate from #tempDate) - 1277) and (select targetDate from #tempDate);  \n\nupdate #temp_first_coat\nset no_1stcoat  = 0                       \nwhere no_1stcoat is null;                                                                       \n\n\n{Express as % top surface newer than underlying pavement layers in last 3.5 years}  \nselect  \n(cast(no_1stcoat as decimal)) / (cast(no_layer as decimal)) *100 Su5_new_surf\nfrom #temp_first_coat, #temp_no_layer;  \n\ndrop table #temp_first_coat, #temp_no_layer, #tempDate;","--------------------------------------------------------------------------------       \n--Surface records newer than pavement:\n--Percentage of top surface records newer than underlying pavement layers \n--in the last 3.5 years\n\n--Ref - Su5\n--------------------------------------------------------------------------------      \n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2017/18 would have a date of 2018-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n                   \nselect treatment_length.road_id, treat_length_id, tl_start_m, tl_end_m, tl_end_m - tl_start_m as length_m, surface_date, layer_date\nfrom treatment_length\njoin carr_way on (carr_way.road_id = treatment_length.road_id)\n          where (tl_start_m >= carr_way.carrway_start_m      \n          and tl_start_m < carr_way.carrway_end_m)\n      and treatment_length.pavement_type in ('T', 'S', 'C')    \n      and treatment_length.tl_disabled = 'N'\n      and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n      and carr_way.owner_type = 'L'              \n      and surface_date < layer_date              \n      and layer_date between ((select targetDate from #tempDate) - 1277) and (select targetDate from #tempDate); "]
["ONRC","Maintenance Activity","Maintenance Activity","MA1","Active","Complete pavement and surface maintenance activity\nThe number of months in the reported financial year with at least one maintenance activity record with a cost group of pavement (PA) or surfacing (SU) on the sealed network","mc_cost\ncarr_way\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'","Completeness","High","<9",">=9",">=11","Confirm that pavement and surface maintenance activity is recorded monthly throughout the reported year","ONRC: Incorrect reporting of maintenance activity level in the Cost Efficiency Performance Measure\nAM: Affects judgement on economics of intended investment (i.e. NPV analysis) and understanding of network performance (Condition vs levels of renewals vs levels of maintenance activity)","Poor process or lack of process for the population of the maintenance cost table in RAMM\nLack of available resource for the maintenance of these dataset\nLack of understanding of the value of this dataset","The project team have assumed that each month should have at least 1 pavement or surfacing cost group record.  The grade ranges make an allowance for this not being the case in one month of the year\nThe metric does not provide a direct indication of likely accuracy, completeness or timeliness of the maintenance activity data, but it does provide some indication of the effort being applied to capture meaningful data","--------------------------------------------------------------------------------  \n--Complete pavement and surface maintenance activity:\n--Number months with at least one pavement (PA) or surfacing (SU) cost group \n--record on sealed network in reported financial year\n\n--Ref - MA1\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n{Count number of months with at least one PU or SU cost group record}\nselect count(distinct datepart(month,transaction_date)) as MA1_month_count\nfrom mc_cost\njoin carr_way on (carr_way.road_id = mc_cost.road_id)\n      where (start_m >= carr_way.carrway_start_m \n          and start_m < carr_way.carrway_end_m)\n      and cost_group in ('SU', 'PA')\n      and transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate)\n      and carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n        and owner_type = 'L';\n        \ndrop table #tempDate;","Not Applicable"]
["ONRC","Maintenance Activity","Maintenance Activity","MA2","Active","Correctly located pavement and surface maintenance activity\nThe proportion of the number of pavement (PA) and surfacing (SU) cost group maintenance activity records on the sealed network for the reported financial year, recorded at an appropriate location (Proportion of records not at the start of the road)","mc_cost\ncarr_way\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'","Accuracy","High","<70%",">=70%",">=90%","Confirm that the location of pavement and surfacing maintenance activity is recorded so it is associated with the correct road section","ONRC: Maintenance activity associated with wrong carriageway impacting how results are reported by ONRC category for the Cost Efficiency Performance Measure\nAM: Incorrectly located activity will impact the outputs of analysis and modelling using this data to consider asset performance and deterioration, e.g. NPV analysis, dTIMS, etc.","Poor, or lack of, process for the accurate capture of maintenance activity location\nLack of understanding of the value of accurately recording the location of the activity\nCyclic type activities recorded against the start of the road","The grade ranges make an allowance for records legitimately recorded at the start of the road.","--------------------------------------------------------------------------------  \n--Correctly located pavement and surface maintenance activity:\n--Proportion of pavement (PA) and surfacing (SU) cost group records \n--recorded at appropriate location on sealed network \n--(Proportion of records not at the start of the road)\n\n--Ref - MA2\n--------------------------------------------------------------------------------  \ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n{Select all records}\nselect count(*) as total_records\ninto #temp_total_records\nfrom carr_way\njoin mc_cost on (carr_way.road_id = mc_cost.road_id)\nwhere (start_m >= carr_way.carrway_start_m \n          and start_m < carr_way.carrway_end_m)\nand carr_way.pavement_type in ('T', 'S', 'C')\nand cost_group in ('SU', 'PA')\nand carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand owner_type = 'L'\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\n\n{Select records at not at road start}\nselect count(*) as records_not_start\ninto #temp_records_not_start\nfrom carr_way\njoin mc_cost on (carr_way.road_id = mc_cost.road_id)\nwhere (start_m >= carr_way.carrway_start_m \n          and start_m < carr_way.carrway_end_m)\nand carr_way.pavement_type in ('T', 'S', 'C')\nand cost_group in ('SU', 'PA')\nand carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand owner_type = 'L'\nand start_m > (select min(carrway_start_m) from carr_way where carr_way.road_id = mc_cost.road_id)\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\n{Express as % of records not at road start}\nselect (cast(records_not_start as decimal))/(cast(total_records as decimal)) *100 as MA2_location\nfrom #temp_records_not_start, #temp_total_records;\n\ndrop table #temp_records_not_start, #temp_total_records, #tempDate;","--------------------------------------------------------------------------------\t\n--Correctly located maintenance activity:\n--Proportion of pavement (PA) and surfacing (SU) cost group records \n--recorded at appropriate location on sealed network \n--(Proportion of records not at the start of the road)\n\n--Ref - MA2\n--------------------------------------------------------------------------------\t\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2017/18 would have a date of 2018-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n\nselect transaction_id, mc_cost.road_id, roadnames.road_name, start_m, end_m, cost_group, transaction_date, activity, fault\nfrom carr_way\njoin mc_cost on (carr_way.road_id = mc_cost.road_id)\njoin roadnames on (carr_way.road_id = roadnames.road_id)\nwhere (start_m >= carr_way.carrway_start_m \n          and start_m < carr_way.carrway_end_m)\nand carr_way.pavement_type in ('T', 'S', 'C')\nand cost_group in ('SU', 'PA')\nand carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand owner_type = 'L'\nand start_m = (select min(carrway_start_m) from carr_way where carr_way.road_id = mc_cost.road_id)\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);"]
["ONRC","Maintenance Activity","Maintenance Activity","MA3","Retired","Maintenance effort recorded\nProportion of pavement (PA) and surfacing (SU) cost group records with a cost recorded for the financial year (i.e. not $0.00 or NULL)"]
["ONRC","Maintenance Activity","Maintenance Activity","MA4","Active","Pavement and surface maintenance activity has a valid location\nThe proportion of the number of pavement (PA) and surfacing (SU) cost group maintenance activity records on the sealed network for the reported financial year located within the extents of the road as defined in the carriageway table","mc_cost\ncarr_way\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'","Accuracy","High","<95%",">=95%",">=98%","Confirm that all pavement and surfacing maintenance activity records are associated with the extents of the road as defined in the carriageway table","ONRC: Maintenance records that are outside of the road as defined in the carriageway table potentially are excluded from Cost Efficiency Performance Measure reporting\nAM: Activity data located outside of the extents of the road network will not be included in analysis and modelling, potentially making sections of the network to appear to be performing better than they are","Poor or lack of process for the accurate capture of maintenance activity location\nTripmeter used for recording displacements not calibrated\nNetwork extents as defined in the carriageway table incorrect",null,"--------------------------------------------------------------------------------  \n--Pavement and surface maintenance activity has a valid location:\n--Proportion of pavement (PA) and surfacing (SU) cost group records on \n--sealed network for the reported financial year located within the \n--extents of the road as defined in the carriageway table\n\n--Ref - MA4\n--------------------------------------------------------------------------------  \ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n{Find the minimum start and maximum end for each road in carr_way}\nselect road_id, min(carrway_start_m) min_start, max(carrway_end_m) max_end\ninto #tmp_min_max\nfrom carr_way\nwhere carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand carr_way.owner_type = 'L'\ngroup by carr_way.road_id;\n\n{Find all mc inside of road limits (i.e. within gap or outside min_start or max_end)}                 \nselect count(*) as mc_within_road\ninto #temp_mc_within_road\nfrom mc_cost mc\njoin #tmp_min_max tm on (mc.road_id = tm.road_id and (mc.start_m >= tm.min_start\n    and mc.end_m <= tm.max_end))\nand cost_group in ('SU', 'PA')\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\n{Total mc records with road_type = 'L'}\nselect count(*) as tot_mc\ninto #temp_tot_mc\nfrom mc_cost mc\njoin roadnames r\non mc.road_id = r.road_id\nwhere road_type = 'L'\nand cost_group in ('SU', 'PA')\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\n\nselect (cast(mc_within_road as decimal))/(cast(tot_mc as decimal)) *100 as MA4_valid\nfrom #temp_mc_within_road, #temp_tot_mc;\n\ndrop table #tmp_min_max, #temp_mc_within_road, #temp_tot_mc, #tempDate;","\n--------------------------------------------------------------------------------\t\n--Maintenance activity has a valid location:\n--Proportion of pavement (PA) and surfacing (SU) cost group records on \n--sealed network for the reported financial year located within the \n--extents of the road as defined in the carriageway table\n\n--Ref - MA4\n--------------------------------------------------------------------------------\t\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2017/18 would have a date of 2018-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n\nselect road_id, min(carrway_start_m) min_cway_start, max(carrway_end_m) max_cway_end\ninto #tmp_min_max\nfrom carr_way\nwhere carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand carr_way.owner_type = 'L'\ngroup by carr_way.road_id;\n\n\n                               \nselect transaction_id, mc.road_id, roadnames.road_name, start_m, end_m, cost_group, transaction_date, activity, fault, min_cway_start, max_cway_end\nfrom mc_cost mc\njoin roadnames on (mc.road_id = roadnames.road_id)\njoin #tmp_min_max tm on (mc.road_id = tm.road_id and (mc.start_m < tm.min_cway_start\n    or mc.end_m > tm.max_cway_end))\nand cost_group in ('SU', 'PA')\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\n\ndrop table #tmp_min_max;"]
["ONRC","Condition","Roughness","Ro1","Active","Roughness survey within 2.5 years\nThe percentage of the sealed network length, based on carriageway sections, with a latest roughness reading less than 2.5 years old at the end of the reported financial year","rough\ncarr_way\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'","Completeness","High","<70%",">=70%",">=90%","Confirm that sealed network has recent roughness data","ONRC: Incorrect/out of date reporting of STE and average/median roughness values in Amenity Performance Measure results\nAM: Out of date data being used to report LoS achieved, other key statistics, effectiveness of investment, or network performance","Poor or lack of process for loading the latest roughness survey in a timely manner\nRoughness surveys either not undertaken, or undertaken of a frequency less than once every 2.5 years\nEntire sealed network not covered by roughness survey",null,"--------------------------------------------------------------------------------  \n--Roughness survey within 2.5 years:\n--Percentage of sealed network length with a latest roughness reading \n--less than 2.5 years old (from 30 June of reported financial year)\n\n--Ref - Ro1\n--------------------------------------------------------------------------------  \ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n{Select total network length}\nselect sum(carrway_end_m - carrway_start_m) as total_lgth\ninto #temp_total_lgth\nfrom carr_way c\n    where c.pavement_type in ('T', 'S', 'C')\n    and c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n        and owner_type = 'L';\n      \n\n{Select network length with roughness reading less than 2.5 years old}      \nselect sum(carrway_end_m - carrway_start_m) as m_rough\ninto #temp_m_temp_rough\nfrom carr_way \nwhere carr_way.pavement_type in ('T', 'S', 'C')\n      and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n        and owner_type = 'L'\n      and carr_way.road_id in (select rough.road_id from rough\n              where carr_way.road_id = rough.road_id\n              and (carrway_start_m >= rough.start_m \n              and carrway_start_m < rough.end_m)\n              and reading_date between ((select targetDate from #tempDate) - 912) and (select targetDate from #tempDate)\n              and latest = 'L');\n\n\nupdate #temp_m_temp_rough   \nset m_rough = 0   \nwhere m_rough is null;\n\n{Express as % network length with recent(2.5 years) roughness reading}\nselect (cast(m_rough as decimal)) / (cast(total_lgth as decimal)) * 100 as Ro1_recent_rough\nfrom #temp_m_temp_rough, #temp_total_lgth;\n\ndrop table #temp_m_temp_rough, #temp_total_lgth, #tempDate;","--------------------------------------------------------------------------------\t\n--Roughness survey within 2.5 years:\n--Percentage of sealed network length with a latest roughness reading \n--less than 2.5 years old (from 30 June of reported financial year)\n\n--Ref - Ro1\n--------------------------------------------------------------------------------\t\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2017/18 would have a date of 2018-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n    \nselect distinct carr_way_no, c.road_id, c.carrway_start_m, c.carrway_end_m, c.carrway_end_m - c.carrway_start_m as length_m, latest, reading_date\nfrom carr_way c\nleft join rough r\non c.road_id = r.road_id\nand c.carrway_start_m >= r.start_m \nand c.carrway_start_m < r.end_m\nand latest = 'L'\nwhere c.pavement_type in ('T', 'S', 'C')\nand c.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand owner_type = 'L'\nand (reading_date not between ((select targetDate from #tempDate) - 912) and (select targetDate from #tempDate)\nor reading_date is null);"]
["ONRC","Condition","Roughness","Ro2","Retired","HSD Roughness survey within 2.5 years\nProportion of the sealed network length with HSD roughness data less than 2.5 years old (from 30 June of reported financial year)",null,null,"Accuracy",null,"<60%",">=60%",">=80%","Test the population of the more granular HSD Roughness table"]
["ONRC","Condition","Roughness","Ro3","Active","Roughness data has valid location\nThe percentage of all latest roughness readings records located within the extents of the road as defined in the carriageway table","rough\ncarr_way\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'","Accuracy","High","<95%",">=95%",">=98%","Confirm that roughness records are within the extents of the road as defined by the carriageway table","ONRC: Records not associated with a carriageway are not included in calculating the Amenity Performance Measure results\nAM: Data used for analysis and reporting purposes may not be representative of the road section","Data not imported to RAMM through correct processes.\nNetwork updates not completed through correct processes resulting in affected tables not being updated",null,"--------------------------------------------------------------------------------  \n--Roughness data has valid location:\n--All latest roughness readings located within the extents of the road \n--as defined in the carriageway table\n\n--Ref - Ro3\n--------------------------------------------------------------------------------\n\n{Find the minimum start and maximum end for each road in carr_way}\nselect road_id, min(carrway_start_m) min_start, max(carrway_end_m) max_end\ninto #tmp_min_max\nfrom carr_way c\nwhere pavement_type in ('T', 'S', 'C', 'B')\n  and c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n  and c.owner_type = 'L'\ngroup by c.road_id;\n\n\n{Find all roughness readings inside road limits (i.e. within gap or outside min_start or max_end)}                  \nselect count(*) as rough_within_road \ninto #temp_rough_within_road\nfrom rough r \njoin #tmp_min_max tm on (r.road_id = tm.road_id)\njoin carr_way c on (c.road_id = r.road_id and (r.start_m  >= c.carrway_start_m\n              and r.start_m  < c.carrway_end_m))\nwhere (r.start_m >= tm.min_start\n    and r.end_m <= tm.max_end)\n    and c.pavement_type in ('T', 'S', 'C', 'B')\n      and c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n      and c.owner_type = 'L'\n    and r.latest = 'L'; \n\n{Count total roughness records on sealed network}\nselect count(*) as tot_rough\ninto #temp_tot_rough\nfrom rough r \njoin carr_way c on (c.road_id = r.road_id and (r.start_m  >= c.carrway_start_m\n              and r.start_m  < c.carrway_end_m))\n      where c.pavement_type in ('T', 'S', 'C', 'B')\n      and c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n      and c.owner_type = 'L'\n      and r.latest = 'L';   \n\n\nselect (cast(rough_within_road as decimal)) / (cast(tot_rough as decimal)) * 100 as Ro3_valid_location\nfrom #temp_rough_within_road, #temp_tot_rough;\n\ndrop table #tmp_min_max, #temp_rough_within_road, #temp_tot_rough;","--------------------------------------------------------------------------------\t\n--Roughness data has valid location:\n--All latest roughness readings located within the extents of the road \n--as defined in the carriageway table\n\n--Ref - Ro3\n--------------------------------------------------------------------------------\n\n\nselect road_id, min(carrway_start_m) min_cway_start, max(carrway_end_m) max_cway_end\ninto #tmp_min_max\nfrom carr_way c\nwhere pavement_type in ('T', 'S', 'C', 'B')\n  and c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n  and c.owner_type = 'L'\ngroup by c.road_id;\n\n                  \nselect rough_id, r.road_id, r.start_m, r.end_m, r.length_m, r.survey_number, tm.min_cway_start, tm.max_cway_end\nfrom rough r \njoin #tmp_min_max tm on (r.road_id = tm.road_id)\njoin carr_way c on (c.road_id = r.road_id and (r.start_m  >= c.carrway_start_m\n              and r.start_m  < c.carrway_end_m))\nwhere (r.start_m < tm.min_cway_start\n    or r.end_m > tm.max_cway_end)\n    and c.pavement_type in ('T', 'S', 'C', 'B')\n      and c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n      and c.owner_type = 'L'\n    and r.latest = 'L'; \n\n\ndrop table #tmp_min_max;"]
["ONRC","Demand/Use","Traffic Count","TC1","Active","Well targeted traffic count programme\nThe proportion of the total network Vehicle Kilometres Travelled (VKT), based on carriageway sections, with a latest traffic count less than 5 years old at the end of the reported financial year.","carr_way_view\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Completeness","High","<30%",">=30%",">=50%","Confirm the level of traffic demand on the network that has been counted recently","ONRC: Potential mis categorisation of ONRC and therefore reported Performance Measures results\nAM: Poor traffic count data is likely to lead to poor traffic estimating and understanding of the current and changing demand on the network in terms of traffic volume and loading","No active traffic count strategy\nCurrent traffic count programme not targeted at carriageway sections making up the greatest proportion of VKT\nCompleted traffic counts not loaded to RAMM in a timely manner.","The RIMS Guideline for Traffic Counting has been used to guide the grade ranges.  This includes an adjustment to be carriageway section based rather than traffic links based.","--------------------------------------------------------------------------------  \n--Well targeted traffic count programme:\n--Proportion of network VKT with latest traffic count less \n--than 5 years old (from 30 June of reported financial year)\n\n--Ref - TC1\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n{Select total network vkt}\n\nselect sum(length_m * traffic_adt_est * 365) as total_network_vkt\ninto #temp_total_network_vkt\nfrom carr_way_view c\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L';\n\n\n{Select network vkt with traffic count less than 5 years}\n\nselect sum(length_m * traffic_adt_est * 365) as vkt_count_ls5yr\ninto #temp_vkt_count_ls5yr\nfrom carr_way_view c\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and traffic_adt_count is not null\n    and count_date between ((select targetDate from #tempDate) - 1824) and (select targetDate from #tempDate);\n\n{Express as % vkt with traffic count older than 5 years}\nselect cast(vkt_count_ls5yr as decimal) / cast(total_network_vkt as decimal) * 100 TC1_vkt_ls5yr\nfrom #temp_vkt_count_ls5yr, #temp_total_network_vkt;\n\ndrop table #temp_vkt_count_ls5yr, #temp_total_network_vkt, #tempDate;","Not Applicable"]
["ONRC","Demand/Use","Traffic Count","TC2","Retired","Historic count data coverage\nThe proportion of the total network Vehicle Kilometres Travelled (VKT), based on carriageway sections, with traffic count records",null,null,"Completeness",null,"<40%",">=40%",">=60%","Test how much of the traffic demand has been counted historically","Poor traffic count data is likely to lead to poor traffic estimating and understanding of the current and changing demand on the network in terms of traffic volume and loading. "]
["ONRC","Demand/Use","Traffic Count","TC3","Active","Traffic count programme activity on sealed network\nThe proportion of the total network Vehicle Kilometres Travelled (VKT), based on carriageway sections, with a latest traffic count record with a count date less than one year old at the end of the reported financial year","carr_way_view\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Timeliness","Moderate","<10%",">=10%",">=20%","Confirm that count activity for reported financial year is well target to understand traffic demand and is being loaded to the asset database","ONRC: Potential mis categorisation of ONRC and therefore reported Performance Measures results\nAM: Poor traffic count data is likely to lead to poor traffic estimating and understanding of the current and changing demand on the network in terms of traffic volume and loading","No active traffic count strategy\nCurrent traffic count programme not targeted at carriageway sections making up the greatest proportion of VKT\nCompleted traffic counts not loaded to RAMM in a timely manner.","The RIMS Guideline for Traffic Counting has been used to guide the grade ranges.  This included an adjustment to be carriageway section based rather than traffic links.","--------------------------------------------------------------------------------  \n--Traffic count programme activity:\n--Proportion of sealed network VKT with traffic count record \n--with a count date less than one year old at the end of the reported financial year\n\n--Ref - TC3\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n{Select total network vkt}\n\nselect sum(length_m * traffic_adt_est * 365) as total_network_vkt\ninto #temp_total_network_vkt\nfrom carr_way_view c\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L';\n\n{Select network vkt with count date in last financial year}     \n\nselect sum(length_m * traffic_adt_est * 365) as vkt_sum\ninto #temp_vkt_sum\nfrom carr_way_view c\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and traffic_adt_count is not null\n    and count_date > ((select targetDate from #tempDate) - 365);\n    \n\nselect cast(vkt_sum as decimal) / cast(total_network_vkt as decimal) * 100 TC3_vkt_activity\nfrom #temp_total_network_vkt, #temp_vkt_sum;\n\ndrop table #temp_vkt_sum, #temp_total_network_vkt, #tempDate;","Not Applicable"]
["ONRC","Demand/Use","Traffic Count","TC4","Active","Traffic loading understood\nThe proportion of the total network Vehicle Kilometres Travelled (VKT), based on carriageway sections, with a latest classified traffic count record less than 5 years old at the end of the reported financial year","carr_way_view\ntraffic_loading\ntraffic_loading_dtl\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Completeness","High","<20%",">=20%",">=30%","Confirm that recent count data includes collecting classification (vehicle mix) so loading demand is understood","ONRC: Potential mis categorisation of ONRC based on heavy vehicles\nAM: Poor traffic count data is likely to lead to poor traffic estimating and understanding of the current and changing demand on the network in terms of traffic volume and loading","Lack of active traffic count strategy with a targeted programme of classified counts on carriageway sections making up the greatest proportion of VKT\nClassified traffic count data not loaded to RAMM in a timely manner","Classified counts have been determined based on the assumption they have a recorded % cars, and no default category code in the loading method field","--------------------------------------------------------------------------------  \n--Traffic loading understood:\n--Proportion of network VKT with classified traffic count records less than\n--5 years old (from June 30 of reported financial year)\n\n--Ref - TC4\n--------------------------------------------------------------------------------\n\n--drop table #temp_recent_loading, #temp_total_network_vkt, #tempDate;\n\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n{Select total network vkt}\n\nselect sum(length_m * traffic_adt_est * 365) as total_network_vkt\ninto #temp_total_network_vkt\nfrom carr_way_view c\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L';\n\n{Select vkt with classified traffic count in last 5 years}\nselect sum(vkt) as recent_loading\ninto #temp_recent_loading\nfrom \n    (select distinct carr_way_no, (length_m * traffic_adt_est * 365) as vkt\n    from carr_way_view c\n    left join  traffic_loading t\n    on t.road_id = c.road_id\n    left join traffic_loading_dtl d\n    on tload_asset_id = traffic_loading_id\n    where c.road_id in (select road_id from roadnames\n            where road_type = 'L')\n    and owner_type = 'L'\n    and t.carrway_start_m = c.carrway_start_m\n    and tload_asset_type = 'TLOAD'\n    and t.latest = 'L'\n    and t.count_status = 'C'\n    and traffic_adt_count is not null\n    and pccar is not null\n    and default_category is null\n    and d.count_date > ((select targetDate from #tempDate) - 1825)) as temp_rload;\n\n\nselect cast(recent_loading as decimal) / cast(total_network_vkt as decimal) * 100 TC4_recent_loading\nfrom #temp_recent_loading, #temp_total_network_vkt;\n\ndrop table #temp_recent_loading, #temp_total_network_vkt, #tempDate;","Not Applicable"]
["ONRC","Demand/Use","Traffic Count","TC5","Retired","Historic traffic loading coverage\nProportion of the network VKT with classified count records (excludes pavement type 'Bridge')",null,null,"Accuracy",null,"<30%",">=30%",">=40%","Test that historic count data includes collecting vehicle mix so loading demand is understood","Poor traffic count data is likely to lead to poor traffic estimating and understanding of the current and changing demand on the network in terms of traffic volume and loading. "]
["ONRC","Demand/Use","Traffic Estimates","TE1","Active","Network has traffic estimates\nThe proportion of the total number of carriageway section records with a recorded traffic estimate","carr_way_view\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Completeness","High","<80%",">=80%",">=95%","Confirm that all 'road' carriageway sections have a traffic estimate recorded in the asset database","ONRC: Poor understanding of traffic demand on the network, potential incorrect ONRC categories affecting reporting of Performance Measure results\nAM: Key data input for understanding network demand, including changing demand, for analysis and planning purposes.","Poor, or lack of process for estimate traffic demand on the network\nLack of available resource for maintaining this dataset\nLack of understanding of the criticality of this dataset",null,"--------------------------------------------------------------------------------    \n--Network has traffic estimates:\n--Proportion of sealed carriageway records having a traffic estimate\n\n--Ref - TE1\n--------------------------------------------------------------------------------\n\n{select total carriageway sections}\nselect count(*) as total_sections\ninto #temp_total_sections\nfrom carr_way_view c\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L';\n        \n\n{select total carriageway sections with traffic estimates}\nselect count(*) as est_sections\ninto #temp_est_sections\nfrom carr_way_view c\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and traffic_adt_est is not null;\n\n{Express as % carriageway records having a traffic estimate}\nselect cast(est_sections as decimal) / cast(total_sections as decimal) *100 TE1_estimates\nfrom #temp_total_sections, #temp_est_sections;\n\ndrop table #temp_total_sections, #temp_est_sections;","--------------------------------------------------------------------------------\t\n--Network has traffic estimates:\n--Proportion of sealed carriageway records having a traffic estimate\n\n--Ref - TE1\n--------------------------------------------------------------------------------\n       \n\nselect c.road_id, r.road_name, c.carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m, traffic_adt_est\nfrom carr_way_view c\njoin roadnames r \non c.road_id = r.road_id\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and traffic_adt_est is null;"]
["ONRC","Demand/Use","Traffic Estimates","TE2a","Active","Traffic estimates are maintained (High Volume to Arterial)\nThe proportion of carriageway sections with a latest traffic estimate record less than 1 year old at the end of the reported financial year where the ONRC category is High Volume, National, Regional or Arterial","carr_way_view\nonrc_cway_view\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Timeliness","High","<60%",">=60%",">=80%","Confirm that traffic estimates on the higher volume network are maintained in a timely manner","ONRC: Poor understanding of traffic demand on the network, potential incorrect ONRC categories affecting reporting of Performance Measure results\nAM: Key data input for understanding network demand, including changing demand, for analysis and planning purposes.","Poor or lack of process for maintaining and updating estimate traffic demand data on the network in a timely manner\nLack of available resource for maintaining this dataset\nLack of understanding of the criticality of this dataset",null,"--------------------------------------------------------------------------------    \n--Traffic estimates are maintained (High Volume to Arterial):\n--Proportion of traffic estimate records less than 1 year old on \n--sealed High Volume, National, Regional and Arterial network \n--(from 30 June of reported financial year)\n\n--Ref - TE2a\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n{Select estimate records with onrc in (5, 6, 7, 8)}\nselect count(*) as estimate_onrc_gr_4\ninto #temp_estimate_onrc_gr_4\nfrom carr_way_view c\njoin onrc_cway_view o on (c.carr_way_no = o.carr_way_no)\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and o.category_id in (5, 6, 7, 8);\n\n\n{Select estimate records with onrc in (5, 6, 7, 8) and less than 1 year old}\nselect count(*) as estimate_less_1yr\ninto #temp_estimate_less_1yr\nfrom carr_way_view c\njoin onrc_cway_view o on (c.carr_way_no = o.carr_way_no)\nwhere  c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and estimate_date > ((select targetDate from #tempDate) - 365) \n    and o.category_id in (5, 6, 7, 8);\n    \n\nupdate #temp_estimate_less_1yr      \nset estimate_less_1yr = 0   \nwhere estimate_less_1yr is null;\n\n{Express as % of High Volume, National, Arterial and Regional records less than 1 year old\nNote: case statement has been used for when estimate_onrc_gr_4 = 0 to avoid divide by 0 error}\nselect case when estimate_onrc_gr_4 = 0 then 0 \n        else cast(estimate_less_1yr as decimal) / cast(estimate_onrc_gr_4 as decimal) *100 \n        end as TE2a_high_vol\nfrom #temp_estimate_onrc_gr_4, #temp_estimate_less_1yr;\n\ndrop table #temp_estimate_onrc_gr_4, #temp_estimate_less_1yr, #tempDate;","--------------------------------------------------------------------------------\t\n--Traffic estimates are maintained (High Volume to Arterial):\n--Proportion of traffic estimate records less than 1 year old on \n--sealed High Volume, National, Regional and Arterial network \n--(from 30 June of reported financial year)\n\n--Ref - TE2a\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2017/18 would have a date of 2018-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n\nselect c.road_id, c.carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m, o.category_id, estimate_date\nfrom carr_way_view c\njoin onrc_cway_view o on (c.carr_way_no = o.carr_way_no)\nwhere  c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and (estimate_date < ((select targetDate from #tempDate) - 364) or estimate_date is null)\n    and o.category_id in (5, 6, 7, 8);"]
["ONRC","Demand/Use","Traffic Estimates","TE2b","Active","Traffic estimates are maintained (Primary and Secondary Collectors)\nThe proportion of carriageway sections with a latest traffic estimate record less than 3 years old at the end of the reported financial year where the ONRC category is Primary Collector or Secondary Collector","carr_way_view\nonrc_cway_view\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Timeliness","High","<60%",">=60%",">=80%","Confirm that traffic estimates on the collector network are maintained in a timely manner","ONRC: Poor understanding of traffic demand on the network, potential incorrect ONRC categories affecting reporting of Performance Measure results\nAM: Key data input for understanding network demand, including changing demand, for analysis and planning purposes.","Poor or lack of process for maintaining and updating estimate traffic demand data on the network in a timely manner\nLack of available resource for maintaining this dataset\nLack of understanding of the criticality of this dataset",null,"--------------------------------------------------------------------------------    \n--Traffic estimates are maintained (Primary and Secondary Collectors):\n--Proportion of traffic estimate records less than 3 years old on \n--sealed Primary and Secondary Collector network  \n--(from 30 June of reported financial year)\n\n--Ref - TE2b\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n{Select estimate records with onrc in (3, 4)}\nselect count(*) as estimate_onrc_3_4\ninto #temp_estimate_onrc_3_4\nfrom carr_way_view c\njoin onrc_cway_view o on (c.carr_way_no = o.carr_way_no)\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and o.category_id in (3 ,4);\n    \n{Select estimate records with onrc in (3, 4) and less than 3 years old}        \nselect count(*) as estimate_less_3yr\ninto #temp_estimate_less_3yr\nfrom carr_way_view c\njoin onrc_cway_view o on (c.carr_way_no = o.carr_way_no)\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and estimate_date > ((select targetDate from #tempDate) - 1095)\n    and o.category_id in (3 ,4);\n    \n\nupdate #temp_estimate_less_3yr      \nset estimate_less_3yr = 0       \nwhere estimate_less_3yr is null;\n\nupdate #temp_estimate_onrc_3_4      \nset estimate_onrc_3_4 = 0       \nwhere estimate_onrc_3_4 is null;\n\n\n{Express as % of Primary and Seconday Collector records less than 3 years old}\nselect cast(estimate_less_3yr as decimal) / cast(estimate_onrc_3_4 as decimal) *100 TE2b_collector\nfrom #temp_estimate_onrc_3_4, #temp_estimate_less_3yr;\n\ndrop table #temp_estimate_onrc_3_4, #temp_estimate_less_3yr, #tempDate;","--------------------------------------------------------------------------------\t\n--Traffic estimates are maintained (Primary and Secondary Collectors):\n--Proportion of traffic estimate records less than 3 years old on \n--sealed Primary and Secondary Collector network  \n--(from 30 June of reported financial year)\n\n--Ref - TE2b\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2017/18 would have a date of 2018-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n      \nselect c.road_id, c.carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m, o.category_id, estimate_date\nfrom carr_way_view c\njoin onrc_cway_view o on (c.carr_way_no = o.carr_way_no)\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and (estimate_date < ((select targetDate from #tempDate) - 1094) or estimate_date is null)\n    and o.category_id in (3 ,4);"]
["ONRC","Demand/Use","Traffic Estimates","TE2c","Active","Traffic estimates are maintained (Access including Low Volume)\nThe proportion of carriageway sections with a latest traffic estimate record less than 5 years old at the end of the reported financial year where the ONRC category is Access or Low Volume","carr_way_view\nonrc_cway_view\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Timeliness","High","<60%",">=60%",">=80%","Confirm that traffic estimates on the lower volume network are maintained in a timely manner","ONRC: Poor understanding of traffic demand on the network, potential incorrect ONRC categories affecting reporting of Performance Measure results\nAM: Key data input for understanding network demand, including changing demand, for analysis and planning purposes.","Poor or lack of process for maintaining and updating estimate traffic demand data on the network in a timely manner\nLack of available resource for maintaining this dataset\nLack of understanding of the criticality of this dataset",null,"--------------------------------------------------------------------------------    \n--Traffic estimates are maintained (Access including Low Volume):\n--Proportion of traffic estimate records less than 5 years old on \n--sealed Access including Low Volume Access network \n--(from 30 June of reported financial year)\n\n--Ref - TE2c\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n{Select estimate records with onrc in (1, 2)}\nselect count(*) as estimate_onrc_1_2\ninto #temp_estimate_onrc_1_2\nfrom carr_way_view c\njoin onrc_cway_view o on (c.carr_way_no = o.carr_way_no)\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and o.category_id in (1 ,2);\n\n\n{Select estimate records with onrc in (1, 2) and less than 5 years old} \nselect count(*) as estimate_less_5yr\ninto #temp_estimate_less_5yr\nfrom carr_way_view c\njoin onrc_cway_view o on (c.carr_way_no = o.carr_way_no)\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and estimate_date > ((select targetDate from #tempDate) - 1825)\n    and o.category_id in (1 ,2);\n    \n\nupdate #temp_estimate_less_5yr      \nset estimate_less_5yr = 0       \nwhere estimate_less_5yr is null;\n\nupdate #temp_estimate_onrc_1_2      \nset estimate_onrc_1_2 = 0       \nwhere estimate_onrc_1_2 is null;\n\n{Express as % of Access and Low Volume records less than 5 years old}\nselect cast(estimate_less_5yr as decimal) / cast(estimate_onrc_1_2 as decimal) *100 TE2c_low_vol\nfrom #temp_estimate_onrc_1_2, #temp_estimate_less_5yr;\n\ndrop table #temp_estimate_onrc_1_2, #temp_estimate_less_5yr, #tempDate;","--------------------------------------------------------------------------------\t\n--Traffic estimates are maintained (Access including Low Volume):\n--Proportion of traffic estimate records less than 5 years old on \n--sealed Access including Low Volume Access network \n--(from 30 June of reported financial year)\n\n--Ref - TE2c\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2017/18 would have a date of 2018-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n\nselect c.road_id, c.carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m, o.category_id, estimate_date\nfrom carr_way_view c\njoin onrc_cway_view o on (c.carr_way_no = o.carr_way_no)\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and (estimate_date < ((select targetDate from #tempDate) - 1824) or estimate_date is null)\n    and o.category_id in (1 ,2);"]
["ONRC","Demand/Use","Traffic Estimates","TE3","Active","Traffic estimates updated following counts\nThe proportion of the number of carriageway sections with a traffic count record where the traffic estimate record has been updated following the latest count","carr_way_view\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Accuracy","High","<70%",">=70%",">=90%","Confirm that traffic estimates are being reviewed and updated in the asset database as traffic counting activities are undertaken","ONRC: Poor understanding of traffic demand on the network, potential incorrect ONRC categories affecting reporting of Performance Measure results\nAM: Key data input for understanding network demand, including changing demand, for analysis and planning purposes.","No process for updating traffic estimates in a timely manner on counted carriageway sections\nLack of available resource for maintaining this dataset\nLack of understanding of the criticality of this dataset",null,"--------------------------------------------------------------------------------    \n--Traffic estimates updated following counts:\n--Proportion of estimate records newer than count records\n\n--Ref - TE3\n--------------------------------------------------------------------------------\n\n{Select total number of count records}\nselect count(*) as total_counts\ninto #temp_total_counts\nfrom carr_way_view c\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and count_date is not null;\n\n\n{Select estimate records newer than count records}\nselect count(*) as est_older_than_count\ninto #temp_est_older_than_count\nfrom carr_way_view c\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and estimate_date >= count_date\n    and count_date is not null;\n\n{Express as % of estimate records newer than count records}\nselect cast(est_older_than_count as decimal) / cast(total_counts as decimal) *100 TE3_est_updated\nfrom #temp_total_counts, #temp_est_older_than_count;\n\ndrop table #temp_est_older_than_count, #temp_total_counts;","--------------------------------------------------------------------------------\t\n--Traffic estimates updated following counts:\n--Proportion of estimate records newer than count records\n\n--Ref - TE3\n--------------------------------------------------------------------------------\n\n\nselect c.road_id, c.carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m, estimate_date, count_date\nfrom carr_way_view c\nwhere c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and (estimate_date < count_date or estimate_date is null)\n    and count_date is not null;"]
["ONRC","Demand/Use","Traffic Estimates","TE4","Active","Considered traffic loading\nThe proportion of the total number of latest traffic estimate records with an estimated traffic loading mix distribution (i.e. not a default)","carr_way_view\ntraffic_loading\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Completeness","High","<80%",">=80%",">=95%","Confirm that traffic mix (classification) is being considered when estimating traffic demand rather than simply using default values","ONRC: Poor understanding of traffic demand on the network, potential incorrect ONRC categories affecting reporting of Performance Measure results\nAM: Key data input for understanding network demand, including changing demand, for analysis and planning purposes.","No process for consideration of traffic loading (mix) as part of maintaining and updating traffic estimate data\nLack of understanding of the criticality of this dataset","Estimates with a considered traffic loading have been determined based on the assumption they have a recorded % cars, and no default category code in the loading method field","--------------------------------------------------------------------------------    \n--Considered traffic loading:\n--Proportion of estimate records with a loading estimate (i.e. not a default)\n\n--Ref - TE4\n--------------------------------------------------------------------------------\n\n{Select count of total estimate records}\nselect count(distinct carr_way_no) as total_estimates\ninto #temp_total_estimates\nfrom traffic_loading t \njoin carr_way c on (t.road_id = c.road_id)\nwhere t.carrway_start_m = c.carrway_start_m\nand c.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand owner_type = 'L'\nand t.latest = 'L'\nand t.count_status = 'E';\n\n\n{Select count of estimates with a loading estimate}\nselect count(distinct carr_way_no) as not_default\ninto #temp_not_default\nfrom traffic_loading t \njoin carr_way c on (t.road_id = c.road_id)\nwhere t.carrway_start_m = c.carrway_start_m\nand c.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand owner_type = 'L'\nand default_category is null\nand t.latest = 'L'\nand t.count_status = 'E';\n\n\n{Express as % of total estimates with a loading estimate}\n\nselect cast(not_default as decimal) / cast(total_estimates as decimal) *100 TE4_loading_est\nfrom #temp_total_estimates, #temp_not_default;\n\ndrop table #temp_total_estimates, #temp_not_default;","--------------------------------------------------------------------------------\t\n--Considered traffic loading:\n--Proportion of estimate records with a loading estimate (i.e. not a default)\n\n--Ref - TE4\n--------------------------------------------------------------------------------\n\n\nselect c.road_id, c.carr_way_no, c.carrway_end_m, c.carrway_start_m, c.carrway_end_m - c.carrway_start_m as length_m, default_category\nfrom traffic_loading t \njoin carr_way c on (t.road_id = c.road_id)\nwhere t.carrway_start_m = c.carrway_start_m\nand c.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand owner_type = 'L'\nand default_category is not null\nand t.latest = 'L'\nand t.count_status = 'E';"]
["ONRC","Crash","Crash","Cr1","Active","Crash data is recent\nThe age (in months) of crash data in terms of the time difference between the RAMM date_added field and the date loaded to the Performance Measure Reporting Tool (PMRT)","cas_crash\ncarr_way","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'","Timeliness","Moderate",">12",">6","<=6","Confirm that crash data is being maintained and updated in RAMM by the RCA in a timely manner","ONRC: Safety Performance Measures results will be impacted by having an incomplete dataset through not being updated/maintained frequently.\nAM: Incomplete dataset used for wet crash analysis associated with skid management processes","Lack of awareness of the process to maintain this dataset in RAMM\nThis data is not used to analyse or report on crashes on the network\nLack of resources to maintain this dataset","The time difference between latest added on date in RAMM and the date imported into the Performance Measures has used a proxy to measure the timeliness of updates to this dataset.","--------------------------------------------------------------------------------    \n--Crash data is recent:\n--Age (in months) of crash data in terms of time difference \n--between RAMM date_added field and date loaded to the PMRT\n\n--Ref - Cr1\n--------------------------------------------------------------------------------    \n\n{Difference between current date and most recent crash_date (in months)}\nselect datediff(month, max(cr.added_on), '2019-06-30') as Cr1_recent\nfrom cas_crash cr\njoin carr_way c on (c.road_id = cr.road_id)\nwhere (location >= c.carrway_start_m    \n          and location < c.carrway_end_m)\nand pavement_type in ('T', 'S', 'C')\nand c.road_id in (select road_id from roadnames\n    where road_type = 'L')\nand owner_type = 'L';","Not applicable"]
["ONRC","Crash","Crash","Cr2","Active","Crash records with valid location\nThe proportion of the total number of crash records located within the extents of the road, as defined in the carriageway table, for the five year period up to the end of the reported financial year","cas_crash\ncarr_way","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Accuracy","Moderate","<95%",">=95%",">=98%","Confirm that crash data in RAMM is within the extents of the road as defined in the carriageway table","ONRC: Any crashes that are outside of a carriageway section are not included in the Safety Performance Measures results\nAM: Incomplete dataset used for wet crash analysis associated with skid management processes","Data not imported to RAMM through correct processes.\nNetwork updates not completed through correct processes (RAMM Network Manager) resulting in affected tables not being updated",null,"--------------------------------------------------------------------------------    \n--Crash records with valid location:\n--Proportion of crash records located within the extents of the road for \n--the five year period up to the end of the reported financial year\n\n--Ref - Cr2\n--------------------------------------------------------------------------------    \ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n{Find the minimum start and maximum end for each road in carr_way}\nselect road_id, min(carrway_start_m) min_start, max(carrway_end_m) max_end\ninto #tmp_min_max\nfrom carr_way\nwhere carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand carr_way.owner_type = 'L'\ngroup by carr_way.road_id;\n\n{Find all mc inside of road limits (i.e. within gap or outside min_start or max_end)}                 \nselect count(*) as cr_within_road\ninto #temp_cr_within_road\nfrom cas_crash cr\njoin #tmp_min_max tm on (cr.road_id = tm.road_id and (cr.location >= tm.min_start\n    and cr.location <= tm.max_end))\nand crash_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\n{Total mc records with road_type = 'L'}\nselect count(*) as tot_cr\ninto #temp_tot_cr\nfrom cas_crash cr\njoin roadnames r\non cr.road_id = r.road_id\nwhere road_type = 'L'\nand crash_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\n\nselect (cast(cr_within_road as decimal))/(cast(tot_cr as decimal)) *100 as Cr2_valid\nfrom #temp_cr_within_road, #temp_tot_cr;\n\ndrop table #tmp_min_max, #temp_cr_within_road, #temp_tot_cr, #tempDate;","--------------------------------------------------------------------------------\t\n--Crash records with valid location:\n--Proportion of crash records located within the extents of the road for \n--the five year period up to the end of the reported financial year\n\n--Ref - Cr2\n--------------------------------------------------------------------------------\t\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2017/18 would have a date of 2018-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n\nselect road_id, min(carrway_start_m) min_cway_start, max(carrway_end_m) max_cway_end\ninto #tmp_min_max\nfrom carr_way\nwhere carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand carr_way.owner_type = 'L'\ngroup by carr_way.road_id;\n\n\n           \nselect crash_id, cr.road_id, cr.location, crash_date, tm.min_cway_start, tm.max_cway_end\nfrom cas_crash cr\njoin #tmp_min_max tm on (cr.road_id = tm.road_id and (cr.location < tm.min_cway_start\n    or cr.location > tm.max_cway_end))\nand crash_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\ndrop table #tmp_min_max;"]
["Asset Management","Network","Carriageway","AM-Ca1","Active","Road network data complete\nThe percentage of the total network length, based on carriageway sections, where the map centreline length is within 10% of measured road length recorded in the carriageway table","carr_way\ncentreline_segment_length\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Accuracy","High","<85",">=85",">=95","Check consistency between the 'measured' road length recorded in the carriageway table and the map centreline length","AM: Poor centreline data has the potential to impact the locational accuracy of data (inventory, maintenance activity, etc.) recorded in the field.  Incorrect network length affects reported results including STE, VKT, etc.  This also has the potential to impact investment decision making","Inaccurate measurement of carriageway section length leading to inaccurate total road length\nCentrelines not calibrated to measured carriageway section lengths\nRoad centrelines not representative of road network\nPoorly calibrated trip meter\nLow quality GPS and insufficient satellites spread across the sky gives a bad map line\nHeight difference with no calibration points. Centrelines in RAMM are flat earth where the road measurement takes into account slope/elevation","The grade ranges have made allowance for those roads that legitimately fail this test","--------------------------------------------------------------------------------\n--Road network complete\n--Alignment in length between carriageways and centrelines.  \n--Percentage of network length (based on carriageway sections) where map centreline length within 10% of measured road length.\n--Where road type = \"L\" and owner type = \"L\"\n\n--Ref - AM-Ca1\n--------------------------------------------------------------------------------\nselect sum(c.length_m) as total_cway_length\ninto #temp_total_cway_length\nfrom carr_way c\nwhere road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L';\n\nselect distinct csl.road_id, csl.map_sequence, csl.length_m as temp_length\ninto #temp_length\nfrom centreline_segment_length csl\njoin carr_way c\non csl.road_id = c.road_id\nwhere csl.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand owner_type = 'L';\n\nselect road_id, sum(temp_length) as centre_length\ninto #temp_centre_length\nfrom #temp_length\ngroup by road_id;\n\nselect road_id, sum(length_m) as cway_length \ninto #temp_cway_length\nfrom carr_way\nwhere road_id in (select road_id from roadnames\n  where road_type = 'L')\nand owner_type = 'L'\ngroup by road_id;\n\nselect l.road_id, centre_length, cway_length,\n(case when cast(centre_length as decimal)/cast(cway_length as decimal) <=1.1 and cast(centre_length as decimal)/cast(cway_length as decimal) >=0.9 then 1 else 0 end) as aligned_length\ninto #temp_aligned_length\nfrom #temp_centre_length l\njoin #temp_cway_length c\non l.road_id = c.road_id\nwhere cway_length > 0;\n\nselect sum(cway_length) as total_centre_length\ninto #temp_total_centre_length\nfrom #temp_aligned_length\nwhere aligned_length = 1;\n\nselect (cast(total_centre_length as decimal)) / (cast(total_cway_length as decimal)) *100 as AM_Ca1  --pc_length_aligned\nfrom #temp_total_centre_length, #temp_total_cway_length;\n\ndrop table #temp_total_cway_length, #temp_length, #temp_centre_length, #temp_cway_length, #temp_aligned_length, #temp_total_centre_length;","--------------------------------------------------------------------------------\n--Road network complete\n--Alignment in length between carriageways and centrelines.  \n--Percentage of network length (based on carriageway sections) where map centreline length within 10% of measured road length.\n--Where road type = \"L\" and owner type = \"L\"\n\n--Ref - AM-Ca1\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2016/17 would have a date of 2017-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n\nselect distinct csl.road_id, csl.map_sequence, csl.length_m as temp_length\ninto #temp_length\nfrom centreline_segment_length csl\njoin carr_way c\non csl.road_id = c.road_id\nwhere csl.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand owner_type = 'L';\n\nselect road_id, sum(temp_length) as centre_length\ninto #temp_centre_length\nfrom #temp_length\ngroup by road_id;\n\nselect road_id, sum(length_m) as cway_length \ninto #temp_cway_length\nfrom carr_way\nwhere road_id in (select road_id from roadnames\n  where road_type = 'L')\nand owner_type = 'L'\ngroup by road_id;\n\nselect l.road_id, r.road_name, centre_length, cway_length, cast(centre_length as decimal)/cast(cway_length as decimal)*100 as ratio_cline_cway\nfrom #temp_centre_length l\njoin #temp_cway_length c\non l.road_id = c.road_id\njoin roadnames r\non r.road_id = c.road_id\nwhere cway_length > 0\nand (cast(centre_length as decimal)/cast(cway_length as decimal) >1.1 or cast(centre_length as decimal)/cast(cway_length as decimal) <0.9);\n\ndrop table #temp_length, #temp_centre_length, #temp_cway_length;"]
["Asset Management","Network","Carriageway","AM-Ca2","Active","ONRC categories assigned to new carriageways\nThe percentage of carriageway sections added to RAMM in the reported financial year with an ONRC category assigned","carr_way\nonrc_cway_view\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Completeness","High","<85",">=85",">=95","Confirm that ONRC categories are being assigned to new carriageways added to the asset database in the financial year to confirm 'complete network'","ONRC: All Performance Measures are reported by ONRC category.  Carriageway sections with no category are not included in the reported results\nAM: Potentially reduced quality of analysis, planning and reporting at an ONRC category level","Poor or lack of process for assigning an ONRC category to new road carriageway sections in a timely manner\nLack of resources for the ongoing maintenance of this dataset\nLack of understanding that this dataset needs maintaining, or how to do it in the system",null,"--------------------------------------------------------------------------------\n--ONRC categories assigned to new carriageways\n--Percentage of carriageway sections added in the reported financial year with an ONRC category assigned.  \n--Where road type = \"L\" and owner type = \"L\". \n\n--Ref - AM-Ca2\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect count(*) as total_cway_onrc\ninto #temp_total_cway_onrc\nfrom carr_way c\njoin onrc_cway_view o\non o.carr_way_no = c.carr_way_no\nwhere c.added_on between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate)\n  and c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and c.owner_type = 'L'\n  and o.category_id is not null;\n\nselect count(*) as total_cway_added\ninto #temp_total_cway_added\nfrom carr_way c\nwhere c.added_on between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate)\n  and c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and c.owner_type = 'L';\n  \nselect (cast(total_cway_onrc as decimal)) / (cast(total_cway_added as decimal)) *100 as AM_Ca2 --pc_new_cway_with_onrc\nfrom #temp_total_cway_added, #temp_total_cway_onrc;\n\ndrop table #temp_total_cway_added, #temp_total_cway_onrc, #tempDate;","--------------------------------------------------------------------------------\n--ONRC categories assigned to new carriageways\n--Percentage of carriageway sections added in the reported financial year with an ONRC category assigned.  \n--Where road type = \"L\" and owner type = \"L\". \n\n--Ref - AM-Ca2\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2016/17 would have a date of 2017-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect c.road_id, c.carr_way_no, carrway_end_m, carrway_start_m, carrway_end_m - carrway_start_m as length_m\nfrom carr_way c\nleft join onrc_cway_view o\non o.carr_way_no = c.carr_way_no\nwhere c.added_on between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate)\n  and c.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and c.owner_type = 'L'\n  and o.category_id is null;"]
["Asset Management","Network","Carriageway","AM-Ca3","Active","Assigned ONRC category aligns with traffic data\nThe percentage of carriageway sections, where the place function doesn't override the traffic, where the assigned ONRC category is aligned with latest traffic estimate volume (AADT) and daily heavy traffic volume","carr_way_view\nonrc_cway_view",null,"Accuracy","Moderate","<80",">=80",">=90","Confirm that assigned ONRC categories are consistent with the traffic estimates unless for carriageway sections where no place function override has been applied","ONRC: Performance Measure results are reported by ONRC category.  Incorrect categorisation therefore impacts the results reported.\nAM: Incorrect LoS defined based on incorrect category.  Over or under delivery of services and potential risk for incorrect forecast investment needs","Poor or lack of process for assigning an ONRC category to carriageway sections\nONRC categories not maintained following updating of traffic estimate data in the asset database\nLack of resources for the ongoing maintenance of this dataset\nRecording of place function override not done",null,"--------------------------------------------------------------------------------\n--Assigned ONRC category aligns with traffic data\n--Percentage of carriageway sections where the place function doesn't override the traffic \n--where the assigned ONRC is aligned with latest traffic estimate (AADT) and daily heavy traffic volume. \n--Where Rules Override is \"False\".\n\n--Ref - AM-Ca3\n--------------------------------------------------------------------------------\n\n-- Low Volume Category Carriageway Section with consistent AADT\nselect count(*) as tot_low_vol\ninto #temp_tot_low_vol\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 1\nand ((c.traffic_adt_est < 50 and c.urban_rural = 'R' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) <25)\nor (c.traffic_adt_est < 200 and c.urban_rural = 'U' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) <25));\n\nupdate #temp_tot_low_vol\nset tot_low_vol = 0\nwhere tot_low_vol is null;\n\n-- Access Category Carriageway Section with consistent AADT\nselect count(*) as tot_access\ninto #temp_tot_access\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 2\nand ((c.traffic_adt_est between 50 and 199 and c.urban_rural = 'R' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) <25)\nor (c.traffic_adt_est between 200 and 999 and c.urban_rural = 'U' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) <25));\n\nupdate #temp_tot_access\nset tot_access = 0\nwhere tot_access is null;\n\n-- Secondary Collector Category Carriageway Section with consistent AADT\nselect count(*) as tot_sec_coll\ninto #temp_tot_sec_coll\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 3\nand ((c.traffic_adt_est between 200 and 999 and c.urban_rural = 'R' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) <150)\nor (c.traffic_adt_est between 1000 and 2999 and c.urban_rural = 'U' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) <150)\nor (c.traffic_adt_est <200 and c.urban_rural = 'R' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) between 25 and 149)\nor (c.traffic_adt_est <3000 and c.urban_rural = 'U' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) between 25 and 149));\n\nupdate #temp_tot_sec_coll\nset tot_sec_coll = 0\nwhere tot_sec_coll is null;\n\n-- Primary Collector Category Carriageway Section with consistent AADT\nselect count(*) as tot_pri_coll\ninto #temp_tot_pri_coll\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 4\nand ((c.traffic_adt_est between 1000 and 2999 and c.urban_rural = 'R' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) <300)\nor (c.traffic_adt_est between 3000 and 4999 and c.urban_rural = 'U' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) <300)\nor (c.traffic_adt_est <1000 and c.urban_rural = 'R' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) between 150 and 299)\nor (c.traffic_adt_est <3000 and c.urban_rural = 'U' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) between 150 and 299));\n\nupdate #temp_tot_pri_coll\nset tot_pri_coll = 0\nwhere tot_pri_coll is null;\n\n-- Arterial Category Carriageway Section with consistent AADT\nselect count(*) as tot_art\ninto #temp_tot_art\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 5\nand ((c.traffic_adt_est between 3000 and 9999 and c.urban_rural = 'R' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) <400)\nor (c.traffic_adt_est between 5000 and 14999 and c.urban_rural = 'U' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) <400)\nor (c.traffic_adt_est <3000 and c.urban_rural = 'R' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) between 300 and 399)\nor (c.traffic_adt_est <5000 and c.urban_rural = 'U' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) between 300 and 399));\n\nupdate #temp_tot_art\nset tot_art = 0\nwhere tot_art is null;\n\n-- Regional Category Carriageway Section with consistent AADT\nselect count(*) as tot_reg\ninto #temp_tot_reg\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 6\nand ((c.traffic_adt_est between 10000 and 14999 and c.urban_rural = 'R' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) <800)\nor (c.traffic_adt_est between 15000 and 24999 and c.urban_rural = 'U'  and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) <800)\nor (c.traffic_adt_est <10000 and c.urban_rural = 'R' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) between 400 and 799)\nor (c.traffic_adt_est <15000 and c.urban_rural = 'U' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) between 400 and 799));\n\nupdate #temp_tot_reg\nset tot_reg = 0\nwhere tot_reg is null;\n\n-- National Category Carriageway Section with consistent AADT\nselect count(*) as tot_nat\ninto #temp_tot_nat\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 7\nand ((c.traffic_adt_est between 15000 and 19999 and c.urban_rural = 'R' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) <1200)\nor (c.traffic_adt_est between 25000 and 34999 and c.urban_rural = 'U' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) <1200)\nor (c.traffic_adt_est <15000 and c.urban_rural = 'R' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) between 800 and 1199)\nor (c.traffic_adt_est <25000 and c.urban_rural = 'U' and cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) between 800 and 1199));\n\nupdate #temp_tot_nat\nset tot_nat = 0\nwhere tot_nat is null;\n\n-- High Volume Category Carriageway Section with consistent AADT\nselect count(*) as tot_high_vol\ninto #temp_tot_high_vol\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 8\nand ((c.traffic_adt_est >= 20000 and c.urban_rural = 'R')\nor (c.traffic_adt_est >= 35000 and c.urban_rural = 'U')\nor cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) >= 1200);\n\nupdate #temp_tot_high_vol\nset tot_high_vol = 0\nwhere tot_high_vol is null;\n\n-- Total sections with ONRC\nselect count(*) tot_onrc\ninto #temp_tot_onrc\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere rules_override = 'false'\nand category_id is not null; \n\nselect (cast((tot_low_vol + tot_access + tot_sec_coll + tot_pri_coll + tot_art + tot_reg + tot_nat + tot_high_vol) as decimal)/cast(tot_onrc as decimal) * 100) as AM_Ca3 --pc_onrc_traffic_alignment\nfrom #temp_tot_low_vol, #temp_tot_access, #temp_tot_sec_coll, #temp_tot_pri_coll, #temp_tot_art, #temp_tot_reg, #temp_tot_nat, #temp_tot_high_vol, #temp_tot_onrc;\n\ndrop table #temp_tot_low_vol, #temp_tot_access, #temp_tot_sec_coll, #temp_tot_pri_coll, #temp_tot_art, #temp_tot_reg, #temp_tot_nat, #temp_tot_high_vol, #temp_tot_onrc;","--------------------------------------------------------------------------------\n--Assigned ONRC category aligns with traffic\n--Percentage of carriageway sections where the place function doesn't override the traffic where the assigned ONRC is aligned with latest traffic estimate AADT and daily heavy traffic . \n--Where Rules Override is \"False\".\n\n--Ref - AM-Ca3\n--------------------------------------------------------------------------------\n\n-- Low Volume Category Carriageway Section with consistent AADT\nselect c.road_id, c.carr_way_no, o.category_id, c.urban_rural, c.traffic_adt_est, c.loading_pc_heavy, cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) as num_heavies\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 1\nand ((c.urban_rural = 'R' and (c.traffic_adt_est >= 50 or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) >= 25))\nor (c.urban_rural = 'U' and (c.traffic_adt_est >= 200 or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) >= 25)))\n\nunion\n\n-- Access Category Carriageway Section with consistent AADT\nselect c.road_id, c.carr_way_no, o.category_id, c.urban_rural, c.traffic_adt_est, c.loading_pc_heavy, cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) as num_heavies\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 2\nand ((c.urban_rural = 'R' and (c.traffic_adt_est not between 50 and 199 or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) >= 25))\nor (c.urban_rural = 'U' and (c.traffic_adt_est not between 200 and 999 or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) >= 25)))\n\nunion\n\n-- Secondary Collector Category Carriageway Section with consistent AADT\nselect c.road_id, c.carr_way_no, o.category_id, c.urban_rural, c.traffic_adt_est, c.loading_pc_heavy, cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) as num_heavies\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 3\nand ((c.traffic_adt_est not between 200 and 999 or c.urban_rural = 'U' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) >=150)\nand (c.traffic_adt_est not between 1000 and 2999 or c.urban_rural = 'R' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) >=150)\nand (c.traffic_adt_est >=200 or c.urban_rural = 'U' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) not between 25 and 149)\nand (c.traffic_adt_est >=3000 or c.urban_rural = 'R' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) not between 25 and 149))\n\nunion\n\n\n-- Primary Collector Category Carriageway Section with consistent AADT\nselect c.road_id, c.carr_way_no, o.category_id, c.urban_rural, c.traffic_adt_est, c.loading_pc_heavy, cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) as num_heavies\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 4\nand ((c.traffic_adt_est not between 1000 and 2999 or c.urban_rural = 'U' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) >=300)\nand (c.traffic_adt_est not between 3000 and 4999 or c.urban_rural = 'R' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) >=300)\nand (c.traffic_adt_est >= 1000 or c.urban_rural = 'U' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) not between 150 and 299)\nand (c.traffic_adt_est >= 3000 or c.urban_rural = 'R' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) not between 150 and 299))\n\nunion\n\n-- Arterial Category Carriageway Section with consistent AADT\nselect c.road_id, c.carr_way_no, o.category_id, c.urban_rural, c.traffic_adt_est, c.loading_pc_heavy, cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) as num_heavies\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 5\nand ((c.traffic_adt_est not between 3000 and 9999 or c.urban_rural = 'U' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) >=400)\nand (c.traffic_adt_est not between 5000 and 14999 or c.urban_rural = 'R' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) >=400)\nand (c.traffic_adt_est >= 3000 or c.urban_rural = 'U' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) not between 300 and 399)\nand (c.traffic_adt_est >= 5000 or c.urban_rural = 'R' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) not between 300 and 399))\n\nunion\n\n-- Regional Category Carriageway Section with consistent AADT\nselect c.road_id, c.carr_way_no, o.category_id, c.urban_rural, c.traffic_adt_est, c.loading_pc_heavy, cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) as num_heavies\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 6\nand ((c.traffic_adt_est not between 10000 and 14999 or c.urban_rural = 'U' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) >=800)\nand (c.traffic_adt_est not between 15000 and 24999 or c.urban_rural = 'R'  or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) >=800)\nand (c.traffic_adt_est >=10000 or c.urban_rural = 'U' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) not between 400 and 799)\nand (c.traffic_adt_est >=15000 or c.urban_rural = 'R' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) not between 400 and 799))\n\nunion\n\n-- National Category Carriageway Section with consistent AADT\nselect c.road_id, c.carr_way_no, o.category_id, c.urban_rural, c.traffic_adt_est, c.loading_pc_heavy, cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) as num_heavies\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 7\nand ((c.traffic_adt_est not between 15000 and 19999 or c.urban_rural = 'U' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) >=1200)\nand (c.traffic_adt_est not between 25000 and 34999 or c.urban_rural = 'R' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) >=1200)\nand (c.traffic_adt_est >=15000 or c.urban_rural = 'U' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) not between 800 and 1199)\nand (c.traffic_adt_est >=25000 or c.urban_rural = 'R' or cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) not between 800 and 1199))\n\nunion\n\n-- High Volume Category Carriageway Section with consistent AADT\nselect c.road_id, c.carr_way_no, o.category_id, c.urban_rural, c.traffic_adt_est, c.loading_pc_heavy, cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) as num_heavies\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand o.category_id = 8\nand ((c.traffic_adt_est < 20000 or c.urban_rural = 'U')\nand (c.traffic_adt_est < 35000 or c.urban_rural = 'R')\nand cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) < 1200);\n\n\n-- These records have either a missing traffic_adt_est or loading_pc_heavy\nselect c.road_id, c.carr_way_no, o.category_id, c.urban_rural, c.traffic_adt_est, c.loading_pc_heavy, cast(c.traffic_adt_est as decimal)*(cast(c.loading_pc_heavy as decimal)/100) as num_heavies\nfrom onrc_cway_view o\njoin carr_way_view c\non o.carr_way_no = c.carr_way_no \nwhere o.rules_override = 'false'\nand (c.traffic_adt_est is null or c.loading_pc_heavy is null);"]
["Asset Management","Network","Treatment Length","AM-TL1","Active","Treatment Length dimensions match sealed area\nThe percentage of the total number of sealed treatment length records where the recorded sealed area is less than 150% of the calculated area of the treatment length (length * width)","treatment_length\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'\nExcludes disabled treatment lengths","Accuracy","High","<85",">=85",">=95","Confirm consistency between the treatment length dimensions and the sealed area recorded in the surfacing table","ONRC: Incorrectly recorded sealed areas will affect the results of the Cost Efficiency Performance Measures\nAM: Incorrect renewal budget forecasting, annual achievement reporting and network statistics","Incorrect surface area recorded in carriageway surface table record\nShort treatment lengths which include 'extra areas' e.g. intersections, side road thresholds, etc.\nIncorrectly recorded carriageway widths in carriageway table","150% selected as this is an existing value used within the RAMM \"Status Check\" processes when summarising data into the treatment length table","--------------------------------------------------------------------------------\n--Treatment Length dimensions match sealed area\n--Percentage of sealed treatment length records with a recorded sealed area <150% of the length * width.\n--Excludes disabled treatment lengths and pavement type \"Bridge\".\n\n--Ref - AM-TL1\n--------------------------------------------------------------------------------\n\nselect count(*) as tls_tot\ninto #temp_tls_tot\nfrom treatment_length t\nwhere tl_disabled = 'N'\n      and t.pavement_type in ('T', 'S', 'C')\n      and t.road_id in (select road_id from roadnames\n          where road_type = 'L')\n      and t.asset_owner = 'L';\n\nselect count(*) as tls_less_150\ninto #temp_tls_less_150\nfrom treatment_length t\nwhere tl_disabled = 'N'\nand tl_sealed_area < (tl_length_m*tl_width*1.5)\n      and t.pavement_type in ('T', 'S', 'C')\n      and t.road_id in (select road_id from roadnames\n          where road_type = 'L')\n      and t.asset_owner = 'L';\n\nselect (cast(tls_less_150 as decimal)) / (cast(tls_tot as decimal)) *100 as AM_TL1  --pc_TL_less_150_sealed_area\nfrom #temp_tls_less_150, #temp_tls_tot;\n\ndrop table #temp_tls_less_150, #temp_tls_tot;","--------------------------------------------------------------------------------\n--Treatment Length dimensions match sealed area\n--Percentage of sealed treatment length records with a recorded sealed area <150% of the length * width.\n--Excludes disabled treatment lengths and pavement type \"Bridge\".\n\n--Ref - AM-TL1\n--------------------------------------------------------------------------------\n\n     \nselect t.road_id, treat_length_id, tl_start_m, tl_end_m, tl_length_m, tl_width, tl_sealed_area, tl_length_m*tl_width*1.5 length_x_width_x_1_5\nfrom treatment_length t\nwhere tl_disabled = 'N'\nand tl_sealed_area >= (tl_length_m*tl_width*1.5) \n      and t.pavement_type in ('T', 'S', 'C')\n      and t.road_id in (select road_id from roadnames\n          where road_type = 'L')\n      and t.asset_owner = 'L';"]
["Asset Management","Asset Inventory","Surfacing","AM-Su1a","Active","Achieved chipseal resurfacing renewal programme as-builted\nThe percentage of the achieved chipseal resurfacing renewals reported in TIO as-builted in RAMM (in m2) with a work category of 212 for reported financial year","surface_structure\ncarr_way\n","Excludes records where road_type <> 'L'","Timeliness","High","<85; >115","85-95; 105-115","95-105","Confirm the financial years chipseal renewals achievement reported in TIO have been recorded, and are identifiable in RAMM","ONRC: Poor data will impact the results of the Cost Efficiency Performance Measures\nAM: Impact on modelling outputs and forward works programming including analysis, annual achievement reporting and investment decision making","Lack of, or poor process and trained personnel for recording correct work category in the carriageway surface table for sealed resurfacing work claimed under work category 212\nReported achieved quantity from different source than as-builted records in RAMM\nSource data used for reporting achievement is not complete or up to date when needed\nUncertainty around the work category to assign the surfacing component of the pavement rehabilitation to","A NA (Not Applicable) result is returned in the event the reported quantity in TIO is NULL or zero and the as-builted quantity in RAMM is NULL.\nA result of zero is returned if the reported quantity in TIO is greater than zero and the as-builted quantity in RAMM is NULL, or vice versa.","--------------------------------------------------------------------------------\n--Achieved chipseal resurfacing renewal programme as-builted\n--Percentage of achieved chipseal resurfacing renewals reported in TIO and as-builted in RAMM (in m2) for reported financial year\n--Where work_category_code = 212\n\n--Ref - AM-Su1a\n--------------------------------------------------------------------------------\n\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect sum(case when ss.surf_width is not null then ss.surf_width*ss.length_m else c.cway_width*ss.length_m end) as AM_Su1a\nfrom surface_structure ss\njoin carr_way c\non ss.road_id = c.road_id\nand ss.carrway_start_m = c.carrway_start_m\njoin surf_material m \non ss.surf_material = m.surf_material\njoin fund_work_origin fw\non ss.work_origin_id = fw.id\njoin fund_work_category fc\non fw.work_category_id = fc.id\nwhere ss.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L'\nand surf_sectioning = 'C'\nand major_surface = 'Y'\nand surf_structure_set = 'T'\nand m.surf_category = 'CHIP'\nand ss.surface_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate)\nand fc.work_category_code = '212';\n\ndrop table #tempDate;","Not applicable"]
["Asset Management","Asset Inventory","Surfacing","AM-Su1b","Active","Achieved asphaltic concrete resurfacing renewal programme as-builted\nThe percentage of the achieved asphaltic concrete resurfacing renewals reported in TIO as-builted in RAMM (in lane.km) with a work category of 212 for reported financial year","surface_structure\nc_surface\ncarr_way\nsurface_material\nfund_work_origin\nfund_work_category\n","Excludes records where road_type <> 'L'","Timeliness","High","<85; >115","85-95; 105-115","95-105","Confirm the financial years asphalt renewals achievement reported in TIO have been recorded, and are identifiable in RAMM","ONRC: Poor data will impact the results of the Cost Efficiency Performance Measures\nAM: Impact on modelling outputs and forward works programming including analysis, annual achievement reporting and investment decision making","Lack of, or poor process and trained personnel for recording correct work category in the carriageway surface table for sealed resurfacing work claimed under work category 212\nReported achieved quantity from different source than as-builted records in RAMM\nSource data used for reporting achievement is not complete or up to date when needed\nUncertainty around the work category to assign the surfacing component of the pavement rehabilitation to","A NA (Not Applicable) result is returned in the event the reported quantity in TIO is NULL or zero and the as-builted quantity in RAMM is NULL.\nA result of zero is returned if the reported quantity in TIO is greater than zero and the as-builted quantity in RAMM is NULL, or vice versa.","--------------------------------------------------------------------------------\n--Achieved asphaltic concrete resurfacing renewal programme as-builted\n--Percentage of achieved asphaltic concrete resurfacing renewals reported in TIO and as-builted in RAMM (in m2) for reported financial year\n--Where work_category_code = 212\n\n--Ref - AM-Su1b\n--------------------------------------------------------------------------------\n\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect sum(case when cs.lane_coverage is not null then cs.lane_coverage*ss.length_m else c.lanes*ss.length_m end)/1000.00 as AM_Su1b\nfrom surface_structure ss\njoin c_surface cs\non cs.c_surface_id = ss.c_surface_id\njoin carr_way c\non ss.road_id = c.road_id\nand ss.carrway_start_m = c.carrway_start_m\njoin surf_material m \non ss.surf_material = m.surf_material\njoin fund_work_origin fw\non ss.work_origin_id = fw.id\njoin fund_work_category fc\non fw.work_category_id = fc.id\nwhere ss.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and owner_type = 'L'\nand ss.surf_sectioning = 'C'\nand ss.major_surface = 'Y'\nand ss.surf_structure_set = 'T'\nand m.surf_category = 'AM'\nand ss.surface_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate)\nand fc.work_category_code = '212';\n\ndrop table #tempDate;","Not applicable"]
["Asset Management","Asset Inventory","Surfacing","AM-Su2","Active","Surface records have valid attribute data\nThe percentage of current top surface records, based on treatment lengths, with a valid chip size (AM>=7, CS<=6, 2nd chip recorded for 2CHIP and RACK), surface function (AC <> \"M\" or \"1\"), a recorded binder type, and a recorded top surface life (not null)","treatment_length\nsurface_material\nroadnames","Excludes pavement type 'Bridge' and 'Concrete'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'\nExcludes disabled treatment lengths\nChip size is ignored where the recorded surface material is slurry","Accuracy","High","<95",">=95",">=98","Data validation check of the surface records added in the reported financial year confirming correct surface data recorded","AM: Surface records have no network level expected surface life.  This can also impact life cycle analysis and investment decision-making","Untrained staff collecting as-built inventory data\nLack of data validation checks as part of adding inventory record to RAMM\nThe system allows for invalid records to be entered","There are surface records that will legitimately fail this validation test (i.e. Slurry surfaces).  These typically constitute a small proportion of each network and have been considered in setting the grade ranges for this metric","--------------------------------------------------------------------------------\n--Surface records have valid attribute data\n--Percentage of top surface records with a valid chip size (AM>=7, CS<=6, 2nd chip recorded for 2CHIP, RACK, B/S, 3CHIP and CAPE),\n--surface function (AC <> \"M\" or \"1\"), and a recorded top surface life (not null)\n--Where owner type = \"L\".\n\n--Ref - AM-Su2\n--------------------------------------------------------------------------------\n\n--Valid AC\nSELECT count(*) valid_ac\ninto #temp_valid_ac\nfrom treatment_length t\njoin surf_material sm\n   on  t.surf_material = sm.surf_material\nwhere surf_category = 'AM'\nand ((t.surf_material <> 'SLRY' and first_chip_size >= 7)\nor  (t.surf_material = 'SLRY' and first_chip_size is not NULL))\nand surf_function not in ('M', '1')\nand top_surface_life is not null\nand surf_binder is not null\nand tl_disabled = 'N'\nand pavement_type in ('T', 'S')\nand t.road_id in (select road_id from roadnames\n          where road_type = 'L')\n      and t.asset_owner = 'L';\n\n--Valid Chip_2coat\nSELECT count(*) valid_chip2\ninto #temp_valid_chip2\nfrom treatment_length t\njoin surf_material sm\n   on  t.surf_material = sm.surf_material\nwhere surf_category = 'CHIP'\nand t.surf_material in ('2CHIP', 'RACK', 'B/S', '3CHIP', 'CAPE')\nand first_chip_size <= 6\nand top_surface_life is not null\nand surf_binder is not null\nand second_chip_size is not null\nand tl_disabled = 'N'\nand pavement_type in ('T', 'S')\nand t.road_id in (select road_id from roadnames\n          where road_type = 'L')\nand t.asset_owner = 'L';\n\n--Valid Chip_1coat\nSELECT count(*) valid_chip1\ninto #temp_valid_chip1\nfrom treatment_length t\njoin surf_material sm\n   on  t.surf_material = sm.surf_material\nwhere surf_category = 'CHIP'\nand t.surf_material not in ('2CHIP', 'RACK', 'B/S', '3CHIP', 'CAPE')\nand first_chip_size <= 6\nand top_surface_life is not null\nand surf_binder is not null\nand tl_disabled = 'N'\nand pavement_type in ('T', 'S')\nand t.road_id in (select road_id from roadnames\n          where road_type = 'L')\nand t.asset_owner = 'L';\n\nSELECT count(*) tot_tls\ninto #temp_tot_tls\nfrom treatment_length t\njoin surf_material sm\n   on  t.surf_material = sm.surf_material\nwhere surf_category in ('AM', 'CHIP')\nand tl_disabled = 'N'\nand pavement_type in ('T', 'S')\nand t.road_id in (select road_id from roadnames\n          where road_type = 'L')\nand t.asset_owner = 'L'; \n\nselect (cast(valid_ac + valid_chip2 + valid_chip1 as decimal) / cast(tot_tls as decimal))*100 as AM_Su2  --pc_valid_surf\nfrom #temp_valid_ac, #temp_valid_chip2, #temp_valid_chip1, #temp_tot_tls;\n\ndrop table #temp_valid_ac, #temp_valid_chip2, #temp_valid_chip1, #temp_tot_tls;","--------------------------------------------------------------------------------\n--Surface records have valid attribute data\n--Percentage of top surface records with a valid chip size (AM>=7, CS<=6, 2nd chip recorded for 2CHIP, RACK, B/S, 3CHIP and CAPE),\n--surface function (AC <> \"M\" or \"1\"), and a recorded top surface life (not null)\n--Where owner type = \"L\".\n\n--Ref - AM-Su2\n--------------------------------------------------------------------------------\n\n--Invalid AC\nSELECT t.road_id, treat_length_id, tl_start_m, tl_end_m, tl_length_m, tl_width, c_surface_id, surface_date, first_chip_size, top_surface_life, surf_binder, surf_category, t.surf_material, surf_function\nfrom treatment_length t\njoin surf_material sm\n   on  t.surf_material = sm.surf_material\nwhere surf_category = 'AM'\nand (((t.surf_material <> 'SLRY' and first_chip_size < 7)\nor  (t.surf_material = 'SLRY' and first_chip_size is NULL))\nor surf_function in ('M', '1')\nor top_surface_life is null\nor surf_binder is null)\nand tl_disabled = 'N'\nand pavement_type in ('T', 'S')\nand t.road_id in (select road_id from roadnames\n          where road_type = 'L')\n      and t.asset_owner = 'L'\n      \nunion\n\n--Invalid Chip_2coat\nSELECT t.road_id, treat_length_id, tl_start_m, tl_end_m, tl_length_m, tl_width, c_surface_id, surface_date, first_chip_size, top_surface_life, surf_binder, surf_category, t.surf_material, surf_function\nfrom treatment_length t\njoin surf_material sm\n   on  t.surf_material = sm.surf_material\nwhere surf_category = 'CHIP'\nand t.surf_material in ('2CHIP', 'RACK', 'B/S', '3CHIP', 'CAPE')\nand (first_chip_size > 6\nor top_surface_life is null\nor surf_binder is null\nor second_chip_size is null)\nand tl_disabled = 'N'\nand pavement_type in ('T', 'S')\nand t.road_id in (select road_id from roadnames\n          where road_type = 'L')\nand t.asset_owner = 'L'\n\nunion\n\n--Invalid Chip_1coat\nSELECT t.road_id, treat_length_id, tl_start_m, tl_end_m, tl_length_m, tl_width, c_surface_id, surface_date, first_chip_size, top_surface_life, surf_binder, surf_category, t.surf_material, surf_function\nfrom treatment_length t\njoin surf_material sm\n   on  t.surf_material = sm.surf_material\nwhere surf_category = 'CHIP'\nand t.surf_material not in ('2CHIP', 'RACK', 'B/S', '3CHIP', 'CAPE')\nand (first_chip_size > 6\nor top_surface_life is null\nor surf_binder is null\nor second_chip_size is not null)\nand tl_disabled = 'N'\nand pavement_type in ('T', 'S')\nand t.road_id in (select road_id from roadnames\n          where road_type = 'L')\nand t.asset_owner = 'L';"]
["Asset Management","Asset Inventory","Pavement","AM-Pa1","Active","Achieved pavement renewal programme as-builted\nThe percentage of the achieved pavement renewals reported in TIO as-builted in RAMM with a work category of 214 for last financial year","pave_structure\ntreatment_length\nfund_work_origin\nfund_work_category","Excludes records where road_type <> 'L'","Timeliness","High","<85; >115","85-95; 105-115","95-105","Confirm the financial years pavement renewals achievement reported in TIO have been recorded, and are identifiable in RAMM","ONRC: Poor data will impact the results of the Cost Efficiency Performance Measures\nAM: Impact on modelling outputs and forward works programming including analysis, annual achievement reporting and investment decision making","Lack of, or poor process for recording correct work category in the pavement layer table for pavement renewal work claimed under work category 214\nReported achieved quantity from different source than as-builted records in RAMM\nSource data used for reporting achievements is not complete or up to date when needed","A NA (Not Applicable) result is returned in the event the reported quantity in TIO is NULL or zero and the as-builted quantity in RAMM is NULL.\nA result of zero is returned if the reported quantity in TIO is greater than zero and the as-builted quantity in RAMM is NULL, or vice versa.","--------------------------------------------------------------------------------\n--Pavement renewal programme achieved and as-builted\n--Percentage of planned and achieved length reported in TIO as-builted in RAMM (in m2) for last financial year\n--Where work category is 214\n\n--Ref - AM-Pa1\n--------------------------------------------------------------------------------\n\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect sum(case when p.width is not null then p.width*p.length_m else t.tl_width*p.length_m end) as AM_Pa1\nfrom pave_structure p\njoin treatment_length t\non p.road_id = t.road_id\nand p.treat_length_id = t.treat_length_id\njoin fund_work_origin fw\non p.work_origin_id = fw.id\njoin fund_work_category fc\non fw.work_category_id = fc.id\nwhere p.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand structure_set = 'M'\nand layer_no = 1\nand p.layer_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate)\nand fc.work_category_code = '214';\n\ndrop table #tempDate;","Not applicable"]
["Asset Management","Asset Inventory","Pavement","AM-Pa2","Active","Pavement layer records have valid attribute data\nThe percentage of pavement layer records with a layer date in the reported financial year and a known material and source, recorded width when not full width and thickness between 50mm and 500mm","pave_layer\ncarr_way\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Accuracy","High","<95",">=95",">=98","Data validation check of the pavement layer records added in the reported financial year confirming correct pavement data recorded","AM: This can impact our analysis and investment decision making","Untrained staff collecting as-built inventory data\nLack of data validation checks as part of adding inventory record to RAMM\nThe system allows for invalid records to be entered\nLack of data standard on how geotextile/fabric layers are to be recorded",null,"--------------------------------------------------------------------------------\n--Pavement layer records have valid attribute data\n--Percentage of pavement layer records with a layer date in the reported financial year and a known material and source, recorded width when not full width and thickness between 50mm and 500mm.  \n--Excludes subgrade records.\n\n--Ref - AM-Pa2\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect count(layer_id) as all_pave\ninto #temp_all_pave\nfrom pave_layer p\njoin carr_way on (carr_way.road_id = p.road_id)\t\n    and (p.start_m >= carr_way.carrway_start_m \t\t\t\t\n    and p.start_m < carr_way.carrway_end_m)\t\t\t\t\n    and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\nand layer_subgrade = 'L'\nand layer_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\nselect count(layer_id) as valid_pave\ninto #temp_valid_pave\nfrom pave_layer p\njoin carr_way on (carr_way.road_id = p.road_id)\t\n    and (p.start_m >= carr_way.carrway_start_m \t\t\t\t\n    and p.start_m < carr_way.carrway_end_m)\t\t\t\t\n    and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\nwhere layer_subgrade = 'L'\nand thickness between 50 and 500 \nand ((width is not null and full_width_flag = 'N') or\nfull_width_flag = 'Y')\nand pave_material is not null\nand pave_material not in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN', 'UNKNOW')\nand pave_source is not null\nand pave_source not in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN', 'UNKNOW')\nand layer_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);  \n     \nselect (cast(valid_pave as decimal)) / (cast(all_pave as decimal))*100 as AM_Pa2 -- pc_pave_layer_valid\t\nfrom #temp_valid_pave, #temp_all_pave;\t\n\ndrop table #temp_valid_pave, #temp_all_pave, #tempDate;","--------------------------------------------------------------------------------\n--Pavement layer records have valid attribute data\n--Percentage of pavement layer records with a layer date in the reported financial year and a known material and source, recorded width when not full width and thickness between 50mm and 500mm.  \n--Excludes subgrade records.\n\n--Ref - AM-Pa2\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2016/17 would have a date of 2017-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect layer_id, p.road_id, start_m, end_m, p.length_m, width, full_width_flag, thickness, pave_material, pave_source\nfrom pave_layer p\njoin carr_way on (carr_way.road_id = p.road_id)\t\n    and (p.start_m >= carr_way.carrway_start_m \t\t\t\t\n    and p.start_m < carr_way.carrway_end_m)\t\t\t\t\n    and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\nwhere layer_subgrade = 'L'\nand (thickness not between 50 and 500 \nor (width is null and full_width_flag = 'N') \nor pave_material is null\nor pave_material in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN', 'UNKNOW')\nor pave_source is null\nor pave_source  in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN', 'UNKNOW'))\nand layer_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate); "]
["Asset Management","Asset Inventory","Pavement","AM-Pa3","Active","Pavement layer records with Work Origin\nThe percentage of pavement layer records entered to RAMM with a layer date in the reported financial year with a recorded Work Origin","pave_layer\ncarr_way\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Completeness","High","<80",">=80",">=95","Confirm that pavement renewals are identifiable in RAMM for the reported financial year.","ONRC: Poor data will impact the results of the Cost Efficiency Performance Measures. \nAM: This can also impact our analysis and investment decision making","Poor or lack of process for the population of this attribute field",null,"--------------------------------------------------------------------------------\t\n--Pavement layer records with work origin\n--Percentage of pavement layer records added in the reported financial year with a recorded work origin/category.  \n--Excludes subgrade records.\n\n--Ref - AM-Pa3\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect count(layer_id) as all_pave\ninto #temp_all_pave\nfrom pave_layer p\njoin carr_way on (carr_way.road_id = p.road_id)\t\n    and (p.start_m >= carr_way.carrway_start_m \t\t\t\t\n    and p.start_m < carr_way.carrway_end_m)\t\t\t\t\n    and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\nand layer_subgrade = 'L'\nand layer_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\n{Select current top surfaces with works origin/category}\nselect count(layer_id) as work_origin_recorded\ninto #temp_work_origin\nfrom pave_layer p\njoin carr_way on (carr_way.road_id = p.road_id)\t\n    and (p.start_m >= carr_way.carrway_start_m \t\t\t\t\n    and p.start_m < carr_way.carrway_end_m)\t\t\t\t\n    and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and layer_subgrade = 'L'\n    and work_origin_id is not null\n    and layer_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\nupdate #temp_work_origin\t\t\nset work_origin_recorded = 0\t\t\nwhere work_origin_recorded is null;\t\n\n{Express as % surfaces with works origin/category}\nselect (cast(work_origin_recorded as decimal)) / (cast(all_pave as decimal)) *100 as AM_Pa3 --pc_layer_with_works_origin\nfrom #temp_all_pave, #temp_work_origin;\n\ndrop table #temp_work_origin, #temp_all_pave, #tempDate;","--------------------------------------------------------------------------------\t\n--Pavement layer records with work origin\n--Percentage of pavement layer records added in the reported financial year with a recorded work origin/category.  \n--Excludes subgrade records.\n\n--Ref - AM-Pa3\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2016/17 would have a date of 2017-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n{Select current top surfaces without works origin/category}\nselect layer_id, p.road_id, start_m, end_m, p.length_m, width, work_origin_id\nfrom pave_layer p\njoin carr_way on (carr_way.road_id = p.road_id)\t\n    and (p.start_m >= carr_way.carrway_start_m \t\t\t\t\n    and p.start_m < carr_way.carrway_end_m)\t\t\t\t\n    and carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\n    and owner_type = 'L'\n    and layer_subgrade = 'L'\n    and work_origin_id is null\n    and layer_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);"]
["Asset Management","Asset Inventory","Footpath","AM-Fp1","Active","Footpath asset known\nThe percentage of the total number of footpath records with a valid recorded length (not null or zero) and width (>0.7m and <20m, and not null), a known surface material and constructed/surface date or recent known condition","footpath","Excludes records where asset_owner <> 'L'","Completeness","Moderate","<80",">=80",">=90","Confirm completeness of footpath assets attribute data is adequate for planning and decision-making purposes","AM: Poor data has the potential to impact our renewal programme development, investment decision making, asset valuations and LoS delivery","Attribute data not known for 'historic' assets\nPoor, or lack of, clarity on what attribute fields are to be collected and maintained for this asset type\nPoor, or lack of quality control undertaken on inventory data","There will be instances where a footpath width falls outside the allowed range of this test.  An allowance in the grade ranges has been made for this.\nLess than 3 years old has been considered a recent condition date.","--------------------------------------------------------------------------------\n--Footpath records have valid dimensions, material and known age\n--Percentage of footpath records with a valid length (not null or zero) and width (>0.7m and <20m, and not null), a known surface material and constructed/surface date or recent known condition\n--Where owner type = \"L\".\n\n--Ref - AM-Fp1\n--------------------------------------------------------------------------------\n\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect count(footpath_id) as valid_fp\ninto #temp_valid_fp\nfrom footpath\nwhere asset_owner = 'L'\nand width > 0.7 and width < 20\nand length_m > 0\nand footpath_surf_mat is not null\nand footpath_surf_mat not in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN')\nand (constructed is not null or surface_date is not null \nor (condition_date > ((select targetDate from #tempDate) - 1095) and condition <> 'U'));\n\nselect count(footpath_id) as all_fp\ninto #temp_all_fp\nfrom footpath\nwhere asset_owner = 'L';\n\nselect (cast(valid_fp as decimal))/(cast(all_fp as decimal)) *100 as AM_Fp1\nfrom #temp_valid_fp, #temp_all_fp;\n\ndrop table #temp_valid_fp, #temp_all_fp;","--------------------------------------------------------------------------------\n--Footpath records have valid dimensions, material and known age\n--Percentage of footpath records with a valid length (not null or zero) and width (>0.7m and <20m, and not null), a known surface material and constructed/surface date.  \n--Where owner type = \"L\".\n\n--Ref - AM-Fp1\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2016/17 would have a date of 2017-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect footpath_id, road_id, start_m, end_m, length_m, width, footpath_surf_mat, constructed, surface_date, condition, condition_date\nfrom footpath\nwhere asset_owner = 'L'\nand (width < 0.7 or width > 20\nor width is null\nor length_m = 0\nor length_m is null\nor footpath_surf_mat is  null\nor footpath_surf_mat in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN')\nor (constructed is null \nand surface_date is null\nand (condition_date < ((select targetDate from #tempDate) - 1095) or condition = 'U'\nor condition_date is null or condition is null)));"]
["Asset Management","Asset Inventory","Footpath","AM-Fp2","Active","Footpath asset records maintained\nThe percentage of the total footpath length entered to RAMM during the three year period to the end of the reported financial year","footpath","Excludes records where asset_owner <> 'L'","Timeliness","Moderate","<1",">=1",">=3","Confirm that footpath asset data is being maintained as new footpaths are constructed/vested, and others are renewed","AM: Poor data has the potential to impact our investment decision making, understanding of the effectiveness of investment and asset valuations","Poor, or lack of process for updating the asset inventory table following renewal activity\nRenewal activity is delivered in quantities less than that identified for capitalisation by internal processes\nLack of resources to maintain this dataset in a timely manner",null,"--------------------------------------------------------------------------------\n--Footpath asset records maintained\n--Percentage of footpath length added within the last three financial years.  \n--Where owner type = \"L\".\n\n--Ref - AM-Fp2\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect sum(length_m) fp_length_added\ninto #temp_fp_length_added\nfrom footpath\nwhere asset_owner = 'L'\nand added_on between ((select targetDate from #tempDate) - 1095) and (select targetDate from #tempDate);\n\nselect sum(length_m) fp_length_tot\ninto #temp_fp_length_tot\nfrom footpath\nwhere asset_owner = 'L';\n\nselect (cast(fp_length_added as decimal))/(cast(fp_length_tot as decimal)) *100 as AM_Fp2\nfrom #temp_fp_length_added, #temp_fp_length_tot;\n\ndrop table #temp_fp_length_added, #temp_fp_length_tot;","Not applicable"]
["Asset Management","Asset Inventory","Drainage","AM-Dr1","Active","Culvert assets known\nThe percentage of the number of culvert records with a recorded length (e.g. not Null or zero length), size, material (i.e. not \"unknown\") and constructed date or recent known condition","drainage","Excludes records where asset_owner <> 'L'","Completeness","Moderate","<60",">=60",">=80","Confirm completeness of culvert assets attribute data is adequate for planning and decision-making purposes","AM: Poor data has the potential to impact our renewal programme development, investment decision making, asset valuations and LoS delivery","Attribute data not known for 'historic' assets\nPoor, or lack of, clarity on what attribute fields are to be collected and maintained for this asset type\nPoor, or lack of quality control undertaken on inventory data","An allowance has been made in the grade ranges for unknown constructed dates of 'older' asset, and the ease and value of estimating these.\nLess than 3 years old has been considered a recent condition date.","--------------------------------------------------------------------------------\n--Culvert assets known\n--Percentage of culvert records with a known length, size, material and constructed/surface date or recent known condition  \n--Where owner type = \"L\".\n\n--Ref - AM-Dr1\n--------------------------------------------------------------------------------\n\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect count(drainage_id) as known_culverts\ninto #temp_known_culverts\nfrom drainage\nwhere drain_culvert is not null\nand asset_owner = 'L'\nand drain_length is not null\nand drain_length > 0\nand drain_size is not null\nand drain_size > 0\nand drain_material is not null\nand drain_material not in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN')\nand (construct_date is not null \nor (condition_date > ((select targetDate from #tempDate) - 1095) and condition <> 'U'));\n\nselect count(drainage_id) as culvert_records\ninto #temp_culvert_records\nfrom drainage\nwhere drain_culvert is not null\nand asset_owner = 'L';\n\nselect (cast(known_culverts as decimal))/(cast(culvert_records as decimal)) *100 as AM_Dr1\nfrom #temp_known_culverts, #temp_culvert_records;\n\ndrop table #temp_known_culverts, #temp_culvert_records;","--------------------------------------------------------------------------------\n--Culvert assets known\n--Percentage of culvert records with a known length, size, material and constructed/surface date.  \n--Where owner type = \"L\".\n\n--Ref - AM-Dr1\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2016/17 would have a date of 2017-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect drainage_id, road_id, location, drain_culvert, drain_length, drain_size, drain_material, construct_date, condition, condition_date\nfrom drainage\nwhere drain_culvert is not null\nand asset_owner = 'L'\nand (drain_length is null\nor drain_length = 0\nor drain_size is null\nor drain_size = 0\nor drain_material is null\nor drain_material in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN')\nor (construct_date is null\nand (condition_date < ((select targetDate from #tempDate) - 1095) or condition = 'U'\nor condition_date is null or condition is null)));"]
["Asset Management","Asset Inventory","Drainage","AM-Dr2","Active","Culvert asset records maintained\nThe percentage of the total culvert length entered to RAMM during the three year period to the end of the reported financial year","drainage\ncarr_way\nroadnames","Excludes records where asset_owner <> 'L'\nExcludes roads where road_type <> 'L' or owner_type <> 'L'","Timeliness","Moderate","<1",">=1",">=3","Confirm that culvert asset data is being maintained as new culverts are constructed/vested, and others are renewed","AM: Poor data has the potential to impact our investment decision making, understanding of the effectiveness of investment and asset valuations","Poor, or lack of process for updating the asset inventory table following renewal activity\nRenewal activity is delivered in quantities less than that identified for capitalisation by internal processes\nLack of resources to maintain this dataset in a timely manner",null,"--------------------------------------------------------------------------------\n--Culvert asset records maintained\n--Percentage of culvert length added within the last three financial years.\n--Where road_type = \"L\" owner type = \"L\".\n\n--Ref - AM-Dr2\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect sum(d.drain_length) as cul_length_added\ninto #temp_cul_len_added\nfrom drainage d\nleft join carr_way c\non d.road_id = c.road_id\nand d.carrway_start_m = c.carrway_start_m\nwhere d.drain_culvert is not null\n    and c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n        and owner_type = 'L'\nand d.added_on between ((select targetDate from #tempDate) - 1095) and (select targetDate from #tempDate);\n\nselect sum(d.drain_length) as cul_length_tot\ninto #temp_cul_len_tot\nfrom drainage d\nleft join carr_way c\non d.road_id = c.road_id\nand d.carrway_start_m = c.carrway_start_m\nwhere d.drain_culvert is not null\n    and c.road_id in (select road_id from roadnames\n        where road_type = 'L')\n        and owner_type = 'L';\n        \nselect cast(cul_length_added as decimal)/cast(cul_length_tot as decimal)*100 as AM_Dr2\nfrom #temp_cul_len_added, #temp_cul_len_tot;\n\ndrop table #temp_cul_len_added, #temp_cul_len_tot, #tempDate;","Not applicable"]
["Asset Management","Asset Inventory","SW Channel","AM-SW1","Active","SWC asset known\nThe percentage of the number of kerb and channel records with a recorded length (e.g. not Null or zero length), a known type (i.e. not \"unknown\") and construction date or recent known condition","sw_channel","Excludes records where asset_owner <> 'L'","Completeness","Moderate","<80",">=80",">=90","Confirm completeness of kerb and channel assets attribute data is adequate for planning and decision-making purposes","AM: Poor data has the potential to impact our renewal programme development, investment decision making, asset valuations and LoS delivery","Attribute data not known for 'historic' assets\nPoor, or lack of, clarity on what attribute fields are to be collected and maintained for this asset type\nPoor, or lack of quality control undertaken on inventory data","Less than 3 years old has been considered a recent condition date.","--------------------------------------------------------------------------------\n--SWC asset known\n--Percentage of kerb and channel records with a recorded length (eg not Null or zero length), a known type and construction date or recent known condition \n--Where owner type = \"L\" and swc type not 'SWCD' or 'SWCS'.\n\n--Ref - AM-SW1\n--------------------------------------------------------------------------------\n\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect count(sw_channel_id) as valid_swc\ninto #temp_recorded\nfrom sw_channel\nwhere asset_owner = 'L'\nand length_m > 0\nand length_m is not null\nand swc_type is not null\nand swc_type not in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN') \nand swc_type not in ('SWCD', 'SWCS')\nand (constructed is not null \nor (condition_date > ((select targetDate from #tempDate) - 1095) and condition <> 'U'));\n\nselect count(sw_channel_id) as swc_records\ninto #temp_swc_records\nfrom sw_channel\nwhere asset_owner = 'L'\nand swc_type not in ('SWCD', 'SWCS');\n\nselect (cast(valid_swc as decimal))/(cast(swc_records as decimal)) *100 as AM_SW1\nfrom #temp_recorded, #temp_swc_records;\n\ndrop table #temp_recorded, #temp_swc_records;","--------------------------------------------------------------------------------\n--SWC asset known\n--Percentage of kerb and channel records with a recorded length (eg not Null or zero length), a known type and construction date.\n--Where owner type = \"L\" and swc type not 'SWCD' or 'SWCS'.\n\n--Ref - AM-SW1\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2016/17 would have a date of 2017-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect sw_channel_id, road_id, start_m, end_m, length_m, swc_type, constructed, condition, condition_date\nfrom sw_channel\nwhere asset_owner = 'L'\nand swc_type not in ('SWCD', 'SWCS')\nand (length_m = 0\nor length_m is null\nor swc_type is null\nor swc_type in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN') \nor (constructed is null\nand (condition_date < ((select targetDate from #tempDate) - 1095) or condition = 'U'\nor condition_date is null or condition is null)));"]
["Asset Management","Asset Inventory","SW Channel","AM-SW2","Active","SWC asset records maintained\nThe percentage of the total kerb and channel length entered to RAMM during the three year period to the end of the reported financial year","sw_channel","Excludes records where asset_owner <> 'L'","Timeliness","Moderate","<1",">=1",">=3","Confirm that kerb and channel asset data is being maintained as new kerb and channels are constructed/vested, and others are renewed","AM: Poor data has the potential to impact our investment decision making, understanding of the effectiveness of investment and asset valuations","Poor, or lack of process for updating the asset inventory table following renewal activity\nRenewal activity is delivered in quantities less than that identified for capitalisation by internal processes\nLack of resources to maintain this dataset in a timely manner",null,"--------------------------------------------------------------------------------\n--SWC asset records maintained\n--Percentage of kerb and channel length added within the last three financial years. \n--Where owner type = \"L\" and swc type not 'SWCD' or 'SWCS'.\n\n--Ref - AM-SW2\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect sum(length_m) as swc_len_added\ninto #temp_swc_len_added\nfrom sw_channel\nwhere asset_owner = 'L'\nand swc_type not in ('SWCD', 'SWCS')\nand added_on between ((select targetDate from #tempDate) - 1095) and (select targetDate from #tempDate);\n\nselect sum(length_m) as swc_len_tot\ninto #temp_swc_len_tot\nfrom sw_channel\nwhere asset_owner = 'L'\nand swc_type not in ('SWCD', 'SWCS');\n\nselect (cast(swc_len_added as decimal))/(cast(swc_len_tot as decimal)) *100 as AM_SW2\nfrom #temp_swc_len_added, #temp_swc_len_tot;\n\ndrop table #temp_swc_len_added, #temp_swc_len_tot, #tempDate;","Not applicable"]
["Asset Management","Asset Inventory","Signs","AM-Si1","Active","Sign assets known\nThe percentage of the number of sign records with recorded dimensions, material and installation date or recent known condition","sign\nroadnames","Excludes roads where road_type <> 'L'","Completeness","Moderate","<30",">=30",">=50","Confirm completeness of sign assets attribute data is adequate for planning and decision-making purposes","AM: Poor data has the potential to impact our investment decision making, maintenance needs and asset valuations","Attribute data not known for 'historic' assets\nPoor, or lack of, clarity on what attribute fields are to be collected and maintained for this asset type\nPoor, or lack of quality control undertaken on inventory data","An allowance has been made in the grade ranges for unknown constructed dates of 'older' asset, and the ease and value of estimating these.\nLess than 3 years old has been considered a recent condition date.","--------------------------------------------------------------------------------\n--Sign asset known\n--Percentage of sign records with known dimensions, material and installation date or recent known condition \n--Where owner type = \"L\".\n\n--Ref - AM-Si1\n--------------------------------------------------------------------------------\n\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect count(sign_id) known_signs\ninto #temp_known_signs\nfrom sign s\nwhere s.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand replace_date is null\nand sign_width > 0\nand sign_height > 0\nand (install_date is not null or (condition_date > ((select targetDate from #tempDate) - 1095) and condition <> 'U'))\nand (bground_material is not null and bground_material not in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN'));\n\nselect count(sign_id) all_signs\ninto #temp_all_signs\nfrom sign s\nwhere s.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand replace_date is null;\n\nselect (cast(known_signs as decimal) / cast(all_signs as decimal))*100 as AM_Si1\nfrom #temp_known_signs, #temp_all_signs;\n\ndrop table #temp_known_signs, #temp_all_signs;","--------------------------------------------------------------------------------\n--Sign asset known\n--Percentage of sign records with known dimensions, material and installation date\n--Where owner type = \"L\".\n\n--Ref - AM-Si1\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2016/17 would have a date of 2017-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect sign_id, road_id, location, sign_width, sign_height, bground_material, install_date, condition, condition_date\nfrom sign s\nwhere s.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand replace_date is null\nand (sign_width = 0\nor sign_width is null\nor sign_height = 0\nor sign_height is null\nor bground_material is null \nor bground_material in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN')\nor (install_date is null\nand (condition_date < ((select targetDate from #tempDate) - 1095) or condition = 'U'\nor condition_date is null or condition is null)));"]
["Asset Management","Asset Inventory","Signs","AM-Si2","Active","Sign asset associated to a 'road'\nThe percentage of the number of sign records located within the extents of the road as defined in the carriageway table and with an offset less than 40m, or with a recorded GPS location","sign\ncarr_way\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Accuracy","Low","<95",">=95",">=98","Confirm that sign assets are correctly located in relation to how the network is defined by the carriageway table","AM: Poor data has the potential to impact our investment decision making and lack of maintenance response","Untrained staff collecting as-built inventory data\nLack of data validation checks as part of adding inventory record to RAMM\nDisplacements recorded using an uncalibrated trip meter","GPS location is based on NTZM within the limits of the North, South, Stewart and Chatham Islands.","--------------------------------------------------------------------------------\n--Sign asset correctly located\n--Percentage of sign records within limits of roads (>= minimum carriageway start and <= maximum carriageway end) and with an offset less than 40m,\n--or with a recorded GPS location.\n--Where owner type = \"L\".\n\n--Ref - AM-Si2\n--------------------------------------------------------------------------------\n\nselect count(sign_id) all_signs\ninto #temp_all_signs\nfrom sign s\nwhere s.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand replace_date is null;\n\n--Find the minimum start and maximum end for each road in carr_way\nselect road_id, min(carrway_start_m) min_start, max(carrway_end_m) max_end\ninto #tmp_min_max\nfrom carr_way\nwhere carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\ngroup by carr_way.road_id;\n\nselect count(*) located_signs\ninto #temp_located_signs\nfrom sign s\n  join #tmp_min_max tm on (s.road_id = tm.road_id) \nwhere s.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand replace_date is null\nand ((s.location >= tm.min_start\n    and s.location <= tm.max_end\n    and offset < 40)\nor (easting between 1015108 and 2592577\nand northing between 4681642 and 6307860));\n\nselect (cast(located_signs as decimal) / cast(all_signs as decimal))*100 as AM_Si2\nfrom #temp_located_signs, #temp_all_signs;\n\ndrop table #temp_located_signs, #tmp_min_max, #temp_all_signs;","--------------------------------------------------------------------------------\n--Sign asset associated to a 'road'\n--Percentage of sign records within limits of roads (>= minimum carriageway start and <= maximum carriageway end) and with an offset less than 40m.\n--Where owner type = \"L\".\n\n--Ref - AM-Si2\n--------------------------------------------------------------------------------\n\n--Find the minimum start and maximum end for each road in carr_way\nselect road_id, min(carrway_start_m) min_cway_start, max(carrway_end_m) max_cway_end\ninto #tmp_min_max\nfrom carr_way\nwhere carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\ngroup by carr_way.road_id;\n\nselect sign_id, s.road_id, tm.min_cway_start, tm.max_cway_end, location, offset, s.easting, s.northing\nfrom sign s\n  join #tmp_min_max tm on (s.road_id = tm.road_id)\n   where (s.location < tm.min_cway_start\n    or s.location > tm.max_cway_end\n    or offset >= 40\n    or location is null\n    or offset is null)\n    and ((easting not between 1015108 and 2592577\nor northing not between 4681642 and 6307860\nor easting is null\nor northing is null))\nand s.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand replace_date is null;\n\ndrop table #tmp_min_max;"]
["Asset Management","Asset Inventory","Signs","AM-Si3","Active","Sign replacement activity\nThe number of signs recorded as replaced in RAMM for the three year period to the end of the reported financial year as a percentage of the total number of signs on the network","sign\nroadnames","Excludes roads where road_type <> 'L'","Timeliness","Low","<7",">=7",">=10","Confirm that sign asset data is being maintained as signs are replaced/renewed","AM: Poor data has the potential to impact our investment decision making, understanding of the effectiveness of investment and asset valuations","Poor, or lack of process for updating the asset inventory table following activity by the maintenance contractor",null,"--------------------------------------------------------------------------------\n--Sign Renewal Activity in last 3 Financial Years\n--Percentage of sign records replaced during last three financial years.\n--Where road_type = 'L'\n\n--Ref - AM-Si3\n--------------------------------------------------------------------------------\n\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect count(sign_id) all_signs\ninto #temp_all_signs\nfrom sign s\nwhere s.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand replace_date is null;\n\nselect count(sign_id) as replace_sign \ninto #temp_replaced_signs\nfrom sign s\nwhere s.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand replace_date between ((select targetDate from #tempDate) - 1095) and (select targetDate from #tempDate);\n\nselect (cast(replace_sign as decimal)) / (cast(all_signs as decimal)) * 100 as AM_Si3 --pc_signs_replaced\nfrom #temp_replaced_signs, #temp_all_signs;\n\ndrop table #temp_replaced_signs, #temp_all_signs, #tempDate;","Not applicable"]
["Asset Management","Asset Inventory","Railings","AM-Ra1","Active","Railing assets known\nThe percentage of the number of railing records with a recorded length (e.g. not Null or zero length), material (e.g. not \"unknown\") and installation date or recent known condition","railings\nroadnames","Excludes roads where road_type <> 'L'\nExcludes records where asset_owner <> 'L'","Completeness","Moderate","<30",">=30",">=50","Confirm completeness of railing assets attribute data is adequate for planning and decision-making purposes","AM: Poor data has the potential to impact our renewal programme development, investment decision making, asset valuations and LoS delivery","Attribute data not known for 'historic' assets\nPoor, or lack of, clarity on what attribute fields are to be collected and maintained for this asset type\nPoor, or lack of quality control undertaken on inventory data","An allowance has been made in the grade ranges for unknown constructed dates of 'older' asset, and the ease and value of estimating these.\nLess than 3 years old has been considered a recent condition date.","--------------------------------------------------------------------------------\n--Railing asset known\n--Percentage of railing records with a recorded length, type, material and installation date or recent known condition \n--Where road type = \"L\"\n\n--Ref - AM-Ra1\n--------------------------------------------------------------------------------\n\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect count(railing_id) known_railings\ninto #temp_known_railings\nfrom railings r\nwhere r.road_id in (select road_id from roadnames\n  where road_type = 'L')\n    and asset_owner = 'L'\nand length_m > 0\nand (railing_type is not null and railing_type not in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN'))\nand (railing_material is not null and railing_material not in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN'))\nand (install_date is not null or (condition_date > ((select targetDate from #tempDate) - 1095) and condition <> 'U'));\n\nselect count(railing_id) all_railings\ninto #temp_all_railings\nfrom railings r\nwhere r.road_id in (select road_id from roadnames\n  where road_type = 'L')\n    and asset_owner = 'L';\n\nselect (cast(known_railings as decimal) / cast(all_railings as decimal))*100 as AM_Ra1 --pc_known_railings\nfrom #temp_known_railings, #temp_all_railings;\n\ndrop table #temp_known_railings, #temp_all_railings;","--------------------------------------------------------------------------------\n--Railing asset known\n--Percentage of railing records with a recorded length, type, material and installation date.\n--Where road type = \"L\"\n\n--Ref - AM-Ra1\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2016/17 would have a date of 2017-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect railing_id, road_id, start_m, end_m, length_m, railing_type, railing_material, install_date, condition, condition_date\nfrom railings r\nwhere r.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand (length_m = 0\nor length_m is null\nor railing_type is null \nor railing_type in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN')\nor railing_material is null \nor railing_material in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN')\nor (install_date is null\nand (condition_date < ((select targetDate from #tempDate) - 1095) or condition = 'U'\nor condition_date is null or condition is null)));"]
["Asset Management","Asset Inventory","Railings","AM-Ra2","Active","Railing asset records maintained\nThe percentage of the total railing length entered in RAMM for the three year period to the end of the reported financial year","railings\nroadnames","Excludes roads where road_type <> 'L'\nExcludes records where asset_owner <> 'L'","Timeliness","Low","<3",">=3",">=6","Confirm that railing asset data is being maintained as new railings are installed/vested, and others are renewed","AM: Poor data has the potential to impact our investment decision making, understanding of the effectiveness of investment and asset valuations","Poor, or lack of process for updating the asset inventory table following renewal activity\nRenewal activity is delivered in quantities less than that identified for capitalisation by internal processes",null,"--------------------------------------------------------------------------------\n--Railing asset records maintained\n--Percentage of railing length added in the previous three financial years.  \n--Where road type = \"L\"\n\n--Ref - AM-Ra2\n--------------------------------------------------------------------------------\n\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect sum(length_m) as railing_act \ninto #temp_railing_act\nfrom railings r\nwhere r.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and asset_owner = 'L'\n  and added_on between ((select targetDate from #tempDate) - 1094) and (select targetDate from #tempDate);\n\nselect sum(length_m) as railing_all \ninto #temp_railing_all\nfrom railings r\nwhere r.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and asset_owner = 'L';\n\nselect (cast(railing_act as decimal)) / (cast(railing_all as decimal)) * 100 as AM_Ra2  --pc_railing_added\nfrom #temp_railing_act, #temp_railing_all;\n\ndrop table #temp_railing_act, #temp_railing_all, #tempDate;","Not applicable"]
["Asset Management","Asset Inventory","Retaining Walls","AM-RW1","Active","Retaining Wall assets known\nThe percentage of the number of retaining wall records with a recorded length (e.g. not Null or zero length), average height (e.g. not Null or zero), material (e.g. not \"unknown\") and constructed date or recent known condition","retaining_wall\nroadnames","Excludes roads where road_type <> 'L'\nExcludes records where asset_owner <> 'L'","Completeness","Moderate","<60",">=60",">=80","Confirm completeness of retaining wall assets attribute data is adequate for planning and decision-making purposes","AM: Poor data has the potential to impact our renewal programme development, investment decision making, asset valuations and LoS delivery","Attribute data not known for 'historic' assets\nPoor, or lack of, clarity on what attribute fields are to be collected and maintained for this asset type\nPoor, or lack of quality control undertaken on inventory data","An allowance has been made in the grade ranges for unknown constructed dates of 'older' asset, and the ease and value of estimating these.","--------------------------------------------------------------------------------\n--Retaining Wall assets known\n--Percentage of railing records with a recorded length, average height, material and constructed date or recent known condition \n--Where road type = \"L\"\n\n--Ref - AM-RW1\n--------------------------------------------------------------------------------\n\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect count(*) known_ret_walls\ninto #temp_known_ret_walls\nfrom retaining_wall r\nwhere r.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand asset_owner = 'L'\nand length_m > 0\nand height_m_avg > 0\nand (ret_wall_type is not null and ret_wall_type not in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN'))\nand (ms_material is not null and ms_material not in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN'))\nand (constructed is not null or condition_date > ((select targetDate from #tempDate) - 1095) and condition <> 'U');\n\nselect count(*) all_ret_walls\ninto #temp_all_ret_walls\nfrom retaining_wall r\nwhere r.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand asset_owner = 'L';\n\nselect (cast(known_ret_walls as decimal) / cast(all_ret_walls as decimal))*100 as AM_RW1  --pc_known_ret_walls\nfrom #temp_known_ret_walls, #temp_all_ret_walls;\n\ndrop table #temp_known_ret_walls, #temp_all_ret_walls;","--------------------------------------------------------------------------------\n--Retaining Wall assets known\n--Percentage of railing records with a recorded length, average height, material and constructed date.  \n--Where road type = \"L\"\n\n--Ref - AM-RW1\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2016/17 would have a date of 2017-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect retaining_wall_id, road_id, start_m, end_m, length_m, height_m_avg, ret_wall_type, ms_material, constructed, condition, condition_date\nfrom retaining_wall r\nwhere r.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand asset_owner = 'L'\nand (length_m = 0\nor length_m is null\nor height_m_avg = 0\nor height_m_avg is null\nor ret_wall_type is null \nor ret_wall_type in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN')\nor ms_material is null \nor ms_material in ('U', 'UN', 'UK', 'UNK', 'UKN', 'UNKN')\nor (constructed is null\nand (condition_date < ((select targetDate from #tempDate) - 1095) or condition = 'U'\nor condition_date is null or condition is null)));"]
["Asset Management","Asset Inventory","Retaining Walls","AM-RW2","Active","Retaining wall asset records maintained\nThe percentage of the total retaining wall length entered in RAMM for the five year period to the end of the reported financial year","retaining_wall\nroadnames","Excludes roads where road_type <> 'L'\nExcludes records where asset_owner <> 'L'","Timeliness","Low","<1",">=1",">=2","Confirm that retaining wall asset data is being maintained as new retaining walls are constructed/vested, and others are renewed","AM: Poor data has the potential to impact our investment decision making, understanding of the effectiveness of investment and asset valuations","Poor, or lack of process for updating the asset inventory table following renewal activity\nRenewal activity is delivered in quantities less than that identified for capitalisation by internal processes\nLack of resources to maintain this dataset in a timely manner",null,"--------------------------------------------------------------------------------\n--Retaining wall asset records maintained\n--Percentage of retaining wall length added in the previous five financial years.  \n--Where road type = \"L\"\n\n--Ref - AM-RW2\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect sum(length_m) as ret_wall_added \ninto #temp_ret_wall_added\nfrom retaining_wall r\nwhere r.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand asset_owner = 'L'\nand r.added_on between ((select targetDate from #tempDate) - 1825) and (select targetDate from #tempDate);\n\nselect sum(length_m) as ret_wall_tot \ninto #temp_ret_wall_tot\nfrom retaining_wall r\nwhere r.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand asset_owner = 'L';\n\nselect (cast(ret_wall_added as decimal)) / (cast(ret_wall_tot as decimal)) * 100 as AM_RW2  --pc_ret_wall_added\nfrom #temp_ret_wall_added, #temp_ret_wall_tot;\n\ndrop table #temp_ret_wall_added, #temp_ret_wall_tot, #tempDate;","Not applicable"]
["Asset Management","Asset Inventory","Streetlights","AM-SL1","Active","Streetlights associated with a 'road'\nThe percentage of the number of streetlight pole records within limits of road as defined in the carriageway table and with an offset less than 40m, or a recorded GPS location","sl_pole\ncarr_way\nroadnames","Excludes roads where road_type <> 'L' or owner_type <> 'L'","Accuracy","Low","<95",">=95",">=98","Confirm that streetlight assets are correctly located in relation to how the network is defined by the carriageway table","AM: Poor data has the potential to impact our investment decision making and lack of maintenance response","Untrained staff collecting as-built inventory data\nLack of data validation checks as part of adding inventory record to RAMM\nDisplacements recorded using an uncalibrated trip meter","GPS location is based on NTZM within the limits of the North, South, Stewart and Chatham Islands.","--------------------------------------------------------------------------------\n--Streetlights associated with a 'road'\n--Percentage of streetlight pole records within limits of road (>= minimum carriageway start and <= maximum carriageway end) and an offset less than 40m,\n--or a recorded GPS location\n--Where road type = \"L\".\n\n--Ref - AM-SL1\n--------------------------------------------------------------------------------\n\n--Find the minimum start and maximum end for each road in carr_way\nselect road_id, min(carrway_start_m) min_start, max(carrway_end_m) max_end\ninto #temp_min_max\nfrom carr_way\nwhere carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\ngroup by carr_way.road_id;\n\nSelect count(*) located_pole\ninto #temp_located_pole\nfrom sl_pole s\n  join #temp_min_max tm on (s.road_id = tm.road_id and (s.location >= tm.min_start\n    and s.location <= tm.max_end))\nwhere s.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand replace_date is null\nand ((s.location >= tm.min_start\n    and s.location <= tm.max_end\n    and offset < 40)\nor (easting between 1015108 and 2592577\nand northing between 4681642 and 6307860));\n\nSelect count(*) all_pole\ninto #temp_all_pole\nfrom sl_pole s\nwhere s.road_id in (select road_id from roadnames\n  where road_type = 'L')\n\nand replace_date is null;\n\nselect (cast(located_pole as decimal)/cast(all_pole as decimal))*100 as AM_SL1  --pc_light_on_road\nfrom #temp_located_pole, #temp_all_pole;\n\ndrop table #temp_min_max, #temp_located_pole, #temp_all_pole;","--------------------------------------------------------------------------------\n--Streetlights associated with a 'road'\n--Percentage of streetlight pole records within limits of road (>= minimum carriageway start and <= maximum carriageway end) and an offset less than 40m.\n--Where road type = \"L\".\n\n--Ref - AM-SL1\n--------------------------------------------------------------------------------\n\n--Find the minimum start and maximum end for each road in carr_way\nselect road_id, min(carrway_start_m) min_cway_start, max(carrway_end_m) max_cway_end\ninto #temp_min_max\nfrom carr_way\nwhere carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\ngroup by carr_way.road_id;\n\nselect pole_id, s.road_id, tm.min_cway_start, tm.max_cway_end, location, offset, s.easting, s.northing\nfrom sl_pole s\n  join #temp_min_max tm on (s.road_id = tm.road_id)\n   where (s.location < tm.min_cway_start\n    or s.location > tm.max_cway_end\n    or offset >= 40\n    or location is null\n    or offset is null)\n    and (easting not between 1015108 and 2592577\nor northing not between 4681642 and 6307860\nor easting is null\nor northing is null)\nand s.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand replace_date is null;\n\ndrop table #temp_min_max;"]
["Asset Management","Asset Inventory","Streetlights","AM-SL2","Active","Streetlights records have a light\nThe percentage of the number of streetlight pole records with at least one associated light record","sl_pole\nsl_bracket\nsl_light\nroadnames","Excludes roads where road_type <> 'L'","Completeness","Low","<95",">=95",">=98","Confirm that streetlight asset records are complete with a pole, bracket and light component","AM: Poor data has the potential to impact our investment decision making and asset valuation","Untrained staff collecting as-built inventory data\nLack of data validation checks as part of adding inventory record to RAMM",null,"--------------------------------------------------------------------------------\n--Streetlights records have a light\n--Percentage of streetlight pole records with at least one light record.  \n--Where road_type = \"L\"\n\n--Ref - AM-SL2\n--------------------------------------------------------------------------------\n\n--No poles with brackets with light \nselect count(distinct p.pole_id) as pole_with_light\ninto #temp_pole_with_light\nfrom sl_pole p\njoin sl_bracket b\non p.pole_id = b.pole_id\njoin sl_light l\non b.bracket_id = l.bracket_id  \n      where p.road_id in (select road_id from roadnames\n  where road_type = 'L')    \nand p.replace_date is null\nand b.replace_date is null\nand b.bracket_id is not null\nand l.light_replace_date is null\nand l.light_id is not null;\n\nSelect count(*) all_pole\ninto #temp_all_pole\nfrom sl_pole s\nwhere s.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand replace_date is null;\n\nselect (cast(pole_with_light as decimal)/cast(all_pole as decimal))*100 as AM_SL2  --pc_sl_pole_with_light\nfrom #temp_pole_with_light, #temp_all_pole;\n\ndrop table #temp_pole_with_light, #temp_all_pole;","--------------------------------------------------------------------------------\n--Streetlights records have a light\n--Percentage of streetlight pole records with at least one light record.  \n--Where road_type = \"L\"\n\n--Ref - AM-SL2\n--------------------------------------------------------------------------------\n\n--No poles with brackets with light\t\nselect p.pole_id, p.road_id, p.location, b.bracket_id, l.light_id\nfrom sl_pole p\nleft join sl_bracket b\non p.pole_id = b.pole_id\nand b.replace_date is null\nleft join sl_light l\non b.bracket_id = l.bracket_id\t\nand l.light_replace_date is null\nwhere p.road_id in (select road_id from roadnames\n  where road_type = 'L')\t\nand p.replace_date is null\nand (b.bracket_id is null\nor l.light_id is null);"]
["Asset Management","Asset Inventory","Streetlights","AM-SL3","Active","Streetlight pole replacement activity\nThe number of streetlight poles recorded as replaced in RAMM for the three year period to the end of the reported financial year as a percentage of the total number of signs on the network","sl_pole\nroadnames","Excludes roads where road_type <> 'L'","Timeliness","Low","<2",">=2",">=5","Confirm that streetlight asset data is being maintained as streetlight poles are replaced/renewed","AM: Poor data has the potential to impact our investment decision making, understanding of the effectiveness of investment and asset valuations","Poor, or lack of process for updating the asset inventory table following activity by the maintenance contractor\nLack of resources for the timely maintenance of this dataset",null,"--------------------------------------------------------------------------------\n--Streetlight pole replacement activtiy\n--Percentage of streetlight poles replaced during the last three financial years.\n--Where road type = \"L\"\n\n--Ref - AM-SL3\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect count(pole_id) replace_pole \ninto #temp_replaced_sl_poles\nfrom sl_pole p\nwhere p.road_id in (select road_id from roadnames\n  where road_type = 'L')\n  and replace_date between ((select targetDate from #tempDate) - 1095) and (select targetDate from #tempDate);\n  \nSelect count(*) all_pole\ninto #temp_all_pole\nfrom sl_pole s\nwhere s.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand replace_date is null;\n  \nselect (cast(replace_pole as decimal)/cast(all_pole as decimal))*100 as AM_SL3  --pc_pole_replaced\nfrom #temp_replaced_sl_poles, #temp_all_pole;\n\ndrop table #temp_replaced_sl_poles, #temp_all_pole, #tempDate;","Not applicable"]
["Asset Management","Maintenance Activity","Maintenance Activity","AM-MA1","Active","Consistency of pavement, surfacing and shoulder maintenance activity units\nThe average number of different units used (e.g. each, m, m2, hr, etc.) per activity (e.g. In situ stabilisation, digout, pot-hole repair, etc.) during the reported financial year for pavement (PA), surfacing (SU) and shoulder (SH) cost group maintenance activities.","mc_cost",null,"Accuracy","Moderate",">2","<=2","<=1.2","Confirm the usability of the maintenance activity data to support our decision-making processes","AM: Difficulty in using the data in network performance analysis, dTIMS modelling and NPV analysis","No defined process for how maintenance activity will be recorded in the maintenance cost table\nPoorly defined scope for the set up of RAMM Contractor for the transfer of maintenance activity to the maintenance cost table\nLack of understanding of the intended use of the data in the maintenance cost table\nUntrained staff transferring/loading data into the maintenance cost table","Metric result is the average number of units per activity.  This is not weighted by the number of records within each activity.","--------------------------------------------------------------------------------\n--Consistency of maintenance activity units\n--Average number of different units used per activity for reported financial year.\n--For pavement, surfacing and shoulder cost groups only.\n\n--Ref - AM-MA1\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n\nselect activity, count(distinct qty_unit) as mc_units \ninto #temp_mc_units\nfrom mc_cost\nwhere cost_group in ('PA', 'SU', 'SH')\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate)\ngroup by activity;\n\nselect avg(cast(mc_units as decimal)) as AM_MA1 --ave_number_units\nfrom #temp_mc_units;\n\ndrop table #temp_mc_units, #tempDate;","Not applicable"]
["Asset Management","Maintenance Activity","Maintenance Activity","AM-MA2","Active","Pavement, surfacing, shoulder and drainage maintenance activity known\nThe percentage of the number of pavement (PA), surfacing (SU), shoulder (SH) and drainage (DR) cost group maintenance activity records with a known fault type (i.e. not \"unknown\") in reported financial year","mc_cost\nroadnames","Excludes roads where road_type <> 'L'","Completeness","Moderate","<90",">=90",">=95","Confirm that the reason for the maintenance activity can be understood","AM: Potential for network performance, or investment needs, to be fully understood without understanding maintenance activity. ","Untrained staff collecting as-built inventory data\nLack of data validation checks as part of adding inventory record to RAMM",null,"--------------------------------------------------------------------------------\n--Pavement, surfacing, shoulder and drainage maintenance activity known\n--Percentage of records with a known fault type in reported financial year (ie not \"unknown\")\n--For pavement, surfacing, shoulder and drainage cost groups only.\n--Where road type = \"L\"\n\n--Ref - AM-MA2\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect count(transaction_id) as known_fault\ninto #temp_known_fault\nfrom mc_cost mc\nwhere mc.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand cost_group in ('PA', 'SU', 'SH', 'DR')\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate)\nand fault <> 'UNKNOWN' \nand fault is not null;\n\nselect count(transaction_id) as tot_records\ninto #temp_tot_records\nfrom mc_cost mc\nwhere mc.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand cost_group in ('PA', 'SU', 'SH', 'DR')\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\nselect (cast(known_fault as decimal))/(cast(tot_records as decimal)) *100 as AM_MA2 --pc_known_fault\nfrom #temp_known_fault, #temp_tot_records;\n\ndrop table #temp_known_fault, #temp_tot_records, #tempDate;","--------------------------------------------------------------------------------\n--Maintenance activity known\n--Percentage of records with a known fault type in reported financial year (ie not \"unknown\")\n--For pavement, surfacing, shoulder and drainage cost groups only.\n--Where road type = \"L\"\n\n--Ref - AM-MA2\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2016/17 would have a date of 2017-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect transaction_id, road_id, start_m, end_m, cost_group, transaction_date, fault\nfrom mc_cost mc\nwhere mc.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand cost_group in ('PA', 'SU', 'SH', 'DR')\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate)\nand (fault = 'UNKNOWN' \nor fault is null);"]
["Asset Management","Maintenance Activity","Maintenance Activity","AM-MA3","Active","Correctly located shoulder and drainage maintenance activity\nThe percentage of the number of shoulder (SH) and drainage (DR) maintenance activity records recorded at appropriate location (percentage of records not at the start of the road) for the reported financial year","mc_cost\ncarr_way\nroadnames","Excludes roads where road_type <> 'L'","Accuracy","Low","<70",">=70",">=90","Confirm that the location of shoulder and drainage maintenance activity is recorded so it is associated with the correct road section","AM: Maintenance records that are outside of the road as defined in the carriageway table potentially are excluded from analysis and investment decision making","Untrained staff collecting as-built inventory data\nLack of data validation checks as part of adding inventory record to RAMM\nLack of awareness of the consequences if activity recorded at incorrect location","The grade ranges make an allowance for records legitimately recorded at the start of the road.","--------------------------------------------------------------------------------\n--Correctly located shoulder and drainage maintenance activity\n--Percentage of records recorded at appropriate location for all cost groups (percentage of records not at the start of the carriageway) for reported financial year.\n--Where road_type = \"L\"\n\n--Ref - AM-MA3\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n--Select all records\nselect count(transaction_id) as total_records\ninto #temp_total_records\nfrom carr_way\njoin mc_cost on (carr_way.road_id = mc_cost.road_id)\nwhere (start_m >= carr_way.carrway_start_m \n          and start_m < carr_way.carrway_end_m)\nand cost_group in ('SH', 'DR')\nand carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\n--Select records at not at road start\nselect count(transaction_id) as records_not_start\ninto #temp_records_not_start\nfrom carr_way\njoin mc_cost on (carr_way.road_id = mc_cost.road_id)\nwhere (start_m >= carr_way.carrway_start_m \n          and start_m < carr_way.carrway_end_m)\nand cost_group in ('SH', 'DR')\nand carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand start_m > (select min(carrway_start_m) from carr_way where carr_way.road_id = mc_cost.road_id)\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\n--Express as % of records not at road start\nselect (cast(records_not_start as decimal))/(cast(total_records as decimal)) *100 as AM_MA3  --pc_not_at_start\nfrom #temp_records_not_start, #temp_total_records;\n\ndrop table #temp_records_not_start, #temp_total_records, #tempDate;","--------------------------------------------------------------------------------\n--Correctly located maintenance activity\n--Percentage of records recorded at appropriate location for all cost groups (percentage of records not at the start of the carriageway) for reported financial year.\n--Where road_type = \"L\"\n\n--Ref - AM-MA3\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2016/17 would have a date of 2017-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect transaction_id, mc_cost.road_id, start_m, end_m, carrway_start_m, carrway_end_m, cost_group, transaction_date, fault\nfrom carr_way\njoin mc_cost on (carr_way.road_id = mc_cost.road_id)\nwhere (start_m >= carr_way.carrway_start_m \n          and start_m < carr_way.carrway_end_m)\nand cost_group in ('SH', 'DR')\nand carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand (start_m = (select min(carrway_start_m) from carr_way where carr_way.road_id = mc_cost.road_id)\nor start_m is null)\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);"]
["Asset Management","Maintenance Activity","Maintenance Activity","AM-MA4","Active","Level of pavement, surfacing, shoulder and drainage maintenance activity known\nThe percentage of the number of pavement (PA), surfacing (SU), shoulder (SH) and drainage (DR) cost group maintenance activity records with a known quantity (e.g. not Null, zero or negative) for the reported financial year","mc_cost\nroadnames","Excludes roads where road_type <> 'L'","Completeness","High","<90",">=90",">=95","Confirm that the quantity of maintenance activity has been adequately captured","AM: Difficulty in using the data in network performance analysis, dTIMS modelling and NPV analysis","No defined process for how maintenance activity will be recorded in the maintenance cost table\nPoorly defined scope for the set up of RAMM Contractor for the transfer of maintenance activity to the maintenance cost table",null,"--------------------------------------------------------------------------------\n--Level of pavement, surface, shoulder and drainage maintenance activity known\n--Percentage of records with a known quantity (eg not null or zero or -ve) for reported financial year.\n--For pavement, surfacing, shoulder and drainage cost groups only\n\n--Ref - AM-MA4\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect count(transaction_id) as known_qty\ninto #temp_known_qty\nfrom mc_cost mc\nwhere mc.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand cost_group in ('PA', 'SU', 'SH', 'DR')\nand (quantity > 0 or adj_quantity >0)\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\nselect count(transaction_id) as tot_records\ninto #temp_tot_records\nfrom mc_cost mc\nwhere mc.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand cost_group in ('PA', 'SU', 'SH', 'DR')\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);\n\nselect (cast(known_qty as decimal))/(cast(tot_records as decimal)) *100 as AM_MA4  --pc_known_qty\nfrom #temp_known_qty, #temp_tot_records;\n\ndrop table #temp_known_qty, #temp_tot_records, #tempDate;","--------------------------------------------------------------------------------\n--Level of maintenance activity known\n--Percentage of records with a known quantity (eg not null or zero or -ve) for reported financial year.\n--For pavement, surfacing, shoulder and drainage cost groups only\n\n--Ref - AM-MA4\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2016/17 would have a date of 2017-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\nselect transaction_id, road_id, start_m, end_m, cost_group, transaction_date, quantity, adj_quantity\nfrom mc_cost mc\nwhere mc.road_id in (select road_id from roadnames\n  where road_type = 'L')\nand cost_group in ('PA', 'SU', 'SH', 'DR')\nand (quantity <= 0 \nor quantity is null)\nand (adj_quantity <= 0\nor adj_quantity is null)\nand transaction_date between ((select targetDate from #tempDate) - 364) and (select targetDate from #tempDate);"]
["Asset Management","Condition","Rating","AM-Co1","Active","Road rating data current\nThe percentage of total sealed network length, based on treatment lengths, with a latest road rating record less than two years old at end of reported financial year","rating\ntreatment_length\nroadnames","Excludes pavement type 'Bridge'\nExcludes roads where road_type <> 'L'\nExcludes disabled treatment lengths","Completeness","High","<70",">=70",">=95","Confirm that the sealed network has recent visual road rating data","AM: Lack of condition data has the potential to affect our ability to identify maintenance and renewal needs, make investment decisions or measure effectiveness of investment","Latest rating survey more than 2 years old\nPoor, or lack of process for loading the latest rating survey in a timely manner",null,"--------------------------------------------------------------------------------\n--Rating data current\n--Percentage of rating data records less than 2 years old at end of reported financial year\n--Where road type = \"L\" and latest = \"L\".\n\n--Ref - AM-Co1\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n--Select total sealed network length\nselect sum(t.tl_length_m) as total_lgth\ninto #temp_total_lgth\nfrom treatment_length t\n    where t.pavement_type in ('T', 'S', 'C')\n    and t.tl_disabled = 'N'\n    and t.road_id in (select road_id from roadnames\n        where road_type = 'L');\n   \n--Select sealed network length with rating reading less than 2 years old      \nselect sum(r.end_m - r.start_m) as m_rating\ninto #temp_m_temp_rating\nfrom rating r\nleft join treatment_length t\non r.road_id = t.road_id\nand r.start_m >= t.tl_start_m\nand r.start_m < t.tl_end_m\n  where t.pavement_type in ('T', 'S', 'C')\n  and t.tl_disabled = 'N'\n  and t.road_id in (select road_id from roadnames\n      where road_type = 'L')\n  and rating_date >= ((select targetDate from #tempDate) - 729) \n  and latest = 'L';\n\nupdate #temp_m_temp_rating      \nset m_rating = 0        \nwhere m_rating is null;\n\n--Express as % network length with recent(2 years) rating reading\nselect (cast(m_rating as decimal)) / (cast(total_lgth as decimal)) * 100  as AM_Co1  --pc_rating_recent\nfrom #temp_m_temp_rating, #temp_total_lgth;\n\ndrop table #temp_m_temp_rating, #temp_total_lgth, #tempDate;","--------------------------------------------------------------------------------\n--Rating data current\n--Percentage of rating data records less than 2 years old at end of reported financial year\n--Where road type = \"L\" and latest = \"L\".\n\n--Ref - AM-Co1\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2016/17 would have a date of 2017-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n\nselect t.road_id, treat_length_id, tl_start_m, tl_end_m, tl_length_m, r.rating_date\nfrom treatment_length t\nleft join rating r\non r.road_id = t.road_id\nand r.start_m >= t.tl_start_m\nand r.start_m < t.tl_end_m\n  where t.pavement_type in ('T', 'S', 'C')\n  and t.tl_disabled = 'N'\n  and t.road_id in (select road_id from roadnames\n      where road_type = 'L')\n  and (rating_date < ((select targetDate from #tempDate) - 729) or rating_date is null)\n  and latest = 'L';"]
["Asset Management","Condition","Rating","AM-Co2","Active","Rating data locations valid\nThe percentage of the number latest rating records less than 2 years old at the end of the reported financial year with a valid inspection section (inspection start >= rating start and start of road, inspection end <= rating end and end of road)","rating\ncarr_way\nroadnames","Excludes roads where road_type <> 'L'","Accuracy","Moderate","<95",">=95",">=98","Confirm inspection section locational accuracy against rating sections and the extents of the network as defined by the carriageway table","AM: Any data located outside of the network as defined in the carriageway table is likely not be included in any analysis or investment decision making","Unskilled staff generating rating survey and loading results to RAMM\nPoor or lack of data validation checks associated with collection and recording of road rating surveys\nNetwork not updated following validation of road lengths","This metric reports a \"NA\" (not applicable) result when AM-Co1 = 0.0.  In this scenario the metric does not contribute to the aggregated dashboard results, or the calculation of the \"Score\".","--------------------------------------------------------------------------------\n--Latest rating data locations valid\n--Percentage of latest rating records with a valid inspection section\n--(inspection start >= rating start and start of road, inspection end <= rating end and end of road)\n\n--Ref - AM-Co2\n--------------------------------------------------------------------------------\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n--Find the minimum start and maximum end for each road in carr_way\nselect road_id, min(carrway_start_m) min_start, max(carrway_end_m) max_end\ninto #tmp_min_max\nfrom carr_way\nwhere carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand carr_way.owner_type = 'L'\ngroup by carr_way.road_id;\n\nselect count(*) as valid_rating\ninto #temp_rating_valid\nfrom rating r\njoin #tmp_min_max tm on (r.road_id = tm.road_id and (r.insp_start_m >= tm.min_start\n    and r.insp_end_m <= tm.max_end))\nwhere r.latest = 'L'\nand rating_date between ((select targetDate from #tempDate) - 729) and (select targetDate from #tempDate)\nand (r.insp_start_m >= r.start_m and r.insp_start_m < r.end_m)\nand (r.insp_end_m > r.start_m and r.insp_end_m <= r.end_m)\n      --and t.pavement_type in ('T', 'S', 'C')\n      and r.road_id in (select road_id from roadnames\n          where road_type = 'L');\n\nselect count(*) as tls_rating\ninto #temp_rating_tot\nfrom rating r\nwhere r.latest = 'L'\nand rating_date between ((select targetDate from #tempDate) - 729) and (select targetDate from #tempDate)\n      --and t.pavement_type in ('T', 'S', 'C')\n      and r.road_id in (select road_id from roadnames\n          where road_type = 'L');\n\nselect (cast(valid_rating as decimal)) / (cast(tls_rating as decimal)) *100 as AM_Co2  --pc_rating_location_valid\nfrom #temp_rating_valid, #temp_rating_tot;\n\ndrop table #temp_rating_valid, #temp_rating_tot, #tmp_min_max, #tempDate;","--------------------------------------------------------------------------------\n--Latest rating data locations valid\n--Percentage of latest rating records with a valid inspection section\n--(inspection start >= rating start and start of road, inspection end <= rating end and end of road)\n\n--Ref - AM-Co2\n--------------------------------------------------------------------------------\n\n--Initialise desired financial year by changing the date below.\n--Example: Results for 2016/17 would have a date of 2017-06-30\ncreate table #tempDate(targetDate datetime);\ninsert #tempDate(targetDate) values('2019-06-30');\n\n\n--Find the minimum start and maximum end for each road in carr_way\nselect road_id, min(carrway_start_m) min_cway_start, max(carrway_end_m) max_cway_end\ninto #tmp_min_max\nfrom carr_way\nwhere carr_way.road_id in (select road_id from roadnames\n        where road_type = 'L')\nand carr_way.owner_type = 'L'\ngroup by carr_way.road_id;\n\nselect rating_id, r.road_id, r.insp_start_m, r.insp_end_m, r.start_m, r.end_m, tm.min_cway_start, tm.max_cway_end, r.survey_number\nfrom rating r\njoin #tmp_min_max tm on (r.road_id = tm.road_id)\nwhere r.latest = 'L'\n and (r.insp_start_m < tm.min_cway_start\n    or r.insp_end_m > tm.max_cway_end)\nand rating_date between ((select targetDate from #tempDate) - 729) and (select targetDate from #tempDate)\nand (r.insp_start_m >= r.start_m and r.insp_start_m < r.end_m)\nand (r.insp_end_m > r.start_m and r.insp_end_m <= r.end_m)\n      --and t.pavement_type in ('T', 'S', 'C')\n      and r.road_id in (select road_id from roadnames\n          where road_type = 'L');\n\n\ndrop table #tmp_min_max;"]

{"sheet":"ONRC Change Register","index":1,"n_rows":41,"n_cols":14,"rows_kept":41,"cut":false,"hidden":false,"merged":[]}
[null,null,null,"Metric and Indicator Change Register | ONRC"]
[null,null,null,"Data Quality Project"]
[]
["DQ Report","Category","Sub-Category","Metric Reference","Metric Description","Status","Primary Dimension","2015/16 Reports – v2.0\nChanges following workshop 3 and feedback received on the 2014/15 reports","2016/17 Reports – v3.0\nChanges identified through case for change identification and move to interrogating the PMRT data ","2016/17 Reports – v3.01\nChanges identified through feedback received on the 2016/17 reports","2017/18 Reports – v4.0\nChanges as part of annual update reporting (R15)","2017/18 Reports - v5.0\nChanges following feedback on draft reports","2017/18 Reports - v5.1\nChanges as part of February 2019 reissue","2018/19 Reports - v6.0\nChanges as part of annual update reporting"]
["ONRC","Network","Carriageway","Ca1a","Rural number of lanes matches carriageway width\nThe percentage of the Rural sealed network length, based on carriageways, with an alignment between the recorded carriageway width and number of lanes. Alignments are as follows:\n- Number of lanes = 1 and width <6m, \n- Number of lanes = 2 and width >4m or <17m, \n- Number of lanes > 2 and width >9m","Active","Accuracy","No change","Minor change to metric description wording","No change","No change","Primary Dimension changed to Accuracy","No change","Metric description updated"]
["ONRC","Network","Carriageway","Ca1b","Urban number of lanes matches carriageway width\nThe percentage of the Urban sealed network length, based on carriageways, with an alignment between the recorded carriageway width and number of lanes. Alignments are as follows:\n- Number of lanes = 1 and width <6m, \n- Number of lanes = 2 and width >4m or <17m, \n- Number of lanes > 2 and width >9m","Active","Accuracy","No change","Minor change to metric description wording","No change","No change","Primary Dimension changed to Accuracy","No change","Metric description updated"]
["ONRC","Network","Carriageway","Ca2","ONRC categories are assigned\nThe proportion of the number of carriageway section records in the carriageway table with an assigned ONRC category ","Active","Completeness","No change","Minor change to metric description wording","No change","No change","No change","No change","No change"]
["ONRC","Network","Carriageway","Ca3a","Rural carriageways are generally not short\nThe proportion of the number of Rural sealed carriageway records with a length greater than 50m (ie. not short)","Active","Accuracy","Metric changed to report proportion greater than 50m in length.","Minor change to metric description wording","No change","No change","No change","No change","No change"]
["ONRC","Network","Carriageway","Ca3b","Urban carriageways are generally not short\nThe proportion of the number of Urban sealed carriageway records with a length greater than 20m (ie. not short)","Active","Accuracy","No change","Minor change to metric description wording","No change","No change","Updated grade range to be consistent with Ca3a","No change","No change"]
["ONRC","Network","Carriageway","Ca4","Sealed/unsealed network correctly defined\nThe percentage of the network length, based on carriageways, with a sealed pavement type and a sealed material surface record, or an unsealed pavement type with either no surface record or an unsealed material surface record.","Active","Accuracy","---","New metric added replacing TL3a and TL3b.  Using the PMRT data allows for the surface structure table to be interrogated as originally planned.","No change","No change","Updated to include recording of METAL surfaces on unsealed network sections as valid","No change","No change"]
["ONRC","Network","Treatment Length","TL1a","Treatment Lengths are generally not short\nThe proportion of the number of sealed treatment length records that are not very short.  Very short is considered to be:\n- <20m Urban\n- <100m Rural","Active","Accuracy","No change","Minor change to metric description wording","No change","No change","No change","No change","No change"]
["ONRC","Network","Treatment Length","TL1b","Treatment Lengths are not too long\nThe proportion of the number of sealed treatment length records that are not exceptionally long.  Exceptionally long is considered to be:\n- >500m Urban\n- >1km Rural","Active","Accuracy","No change","Minor change to metric description wording","No change","No change","No change","No change","No change"]
["ONRC","Network","Treatment Length","TL2","Treatment Lengths match major surfaces\nThe proportion of the number of treatment length records where the major top surface covers at least 80% of the treatment length","Active","Accuracy","Metric changed to report proportion of treatment length records rather than length to be consistent with other treatment length sub-category metrics","Minor change to metric description wording","No change","No change","No change","No change","No change"]
["ONRC","Network","Treatment Length","TL4","Network coverage with STE reading\nThe proportion of the number sealed treatment length records with a recorded Smooth Travel Exposure (STE) value","Active","Completeness","No change","Minor change to metric description wording","No change","No change","No change","No change","Metric description updated"]
["ONRC","Network","Treatment Length","TL5","Treatment Lengths match renewals\nThe proportion of the number of treatment length records where the major top surface covers at least 80% of the treatment length where the surface date in the reported financial year.","Retired","Timeliness",null,"Metric retired","---","---","---","---","---"]
["ONRC","Network","Treatment Length","TL5.1","Treatment Lengths match renewals\nThe proportion of the number of treatment length records where the major top surface covers at least 80% of the treatment length where the surface date in the reported financial year.","Active","Timeliness","---","New metric introduced to replace TL5","No change","No change","No change","No change","No change"]
["ONRC","Asset Inventory","Surfacing","Su1","Last years' renewals as recorded in RAMM\nPercentage of the sealed network length, based on treatment lengths, with a surface recorded entered into RAMM for the reported financial year","Retired","Timeliness","No change","Minor change to metric description wording","No change","Metric retired","---","---","---"]
["ONRC","Asset Inventory","Surfacing","Su2","Surface records correctly located\nThe proportion of the number of surface records entered to RAMM with a surface date in the reported financial year that have a recorded start and end displacement within the limits of the road (as defined in the carriageway table), and have a recorded surface width no greater than 2m wider than the carriageway width","Active","Accuracy","No change","Minor change to metric description wording","No change","No change","No change","No change","No change"]
["ONRC","Asset Inventory","Surfacing","Su3","Surface records with original cost\nThe proportion of surface records entered to RAMM with a surface date greater than 30 June 2016 and an original cost recorded where the recorded works category is 212 or 214","Active","Completeness","No change","Minor change to metric description wording","No change","No change","Changed to only include work category codes of 212 and 214. Minor change to metric description wording","The change to only include work category codes of 212 and 214 is now fully implemented. ","No change"]
["ONRC","Asset Inventory","Surfacing","Su4","Surface records with work origin\nThe proportion of surface records entered to RAMM with a surface date greater than 30 June 2016 and a work origin and category recorded","Active","Completeness","No change","Minor change to metric description wording","No change","Metric result reported and included in summary results for 1st time","Minor change to metric description wording","No change","No change"]
["ONRC","Asset Inventory","Surfacing","Su5","Surface records newer than pavement\nThe percentage of the length, based on treatment lengths, where the major top surface is newer than underlying major pavement layer and the recorded pavement layer date is less than 3.5 years old at the end of the reported financial year.","Active","Accuracy","SQL script updated to correctly report 3.5 year period.  Previously reported a 3 month to 3.5 year period.","Minor change to metric description wording","No change","Dates range updated to end 30/06/2018","Primary Dimension changed to Accuracy","No change","No change"]
["ONRC","Maintenance Activity","Maintenance Activity","MA1","Complete pavement and surface maintenance activity\nThe number of months in the reported financial year with at least one maintenance activity record with a cost group of pavement (PA) or surfacing (SU) on the sealed network","Active","Completeness","No change","No change","No change","Dates updated to 2017/18 financial year","Primary Dimension changed to Completeness","No change","Metric description updated"]
["ONRC","Maintenance Activity","Maintenance Activity","MA2","Correctly located pavement and surface maintenance activity\nThe proportion of the number of pavement (PA) and surfacing (SU) cost group maintenance activity records on the sealed network for the reported financial year, recorded at an appropriate location (Proportion of records not at the start of the road)","Active","Accuracy","Metric updated to report on pavement and surfacing cost groups only.  These are the ones impacting the cost efficiency measures.","No change","No change","Dates updated to 2017/18 financial year","No change","No change","Metric description updated"]
["ONRC","Maintenance Activity","Maintenance Activity","MA3","Maintenance effort recorded\nProportion of pavement (PA) and surfacing (SU) cost group records on sealed network with a cost recorded for the reported financial year (ie not $0.00 or null)","Retired","Accuracy","No change","Metric retired","---","---","---","---","---"]
["ONRC","Maintenance Activity","Maintenance Activity","MA4","Pavement and surface maintenance activity has a valid location\nThe proportion of the number of pavement (PA) and surfacing (SU) cost group maintenance activity records on the sealed network for the reported financial year located within the extents of the road as defined in the carriageway table","Active","Accuracy","No change","No change","No change","Dates updated to 2017/18 financial year","No change","No change","Metric description updated"]
["ONRC","Condition","Roughness","Ro1","Roughness survey within 2.5 years\nThe percentage of the sealed network length, based on carriageway sections, with a latest roughness reading less than 2.5 years old at the end of the reported financial year","Active","Completeness","No change","Minor change to metric description wording","No change","Dates range updated to end 30/06/2018","Primary Dimension changed to Completeness","No change","No change"]
["ONRC","Condition","Roughness","Ro2","HSD Roughness survey within 2.5 years\nPercentage of the sealed network length with latest HSD roughness data less than 2.5 years old (from 30 June of reported financial year) (excludes pavement type 'Bridge')","Retired","Accuracy","No change","Minor change to metric description wording","No change","Metric retired","---","---","---"]
["ONRC","Condition","Roughness","Ro3","Roughness data has valid location\nThe percentage of all latest roughness readings records located within the extents of the road as defined in the carriageway table","Active","Accuracy","---","New metric added.  This metric was previously dropped due to a lack of confidence in the result when run against the DW data.","No change","No change","No change","No change","No change"]
["ONRC","Demand/Use","Traffic Count","TC1","Well targeted traffic count programme\nThe proportion of the total network Vehicle Kilometres Travelled (VKT), based on carriageway sections, with a latest traffic count less than 5 years old at the end of the reported financial year.","Active","Completeness","No change","Minor change to metric description wording","No change","Dates range updated to end 30/06/2018","Updated to include both sealed and unsealed network. \nPrimary Dimension changed to Completeness. \nMinor change to metric description wording","No change","No change"]
["ONRC","Demand/Use","Traffic Count","TC2","Historic count data coverage\nThe proportion of the total network Vehicle Kilometres Travelled (VKT), based on carriageway sections, with traffic count records","Retired","Completeness","No change","Minor change to metric description wording","No change","Metric retired","---","---","---"]
["ONRC","Demand/Use","Traffic Count","TC3","Traffic count programme activity on sealed network\nThe proportion of the total network Vehicle Kilometres Travelled (VKT), based on carriageway sections, with a latest traffic count record with a count date less than one year old at the end of the reported financial year","Active","Timeliness","SQL script corrected to return results for the reported financial year.  This previously incorrectly reported a 2.5 year period.","Minor change to metric description wording","No change","Dates updated to 2017/18 financial year","Updated to include both sealed and unsealed network. \nMinor change to metric description wording","Results have been updated. Most results for TC4 have improved with 11 more RCAs now achieving the expected standard.","Upper time limit removed from query"]
["ONRC","Demand/Use","Traffic Count","TC4","Traffic loading understood\nThe proportion of the total network Vehicle Kilometres Travelled (VKT), based on carriageway sections, with a latest classified traffic count record less than 5 years old at the end of the reported financial year","Active","Completeness","---","New metric added.  The PMRT holds the traffic and loading detail table data needed.  This metric was previously dropped due to this reason.","No change","Dates range updated to end 30/06/2018","Updated to include both sealed and unsealed network. \nPrimary Dimension changed to Completeness. \nMinor change to metric description wording","No change","Upper time limit removed from query"]
["ONRC","Demand/Use","Traffic Count","TC5","Historic traffic loading coverage\nProportion of network VKT with classified traffic count records (excludes pavement type 'Bridge')","Retired","Accuracy","---","New metric added.  The PMRT holds the traffic and loading detail table data needed.  This metric was previously dropped due to this reason.","No change","Metric retired","---","---","---"]
["ONRC","Demand/Use","Traffic Estimates","TE1","Network has traffic estimates\nThe proportion of the total number of carriageway section records with a recorded traffic estimate","Active","Completeness","Metric updated to report proportion of carriageway section records rather than length.  This makes it consistent with the other Traffic Estimate metrics.","Minor change to metric description wording","No change","No change","Updated to include both sealed and unsealed network. \nMinor change to metric description wording","No change","No change"]
["ONRC","Demand/Use","Traffic Estimates","TE2a","Traffic estimates are maintained (High Volume to Arterial)\nThe proportion of carriageway sections with a latest traffic estimate record less than 1 year old at the end of the reported financial year where the ONRC category is High Volume, National, Regional or Arterial","Active","Timeliness","No change","Minor change to metric description wording","Metric updated to report a N/A result for RCAs with no roads in these ONRC categories.  This is also reflected in the pie chart on page 1 of the report","Dates range updated to end 30/06/2018","Updated to include full network. \nMinor change to metric description wording","No change","Upper time limit removed from query"]
["ONRC","Demand/Use","Traffic Estimates","TE2b","Traffic estimates are maintained (Primary and Secondary Collectors)\nThe proportion of carriageway sections with a latest traffic estimate record less than 3 years old at the end of the reported financial year where the ONRC category is Primary Collector or Secondary Collector","Active","Timeliness","No change","Minor change to metric description wording","No change","Dates range updated to end 30/06/2018","Updated to include both sealed and unsealed network. \nMinor change to metric description wording","No change","Upper time limit removed from query"]
["ONRC","Demand/Use","Traffic Estimates","TE2c","Traffic estimates are maintained (Access including Low Volume)\nThe proportion of carriageway sections with a latest traffic estimate record less than 5 years old at the end of the reported financial year where the ONRC category is Access or Low Volume","Active","Timeliness","No change","Minor change to metric description wording","No change","Dates range updated to end 30/06/2018","Updated to include both sealed and unsealed network. \nMinor change to metric description wording","No change","Upper time limit removed from query"]
["ONRC","Demand/Use","Traffic Estimates","TE3","Traffic estimates updated following counts\nThe proportion of the number of carriageway sections with a traffic count record where the traffic estimate record has been updated following the latest count","Active","Accuracy","No change","Changed to be proportion of count records that have a newer estimate record. Minor change to metric description wording","No change","No change","Updated to include both sealed and unsealed network. \nMinor change to metric description wording","No change","No change"]
["ONRC","Demand/Use","Traffic Estimates","TE4","Considered traffic loading\nThe proportion of the total number of latest traffic estimate records with an estimated traffic loading mix distribution (ie not a default)","Active","Completeness","---","New metric added.  The PMRT holds the traffic and loading detail table data needed.  This metric was previously excluded due to this reason.","No change","No change","Updated to include both sealed and unsealed network. \nPrimary Dimension changed to Completeness. \nMinor change to metric description wording","No change","No change"]
["ONRC","Crash","Crash","Cr1","Crash data is recent\nThe age (in months) of crash data in terms of the time difference between the RAMM date_added field and the date loaded to the Performance Measure Reporting Tool (PMRT)","Active","Timeliness","No change","No change","No change","No change","No change","No change","Metric changed to report difference between latest date added to RAMM (max(added_on)) and PMRT import date"]
["ONRC","Crash","Crash","Cr2","Crash records with valid location\nThe proportion of the total number of crash records located within the extents of the road, as defined in the carriageway table, for the five year period up to the end of the reported financial year","Active","Accuracy","No change","No change","No change","Dates range updated to end 30/06/2018","No change","No change","No change"]

{"sheet":"AM Change Register","index":2,"n_rows":36,"n_cols":13,"rows_kept":36,"cut":false,"hidden":false,"merged":[]}
[null,null,null,"Metric and Indicator Change Register | Asset Management"]
[null,null,null,"Data Quality Project"]
[]
["DQ Report","Category","Sub-Category","Metric Reference","Metric Description","Status","Primary Dimension","2016/17 Reports – v1.0\nChanges following feedback on prototype report","2017/18 Reports – v2.1\nChanges as part of annual update reporting","2016/17 & 2017/18 Final Reports - v3.0\nChanges following sector feedback on draft report","2017/18 Reports - v3.1\nChanges as part of February 2019 reissue","2018/19 Reports - v4.0\nChanges as part of annual update reporting","2018/19 Reports - v4.1\nChanges as part of revised 2018/19 reporting"]
["Asset Management","Network","Carriageway","AM-Ca1","Road network data complete\nThe percentage of the total network length, based on carriageway sections, where the map centreline length is within 10% of measured road length recorded in the carriageway table","Active","Accuracy","No change","No change","Primary dimension updated\n(NOT REPORTED IN 2016/17 REPORT)","No change","No change","No change"]
["Asset Management","Network","Carriageway","AM-Ca2","ONRC categories assigned to new carriageways\nThe percentage of carriageway sections added to RAMM in the reported financial year with an ONRC category assigned","Active","Completeness","Metric reference updated","Dates updated to 2017/18 financial year","No change","No change","A result of \"Not Applicable\" is returned in the event a network has no new carriageway sections in the reported year.  This then doesn't contribute to the aggregated results and \"Score\" calculation.","No change"]
["Asset Management","Network","Carriageway","AM-Ca3","Assigned ONRC category aligns with traffic data\nThe percentage of carriageway sections, where the place function doesn't override the traffic, where the assigned ONRC category is aligned with latest traffic estimate volume (AADT) and daily heavy traffic volume","Active","Accuracy","Metric reference and description updated","No change","Primary dimension updated","No change","Metric description updated","No change"]
["Asset Management","Network","Treatment Length","AM-TL1","Treatment Length dimensions match sealed area\nThe percentage of the total number of sealed treatment length records where the recorded sealed area is less than 150% of the calculated area of the treatment length (length * width)","Active","Accuracy","New metric added, replacing previous AM-TL1 considered not to be adding value","No change","Primary dimension updated","No change","No change","No change"]
["Asset Management","Asset Inventory","Surfacing","AM-Su1a","Achieved chipseal resurfacing renewal programme as-builted\nThe percentage of the achieved chipseal resurfacing renewals reported in TIO as-builted in RAMM (in m2) with a work category of 212 for reported financial year","Active","Timeliness","Metric updated to return RAMM quantity recorded with work category of 212 only","Dates updated to 2017/18 financial year","Primary dimension updated \n(NOT REPORTED IN 2016/17 REPORT)","No change","Metric updated to report where road_type = 'L' only (exclude Crown,  Private, etc.)","Metric updated to report a zero result when TIO quantity >0 and RAMM as-built quantity = NULL or zero, or vice versa"]
["Asset Management","Asset Inventory","Surfacing","AM-Su1b","Achieved asphaltic concrete resurfacing renewal programme as-builted\nThe percentage of the achieved asphaltic concrete resurfacing renewals reported in TIO as-builted in RAMM (in lane.km) with a work category of 212 for reported financial year","Active","Timeliness","Metric updated to return RAMM quantity recorded with work category of 212 only","Dates updated to 2017/18 financial year","Primary dimension updated \n(NOT REPORTED IN 2016/17 REPORT)","No change","Metric updated to report where road_type = 'L' only (exclude Crown,  Private, etc.)","Metric updated to report a zero result when TIO quantity >0 and RAMM as-built quantity = NULL or zero, or vice versa"]
["Asset Management","Asset Inventory","Surfacing","AM-Su2","Surface records have valid attribute data\nThe percentage of current top surface records, based on treatment lengths, with a valid chip size (AM>=7, CS<=6, 2nd chip recorded for 2CHIP and RACK), surface function (AC <> \"M\" or \"1\"), a recorded binder type, and a recorded top surface life (not null)","Active","Accuracy","Metric reference updated","No change","No change","No change","Metric updated to ignore chip size for slurry surfaces","No change"]
["Asset Management","Asset Inventory","Pavement","AM-Pa1","Achieved pavement renewal programme as-builted\nThe percentage of the achieved pavement renewals reported in TIO as-builted in RAMM with a work category of 214 for last financial year","Active","Timeliness","Metric reference updated.","Dates updated to 2017/18 financial year","No change\n(NOT REPORTED IN 2016/17 REPORT)","No change","Metric updated to report where road_type = 'L' only (exclude Crown,  Private, etc.)","Metric updated to report a zero result when TIO quantity >0 and RAMM as-built quantity = NULL or zero, or vice versa"]
["Asset Management","Asset Inventory","Pavement","AM-Pa2","Pavement layer records have valid attribute data\nThe percentage of pavement layer records with a layer date in the reported financial year and a known material and source, recorded width when not full width and thickness between 50mm and 500mm","Active","Accuracy","New metric added","Dates updated to 2017/18 financial year","No change","Incorrect result calculation has been fixed. Sixteen more RCAs now achieve the expect standard.","No change","No change"]
["Asset Management","Asset Inventory","Pavement","AM-Pa3","Pavement layer records with Work Origin\nThe percentage of pavement layer records entered to RAMM with a layer date in the reported financial year with a recorded Work Origin","Active","Completeness","New metric added","Dates updated to 2017/18 financial year","Primary dimension updated\nMinor change to metric description wording","No change","No change","No change"]
["Asset Management","Asset Inventory","Footpath","AM-Fp1","Footpath asset known\nThe percentage of the total number of footpath records with a valid recorded length (not null or zero) and width (>0.7m and <20m, and not null), a known surface material and constructed/surface date or recent known condition","Active","Completeness","No change","No change","Primary dimension updated ","No change","Metric updated to allow either constructed date, or a known condition less than three years old at the end of the reported financial year","No change"]
["Asset Management","Asset Inventory","Footpath","AM-Fp2","Footpath asset records maintained\nThe percentage of the total footpath length entered to RAMM during the three year period to the end of the reported financial year","Active","Timeliness","Metric reference updated.","Dates range updated to end 30/06/2018","No change","No change","No change","No change"]
["Asset Management","Asset Inventory","Drainage","AM-Dr1","Culvert assets known\nThe percentage of the number of culvert records with a recorded length (eg not Null or zero length), size, material (ie not \"unknown\") and constructed date or recent known condition","Active","Completeness","No change","No change","Primary dimension updated\nUpdated grade range","No change","Metric updated to allow either constructed date, or a known condition less than three years old at the end of the reported financial year","No change"]
["Asset Management","Asset Inventory","Drainage","AM-Dr2","Culvert asset records maintained\nThe percentage of the total culvert length entered to RAMM during the three year period to the end of the reported financial year","Active","Timeliness","Metric reference updated.","Dates range updated to end 30/06/2018","No change","No change","No change","No change"]
["Asset Management","Asset Inventory","SW Channel","AM-SW1","SWC asset known\nThe percentage of the number of kerb and channel records with a recorded length (eg not Null or zero length), a known type (ie not \"unknown\") and construction date or recent known condition","Active","Completeness","No change","No change","Primary dimension updated\nUpdated grade range","No change","Metric updated to allow either constructed date, or a known condition less than three years old at the end of the reported financial year","No change"]
["Asset Management","Asset Inventory","SW Channel","AM-SW2","SWC asset records maintained\nThe percentage of the total kerb and channel length entered to RAMM during the three year period to the end of the reported financial year","Active","Timeliness","Metric reference updated.","Dates range updated to end 30/06/2018","No change","No change","No change","No change"]
["Asset Management","Asset Inventory","Signs","AM-Si1","Sign assets known\nThe percentage of the number of sign records with recorded dimensions, material and installation date or recent known condition","Active","Completeness","No change","No change","Primary dimension updated\nUpdated grade range","No change","Metric updated to allow either constructed date, or a known condition less than three years old at the end of the reported financial year","No change"]
["Asset Management","Asset Inventory","Signs","AM-Si2","Sign asset associated to a 'road'\nThe percentage of the number of sign records located within the extents of the road as defined in the carriageway table and with an offset less than 40m, or a recorded GPS location","Active","Accuracy","No change","No change","No change","No change","Metric updated to allow either a recorded linear displacement and offset, or GPS coordinate.  Change in metric description as a result.","No change"]
["Asset Management","Asset Inventory","Signs","AM-Si3","Sign replacement activity\nThe number of signs recorded as replaced in RAMM for the three year period to the end of the reported financial year as a percentage of the total number of signs on the network","Active","Timeliness","No change","Dates range updated to end 30/06/2018","Result now reported for both 2016/17 and 2017/18","No change","Metric description updated\nand updated to include asset owner of Local Authority only","No change"]
["Asset Management","Asset Inventory","Railings","AM-Ra1","Railing assets known\nThe percentage of the number of railing records with a recorded length (eg not Null or zero length), material (eg not \"unknown\") and installation date or recent known condition","Active","Completeness","No change","No change","Primary dimension updated\nUpdated grade range","No change","Metric updated to allow either constructed date, or a known condition less than three years old at the end of the reported financial year.  Also restricted to asset owner of Local Authority only","No change"]
["Asset Management","Asset Inventory","Railings","AM-Ra2","Railing asset records maintained\nThe percentage of the total railing length entered in RAMM for the three year period to the end of the reported financial year","Active","Timeliness","Metric reference updated","Dates range updated to end 30/06/2018","Updated grade range","No change","Metric updated to include asset owner of Local Authority only","No change"]
["Asset Management","Asset Inventory","Retaining Walls","AM-RW1","Retaining Wall assets known\nThe percentage of the number of retaining wall records with a recorded length (eg not Null or zero length), average height (eg not Null or zero), material (eg not \"unknown\") and constructed date or recent known condition","Active","Completeness","No change","No change","Primary dimension updatedUpdated grade range","No change","Metric updated to allow either constructed date, or a known condition less than three years old at the end of the reported financial year.  Also restricted to asset owner of Local Authority only","No change"]
["Asset Management","Asset Inventory","Retaining Walls","AM-RW2","Retaining wall asset records maintained\nThe percentage of the total retaining wall length entered in RAMM for the five year period to the end of the reported financial year","Active","Timeliness","Metric reference updated","Dates range updated to end 30/06/2018","Updated grade range","No change","Metric updated to include asset owner of Local Authority only","No change"]
["Asset Management","Asset Inventory","Streetlights","AM-SL1","Streetlights associated with a 'road'\nThe percentage of the number of streetlight pole records within limits of road as defined in the carriageway table and with an offset less than 40m, or a recorded GPS location","Active","Accuracy","No change","No change","No change","No change","Metric updated to allow either a recorded linear displacement and offset, or GPS coordinate.  Change in metric description as a result.","No change"]
["Asset Management","Asset Inventory","Streetlights","AM-SL2","Streetlights records have a light\nThe percentage of the number of streetlight pole records with at least one associated light record","Active","Completeness","No change","No change","Primary dimension updated","No change","No change","No change"]
["Asset Management","Asset Inventory","Streetlights","AM-SL3","Streetlight pole replacement activity\nThe number of streetlight poles recorded as replaced in RAMM for the three year period to the end of the reported financial year as a percentage of the total number of signs on the network","Active","Timeliness","No change","Dates range updated to end 30/06/2018","Result now reported for both 2016/17 and 2017/18","No change","Metric description updated","No change"]
["Asset Management","Maintenance Activity","Maintenance Activity","AM-MA1","Consistency of pavement, surfacing and shoulder maintenance activity units\nThe average number of different units used (eg each, m, m2, hr, etc.) per activity (eg In situ stabilisation, digout, pot-hole repair, etc.) during the reported financial year for pavement (PA), surfacing (SU), shoulder (SH) and drainage (DR) cost group maintenance activities.","Active","Accuracy","Metric description updated","Dates updated to 2017/18 financial year","Primary dimension updated ","No change","Metric update to exclude drainage cost group as the unit is dependant on the fault.  Metric description also updated.","No change"]
["Asset Management","Maintenance Activity","Maintenance Activity","AM-MA2","Pavement, surfacing, shoulder and drainage maintenance activity known\nThe percentage of the number of pavement (PA), surfacing (SU), shoulder (SH) and drainage (DR) cost group maintenance activity records with a known fault type (ie not \"unknown\") in reported financial year","Active","Completeness","No change","Dates updated to 2017/18 financial year","Primary dimension updated","No change","Metric description updated","No change"]
["Asset Management","Maintenance Activity","Maintenance Activity","AM-MA3","Correctly located shoulder and drainage maintenance activity\nThe percentage of the number of shoulder (SH) and drainage (DR) maintenance activity records recorded at appropriate location (percentage of records not at the start of the road) for the reported financial year","Active","Accuracy","No change","Dates updated to 2017/18 financial year","No change","No change","Metric description updated","No change"]
["Asset Management","Maintenance Activity","Maintenance Activity","AM-MA4","Level of pavement, surfacing, shoulder and drainage maintenance activity known\nThe percentage of the number of pavement (PA), surfacing (SU), shoulder (SH) and drainage (DR) cost group maintenance acitivty records with a known quantity (eg not Null, zero or negative) for the reported financial year","Active","Completeness","No change","Dates updated to 2017/18 financial year","No change","No change","Metric description updated","No change"]
["Asset Management","Condition","Rating","AM-Co1","Road rating data current\nThe percentage of total sealed network length, based on treatment lengths, with a latest road rating record less than two years old at end of reported financial year","Active","Completeness","Metric updated to report as percentage of sealed network length with rating data <2 years old.","Dates range updated to end 30/06/2018","Primary dimension updated","No change","No change","No change"]
["Asset Management","Condition","Rating","AM-Co2","Rating data locations valid\nThe percentage of the number latest rating records less than 2 years old at the end of the reported financial year with a valid inspection section (inspection start >= rating start and start of road, inspection end <= rating end and end of road)","Active","Accuracy","New metric added","Dates range updated to end 30/06/2018","No change","No change","Reported result to updated report \"NA\" if AM-Co1 = 0.0 and not contribute the aggregated results and \"Score\" calculation.","No change"]