-- This is a query to output all dedup numbers for past 24 hours, 7 days and all time.  3 separate SELECT statements are brought together with a UNION ALL statement.  The DB decides on a weird ordering, so the following order 24 hrs/ all time/ 7 days   makes the output correctly as 24 hrs/7days/all time.
--You can change the column names by changing the text after "AS"  make sure to repeat the change for each query.   You can change the timespan for each query by modifying the  DATEDIFF(X, UTCBigIntToNomTime(endTime), GETDATE()) <= Y       X can be   minue/hour/day/week/month.  It basically says when the difference between the job's endtime and right now (getdate function)  is less than Y  units of X.  You can change it to be an absolute time range by removing that statement and putting a  UTCBigIntToNomTime(endTime) BETWEEN '2010-04-01 00:00:00' AND '2010-04-05 00:00:00'   which puts the time range between midnight april first to midnight april 5th inclusive.
 
 
SELECT 'Last 24 hours' AS 'Time Frame', COALESCE(CAST(SUM(preSISSize)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Pre Dedup Size (GB)', COALESCE(CAST(SUM(bytesWritten)/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Post Dedup Size (GB)', COALESCE(CAST(SUM(preSisSize)/SUM(bytesWritten) AS NUMERIC (20,2)), 1) || 'x' AS 'Deduplication Factor', COALESCE(CAST((SUM(preSisSize) - SUM(bytesWritten)) / SUM(preSisSize) * 100 AS NUMERIC(20,2)), 0) || '%' AS 'Deduplication Rate', COALESCE(CAST((SUM(preSisSize) - SUM(bytesWritten))/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Total Savings (GB)' FROM domain_JobArchive WHERE DATEDIFF(hour, UtcBigIntToNomTime(endTime), GETDATE()) <=24 and presisSize != bytesWritten and presissize !=32768
UNION ALL
SELECT 'All Time' AS 'Time Frame', COALESCE(CAST(SUM(preSISSize)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Pre Dedup Size (GB)', COALESCE(CAST(SUM(bytesWritten)/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Post Dedup Size (GB)', COALESCE(CAST(SUM(preSisSize)/SUM(bytesWritten) AS NUMERIC (20,2)), 1) || 'x' AS 'Deduplication Factor', COALESCE(CAST((SUM(preSisSize) - SUM(bytesWritten)) / SUM(preSisSize) * 100 AS NUMERIC(20,2)), 0) || '%' AS 'Deduplication Rate', COALESCE(CAST((SUM(preSisSize) - SUM(bytesWritten))/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Total Savings (GB)' FROM domain_JobArchive WHERE presisSize != bytesWritten and presissize !=32768
UNION ALL
SELECT 'Last 7 days' AS 'Time Frame', COALESCE(CAST(SUM(preSISSize)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Pre Dedup Size (GB)', COALESCE(CAST(SUM(bytesWritten)/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Post Dedup Size (GB)', COALESCE(CAST(SUM(preSisSize)/SUM(bytesWritten) AS NUMERIC (20,2)), 1) || 'x' AS 'Deduplication Factor', COALESCE(CAST((SUM(preSisSize) - SUM(bytesWritten)) / SUM(preSisSize) * 100 AS NUMERIC(20,2)), 0) || '%' AS 'Deduplication Rate', COALESCE(CAST((SUM(preSisSize) - SUM(bytesWritten))/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Total Savings (GB)' FROM domain_JobArchive WHERE DATEDIFF(day, UtcBigIntToNomTime(endTime), GETDATE()) <=7 and presisSize != bytesWritten and presissize !=32768
 
 

-- If you wanted to group the overall dedup rate for each policy,   you can do something like this. 
-- Here we added a GROUP BY clause at the end and put the policyName as a column.  This basically says,  for each policyname, calculate all the columns.  The last ORDER BY clause just sorts the results by the policyname.  Since certain policies could have 0 bytesWritten we need to take care not to do a divide by zero.  Hence the addition of a HAVING clause to filter those 0's out.
 
SELECT policyName AS 'Policy Name', COALESCE(CAST(SUM(preSISSize)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Pre Dedup Size (GB)', COALESCE(CAST(SUM(bytesWritten)/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Post Dedup Size (GB)', COALESCE(CAST(SUM(preSisSize)/SUM(bytesWritten) AS NUMERIC (20,2)), 1) || 'x' AS 'Deduplication Factor', COALESCE(CAST((SUM(preSisSize) - SUM(bytesWritten)) / SUM(preSisSize) * 100 AS NUMERIC(20,2)), 0) || '%' AS 'Deduplication Rate', COALESCE(CAST((SUM(preSisSize) - SUM(bytesWritten))/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Total Savings (GB)' FROM domain_JobArchive WHERE presisSize != bytesWritten and presissize !=32768 GROUP BY policyName HAVING SUM(bytesWritten) > 0 AND SUM(bytesWritten) > 0 ORDER BY policyName