SELECT 
DM.friendlyName as "Master", 
JA.clientName as "Client",
JA.policyName  as "Policy",
JA.scheduleName as "Schedule",
( SELECT UtcBigIntToNomTime(MAX(JA1.endTime)) as "Last successful"

	FROM   domain_JobArchive JA1
	
	WHERE JA1.masterServerId = DM.id
		AND JA1.Id = JA1.parentJobId
		AND JA1.clientName = JA.clientName 
		AND JA1.scheduleName = JA.scheduleName
		AND JA1.policyName = JA.policyName
		AND (JA1.statusCode = 0
			OR (JA1.policyType IN (0, 13) 
				AND JA1.statusCode = 1))),
					
( SELECT COUNT((CASE
	WHEN 
		(JA2.statusCode = 0
			OR (JA2.policyType IN (0, 13) 
				AND JA2.statusCode = 1)) THEN 1
		ELSE NULL
	END)) as "Total Successful Backups"
	
	FROM domain_JobArchive JA2
	
	WHERE JA2.masterServerId = DM.id
		AND JA2.Id = JA2.parentJobId
		AND JA2.clientId = JA.clientId
		AND JA2.policyName = JA.policyName
		AND DATEDIFF(day,UTCBigIntToNomTime(JA2.endTime), GETDATE()) = 1),
			
COUNT((CASE
	WHEN 
		(JA.statusCode = 0
			OR (JA.policyType IN (0, 13) 
				AND JA.statusCode = 1)) THEN 1
		ELSE NULL
	END)) as "Successful Scheduled",
	
COUNT(*) as "Total Scheduled",

CAST(100*( SELECT COUNT((CASE
	WHEN 
		(JA2.statusCode = 0
			OR (JA2.policyType IN (0, 13) 
				AND JA2.statusCode = 1)) THEN 1
		ELSE NULL
	END)) as "Total Successful Backups"
	
	FROM domain_JobArchive JA2
	
	WHERE JA2.masterServerId = DM.id
		AND JA2.Id = JA2.parentJobId
		AND JA2.clientId = JA.clientId
		AND JA2.policyName = JA.policyName
		AND DATEDIFF(day,UTCBigIntToNomTime(JA2.endTime), GETDATE()) = 1) /COUNT(*)  AS NUMERIC(20,2)) AS "KPI"

FROM domain_JobArchive JA, domain_ScheduledJob SJ, domain_MasterServer DM

WHERE JA.masterServerId= SJ.masterServerId
	AND JA.clientName = SJ.clientName
AND JA.scheduleName = SJ.scheduleName 
	AND JA.id = SJ.jobId
AND  JA.id IN(SELECT DISTINCT jobId FROM domain_ScheduledJob)
	AND JA.masterServerId = DM.Id
	AND DATEDIFF(day,UTCBigIntToNomTime(JA.endTime), GETDATE()) = 1
AND DATEDIFF(day,UTCBigIntToNomTime(SJ.scheduledTime), GETDATE()) = 1
GROUP BY DM.friendlyName , JA.clientName, JA.scheduleName, DM.id, JA.clientid, JA.policyId, JA.policyType, JA.policyName
ORDER BY  DM.friendlyName, "KPI"