DECLARE @ReportStartDate INT, @ReportEndDate INT
SET @ReportStartDate = 20190101
SET @ReportEndDate = 20241231
-----------------------------------------------------------------------------------------------
--CALCULATING AVERAGE DESPATCH SECONDS, TRAVEL SECONDS AND AT SCENE SECONDS FOR ALL METRO EVENTS WHERE AT SCENE = 1 AND BOOKED EVENTS EXCLUDED AND EVENTS OVER 4 HOURS EXCLUDED
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, AVG(DATEDIFF(SECOND,CreatedDateTime, AllocationDateTime)) AS DespatchSeconds, AVG(DATEDIFF(SECOND,OnRouteDateTime, ArrivedDateTime
)) AS TravelSeconds, AVG(DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime)) AS AtSceneSeconds
into #temptable1
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate AND JobServiceCategory = 'Road Service' and PatrolUnit like '5MR%' AND IsAtSceneJob = 1 AND NOT Booked = 'Y' and DATEDIFF(SECOND,CreatedDateTime, AllocationDateTime) > 0 AND DATEDIFF(SECOND,OnRouteDateTime, ArrivedDateTime) > 0 AND
DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime) > 0 and DATEDIFF(SECOND,CreatedDateTime, AllocationDateTime) < 14400 AND DATEDIFF(SECOND,OnRouteDateTime, ArrivedDateTime) < 14400 AND DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime) < 14400
group by CreatedDateKey
order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--CALCULATING AVERAGE ALLOCATED SECONDS FOR ALL METRO EVENTS WHERE AT SCENE = 1 AND BOOKED EVENTS EXCLUDE AND DISPATCH ASSIGNED EXCLUDED
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, AVG(DATEDIFF(SECOND,AllocationDateTime, OnRouteDateTime)) AS AllocatedSeconds
into #temptable1a
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate AND JobServiceCategory = 'Road Service' and PatrolUnit like '5MR%' AND IsAtSceneJob = 1 AND Dispatcher IS NULL AND NOT Booked = 'Y' AND DATEDIFF(SECOND,AllocationDateTime, OnRouteDateTime) > 0 and DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime) < 14400
group by CreatedDateKey
order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--CALCULATING TOTAL EVENTS
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, count(CreatedDateKey) as TotalEvents
into #temptable2
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate AND JobServiceCategory = 'Road Service' and PatrolUnit like '5MR%' AND IsCompletedJob = 1
group by CreatedDateKey
order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--CALCULATING TOTAL BOOKED EVENTS
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, count(CreatedDateKey) as TotalBooked
into #temptable3
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate AND JobServiceCategory = 'Road Service' and PatrolUnit like '5MR%' AND IsCompletedJob = 1 AND Booked = 'Y'
group by CreatedDateKey
order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--CALCULATING APP EVENTS (Excluded now as part of query)
-----------------------------------------------------------------------------------------------
--SELECT CreatedDateKey, COUNT(*) AS TotalAppEvents
--into #temptable4
--FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate AND JobServiceCategory = 'Road Service' and PatrolUnit like '5MR%' AND CallTaker IS NULL
--group by CreatedDateKey
--order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--CALCULATING PATROL BATTERY SALES
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, COUNT(*) AS PatrolTotalBatterySales
into #temptable5
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate AND JobServiceCategory = 'Road Service' AND (PatrolUnit like '5MRR%' OR PatrolUnit like '5MR1%' OR PatrolUnit like '5MR2%' OR PatrolUnit like '5MR4%' OR PatrolUnit like '5MR6%' OR PatrolUnit LIKE '5MREV%' OR PatrolUnit LIKE '5MRSTH%') AND IsCompletedJob = 1 AND BatteryCode IS NOT NULL AND BatteryPrice > 0
group by CreatedDateKey
order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--CALCULATING CONTRACTOR BATTERY SALES
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, COUNT(*) AS ContractorTotalBatterySales
into #temptable5a
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate AND JobServiceCategory = 'Road Service' AND (PatrolUnit like '5MR50%' or PatrolUnit like '5MR51%' or PatrolUnit like '5MR52%' or PatrolUnit like '5MR54%' OR PatrolUnit like '5MR53%') AND IsCompletedJob = 1 AND BatteryCode IS NOT NULL AND BatteryPrice > 0
group by CreatedDateKey
order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--CALCULATING CONTRACTOR EVENTS EXCLUDING AM SHIFT
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, COUNT(*) AS TotalContractorNoAM
into #temptable6
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate AND JobServiceCategory = 'Road Service' AND (PatrolUnit like '5MR50%' or PatrolUnit like '5MR51%' or PatrolUnit like '5MR52%' or PatrolUnit like '5MR54%') AND IsCompletedJob = 1
group by CreatedDateKey
order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--CALCULATING CONTRACTOR AM EVENTS
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, COUNT(*) AS TotalContractorAM
into #temptable7
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate AND JobServiceCategory = 'Road Service' AND PatrolUnit like '5MR53%' AND IsCompletedJob = 1
group by CreatedDateKey
order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--CALCULATING SERVICE LEVEL (BOOKED EVENTS EXCLUDED AND EVENTS OVER 4 HOURS)
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, COUNT(CASE WHEN ((DATEDIFF(SECOND,CreatedDateTime, ArrivedDateTime))/60) > 60 THEN 1 END) AS NumberOfGreaterThan60, count(CreatedDateKey) as TotalEvents
into #temptable8A
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate AND JobServiceCategory = 'Road Service' and PatrolUnit like '5MR%' AND IsAtSceneJob = 1 AND NOT Booked = 'Y' and DATEDIFF(SECOND,CreatedDateTime, AllocationDateTime) > 0 AND DATEDIFF(SECOND,AllocationDateTime, OnRouteDateTime) > 0 AND DATEDIFF(SECOND,OnRouteDateTime, ArrivedDateTime) > 0 AND
DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime) > 0 and DATEDIFF(SECOND,CreatedDateTime, AllocationDateTime) < 14400 AND DATEDIFF(SECOND,AllocationDateTime, OnRouteDateTime) < 14400 and DATEDIFF(SECOND,OnRouteDateTime, ArrivedDateTime) < 14400 AND DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime) < 14400
group by CreatedDateKey
order by CreatedDateKey
SELECT CreatedDateKey, CAST(1-(1.0*NumberOfGreaterThan60/TotalEvents) AS DECIMAL(18,4)) as TotalServiceLevel
into #temptable8
from #temptable8A
-----------------------------------------------------------------------------------------------
--TOTAL CONTACTS RSD
-----------------------------------------------------------------------------------------------
SELECT IntervalStartDateKey, sum(ContactReceived) as TotalRSDContacts
into #temptable9
FROM [EDW].[RS].[FactPatrolInterval]
WHERE IntervalType IN ('61','62') AND IntervalStartDateKey BETWEEN @ReportStartDate AND @ReportEndDate
group by IntervalStartDateKey
order by IntervalStartDateKey
-----------------------------------------------------------------------------------------------
--TOTAL CONTACTS FORECAST RSD
-----------------------------------------------------------------------------------------------
SELECT DateKey, ForecastVolume AS TotalRSDContactsForecasted
into #temptable10
FROM [EDW].[RoadService].[FactMRSDForcastActual] where DateKey BETWEEN @ReportStartDate AND @ReportEndDate
group by DateKey, ForecastVolume
order by DateKey
-----------------------------------------------------------------------------------------------
--STANDBY PATROLS
-----------------------------------------------------------------------------------------------
SELECT IntervalStartDateKey, CAST((1.0*sum(DurationStandBy))/(1.0*sum(DurationLoggedIn)) AS DECIMAL(18,4)) as StandbyPatrol
into #temptable11
FROM [EDW].[RS].[FactPatrolInterval]
WHERE IntervalType IN ('61','62') AND IntervalStartDateKey BETWEEN @ReportStartDate AND @ReportEndDate and (PatrolUnit like '5MRR%' OR PatrolUnit like '5MR1%' OR PatrolUnit like '5MR2%' OR PatrolUnit like '5MR4%' OR PatrolUnit like '5MR6%' OR PatrolUnit LIKE '5MREV%' OR PatrolUnit LIKE '5MRSTH%')
GROUP BY IntervalStartDateKey
-----------------------------------------------------------------------------------------------
--STANDBY CONTRACTORS NOT AM
-----------------------------------------------------------------------------------------------
SELECT IntervalStartDateKey, CAST((1.0*sum(DurationStandBy))/(1.0*sum(DurationLoggedIn)) AS DECIMAL(18,4)) as StandbyContractorNoAM
into #temptable12
FROM [EDW].[RS].[FactPatrolInterval]
WHERE IntervalType IN ('61','62') AND IntervalStartDateKey BETWEEN @ReportStartDate AND @ReportEndDate and (PatrolUnit like '5MR50%' or PatrolUnit like '5MR51%' or PatrolUnit like '5MR52%' or PatrolUnit like '5MR54%')
GROUP BY IntervalStartDateKey
-----------------------------------------------------------------------------------------------
--STANDBY CONTRACTORS AM
-----------------------------------------------------------------------------------------------
SELECT IntervalStartDateKey, CAST((1.0*sum(DurationStandBy))/(1.0*sum(DurationLoggedIn)) AS DECIMAL(18,4)) as StandbyContractorAM
into #temptable13
FROM [EDW].[RS].[FactPatrolInterval]
WHERE IntervalType IN ('61','62') AND IntervalStartDateKey BETWEEN @ReportStartDate AND @ReportEndDate and PatrolUnit like '5MR53%'
GROUP BY IntervalStartDateKey
-----------------------------------------------------------------------------------------------
--PATROL PROCESS TIME BREAKDOWN (BOOKED EVENTS EXCLUDED) AND EVENTS OVER 4 HOURS EXCLUDED AND ALLOCATED EVENTS EXCLUDED
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, AVG(DATEDIFF(SECOND,OnRouteDateTime, ArrivedDateTime)) AS PatrolTravelSeconds, AVG(DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime)) AS PatrolAtSceneSeconds
into #temptable14
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate and (PatrolUnit like '5MRR%' OR PatrolUnit like '5MR1%' OR PatrolUnit like '5MR2%' OR PatrolUnit like '5MR4%' OR PatrolUnit like '5MR6%' OR PatrolUnit LIKE '5MREV%' OR PatrolUnit LIKE '5MRSTH%') AND IsAtSceneJob = 1 AND NOT Booked = 'Y' and DATEDIFF(SECOND,CreatedDateTime, AllocationDateTime) > 0 AND DATEDIFF(SECOND,OnRouteDateTime, ArrivedDateTime) > 0 AND
DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime) > 0 and DATEDIFF(SECOND,CreatedDateTime, AllocationDateTime) < 14400 AND DATEDIFF(SECOND,OnRouteDateTime, ArrivedDateTime) < 14400 AND DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime) < 14400
group by CreatedDateKey
order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--PATROL PROCESS TIME BREAKDOWN FOR ALLOCATION TIME (BOOKED EVENTS EXCLUDED) AND EVENTS OVER 4 HOURS EXCLUDED AND DISPATCH ASSIGNED EXCLUDED
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, avg(DATEDIFF(SECOND,AllocationDateTime, OnRouteDateTime)) AS PatrolAllocatedSeconds
into #temptable14A
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate AND JobServiceCategory = 'Road Service' and (PatrolUnit like '5MRR%' OR PatrolUnit like '5MR1%' OR PatrolUnit like '5MR2%' OR PatrolUnit like '5MR4%' OR PatrolUnit like '5MR6%' OR PatrolUnit LIKE '5MREV%' OR PatrolUnit LIKE '5MRSTH%') AND IsAtSceneJob = 1 AND Dispatcher IS NULL AND NOT Booked = 'Y' AND DATEDIFF(SECOND,AllocationDateTime, OnRouteDateTime) > 0 and DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime) < 14400
group by CreatedDateKey
order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--CONTRACTOR NOT AM PROCESS TIME BREAKDOWN (BOOKED EVENTS EXCLUDED) AND EVENTS OVER 4 HOURS EXCLUDED AND ALLOCATED EVENTS EXCLUDED
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, AVG(DATEDIFF(SECOND,OnRouteDateTime, ArrivedDateTime)) AS ContractorTravelSeconds, AVG(DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime)) AS ContractorAtSceneSeconds
into #temptable15
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate and (PatrolUnit like '5MR50%' or PatrolUnit like '5MR51%' or PatrolUnit like '5MR52%' or PatrolUnit like '5MR54%') AND IsAtSceneJob = 1 AND NOT Booked = 'Y' and DATEDIFF(SECOND,CreatedDateTime, AllocationDateTime) > 0 AND DATEDIFF(SECOND,OnRouteDateTime, ArrivedDateTime) > 0 AND
DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime) > 0 and DATEDIFF(SECOND,CreatedDateTime, AllocationDateTime) < 14400 AND DATEDIFF(SECOND,OnRouteDateTime, ArrivedDateTime) < 14400 AND DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime) < 14400
group by CreatedDateKey
order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--CONTRACTOR NOT AM PROCESS TIME BREAKDOWN (BOOKED EVENTS EXCLUDED) AND EVENTS OVER 4 HOURS EXCLUDED AND DISPATCH ASSIGNED EXCLUDED
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, avg(DATEDIFF(SECOND,AllocationDateTime, OnRouteDateTime)) AS ContractorAllocatedSeconds
into #temptable15A
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate AND JobServiceCategory = 'Road Service' and (PatrolUnit like '5MR50%' or PatrolUnit like '5MR51%' or PatrolUnit like '5MR52%' or PatrolUnit like '5MR54%') AND IsAtSceneJob = 1 AND Dispatcher IS NULL AND NOT Booked = 'Y' AND DATEDIFF(SECOND,AllocationDateTime, OnRouteDateTime) > 0 and DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime) < 14400
group by CreatedDateKey
order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--CALCULATING TOTAL PATROL EVENTS
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, COUNT(*) AS TotalPatrolJobs
into #temptable16
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate AND JobServiceCategory = 'Road Service' AND (PatrolUnit like '5MRR%' OR PatrolUnit like '5MR1%' OR PatrolUnit like '5MR2%' OR PatrolUnit like '5MR4%' OR PatrolUnit like '5MR6%' OR PatrolUnit LIKE '5MREV%' OR PatrolUnit LIKE '5MRSTH%') AND IsCompletedJob = 1 and DATEDIFF(SECOND,CreatedDateTime, AllocationDateTime) > 0 AND DATEDIFF(SECOND,AllocationDateTime, OnRouteDateTime) > 0 AND
DATEDIFF(SECOND,OnRouteDateTime, ArrivedDateTime) > 0 AND DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime) > 0
group by CreatedDateKey
order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--CALCULATING TOTAL PATROL EVENTS (BOOKED EVENTS EXCLUDED)
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, COUNT(*) AS TotalPatrolJobsNotBooked
into #temptable17
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate AND JobServiceCategory = 'Road Service' AND (PatrolUnit like '5MRR%' OR PatrolUnit like '5MR1%' OR PatrolUnit like '5MR2%' OR PatrolUnit like '5MR4%' OR PatrolUnit like '5MR6%' OR PatrolUnit LIKE '5MREV%' OR PatrolUnit LIKE '5MRSTH%') AND NOT Booked = 'Y' AND IsCompletedJob = 1 and DATEDIFF(SECOND,CreatedDateTime, AllocationDateTime) > 0 AND DATEDIFF(SECOND,AllocationDateTime, OnRouteDateTime) > 0 AND
DATEDIFF(SECOND,OnRouteDateTime, ArrivedDateTime) > 0 AND DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime) > 0
group by CreatedDateKey
order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--CALCULATING CONTRACTOR NOT AM EVENTS (BOOKED EVENTS EXCLUDED)
-----------------------------------------------------------------------------------------------
SELECT CreatedDateKey, COUNT(*) AS TotalContractorJobsNotBooked
into #temptable18
FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN @ReportStartDate AND @ReportEndDate AND JobServiceCategory = 'Road Service' AND (PatrolUnit like '5MR50%' or PatrolUnit like '5MR51%' or PatrolUnit like '5MR52%' or PatrolUnit like '5MR54%') AND NOT Booked = 'Y' AND IsCompletedJob = 1 AND Dispatcher IS NULL and DATEDIFF(SECOND,CreatedDateTime, AllocationDateTime) > 0 AND DATEDIFF(SECOND,AllocationDateTime, OnRouteDateTime) > 0 AND
DATEDIFF(SECOND,OnRouteDateTime, ArrivedDateTime) > 0 AND DATEDIFF(SECOND,ArrivedDateTime, FinishedDateTime) > 0
group by CreatedDateKey
order by CreatedDateKey
-----------------------------------------------------------------------------------------------
--FINAL JOIN
-----------------------------------------------------------------------------------------------
Select A.CreatedDateKey, A.DespatchSeconds, S.AllocatedSeconds, A.TravelSeconds, A.AtSceneSeconds, b.TotalEvents, C.TotalBooked, E.PatrolTotalBatterySales, T.ContractorTotalBatterySales, F.TotalContractorNoAM, G.TotalContractorAM,
H.TotalServiceLevel, I.TotalRSDContacts, J.TotalRSDContactsForecasted, K.StandbyPatrol, L.StandbyContractorNoAM, M.StandbyContractorAM, u.PatrolAllocatedSeconds, N.PatrolTravelSeconds, N.PatrolAtSceneSeconds, V.ContractorAllocatedSeconds, O.ContractorTravelSeconds, O.ContractorAtSceneSeconds, P.TotalPatrolJobs, Q.TotalPatrolJobsNotBooked, R.TotalContractorJobsNotBooked
INTO #temptable19
from #temptable1 A
LEFT JOIN #temptable1a S ON A.CreatedDateKey = S.CreatedDateKey
LEFT JOIN #temptable2 B ON A.CreatedDateKey = B.CreatedDateKey
LEFT JOIN #temptable3 C ON A.CreatedDateKey = C.CreatedDateKey
LEFT JOIN #temptable5 E ON A.CreatedDateKey = E.CreatedDateKey
LEFT JOIN #temptable5a T ON A.CreatedDateKey = T.CreatedDateKey
LEFT JOIN #temptable6 F ON A.CreatedDateKey = F.CreatedDateKey
LEFT JOIN #temptable7 G ON A.CreatedDateKey = G.CreatedDateKey
LEFT JOIN #temptable8 H ON A.CreatedDateKey = H.CreatedDateKey
LEFT JOIN #temptable9 I ON A.CreatedDateKey = I.IntervalStartDateKey
LEFT JOIN #temptable10 J ON A.CreatedDateKey = J.DateKey
LEFT JOIN #temptable11 K ON A.CreatedDateKey = K.IntervalStartDateKey
LEFT JOIN #temptable12 L ON A.CreatedDateKey = L.IntervalStartDateKey
LEFT JOIN #temptable13 M ON A.CreatedDateKey = M.IntervalStartDateKey
LEFT JOIN #temptable14 N ON A.CreatedDateKey = N.CreatedDateKey
LEFT JOIN #temptable14a U ON A.CreatedDateKey =U.CreatedDateKey
LEFT JOIN #temptable15 O ON A.CreatedDateKey = O.CreatedDateKey
LEFT JOIN #temptable15A V ON A.CreatedDateKey = V.CreatedDateKey
LEFT JOIN #temptable16 P ON A.CreatedDateKey = P.CreatedDateKey
LEFT JOIN #temptable17 Q ON A.CreatedDateKey = Q.CreatedDateKey
LEFT JOIN #temptable18 R ON A.CreatedDateKey = R.CreatedDateKey
group by A.CreatedDateKey, A.DespatchSeconds, S.AllocatedSeconds, A.TravelSeconds, A.AtSceneSeconds, b.TotalEvents, C.TotalBooked, E.PatrolTotalBatterySales, T.ContractorTotalBatterySales, F.TotalContractorNoAM, G.TotalContractorAM,
H.TotalServiceLevel, I.TotalRSDContacts, J.TotalRSDContactsForecasted, K.StandbyPatrol, L.StandbyContractorNoAM, M.StandbyContractorAM, u.PatrolAllocatedSeconds, N.PatrolTravelSeconds, N.PatrolAtSceneSeconds, V.ContractorAllocatedSeconds, O.ContractorTravelSeconds, O.ContractorAtSceneSeconds, P.TotalPatrolJobs, Q.TotalPatrolJobsNotBooked, R.TotalContractorJobsNotBooked
order by A.CreatedDateKey
-----------------------------------------------------------------------------------------------
--UPDATE NULL VALUES TO = ZERO
-----------------------------------------------------------------------------------------------
UPDATE #temptable19 SET TotalBooked = 0 WHERE TotalBooked IS NULL
-----------------------------------------------------------------------------------------------
--SELECT FINAL TABLE
-----------------------------------------------------------------------------------------------
SELECT * FROM #temptable19
-----------------------------------------------------------------------------------------------
--DROP TEMP TABLES
-----------------------------------------------------------------------------------------------
DROP TABLE #temptable1
DROP TABLE #temptable1A
DROP TABLE #temptable2
DROP TABLE #temptable3
DROP TABLE #temptable5
DROP TABLE #temptable5a
DROP TABLE #temptable6
DROP TABLE #temptable7
DROP TABLE #temptable8
DROP TABLE #temptable8A
DROP TABLE #temptable9
DROP TABLE #temptable10
DROP TABLE #temptable11
DROP TABLE #temptable12
DROP TABLE #temptable13
DROP TABLE #temptable14
DROP TABLE #temptable14A
DROP TABLE #temptable15
DROP TABLE #temptable16
DROP TABLE #temptable17
DROP TABLE #temptable18
DROP TABLE #temptable19