SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.NumOfMsgsByDate
AS
SELECT     TOP 100 PERCENT COUNT(CONVERT(varchar(10), StoreDate, 101)) AS NumOfMsgs, CONVERT(varchar(10), StoreDate, 101) AS [Date]
FROM         modus.tblMsgCatalog
GROUP BY CONVERT(varchar(10), StoreDate, 101)
ORDER BY CONVERT(varchar(10), StoreDate, 101) DESC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.PercentageBySieveCategory
AS
SELECT TOP 100 PERCENT 
	CONVERT(DECIMAL(5,2),
	CONVERT(DECIMAL,(COUNT(LEFT(RIGHT(Cause,LEN(Cause)-1), CHARINDEX('\',RIGHT(Cause, LEN(Cause)-1))-1))*100))/
	CONVERT(DECIMAL,(
		SELECT COUNT(*) FROM modus.tblMsgCatalog WHERE MsgType = 2))) as Percentage, 
		CASE
		WHEN LOWER(LEFT(RIGHT(Cause, LEN(Cause)-1), CHARINDEX('\',RIGHT(Cause, LEN(Cause)-1))-1)) = 'custom'
		THEN RIGHT(RIGHT(RIGHT(Cause, LEN(Cause)-2), LEN(RIGHT(Cause, LEN(Cause)-2))-CHARINDEX('\', RIGHT(Cause, Len(Cause)-2))), LEN(RIGHT(RIGHT(Cause, Len(Cause)-2), LEN(RIGHT(Cause, Len(Cause)-2))-CHARINDEX('\', RIGHT(Cause, Len(Cause)-2)))) - CHARINDEX('\', RIGHT(RIGHT(Cause, Len(Cause)-2), LEN(RIGHT(Cause, Len(Cause)-2))-CHARINDEX('\', RIGHT(Cause, Len(Cause)-2))))) 
		WHEN LOWER(LEFT(  RIGHT(Cause, LEN(Cause)-2)  , CHARINDEX('\',  RIGHT(Cause, LEN(Cause)-2)  )-1)) = 'scabase'
		THEN LOWER(LEFT(  RIGHT(Cause, LEN(Cause)-(2+CHARINDEX('\', RIGHT(Cause,LEN(Cause)-2)))), CHARINDEX('\', RIGHT(Cause,LEN(Cause)-10))-1))
		ELSE LEFT(RIGHT(Cause, LEN(Cause)-1),CHARINDEX('\',RIGHT(Cause, LEN(Cause)-1))-1)
		END as Category
	FROM modus.tblMsgCatalog 
	WHERE MsgType = 2 
	GROUP BY CASE 
		WHEN LOWER(LEFT(RIGHT(Cause, LEN(Cause)-1), CHARINDEX('\',RIGHT(Cause, LEN(Cause)-1))-1)) = 'custom'
		THEN RIGHT(RIGHT(RIGHT(Cause, LEN(Cause)-2), LEN(RIGHT(Cause, LEN(Cause)-2))-CHARINDEX('\', RIGHT(Cause, Len(Cause)-2))), LEN(RIGHT(RIGHT(Cause, Len(Cause)-2), LEN(RIGHT(Cause, Len(Cause)-2))-CHARINDEX('\', RIGHT(Cause, Len(Cause)-2)))) - CHARINDEX('\', RIGHT(RIGHT(Cause, Len(Cause)-2), LEN(RIGHT(Cause, Len(Cause)-2))-CHARINDEX('\', RIGHT(Cause, Len(Cause)-2))))) 
		WHEN LOWER(LEFT(  RIGHT(Cause, LEN(Cause)-2)  , CHARINDEX('\',  RIGHT(Cause, LEN(Cause)-2)  )-1)) = 'scabase'
		THEN LOWER(LEFT(  RIGHT(Cause, LEN(Cause)-(2+CHARINDEX('\', RIGHT(Cause,LEN(Cause)-2)))), CHARINDEX('\', RIGHT(Cause,LEN(Cause)-10))-1))
		ELSE LEFT(RIGHT(Cause, LEN(Cause)-1),CHARINDEX('\',RIGHT(Cause, LEN(Cause)-1))-1)
		END
	ORDER BY CASE 
		WHEN LOWER(LEFT(RIGHT(Cause, LEN(Cause)-1), CHARINDEX('\',RIGHT(Cause, LEN(Cause)-1))-1)) = 'custom'
		THEN RIGHT(RIGHT(RIGHT(Cause, LEN(Cause)-2), LEN(RIGHT(Cause, LEN(Cause)-2))-CHARINDEX('\', RIGHT(Cause, Len(Cause)-2))), LEN(RIGHT(RIGHT(Cause, Len(Cause)-2), LEN(RIGHT(Cause, Len(Cause)-2))-CHARINDEX('\', RIGHT(Cause, Len(Cause)-2)))) - CHARINDEX('\', RIGHT(RIGHT(Cause, Len(Cause)-2), LEN(RIGHT(Cause, Len(Cause)-2))-CHARINDEX('\', RIGHT(Cause, Len(Cause)-2))))) 
		WHEN LOWER(LEFT(  RIGHT(Cause, LEN(Cause)-2)  , CHARINDEX('\',  RIGHT(Cause, LEN(Cause)-2)  )-1)) = 'scabase'
		THEN LOWER(LEFT(  RIGHT(Cause, LEN(Cause)-(2+CHARINDEX('\', RIGHT(Cause,LEN(Cause)-2)))), CHARINDEX('\', RIGHT(Cause,LEN(Cause)-10))-1))
		ELSE LEFT(RIGHT(Cause, LEN(Cause)-1),CHARINDEX('\',RIGHT(Cause, LEN(Cause)-1))-1)
		END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.Top10Filters
AS
SELECT     TOP 10 COUNT(Cause) AS FilterCount, CASE 
			WHEN LEFT(Cause,2) = '\\' 
			THEN RIGHT(Cause,(LEN(Cause)-9)) 
			ELSE Cause 
			END AS Cause
FROM         modus.tblMsgCatalog
GROUP BY Cause
ORDER BY FilterCount DESC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.Top10EmailAddressesBlockingMail
AS
SELECT     TOP 10 COUNT(EMailAddress) AS MessageCount, EMailAddress
FROM         modus.tblRecipients
GROUP BY EMailAddress
ORDER BY MessageCount DESC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

