cancel
Showing results for 
Search instead for 
Did you mean: 

SQL query - Backup policy report

Hi,

I'm trying to get a report with the following fields

"Policy Name" "GB Written" "Master Server"

I tried to get the report by 2 ways

SELECT distinct
d.policyName AS "Policy Name",
CAST(SUM(d.sizeOfImageInKBytes)/1024/1024/1024 AS DEC(8,2)) AS "GB Written",
m.masterServerName AS "Master Server"

FROM domain_image d, nom_NBJob m

WHERE DATEDIFF(day,UTCBigIntToNomTime(d.writeEndTime), GETDATE()) <= 1
AND d.masterServerId=nom_NBJob.masterServerId
GROUP BY d.policyName, m.masterServerName
ORDER by 1
SELECT distinct
d.policyName AS "Policy Name",
CAST(SUM(d.sizeOfImageInKBytes)/1024/1024/1024 AS DEC(8,2)) AS "GB Written",
m.masterServerName AS "Master Server"

FROM domain_image d

INNER JOIN nom_NBJob AS m
	ON d.masterServerId=m.masterServerId AND
	DATEDIFF(day,UTCBigIntToNomTime(d.writeEndTime), GETDATE()) <= 1 AND
	d.backupStatus=0
GROUP BY d.policyName, m.masterServerName
ORDER by 1

But both queries are taking much time to get the information, is there a way to improve the performance of the query or another query to get the required information?