Developers and Reporting Services

Posted on by

Hello again. Recently I accepted a new job offer at the same company, I’m now the “Supervisor of Quality” which means I’m going to be working on Service Management, Change Management, Problem Management, etc, etc, etc.

So I kind of figured that my SQL posting days were done. However, my company likes to put the cart way in front of the horse and I’m still the lone DBA for 50 production servers, 70 production instances, and thousands of databases, scattered around the globe from South Africa to the UK, from Australia to Amsterdam. Oh, and everywhere in-between.

Today, I got a message from a “developer” that his new report is timing out.

I ran the report manually with his parameters, and do a little “SP_WHOISACTIVE” while the report was running. Here’s one of the queries I captured…

WITH CTEPreFea AS					   
(
	SELECT tof.* 
		FROM @tmpOriginalFea AS tof
		INNER JOIN DimMachine dm ON tof.MachineId = dm.Id 
		WHERE (LEN(dm.SerialNo)<>4 AND dm.SerialNo LIKE '28%')
		AND (
		HoistBankEventDuration<>0 OR 
		SwingEventDuration<>0 OR 
		TuckEventDuration<>0 OR 
		DigPrepEventDuration<>0 OR 
		NonProductiveDigEventDuration<>0 OR 
		WaitingForTruckEventDuration<>0 OR 
		IdleEventDuration<>0 OR 
		PropelEventDuration<>0 OR 
		StopOperatorStateDuration<> 0 OR 
		MotivatorEventDuration<>0 OR 
		LimitsEventDuration<>0 OR 
		StartedInTestStateDuration<>0 OR 
		InstantStopStateDuration<>0 OR 
		DelayedStopStateDuration<>0 
		)
		AND CycleEventCount<>0
		AND (DigPrepEventDuration +HoistBankEventDuration+SwingEventDuration+TuckEventDuration)/CycleEventCount>=20
		AND (DigPrepEventDuration +HoistBankEventDuration+SwingEventDuration+TuckEventDuration)/CycleEventCount<=70
)
,CTEPreFeaAllMachine AS					   
(
	SELECT tof.* 
		FROM @tmpOriginalFeaAllMachine AS tof
		INNER JOIN DimMachine dm 
		ON tof.MachineId = dm.Id 
		WHERE (LEN(dm.SerialNo)<>4 AND dm.SerialNo LIKE '28%')
		AND (
		HoistBankEventDuration<>0 OR 
		SwingEventDuration<>0 OR 
		TuckEventDuration<>0 OR 
		DigPrepEventDuration<>0 OR 
		NonProductiveDigEventDuration<>0 OR 
		WaitingForTruckEventDuration<>0 OR 
		IdleEventDuration<>0 OR 
		PropelEventDuration<>0 OR 
		StopOperatorStateDuration<> 0 OR 
		MotivatorEventDuration<>0 OR 
		LimitsEventDuration<>0 OR 
		StartedInTestStateDuration<>0 OR 
		InstantStopStateDuration<>0 OR 
		DelayedStopStateDuration<>0 
		)
		AND CycleEventCount<>0
		AND (DigPrepEventDuration +HoistBankEventDuration+SwingEventDuration+TuckEventDuration)/CycleEventCount>=20
		AND (DigPrepEventDuration +HoistBankEventDuration+SwingEventDuration+TuckEventDuration)/CycleEventCount<=70
)
,CTEFea AS					   
(
	SELECT 
	LocalCalendarDate,
	ShiftStartTime,
	CycleEventCount,
	(DigPrepEventDuration +HoistBankEventDuration+SwingEventDuration+TuckEventDuration)/CycleEventCount 
	AS CycleTimeDuration,
	(HoistBankEventDuration+SwingEventDuration+TuckEventDuration+DigPrepEventDuration+NonProductiveDigEventDuration+WaitingForTruckEventDuration+IdleEventDuration+PropelEventDuration)
	AS RunTimeSecond,
	(SwingImpact1EventCount+SwingImpact2EventCount) 
	AS SwingImpact,
	(BoomJack1EventCount+BoomJack2EventCount) 
	AS BoomJack,
	MotorStallEventCount 
	AS MotorStall,
	MachineId
	FROM CTEPreFea
)
,CTEFeaAllMachine AS					   
(
	SELECT 
	LocalCalendarDate,
	ShiftStartTime,
	CycleEventCount,
	(DigPrepEventDuration +HoistBankEventDuration+SwingEventDuration+TuckEventDuration)/CycleEventCount 
	AS CycleTimeDuration,
	(HoistBankEventDuration+SwingEventDuration+TuckEventDuration+DigPrepEventDuration+NonProductiveDigEventDuration+WaitingForTruckEventDuration+IdleEventDuration+PropelEventDuration)
	AS RunTimeSecond,
	(SwingImpact1EventCount+SwingImpact2EventCount) 
	AS SwingImpact,
	(BoomJack1EventCount+BoomJack2EventCount) 
	AS BoomJack,
	MotorStallEventCount 
	AS MotorStall,
	MachineId
	FROM CTEPreFeaAllMachine
)
,CTEFeaMachineList AS
(
	SELECT 
	MachineId
	FROM CTEFea
	GROUP BY MachineId	
)
,CTEFeaPerDay AS					   
(
	SELECT 
	MachineId,
	SUM(RunTimeSecond)/3600.0 AS RunTime,
	SUM(SwingImpact)*100/SUM(CycleEventCount) AS SwingImpact,
	SUM(BoomJack)*100/SUM(CycleEventCount) AS BoomJack,
	SUM(MotorStall)*100/SUM(CycleEventCount) AS MotorStall,
	DATEDIFF(SECOND,@pBeginDateTime,LocalCalendarDate)/@secondsPerDay+1 AS DayNumber
	FROM CTEFea
	GROUP BY MachineId,DATEDIFF(SECOND,@pBeginDateTime,LocalCalendarDate)/@secondsPerDay+1
)
,CTEFeaPerDayAllMachine AS					   
(
	SELECT 
	MachineId,
	SUM(RunTimeSecond)/3600.0 AS RunTime,
	SUM(SwingImpact)*100/SUM(CycleEventCount) AS SwingImpact,
	SUM(BoomJack)*100/SUM(CycleEventCount) AS BoomJack,
	SUM(MotorStall)*100/SUM(CycleEventCount) AS MotorStall,
	DATEDIFF(SECOND,@pBeginDateTime,LocalCalendarDate)/@secondsPerDay+1 AS DayNumber
	FROM CTEFeaAllMachine
	GROUP BY MachineId,DATEDIFF(SECOND,@pBeginDateTime,LocalCalendarDate)/@secondsPerDay+1
)
,CTEFeaPerDayAvgMachine AS					   
(
	SELECT 
	0 AS MachineId,
	AVG(RunTime)AS RunTime,
	AVG(SwingImpact) AS SwingImpact,
	AVG(BoomJack) AS BoomJack,
	AVG(MotorStall) AS MotorStall,
	DayNumber
	FROM CTEFeaPerDayAllMachine
	GROUP BY DayNumber
)
,CTEFeaPerDaySumMachine AS					   
(
	SELECT 
	0 AS MachineId,
	SUM(RunTime)AS RunTime,
	SUM(SwingImpact) AS SwingImpact,
	SUM(BoomJack) AS BoomJack,
	SUM(MotorStall) AS MotorStall,
	DayNumber
	FROM CTEFeaPerDayAllMachine
	GROUP BY DayNumber
)
,CTEFeaPerShift AS 
(
	SELECT MachineId,
	ShiftStartTime,
	SUM(RunTimeSecond)/3600.0 AS OperatingTimePerShift,
	SUM(CycleEventCount) AS CyclesPerOperatingHour
	FROM CTEFea
	GROUP BY MachineId,ShiftStartTime
)
,CTEFeaPerOperatingHour AS 
(
	SELECT MachineId,
	CycleEventCount/RunTimeSecond * 3600.0  AS CyclesPerOperatingHour
	FROM CTEFea WHERE RunTimeSecond IS NOT NULL AND RunTimeSecond <>0 
)
,CTEFeaPerShiftAllMachine AS 
(
	SELECT MachineId,
	ShiftStartTime,
	SUM(RunTimeSecond)/3600.0 AS OperatingTimePerShift,
	SUM(CycleEventCount) AS CyclesPerOperatingHour
	FROM CTEFeaAllMachine
	GROUP BY MachineId,ShiftStartTime
)
,CTEFeaPerShiftAvgMachine AS 
(
	SELECT 0 AS MachineId,
	ShiftStartTime,
	AVG(OperatingTimePerShift) AS OperatingTimePerShift,
	AVG(CyclesPerOperatingHour) AS CyclesPerOperatingHour
	FROM CTEFeaPerShiftAllMachine
	GROUP BY ShiftStartTime
)
,CTEFeaCountPerMachine AS
(
	SELECT MachineId,
	COUNT(1) AS TotalCount
	FROM CTEFea
	GROUP BY MachineId
)
,CTEFeaCountAllMachine AS
(
	SELECT 0 AS MachineId,
	COUNT(1) AS TotalCount
	FROM CTEFeaAllMachine
)
,CTEFeaCountPerOperatingHourPerMachine AS
(
	SELECT MachineId,
	COUNT(1) AS TotalCount
	FROM CTEFeaPerOperatingHour
	GROUP BY MachineId
)
,CTEFeaCountPerOperatingHourAllMachine AS
(
	SELECT 0 AS MachineId,
	COUNT(1) AS TotalCount
	FROM CTEFeaPerOperatingHour
)
,CTEFeaCountPerDayPerMachine AS
(
	SELECT MachineId,
	COUNT(1) AS TotalCount
	FROM CTEFeaPerDay
	GROUP BY MachineId
)
,CTEFeaCountPerDayAllMachine AS
(
	SELECT 0 AS MachineId,
	COUNT(1) AS TotalCount
	FROM CTEFeaPerDayAvgMachine
)
,CTEFeaCountPerShiftPerMachine AS
(
	SELECT MachineId,
	COUNT(1) AS TotalCount
	FROM CTEFeaPerShift
	GROUP BY MachineId
)
,CTEFeaCountPerShiftAllMachine AS
(
	SELECT 0 AS MachineId,
	COUNT(1) AS TotalCount
	FROM CTEFeaPerShiftAvgMachine
)
,CTENtileCycleTime0 AS					   
(
	SELECT 
	CycleTimeDuration,
	ROW_NUMBER() OVER(PARTITION BY MachineId ORDER BY CycleTimeDuration ) AS RowNumber,
	MachineId
	FROM CTEFea
)
,CTENtileCycleTime AS					   
(
	SELECT 
	CycleTimeDuration,
	a.RowNumber*1.0/b.TotalCount AS Percentile,
	a.MachineId
	FROM CTENtileCycleTime0 a
	INNER JOIN CTEFeaCountPerMachine b 
	ON a.MachineId=b.MachineId
)
,CTENtileCycleTime1 AS
(
	SELECT
	MachineId,
	MAX(CycleTimeDuration) AS CycleTimeDuration
	FROM 
	CTENtileCycleTime 
	WHERE Percentile<=@lineBottomPercentile
	GROUP BY MachineId
)
,CTENtileCycleTime5 AS
(
	SELECT
	MachineId,
	MAX(CycleTimeDuration) AS CycleTimeDuration
	FROM 
	CTENtileCycleTime 
	WHERE Percentile<=@boxBottomPercentile
	GROUP BY MachineId
)
,CTENtileCycleTime15 AS
(
	SELECT
	MachineId,
	MAX(CycleTimeDuration) AS CycleTimeDuration
	FROM 
	CTENtileCycleTime 
	WHERE Percentile<=@boxTopPercentile
	GROUP BY MachineId
)
,CTENtileCycleTime19 AS
(
	SELECT
	MachineId,
	MAX(CycleTimeDuration) AS CycleTimeDuration
	FROM 
	CTENtileCycleTime 
	WHERE Percentile<=@lineTopPercentile
	GROUP BY MachineId
)
,CTENtileSwingImpact0 AS					   
(
	SELECT 
	SwingImpact,
	ROW_NUMBER() OVER(PARTITION BY MachineId ORDER BY SwingImpact ) AS RowNumber,
	MachineId
	FROM CTEFeaPerDay
)
,CTENtileSwingImpact AS					   
(
	SELECT 
	SwingImpact,
	a.RowNumber*1.0/b.TotalCount AS Percentile,
	a.MachineId
	FROM CTENtileSwingImpact0 a
	INNER JOIN CTEFeaCountPerDayPerMachine b 
	ON a.MachineId=b.MachineId
)
,CTENtileSwingImpact1 AS
(
	SELECT
	MachineId,
	MAX(SwingImpact) AS SwingImpact
	FROM 
	CTENtileSwingImpact 
	WHERE Percentile<=@lineBottomPercentile
	GROUP BY MachineId
)
,CTENtileSwingImpact5 AS
(
	SELECT
	MachineId,
	MAX(SwingImpact) AS SwingImpact
	FROM 
	CTENtileSwingImpact 
	WHERE Percentile<=@boxBottomPercentile
	GROUP BY MachineId
)
,CTENtileSwingImpact15 AS
(
	SELECT
	MachineId,
	MAX(SwingImpact) AS SwingImpact
	FROM 
	CTENtileSwingImpact 
	WHERE Percentile<=@boxTopPercentile
	GROUP BY MachineId
)
,CTENtileSwingImpact19 AS
(
	SELECT
	MachineId,
	MAX(SwingImpact) AS SwingImpact
	FROM 
	CTENtileSwingImpact 
	WHERE Percentile<=@lineTopPercentile
	GROUP BY MachineId
)
,CTENtileBoomJack0 AS					   
(
	SELECT 
	BoomJack,
	ROW_NUMBER() OVER(PARTITION BY MachineId ORDER BY BoomJack ) AS RowNumber,
	MachineId
	FROM CTEFeaPerDay
)
,CTENtileBoomJack AS					   
(
	SELECT 
	BoomJack,
	a.RowNumber*1.0/b.TotalCount AS Percentile,
	a.MachineId
	FROM CTENtileBoomJack0 a
	INNER JOIN CTEFeaCountPerDayPerMachine b 
	ON a.MachineId=b.MachineId
)
,CTENtileBoomJack1 AS
(
	SELECT
	MachineId,
	MAX(BoomJack) AS BoomJack
	FROM 
	CTENtileBoomJack 
	WHERE Percentile<=@lineBottomPercentile
	GROUP BY MachineId
)
,CTENtileBoomJack5 AS
(
	SELECT
	MachineId,
	MAX(BoomJack) AS BoomJack
	FROM 
	CTENtileBoomJack 
	WHERE Percentile<=@boxBottomPercentile
	GROUP BY MachineId
)
,CTENtileBoomJack15 AS
(
	SELECT
	MachineId,
	MAX(BoomJack) AS BoomJack
	FROM 
	CTENtileBoomJack 
	WHERE Percentile<=@boxTopPercentile
	GROUP BY MachineId
)
,CTENtileBoomJack19 AS
(
	SELECT
	MachineId,
	MAX(BoomJack) AS BoomJack
	FROM 
	CTENtileBoomJack 
	WHERE Percentile<=@lineTopPercentile
	GROUP BY MachineId
)
,CTENtileMotorStall0 AS					   
(
	SELECT 
	MotorStall,
	ROW_NUMBER() OVER(PARTITION BY MachineId ORDER BY MotorStall ) AS RowNumber,
	MachineId
	FROM CTEFeaPerDay
)
,CTENtileMotorStall AS					   
(
	SELECT 
	MotorStall,
	a.RowNumber*1.0/b.TotalCount AS Percentile,
	a.MachineId
	FROM CTENtileMotorStall0 a
	INNER JOIN CTEFeaCountPerDayPerMachine b 
	ON a.MachineId=b.MachineId
)
,CTENtileMotorStall1 AS
(
	SELECT
	MachineId,
	MAX(MotorStall) AS MotorStall
	FROM 
	CTENtileMotorStall 
	WHERE Percentile<=@lineBottomPercentile
	GROUP BY MachineId
)
,CTENtileMotorStall5 AS
(
	SELECT
	MachineId,
	MAX(MotorStall) AS MotorStall
	FROM 
	CTENtileMotorStall 
	WHERE Percentile<=@boxBottomPercentile
	GROUP BY MachineId
)
,CTENtileMotorStall15 AS
(
	SELECT
	MachineId,
	MAX(MotorStall) AS MotorStall
	FROM 
	CTENtileMotorStall 
	WHERE Percentile<=@boxTopPercentile
	GROUP BY MachineId
)
,CTENtileMotorStall19 AS
(
	SELECT
	MachineId,
	MAX(MotorStall) AS MotorStall
	FROM 
	CTENtileMotorStall 
	WHERE Percentile<=@lineTopPercentile
	GROUP BY MachineId
)
,CTENtileOperatingTimePerShift0 AS					   
(
	SELECT 
	OperatingTimePerShift,
	ROW_NUMBER() OVER(PARTITION BY MachineId ORDER BY OperatingTimePerShift ) AS RowNumber,
	MachineId
	FROM CTEFeaPerShift
)
,CTENtileOperatingTimePerShift AS					   
(
	SELECT 
	OperatingTimePerShift,
	a.RowNumber*1.0/b.TotalCount AS Percentile,
	a.MachineId
	FROM CTENtileOperatingTimePerShift0	a
	INNER JOIN CTEFeaCountPerShiftPerMachine b 
	ON a.MachineId=b.MachineId
)
,CTENtileOperatingTimePerShift1 AS
(
	SELECT
	MachineId,
	MAX(OperatingTimePerShift) AS OperatingTimePerShift
	FROM 
	CTENtileOperatingTimePerShift 
	WHERE Percentile<=@lineBottomPercentile
	GROUP BY MachineId
)
,CTENtileOperatingTimePerShift5 AS
(
	SELECT
	MachineId,
	MAX(OperatingTimePerShift) AS OperatingTimePerShift
	FROM 
	CTENtileOperatingTimePerShift 
	WHERE Percentile<=@boxBottomPercentile
	GROUP BY MachineId
)
,CTENtileOperatingTimePerShift15 AS
(
	SELECT
	MachineId,
	MAX(OperatingTimePerShift) AS OperatingTimePerShift
	FROM 
	CTENtileOperatingTimePerShift 
	WHERE Percentile<=@boxTopPercentile
	GROUP BY MachineId
)
,CTENtileOperatingTimePerShift19 AS
(
	SELECT
	MachineId,
	MAX(OperatingTimePerShift) AS OperatingTimePerShift
	FROM 
	CTENtileOperatingTimePerShift 
	WHERE Percentile<=@lineTopPercentile
	GROUP BY MachineId
)
,CTENtileCyclesPerOperatingHour0 AS					   
(
	SELECT 
	CyclesPerOperatingHour,
	ROW_NUMBER() OVER(PARTITION BY MachineId ORDER BY CyclesPerOperatingHour ) AS RowNumber,
	MachineId
	FROM CTEFeaPerOperatingHour
)
,CTENtileCyclesPerOperatingHour AS					   
(
	SELECT 
	CyclesPerOperatingHour,
	a.RowNumber*1.0/b.TotalCount AS Percentile,
	a.MachineId
	FROM CTENtileCyclesPerOperatingHour0 a
	INNER JOIN CTEFeaCountPerOperatingHourPerMachine b 
	ON a.MachineId=b.MachineId
)
,CTENtileCyclesPerOperatingHour1 AS
(
	SELECT
	MachineId,
	MAX(CyclesPerOperatingHour) AS CyclesPerOperatingHour
	FROM CTENtileCyclesPerOperatingHour 
	WHERE Percentile<=@lineBottomPercentile
	GROUP BY MachineId
)
,CTENtileCyclesPerOperatingHour5 AS
(
	SELECT
	MachineId,
	MAX(CyclesPerOperatingHour) AS CyclesPerOperatingHour
	FROM CTENtileCyclesPerOperatingHour 
	WHERE Percentile<=@boxBottomPercentile
	GROUP BY MachineId
)
,CTENtileCyclesPerOperatingHour15 AS
(
	SELECT
	MachineId,
	MAX(CyclesPerOperatingHour) AS CyclesPerOperatingHour
	FROM CTENtileCyclesPerOperatingHour 
	WHERE Percentile<=@boxTopPercentile
	GROUP BY MachineId
)
,CTENtileCyclesPerOperatingHour19 AS
(
	SELECT
	MachineId,
	MAX(CyclesPerOperatingHour) AS CyclesPerOperatingHour
	FROM 
	CTENtileCyclesPerOperatingHour 
	WHERE Percentile<=@lineTopPercentile
	GROUP BY MachineId
)
,CTERunTimePerMachine AS 
(
	SELECT 
		MachineId,
		SUM(RunTimeSecond)/3600.0 AS RunTime
	FROM CTEFea
	GROUP BY MachineId
)
,CTEFaultsPerMachine AS 
(
	SELECT 
		e.Id AS MachineId,
		COUNT(1) AS FaultCount
	FROM @mineMachinesFault mf
	INNER JOIN Prevail.dbo.Equipment e WITH(NOLOCK) ON mf.EquipmentName= e.EquipmentName
	GROUP BY e.Id
)
,CTETripEventsPerMachine AS 
(
	SELECT CASE WHEN ISNULL(RunTime,0)=0 THEN 0 ELSE ISNULL(FaultCount,0)/RunTime * 100 END AS TripEventsPerHour
	,m.MachineId AS MachineId
	FROM @mineMachineList m
	LEFT OUTER JOIN CTERunTimePerMachine r
	ON m.MachineId = r.MachineId
	LEFT OUTER JOIN CTEFaultsPerMachine f
	ON m.MachineId = f.MachineId
)
,CTEFaultsPerDay AS 
(
	SELECT 
		e.Id AS MachineId,
		DATEDIFF(SECOND,@pBeginDateTime,EpisodeLocalTime)/@secondsPerDay+1 AS DayNumber,
		COUNT(1) AS FaultCount
	FROM @mineMachinesFault mf
	INNER JOIN Prevail.dbo.Equipment e WITH(NOLOCK) ON mf.EquipmentName= e.EquipmentName
	GROUP BY e.Id,DATEDIFF(SECOND,@pBeginDateTime,EpisodeLocalTime)/@secondsPerDay+1
)
,CTETripEventsPerDay AS 
(
	SELECT CASE WHEN ISNULL(RunTime,0)=0 THEN 0 ELSE ISNULL(FaultCount,0)/RunTime * 100 END AS TripEventsPerHour
	,m.MachineId,m.DayNumber
	FROM (SELECT DayNumber,MachineId FROM @dayTable ,@mineMachineList) m 
	LEFT OUTER JOIN CTEFeaPerDay r ON m.MachineId=r.MachineId AND m.DayNumber=r.DayNumber
	LEFT OUTER JOIN CTEFaultsPerDay f ON m.MachineId=f.MachineId AND m.DayNumber=f.DayNumber
)
,CTENtileTripEventsPerHour0 AS					   
(
	SELECT 
	TripEventsPerHour,
	ROW_NUMBER() OVER(PARTITION BY MachineId ORDER BY TripEventsPerHour ) AS RowNumber,
	MachineId
	FROM CTETripEventsPerDay
)
,CTENtileTripEventsPerHour AS					   
(
	SELECT 
	TripEventsPerHour,
	a.RowNumber*1.0/b.TotalCount AS Percentile,
	a.MachineId
	FROM CTENtileTripEventsPerHour0 a
	INNER JOIN CTEFeaCountPerDayPerMachine b 
	ON a.MachineId=b.MachineId
)
,CTENtileTripEventsPerHour1 AS
(
	SELECT
	MachineId,
	MAX(TripEventsPerHour) AS TripEventsPerHour
	FROM 
	CTENtileTripEventsPerHour 
	WHERE Percentile<=@lineBottomPercentile
	GROUP BY MachineId
)
,CTENtileTripEventsPerHour5 AS
(
	SELECT
	MachineId,
	MAX(TripEventsPerHour) AS TripEventsPerHour
	FROM 
	CTENtileTripEventsPerHour 
	WHERE Percentile<=@boxBottomPercentile
	GROUP BY MachineId
)
,CTENtileTripEventsPerHour15 AS
(
	SELECT
	MachineId,
	MAX(TripEventsPerHour) AS TripEventsPerHour
	FROM 
	CTENtileTripEventsPerHour 
	WHERE Percentile<=@boxTopPercentile
	GROUP BY MachineId
)
,CTENtileTripEventsPerHour19 AS
(
	SELECT
	MachineId,
	MAX(TripEventsPerHour) AS TripEventsPerHour
	FROM 
	CTENtileTripEventsPerHour 
	WHERE Percentile<=@lineTopPercentile
	GROUP BY MachineId
)
,CTECombined AS
(
	SELECT
	fml.MachineId AS MachineId,
	dm.MachineName,
	
	ISNULL(ct1.CycleTimeDuration,0) AS LineBottomCycleTimeDuration,
	ISNULL(ct5.CycleTimeDuration,0) AS BoxBottomCycleTimeDuration,
	ISNULL(ct15.CycleTimeDuration,0) AS BoxTopCycleTimeDuration,
	ISNULL(ct19.CycleTimeDuration,0) AS LineTopCycleTimeDuration,
	
	ISNULL(si1.SwingImpact,0) AS LineBottomSwingImpact,
	ISNULL(si5.SwingImpact,0) AS BoxBottomSwingImpact,
	ISNULL(si15.SwingImpact,0) AS BoxTopSwingImpact,
	ISNULL(si19.SwingImpact,0) AS LineTopSwingImpact,
	
	ISNULL(bj1.BoomJack,0) AS LineBottomBoomJack,
	ISNULL(bj5.BoomJack,0) AS BoxBottomBoomJack,
	ISNULL(bj15.BoomJack,0) AS BoxTopBoomJack,
	ISNULL(bj19.BoomJack,0) AS LineTopBoomJack,
	
	ISNULL(ms1.MotorStall,0) AS LineBottomMotorStall,
	ISNULL(ms5.MotorStall,0) AS BoxBottomMotorStall,
	ISNULL(ms15.MotorStall,0) AS BoxTopMotorStall,
	ISNULL(ms19.MotorStall,0) AS LineTopMotorStall,
	
	ISNULL(ot1.OperatingTimePerShift,0) AS LineBottomOperatingTimePerShift,
	ISNULL(ot5.OperatingTimePerShift,0) AS BoxBottomOperatingTimePerShift,
	ISNULL(ot15.OperatingTimePerShift,0) AS BoxTopOperatingTimePerShift,
	ISNULL(ot19.OperatingTimePerShift,0) AS LineTopOperatingTimePerShift,
	
	ISNULL(cp1.CyclesPerOperatingHour,0) AS LineBottomCyclesPerOperatingHour,
	ISNULL(cp5.CyclesPerOperatingHour,0) AS BoxBottomCyclesPerOperatingHour,
	ISNULL(cp15.CyclesPerOperatingHour,0) AS BoxTopCyclesPerOperatingHour,
	ISNULL(cp19.CyclesPerOperatingHour,0) AS LineTopCyclesPerOperatingHour,
	
	ISNULL(te1.TripEventsPerHour,0) AS LineBottomTripEventsPerHour,
	ISNULL(te5.TripEventsPerHour,0) AS BoxBottomTripEventsPerHour,
	ISNULL(te15.TripEventsPerHour,0) AS BoxTopTripEventsPerHour,
	ISNULL(te19.TripEventsPerHour,0) AS LineTopTripEventsPerHour,
	
	ISNULL(te.TripEventsPerHour,0) TripEventsPerHour
	FROM 
	CTEFeaMachineList fml
	INNER JOIN DimMachine dm ON fml.MachineId=dm.Id 
	LEFT OUTER JOIN CTENtileCycleTime1 ct1 
	ON fml.MachineId=ct1.MachineId
	LEFT OUTER JOIN CTENtileCycleTime5 ct5 
	ON fml.MachineId=ct5.MachineId 
	LEFT OUTER JOIN CTENtileCycleTime15 ct15 
	ON fml.MachineId=ct15.MachineId 
	LEFT OUTER JOIN CTENtileCycleTime19 ct19 
	ON fml.MachineId=ct19.MachineId 

	LEFT OUTER JOIN CTENtileSwingImpact1 si1 
	ON fml.MachineId=si1.MachineId
	LEFT OUTER JOIN CTENtileSwingImpact5 si5 
	ON fml.MachineId=si5.MachineId 
	LEFT OUTER JOIN CTENtileSwingImpact15 si15 
	ON fml.MachineId=si15.MachineId 
	LEFT OUTER JOIN CTENtileSwingImpact19 si19 
	ON fml.MachineId=si19.MachineId 

	LEFT OUTER JOIN CTENtileBoomJack1 bj1 
	ON fml.MachineId=bj1.MachineId
	LEFT OUTER JOIN CTENtileBoomJack5 bj5 
	ON fml.MachineId=bj5.MachineId 
	LEFT OUTER JOIN CTENtileBoomJack15 bj15 
	ON fml.MachineId=bj15.MachineId 
	LEFT OUTER JOIN CTENtileBoomJack19 bj19 
	ON fml.MachineId=bj19.MachineId 

	LEFT OUTER JOIN CTENtileMotorStall1 ms1 
	ON fml.MachineId=ms1.MachineId
	LEFT OUTER JOIN CTENtileMotorStall5 ms5 
	ON fml.MachineId=ms5.MachineId 
	LEFT OUTER JOIN CTENtileMotorStall15 ms15 
	ON fml.MachineId=ms15.MachineId 
	LEFT OUTER JOIN CTENtileMotorStall19 ms19 
	ON fml.MachineId=ms19.MachineId 

	LEFT OUTER JOIN CTENtileOperatingTimePerShift1 ot1 
	ON fml.MachineId=ot1.MachineId
	LEFT OUTER JOIN CTENtileOperatingTimePerShift5 ot5 
	ON fml.MachineId=ot5.MachineId 
	LEFT OUTER JOIN CTENtileOperatingTimePerShift15 ot15 
	ON fml.MachineId=ot15.MachineId 
	LEFT OUTER JOIN CTENtileOperatingTimePerShift19 ot19 
	ON fml.MachineId=ot19.MachineId 

	LEFT OUTER JOIN CTENtileCyclesPerOperatingHour1 cp1 
	ON fml.MachineId=cp1.MachineId
	LEFT OUTER JOIN CTENtileCyclesPerOperatingHour5 cp5 
	ON fml.MachineId=cp5.MachineId 
	LEFT OUTER JOIN CTENtileCyclesPerOperatingHour15 cp15 
	ON fml.MachineId=cp15.MachineId 
	LEFT OUTER JOIN CTENtileCyclesPerOperatingHour19 cp19 
	ON fml.MachineId=cp19.MachineId 

	LEFT OUTER JOIN CTENtileTripEventsPerHour1 te1 
	ON fml.MachineId=te1.MachineId
	LEFT OUTER JOIN CTENtileTripEventsPerHour5 te5 
	ON fml.MachineId=te5.MachineId 
	LEFT OUTER JOIN CTENtileTripEventsPerHour15 te15 
	ON fml.MachineId=te15.MachineId 
	LEFT OUTER JOIN CTENtileTripEventsPerHour19 te19 
	ON fml.MachineId=te19.MachineId 
	
	LEFT OUTER JOIN CTETripEventsPerMachine te
	ON fml.MachineId=te.MachineId
)
,CTENtileCycleTimeAllMachine0 AS					   
(
	SELECT 
	CycleTimeDuration,
	ROW_NUMBER() OVER(ORDER BY CycleTimeDuration ) AS RowNumber,
	0 AS MachineId
	FROM CTEFeaAllMachine
)
,CTENtileCycleTimeAllMachine AS					   
(
	SELECT 
	CycleTimeDuration,
	a.RowNumber*1.0/b.TotalCount AS Percentile,
	a.MachineId
	FROM CTENtileCycleTimeAllMachine0 a
	INNER JOIN CTEFeaCountAllMachine b 
	ON a.MachineId=b.MachineId
)
,CTENtileCycleTimeAllMachine1 AS
(
	SELECT
	0 AS MachineId,
	MAX(CycleTimeDuration) AS CycleTimeDuration
	FROM CTENtileCycleTimeAllMachine 
	WHERE Percentile<=@lineBottomPercentile
)
,CTENtileCycleTimeAllMachine5 AS
(
	SELECT
	0 AS MachineId,
	MAX(CycleTimeDuration) AS CycleTimeDuration
	FROM CTENtileCycleTimeAllMachine 
	WHERE Percentile<=@boxMiddlePercetile
)
,CTENtileCycleTimeAllMachine15 AS
(
	SELECT
	0 AS MachineId,
	MAX(CycleTimeDuration) AS CycleTimeDuration
	FROM CTENtileCycleTimeAllMachine 
	WHERE Percentile<=@boxMiddlePercetile
)
,CTENtileCycleTimeAllMachine19 AS
(
	SELECT
	0 AS MachineId,
	MAX(CycleTimeDuration) AS CycleTimeDuration
	FROM CTENtileCycleTimeAllMachine 
	WHERE Percentile<=@lineTopPercentile
)
,CTENtileSwingImpactAllMachine0 AS					   
(
	SELECT 
	SwingImpact,
	ROW_NUMBER() OVER(ORDER BY SwingImpact ) AS RowNumber,
	0 AS MachineId
	FROM CTEFeaPerDayAvgMachine
)
,CTENtileSwingImpactAllMachine AS					   
(
	SELECT 
	SwingImpact,
	a.RowNumber*1.0/b.TotalCount AS Percentile,
	a.MachineId
	FROM CTENtileSwingImpactAllMachine0 a
	INNER JOIN CTEFeaCountPerDayAllMachine b 
	ON a.MachineId=b.MachineId
)
,CTENtileSwingImpactAllMachine1 AS
(
	SELECT
	0 AS MachineId,
	MAX(SwingImpact) AS SwingImpact
	FROM CTENtileSwingImpactAllMachine 
	WHERE Percentile<=@lineBottomPercentile
)
,CTENtileSwingImpactAllMachine5 AS
(
	SELECT
	0 AS MachineId,
	MAX(SwingImpact) AS SwingImpact
	FROM CTENtileSwingImpactAllMachine 
	WHERE Percentile<=@boxMiddlePercetile
)
,CTENtileSwingImpactAllMachine15 AS
(
	SELECT
	0 AS MachineId,
	MAX(SwingImpact) AS SwingImpact
	FROM CTENtileSwingImpactAllMachine 
	WHERE Percentile<=@boxMiddlePercetile
)
,CTENtileSwingImpactAllMachine19 AS
(
	SELECT
	0 AS MachineId,
	MAX(SwingImpact) AS SwingImpact
	FROM CTENtileSwingImpactAllMachine 
	WHERE Percentile<=@lineTopPercentile
)
,CTENtileBoomJackAllMachine0 AS					   
(
	SELECT 
	BoomJack,
	ROW_NUMBER() OVER(ORDER BY BoomJack ) AS RowNumber,
	0 AS MachineId
	FROM CTEFeaPerDayAvgMachine
)
,CTENtileBoomJackAllMachine AS					   
(
	SELECT 
	BoomJack,
	a.RowNumber*1.0/b.TotalCount AS Percentile,
	a.MachineId
	FROM CTENtileBoomJackAllMachine0 a
	INNER JOIN CTEFeaCountPerDayAllMachine b 
	ON a.MachineId=b.MachineId
)
,CTENtileBoomJackAllMachine1 AS
(
	SELECT
	0 AS MachineId,
	MAX(BoomJack) AS BoomJack
	FROM CTENtileBoomJackAllMachine 
	WHERE Percentile<=@lineBottomPercentile
)
,CTENtileBoomJackAllMachine5 AS
(
	SELECT
	0 AS MachineId,
	MAX(BoomJack) AS BoomJack
	FROM CTENtileBoomJackAllMachine 
	WHERE Percentile<=@boxMiddlePercetile
)
,CTENtileBoomJackAllMachine15 AS
(
	SELECT
	0 AS MachineId,
	MAX(BoomJack) AS BoomJack
	FROM CTENtileBoomJackAllMachine 
	WHERE Percentile<=@boxMiddlePercetile
)
,CTENtileBoomJackAllMachine19 AS
(
	SELECT
	0 AS MachineId,
	MAX(BoomJack) AS BoomJack
	FROM CTENtileBoomJackAllMachine 
	WHERE Percentile<=@lineTopPercentile
)
,CTENtileMotorStallAllMachine0 AS					   
(
	SELECT 
	MotorStall,
	ROW_NUMBER() OVER(ORDER BY MotorStall ) AS RowNumber,
	0 AS MachineId
	FROM CTEFeaPerDayAvgMachine
)
,CTENtileMotorStallAllMachine AS					   
(
	SELECT 
	MotorStall,
	a.RowNumber*1.0/b.TotalCount AS Percentile,
	a.MachineId
	FROM CTENtileMotorStallAllMachine0 a
	INNER JOIN CTEFeaCountPerDayAllMachine b ON a.MachineId=b.MachineId
)
,CTENtileMotorStallAllMachine1 AS
(
	SELECT
	0 AS MachineId,
	MAX(MotorStall) AS MotorStall
	FROM CTENtileMotorStallAllMachine 
	WHERE Percentile<=@lineBottomPercentile
)
,CTENtileMotorStallAllMachine5 AS
(
	SELECT
	0 AS MachineId,
	MAX(MotorStall) AS MotorStall
	FROM CTENtileMotorStallAllMachine 
	WHERE Percentile<=@boxMiddlePercetile
)
,CTENtileMotorStallAllMachine15 AS
(
	SELECT
	0 AS MachineId,
	MAX(MotorStall) AS MotorStall
	FROM CTENtileMotorStallAllMachine 
	WHERE Percentile<=@boxMiddlePercetile
)
,CTENtileMotorStallAllMachine19 AS
(
	SELECT
	0 AS MachineId,
	MAX(MotorStall) AS MotorStall
	FROM CTENtileMotorStallAllMachine 
	WHERE Percentile<=@lineTopPercentile
)
,CTENtileOperatingTimePerShiftAllMachine0 AS					   
(
	SELECT 
	OperatingTimePerShift,
	ROW_NUMBER() OVER(ORDER BY OperatingTimePerShift ) AS RowNumber,
	0 AS MachineId
	FROM CTEFeaPerShiftAvgMachine
)
,CTENtileOperatingTimePerShiftAllMachine AS					   
(
	SELECT 
	OperatingTimePerShift,
	a.RowNumber*1.0/b.TotalCount AS Percentile,
	a.MachineId
	FROM CTENtileOperatingTimePerShiftAllMachine0 a
	INNER JOIN CTEFeaCountPerShiftAllMachine b ON a.MachineId=b.MachineId
)
,CTENtileOperatingTimePerShiftAllMachine1 AS
(
	SELECT
	0 AS MachineId,
	MAX(OperatingTimePerShift) AS OperatingTimePerShift
	FROM 
	CTENtileOperatingTimePerShiftAllMachine 
	WHERE Percentile<=@lineBottomPercentile
)
,CTENtileOperatingTimePerShiftAllMachine5 AS
(
	SELECT
	0 AS MachineId,
	MAX(OperatingTimePerShift) AS OperatingTimePerShift
	FROM 
	CTENtileOperatingTimePerShiftAllMachine 
	WHERE Percentile<=@boxMiddlePercetile
)
,CTENtileOperatingTimePerShiftAllMachine15 AS
(
	SELECT
	0 AS MachineId,
	MAX(OperatingTimePerShift) AS OperatingTimePerShift
	FROM 
	CTENtileOperatingTimePerShiftAllMachine 
	WHERE Percentile<=@boxMiddlePercetile
)
,CTENtileOperatingTimePerShiftAllMachine19 AS
(
	SELECT
	0 AS MachineId,
	MAX(OperatingTimePerShift) AS OperatingTimePerShift
	FROM 
	CTENtileOperatingTimePerShiftAllMachine 
	WHERE Percentile<=@lineTopPercentile
)
,CTENtileCyclesPerOperatingHourAllMachine0 AS					   
(
	SELECT 
	CyclesPerOperatingHour,
	ROW_NUMBER() OVER(ORDER BY CyclesPerOperatingHour ) AS RowNumber,
	0 AS MachineId
	FROM CTEFeaPerOperatingHour
)
,CTENtileCyclesPerOperatingHourAllMachine AS					   
(
	SELECT 
	CyclesPerOperatingHour,
	a.RowNumber*1.0/b.TotalCount AS Percentile,
	a.MachineId
	FROM CTENtileCyclesPerOperatingHourAllMachine0 a
	INNER JOIN CTEFeaCountPerOperatingHourAllMachine b ON a.MachineId=b.MachineId
)
,CTENtileCyclesPerOperatingHourAllMachine1 AS
(
	SELECT
	0 AS MachineId,
	MAX(CyclesPerOperatingHour) AS CyclesPerOperatingHour
	FROM 
	CTENtileCyclesPerOperatingHourAllMachine 
	WHERE Percentile<=@lineBottomPercentile
)
,CTENtileCyclesPerOperatingHourAllMachine5 AS
(
	SELECT
	0 AS MachineId,
	MAX(CyclesPerOperatingHour) AS CyclesPerOperatingHour
	FROM 
	CTENtileCyclesPerOperatingHourAllMachine 
	WHERE Percentile<=@boxMiddlePercetile
)
,CTENtileCyclesPerOperatingHourAllMachine15 AS
(
	SELECT
	0 AS MachineId,
	MAX(CyclesPerOperatingHour) AS CyclesPerOperatingHour
	FROM 
	CTENtileCyclesPerOperatingHourAllMachine 
	WHERE Percentile<=@boxMiddlePercetile
)
,CTENtileCyclesPerOperatingHourAllMachine19 AS
(
	SELECT
	0 AS MachineId,
	MAX(CyclesPerOperatingHour) AS CyclesPerOperatingHour
	FROM 
	CTENtileCyclesPerOperatingHourAllMachine 
	WHERE Percentile<=@lineTopPercentile
)
,CTERunTimeAllMachine AS 
(
	SELECT 
		SUM(RunTimeSecond)/3600.0 AS RunTime
	FROM CTEFeaAllMachine
)
,CTEFaultsAllMachine AS 
(
	SELECT 
		COUNT(1) AS FaultCount
	FROM @allMachinesFault mf
)
,CTETripEventsAllMachine AS 
(
	SELECT CASE WHEN ISNULL(RunTime,0)=0 THEN 0 ELSE ISNULL(FaultCount,0)/RunTime * 100 END AS TripEventsPerHour
	,0 AS MachineId
	FROM CTERunTimeAllMachine,CTEFaultsAllMachine
)
,CTEFaultsPerDayAllMachine AS 
(
	SELECT 
		0 AS MachineId,
		DATEDIFF(SECOND,@pBeginDateTime,EpisodeLocalTime)/@secondsPerDay+1 AS DayNumber,
		COUNT(1) AS FaultCount
	FROM @allMachinesFault mf
	GROUP BY DATEDIFF(SECOND,@pBeginDateTime,EpisodeLocalTime)/@secondsPerDay+1
)
,CTETripEventsPerDayAllMachine AS 
(
	SELECT CASE WHEN ISNULL(RunTime,0)=0 THEN 0 ELSE ISNULL(FaultCount,0)/RunTime * 100 END AS TripEventsPerHour
	,0 AS MachineId
	FROM @dayTable d
	LEFT OUTER JOIN CTEFaultsPerDayAllMachine f ON d.DayNumber=f.DayNumber
	LEFT OUTER JOIN CTEFeaPerDaySumMachine r ON d.DayNumber=r.DayNumber
)
,CTETripEventsPerDayAllMachineCount AS
(
	SELECT
	0 AS MachineId,
	COUNT(1) AS TotalCount
	FROM CTETripEventsPerDayAllMachine
)
,CTENtileTripEventsPerHourAllMachine0 AS					   
(
	SELECT 
	TripEventsPerHour,
	ROW_NUMBER() OVER(ORDER BY TripEventsPerHour ) AS RowNumber,
	0 AS MachineId
	FROM CTETripEventsPerDayAllMachine
)
,CTENtileTripEventsPerHourAllMachine AS					   
(
	SELECT 
	TripEventsPerHour,
	a.RowNumber*1.0/b.TotalCount AS Percentile,
	a.MachineId
	FROM CTENtileTripEventsPerHourAllMachine0 a
	INNER JOIN CTETripEventsPerDayAllMachineCount b ON a.MachineId=b.MachineId
)
,CTENtileTripEventsPerHourAllMachine1 AS
(
	SELECT
	0 AS MachineId,
	MAX(TripEventsPerHour) AS TripEventsPerHour
	FROM CTENtileTripEventsPerHourAllMachine 
	WHERE Percentile<=@lineBottomPercentile
)
,CTENtileTripEventsPerHourAllMachine5 AS
(
	SELECT
	0 AS MachineId,
	MAX(TripEventsPerHour) AS TripEventsPerHour
	FROM CTENtileTripEventsPerHourAllMachine 
	WHERE Percentile<=@boxMiddlePercetile
)
,CTENtileTripEventsPerHourAllMachine15 AS
(
	SELECT
	0 AS MachineId,
	MAX(TripEventsPerHour) AS TripEventsPerHour
	FROM CTENtileTripEventsPerHourAllMachine 
	WHERE Percentile<=@boxMiddlePercetile
)
,CTENtileTripEventsPerHourAllMachine19 AS
(
	SELECT
	0 AS MachineId,
	MAX(TripEventsPerHour) AS TripEventsPerHour
	FROM CTENtileTripEventsPerHourAllMachine 
	WHERE Percentile<=@lineTopPercentile
)
,CTECombinedAllMachine AS
(
	SELECT
	0 AS MachineId,
	'Average All Shovels' AS MachineName,
	
	ct1.CycleTimeDuration AS LineBottomCycleTimeDuration,
	ct5.CycleTimeDuration AS BoxBottomCycleTimeDuration,
	ct15.CycleTimeDuration AS BoxTopCycleTimeDuration,
	ct19.CycleTimeDuration AS LineTopCycleTimeDuration,
	
	si1.SwingImpact AS LineBottomSwingImpact,
	si5.SwingImpact AS BoxBottomSwingImpact,
	si15.SwingImpact AS BoxTopSwingImpact,
	si19.SwingImpact AS LineTopSwingImpact,
	
	bj1.BoomJack AS LineBottomBoomJack,
	bj5.BoomJack AS BoxBottomBoomJack,
	bj15.BoomJack AS BoxTopBoomJack,
	bj19.BoomJack AS LineTopBoomJack,
	
	ms1.MotorStall AS LineBottomMotorStall,
	ms5.MotorStall AS BoxBottomMotorStall,
	ms15.MotorStall AS BoxTopMotorStall,
	ms19.MotorStall AS LineTopMotorStall,
	
	ot1.OperatingTimePerShift AS LineBottomOperatingTimePerShift,
	ot5.OperatingTimePerShift AS BoxBottomOperatingTimePerShift,
	ot15.OperatingTimePerShift AS BoxTopOperatingTimePerShift,
	ot19.OperatingTimePerShift AS LineTopOperatingTimePerShift,
	
	cp1.CyclesPerOperatingHour AS LineBottomCyclesPerOperatingHour,
	cp5.CyclesPerOperatingHour AS BoxBottomCyclesPerOperatingHour,
	cp15.CyclesPerOperatingHour AS BoxTopCyclesPerOperatingHour,
	cp19.CyclesPerOperatingHour AS LineTopCyclesPerOperatingHour,
	
	te1.TripEventsPerHour AS LineBottomTripEventsPerHour,
	te5.TripEventsPerHour AS BoxBottomTripEventsPerHour,
	te15.TripEventsPerHour AS BoxTopTripEventsPerHour,
	te19.TripEventsPerHour AS LineTopTripEventsPerHour,
	
	te.TripEventsPerHour AS TripEventsPerHour
	FROM 
	CTENtileCycleTimeAllMachine1 ct1 , 
	CTENtileCycleTimeAllMachine5 ct5 ,
	CTENtileCycleTimeAllMachine15 ct15 ,
	CTENtileCycleTimeAllMachine19 ct19 ,

	CTENtileSwingImpactAllMachine1 si1 ,
	CTENtileSwingImpactAllMachine5 si5 ,
	CTENtileSwingImpactAllMachine15 si15 ,
	CTENtileSwingImpactAllMachine19 si19 ,

	CTENtileBoomJackAllMachine1 bj1 ,
	CTENtileBoomJackAllMachine5 bj5 ,
	CTENtileBoomJackAllMachine15 bj15 ,
	CTENtileBoomJackAllMachine19 bj19 ,

	CTENtileMotorStallAllMachine1 ms1 ,
	CTENtileMotorStallAllMachine5 ms5 , 
	CTENtileMotorStallAllMachine15 ms15 ,
	CTENtileMotorStallAllMachine19 ms19 ,

	CTENtileOperatingTimePerShiftAllMachine1 ot1 ,
	CTENtileOperatingTimePerShiftAllMachine5 ot5 ,
	CTENtileOperatingTimePerShiftAllMachine15 ot15 ,
	CTENtileOperatingTimePerShiftAllMachine19 ot19 ,

	CTENtileCyclesPerOperatingHourAllMachine1 cp1 ,
	CTENtileCyclesPerOperatingHourAllMachine5 cp5 ,
	CTENtileCyclesPerOperatingHourAllMachine15 cp15 ,
	CTENtileCyclesPerOperatingHourAllMachine19 cp19 ,

	CTENtileTripEventsPerHourAllMachine1 te1 ,
	CTENtileTripEventsPerHourAllMachine5 te5 ,
	CTENtileTripEventsPerHourAllMachine15 te15 ,
	CTENtileTripEventsPerHourAllMachine19 te19 ,
	
	CTETripEventsAllMachine te
)
,CTEFinalResult AS
(
	SELECT * FROM CTECombinedAllMachine UNION ALL
	SELECT * FROM CTECombined
)

SELECT * FROM CTEFinalResult

5 thoughts on “Developers and Reporting Services”

  2. Wow, that’s really something! Just a sanity check here – is your company okay with you posting this? It’s quite a bit of very specific business logic, and they might not want proprietary info like this out in the wild.

    Reply ↓

    • I talked with my manager about it before posting. He was ok with it… not saying that the legal department might not second guess that. My take on it is most of the logic is calculating percentages, and converting seconds into hours by multiplying everything by 3600. Some additional magic numbers for upper and lower limits… and if anyone can pull some trade secrets from that 1000+ liner, more power to them. 🙂

      Reply ↓

  4. Pingback: Something for the Weekend - SQL Server Links 17/05/13 • John Sansom

Leave a Reply

Your email address will not be published.

CAPTCHA

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.