DECLARE @filter NVARCHAR(64) = ''
IF OBJECT_ID('tempdb..#data') IS NOT NULL DROP TABLE #data
CREATE TABLE #data(
Criterion NVARCHAR(128)
, [Region] NVARCHAR(128)
, [Count] NVARCHAR(128)
, [Index] INT
)
INSERT INTO #data (Criterion, [Region], [Count], [Index])
SELECT '< 250MB' AS Criterion
, sr.Region AS Region
, CAST(COUNT(*) AS NVARCHAR) AS [Count]
, 10 AS [Index]
FROM [Mailscape].[dbo].[Mailbox] m INNER JOIN ServerRegion sr ON m.ServerName = sr.Server
WHERE LTRIM(ISNULL(EMailAddress,'')) <> ''
AND ([Size]+[DeletedSize]+[SentSize])/(1024.0*1024.0) < 250.0
AND (@filter = '' OR sr.Region = @filter)
GROUP BY sr.Region
INSERT INTO #data (Criterion, [Region], [Count], [Index])
SELECT 'Between 250MB And 500MB' AS Criterion
, sr.Region AS Region
, CAST(COUNT(*) AS NVARCHAR) AS [Count]
, 20 AS [Index]
FROM [Mailscape].[dbo].[Mailbox] m INNER JOIN ServerRegion sr ON m.ServerName = sr.Server
WHERE LTRIM(ISNULL(EMailAddress,'')) <> ''
AND ([Size]+[DeletedSize]+[SentSize])/(1024.0*1024.0) < 500.0
AND ([Size]+[DeletedSize]+[SentSize])/(1024.0*1024.0) >= 250.0
AND (@filter = '' OR sr.Region = @filter)
GROUP BY sr.Region
INSERT INTO #data (Criterion, [Region], [Count], [Index])
SELECT 'Between 500MB And 1GB' AS Criterion
, sr.Region AS Region
, CAST(COUNT(*) AS NVARCHAR) AS [Count]
, 30 AS [Index]
FROM [Mailscape].[dbo].[Mailbox] m INNER JOIN ServerRegion sr ON m.ServerName = sr.Server
WHERE LTRIM(ISNULL(EMailAddress,'')) <> ''
AND ([Size]+[DeletedSize]+[SentSize])/(1024.0*1024.0) >= 500.0
AND ([Size]+[DeletedSize]+[SentSize])/(1024.0*1024.0) < 1024.0
AND (@filter = '' OR sr.Region = @filter)
GROUP BY sr.Region
INSERT INTO #data (Criterion, [Region], [Count], [Index])
SELECT 'Between 1GB And 2GB' AS Criterion
, sr.Region AS Region
, CAST(COUNT(*) AS NVARCHAR) AS [Count]
, 40 AS [Index]
FROM [Mailscape].[dbo].[Mailbox] m INNER JOIN ServerRegion sr ON m.ServerName = sr.Server
WHERE LTRIM(ISNULL(EMailAddress,'')) <> ''
AND ([Size]+[DeletedSize]+[SentSize])/(1024.0*1024.0) < 2048.0
AND ([Size]+[DeletedSize]+[SentSize])/(1024.0*1024.0) >= 1024.0
AND (@filter = '' OR sr.Region = @filter)
GROUP BY sr.Region
INSERT INTO #data (Criterion, [Region], [Count], [Index])
SELECT 'Between 2GB And 5GB' AS Criterion
, sr.Region AS Region
, CAST(COUNT(*) AS NVARCHAR) AS [Count]
, 50 AS [Index]
FROM [Mailscape].[dbo].[Mailbox] m INNER JOIN ServerRegion sr ON m.ServerName = sr.Server
WHERE LTRIM(ISNULL(EMailAddress,'')) <> ''
AND ([Size]+[DeletedSize]+[SentSize])/(1024.0*1024.0) < 5120.0
AND ([Size]+[DeletedSize]+[SentSize])/(1024.0*1024.0) >= 2048.0
AND (@filter = '' OR sr.Region = @filter)
GROUP BY sr.Region
INSERT INTO #data (Criterion, [Region], [Count], [Index])
SELECT '> 5GB' AS Criterion
, sr.Region AS Region
, CAST(COUNT(*) AS NVARCHAR) AS [Count]
, 60 AS [Index]
FROM [Mailscape].[dbo].[Mailbox] m INNER JOIN ServerRegion sr ON m.ServerName = sr.Server
WHERE LTRIM(ISNULL(EMailAddress,'')) <> ''
AND ([Size]+[DeletedSize]+[SentSize])/(1024.0*1024.0) >= 5120.0
AND (@filter = '' OR sr.Region = @filter)
GROUP BY sr.Region
INSERT INTO #data (Criterion, [Region], [Count], [Index])
SELECT '<div style="background-color: rgb(19, 27, 40);">Total</div><div>' AS Criterion
, sr.Region AS Region
, '<div style="background-color: rgb(19, 27, 40);">' + CAST(COUNT(*) AS NVARCHAR) + '</div><div>' AS [Count]
, 100 AS [Index]
FROM [Mailscape].[dbo].[Mailbox] m INNER JOIN ServerRegion sr ON m.ServerName = sr.Server
WHERE LTRIM(ISNULL(EMailAddress,'')) <> ''
--AND ([Size]+[DeletedSize]+[SentSize])/(1024.0*1024.0) >= 5120.0
AND (@filter = '' OR sr.Region = @filter)
GROUP BY sr.Region
SELECT Criterion, [Region], [Count]
FROM #data
ORDER BY Region, [Index]
DROP TABLE #data