Page Comparison

SELECT dr.*, mr.*, 
mr.#SITES_COLLECTED+mr.#SITES_DELIVERED as #SITES, 
mr.#SITES_COLLECTED_PREDICTED+mr.#SITES_DELIVERED_PREDICTED as #SITES_PREDICTED,
((mr.#SITES_COLLECTED+mr.#SITES_DELIVERED) - (mr.#SITES_COLLECTED_PREDICTED+mr.#SITES_DELIVERED_PREDICTED)) as #NOMINAL_SITES,
mr.UV_COLLECTED+mr.UV_DELIVERED as UV, 
mr.UV_COLLECTED_PREDICTED+mr.UV_DELIVERED_PREDICTED as UV_PREDICTED,
((mr.UV_COLLECTED+mr.UV_DELIVERED) - (mr.UV_COLLECTED_PREDICTED+mr.UV_DELIVERED_PREDICTED)) as NOMINAL_UV, 
mr.UP_COLLECTED+mr.UP_DELIVERED as UP, 
mr.UP_COLLECTED_PREDICTED+mr.UP_DELIVERED_PREDICTED as UP_PREDICTED,
((mr.UP_COLLECTED+mr.UP_DELIVERED) - (mr.UP_COLLECTED_PREDICTED+mr.UP_DELIVERED_PREDICTED)) as NOMINAL_UP,
mr.UQ_COLLECTED+mr.UQ_DELIVERED as UQ, 
mr.UQ_COLLECTED_PREDICTED+mr.UQ_DELIVERED_PREDICTED as UQ_PREDICTED,
((mr.UQ_COLLECTED+mr.UQ_DELIVERED) - (mr.UQ_COLLECTED_PREDICTED+mr.UQ_DELIVERED_PREDICTED)) as NOMINAL_UQ,
mr.UM_COLLECTED+mr.UM_DELIVERED as UM, 
mr.UM_COLLECTED_PREDICTED+mr.UM_DELIVERED_PREDICTED as UM_PREDICTED,
((mr.UM_COLLECTED+mr.UM_DELIVERED) - (mr.UM_COLLECTED_PREDICTED+mr.UM_DELIVERED_PREDICTED)) as NOMINAL_UM
FROM IC_M_ROUTE mr
INNER JOIN IC_D_ROUTE dr ON mr.ID_ROUTE = dr.ID
WHERE dr.DOMAIN_CODE in ($P{logistic_area()})
AND dr.ROUTE_DATE >= CONVERT(DateTime, $P{start_date})
AND dr.ROUTE_DATE <= CONVERT(DateTime, $P{end_date})
order by dr.DOMAIN_CODE, dr.CODE

with AXD_ROUTES_VAR as (
SELECT dr.ID, dr.CODE, dr.EXTERNAL_ID, dr.ROUTE_DATE, dr.DOMAIN_CODE,
DATEPART(WW, dr.ROUTE_DATE) as ROUTE_WEEK, DATEPART(MM, dr.ROUTE_DATE) as ROUTE_MONTH, DATEPART(YY, dr.ROUTE_DATE) as ROUTE_YEAR, 
mr.TOTAL_DISTANCE_PREDICTED, mr.TOTAL_DISTANCE,
mr.TOTAL_TIME_PREDICTED, mr.TOTAL_TIME,
mr.TRANSPORTATION_COST_PREDICTED, mr.TRANSPORTATION_COST,
mr.#SITES_COLLECTED+mr.#SITES_DELIVERED as #SITES, 
mr.#SITES_COLLECTED_PREDICTED+mr.#SITES_DELIVERED_PREDICTED as #SITES_PREDICTED,
mr.UV_COLLECTED+mr.UV_DELIVERED as UV, 
mr.UV_COLLECTED_PREDICTED+mr.UV_DELIVERED_PREDICTED as UV_PREDICTED,
mr.UP_COLLECTED+mr.UP_DELIVERED as UP, 
mr.UP_COLLECTED_PREDICTED+mr.UP_DELIVERED_PREDICTED as UP_PREDICTED
FROM IC_M_ROUTE mr
INNER JOIN IC_D_ROUTE dr ON mr.ID_ROUTE = dr.ID
WHERE dr.DOMAIN_CODE in ($P{logistic_area()})
AND dr.ROUTE_DATE >= CONVERT(DateTime, $P{start_date})
AND dr.ROUTE_DATE <= CONVERT(DateTime, $P{end_date})
)
select AXD_ROUTES_VAR.*, cast(AXD_ROUTES_VAR.ROUTE_YEAR as nvarchar) as X_VALUE
from AXD_ROUTES_VAR
where 0 = $P{axis_type}
UNION ALL
select AXD_ROUTES_VAR.*, cast(AXD_ROUTES_VAR.ROUTE_MONTH as nvarchar) as X_VALUE
from AXD_ROUTES_VAR
where 1 = $P{axis_type}
UNION ALL
select AXD_ROUTES_VAR.*, cast(AXD_ROUTES_VAR.ROUTE_WEEK as nvarchar) as X_VALUE
from AXD_ROUTES_VAR
where 2 = $P{axis_type}
UNION ALL
select AXD_ROUTES_VAR.*, cast(AXD_ROUTES_VAR.ROUTE_DATE as nvarchar) as X_VALUE
from AXD_ROUTES_VAR
where 3 = $P{axis_type}

SELECT dr.DRIVER_CODE, dd.LABEL, 
mr.TOTAL_TIME_PREDICTED, mr.TOTAL_TIME, mr.NOMINAL_DIFF_TOTAL_TIME, mr.RELATIVE_DIFF_TOTAL_TIME, 
mr.TOTAL_DISTANCE_PREDICTED, mr.TOTAL_DISTANCE, mr.NOMINAL_DIFF_TOTAL_DISTANCE, mr.RELATIVE_DIFF_TOTAL_DISTANCE 
FROM IC_M_ROUTE mr
INNER JOIN IC_D_ROUTE dr ON mr.ID_ROUTE = dr.ID
INNER JOIN IC_D_DRIVER dd ON dr.DRIVER_CODE = dd.ID
WHERE dr.DOMAIN_CODE in ($P{logistic_area()})
AND dr.ROUTE_DATE >= CONVERT(DateTime, $P{start_date})
AND dr.ROUTE_DATE <= CONVERT(DateTime, $P{end_date})
order by dr.DRIVER_CODE