SQL Help

[Douglas_A]

Hi All, 

im trying to get figure out this report and i have it working as i want it to but i cant for the life of me figure out how to translate the MasterServer ID into the name when the report generates, im hopeing someone on here might be able to take a quick look and tell me what im doing wrong. 

Working report:

SELECT TOP 10 START at 1
masterServerId as "Master Server",
clientName as 'Client',
policyName as "Policy",

COALESCE(CAST(SUM(preSISSize)/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Pre Dedup Size in GB',
COALESCE(CAST(SUM(bytesWritten)/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Post Dedup Size in GB',
COALESCE(CAST((SUM(preSISSize) - SUM(bytesWritten))/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Total Savings in GB',
IF sum(domain_JobArchive.preSISSize)=0 THEN 0 ELSE ((sum(domain_JobArchive.preSISSize)-sum(domain_JobArchive.bytesWritten)) * 100.0/sum(domain_JobArchive.preSISSize)) ENDIF 'Percent Savings'

FROM
domain_JobArchive

WHERE
DATEDIFF(day, UtcBigIntToNomTime(endTime), GETDATE()) < 7 and presisSize != bytesWritten and presissize !=32768

GROUP BY
masterServerId,
clientName,
policyName
ORDER BY
"Percent Savings";

What i think i need to do:

SELECT TOP 10 START at 1
masterServerId as "Master Server",
clientName as 'Client',
policyName as "Policy",

COALESCE(CAST(SUM(preSISSize)/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Pre Dedup Size in GB',
COALESCE(CAST(SUM(bytesWritten)/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Post Dedup Size in GB',
COALESCE(CAST((SUM(preSISSize) - SUM(bytesWritten))/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Total Savings in GB',
IF sum(domain_JobArchive.preSISSize)=0 THEN 0 ELSE ((sum(domain_JobArchive.preSISSize)-sum(domain_JobArchive.bytesWritten)) * 100.0/sum(domain_JobArchive.preSISSize)) ENDIF 'Percent Savings'

FROM
domain_JobArchive, domain_Entity, domain_MasterServer

WHERE
DATEDIFF(day, UtcBigIntToNomTime(endTime), GETDATE()) < 7 and presisSize != bytesWritten and presissize !=32768
AND
domain_Entity where domain_MasterServer.id = domain_JobArchive.masterServerId

GROUP BY
masterServerId,
clientName,
policyName
ORDER BY
"Percent Savings";

Any Help is really appreciated.

[Deeps]

Try this.. select domain_masterserver.friendlyname as "Master Server", domain_JobArchive.clientName as "Client", domain_JobArchive.policyName as "Policy", COALESCE(CAST(SUM(preSISSize)/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Pre Dedup Size in GB', COALESCE(CAST(SUM(bytesWritten)/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Post Dedup Size in GB', COALESCE(CAST((SUM(preSISSize) - SUM(bytesWritten))/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Total Savings in GB', IF sum(domain_JobArchive.preSISSize)=0 THEN 0 ELSE ((sum(domain_JobArchive.preSISSize)-sum(domain_JobArchive.bytesWritten)) * 100.0/sum(domain_JobArchive.preSISSize)) ENDIF 'Percent Savings' FROM domain_masterserver, domain_JobArchive WHERE DATEDIFF(day, UtcBigIntToNomTime(endTime), GETDATE()) < 7 and presisSize != bytesWritten and presissize !=32768 AND domain_masterserver.id=domain_jobarchive.masterserverid GROUP BY Friendlyname, clientName, policyName ORDER BY "Percent Savings"; Deeps

[RiaanBadenhorst]

You need to have 

select 

dja.xxx as "blah"

dms.friendlyName as "Master Server Name"

dja.yyy as "blah blah"

from

domain_MasterServer dms, domain_JobArchive dja

where

dms.id=dja.masterServerId