Archive Shuttle 11.5 - SQL Best Practices

Overall statistics

-- Get overall stats

WITH CTE_CaseSummary AS

(

   SELECT vdi.CaseID,

          COUNT(1) AS ItemCount,

          SUM(CAST(vdi.size AS BIGINT)) AS ItemSize

   FROM dbo.view_DiscoveredItems vdi

   GROUP BY vdi.CaseID

)

SELECT SUM(ctecs.ItemCount) AS CountOfItems,

      SUM((ctecs.ItemSize / 1024)) AS TotalSizeInMB

FROM dbo.view_case vc

   LEFT JOIN CTE_CaseSummary ctecs ON ctecs.CaseID = vc.CaseID

WHERE vc.[Type] = 101;

-- 5. Get overall stats with date filter

WITH CTE_CaseSummary AS

(

   SELECT vdi.CaseID,

          COUNT(1) AS ItemCount,

          SUM(CAST(vdi.size AS BIGINT)) AS ItemSize

   FROM dbo.view_DiscoveredItems vdi

WHERE vdi.MailDate BETWEEN '2016-03-01' AND '2021-07-31'

   GROUP BY vdi.CaseID

)

SELECT SUM(ctecs.ItemCount) AS CountOfItems,

      SUM((ctecs.ItemSize / 1024)) AS TotalSizeInMB

FROM dbo.view_case vc

   LEFT JOIN CTE_CaseSummary ctecs ON ctecs.CaseID = vc.CaseID

WHERE vc.[Type] = 101;

Statistics per DA case

-- Get stats per DA case

WITH CTE_CaseSummary AS

(

   SELECT vdi.CaseID,

          COUNT(1) AS ItemCount,

          SUM(CAST(vdi.size AS BIGINT)) AS ItemSize

   FROM dbo.view_DiscoveredItems vdi

   GROUP BY vdi.CaseID

)

SELECT vc.CaseID,

      vc.[Name],

      ctecs.ItemCount AS CountOfItems,

      (ctecs.ItemSize / 1024) AS TotalSizeInMB

FROM dbo.view_case vc

   LEFT JOIN CTE_CaseSummary ctecs ON ctecs.CaseID = vc.CaseID

WHERE vc.[Type] = 101

ORDER BY vc.CaseID;

Statistics per DA case with date filter

-- Get stats per DA case with date filter

WITH CTE_CaseSummary AS

(

   SELECT vdi.CaseID,

          COUNT(1) AS ItemCount,

          SUM(CAST(vdi.size AS BIGINT)) AS ItemSize

   FROM dbo.view_DiscoveredItems vdi

WHERE vdi.MailDate BETWEEN '2016-03-01' AND '2021-07-31'

   GROUP BY vdi.CaseID

)

SELECT vc.CaseID,

      vc.[Name],

      ctecs.ItemCount AS CountOfItems,

      (ctecs.ItemSize / 1024) AS TotalSizeInMB

FROM dbo.view_case vc

   LEFT JOIN CTE_CaseSummary ctecs ON ctecs.CaseID = vc.CaseID

WHERE vc.[Type] = 101

ORDER BY vc.CaseID;

Statistics per DA case with minimum and maximum MailDate

-- Get stats per DA case with minimum and maximum MailDate

WITH CTE_CaseSummary AS

(

   SELECT vdi.CaseID,

          COUNT(1) AS ItemCount,

          SUM(CAST(vdi.size AS BIGINT)) AS ItemSize,

  MIN(vdi.MailDate) AS OldestMailDate,

  MAX(vdi.MailDate) AS YoungestMailDate

   FROM dbo.view_DiscoveredItems vdi

   GROUP BY vdi.CaseID

)

SELECT vc.CaseID,

      vc.[Name],

      ctecs.ItemCount AS CountOfItems,

      (ctecs.ItemSize / 1024) AS TotalSizeInMB,

  ctecs.OldestMailDate,

  ctecs.YoungestMailDate

FROM dbo.view_case vc

   LEFT JOIN CTE_CaseSummary ctecs ON ctecs.CaseID = vc.CaseID

WHERE vc.[Type] = 101

ORDER BY vc.CaseID;