SQL Scripts to assist in gathering information that is useful in monitoring or troubleshooting Random Capture issues.



1.  Displays the Departments that actively monitor (Random Capturing) employees as well as employee settings that differ from the Department settings.

/* **************************************
*
*  Apply Against: Compliance Customer database
*  Versions: 6, 7, 2007
*
*  Description: First set of results are the cases that are actively Random Capturing messages of
*                     their employees.
*                     The second set of results are the employees that have different capturing
*                     percentages from the case settings
*
********************************************/

SELECT tc.[Name] AS ' Case Name', ippr.PercentageReviewExchInternal AS '% Internal',
ippr.PercentageReviewExchExtIn AS '% Ext Inbound', ippr.PercentageReviewExchExtOut AS '% Ext Outbound',
ippr.PercentageReviewIM AS '% IM', ippr.PercentageReviewBloomberg AS '% Bloomberg'
FROM tblIntPolicyPercentageReview ippr
JOIN tblCase tc ON tc.CaseID = ippr.CaseID
Where tc.BypassMonitoring = 0 AND tc.StatusID = 20 AND ippr.AddressOwnerID IS NULL


SELECT au.DisplayName, ta.Address, tc.[Name] AS 'Case Name',
ippr.PercentageReviewExchInternal AS '% Internal', ippr.PercentageReviewExchExtIn AS '% Ext Inbound',
ippr.PercentageReviewExchExtOut AS '% Ext Outbound',ippr.PercentageReviewIM AS '% IM',
ippr.PercentageReviewBloomberg AS '% Bloomberg'
FROM tblIntPolicyPercentageReview ippr
JOIN tblAddressUser au ON au.AddressOwnerID = ippr.AddressOwnerID
JOIN tblAddressOwnership ao ON ao.AddressOwnerID = au.AddressOwnerID
JOIN tblAddress ta ON ta.AddressID = ao.AddressID
JOIN tblIntTarget it ON it.AddressOwnerID = au.AddressOwnerID
JOIN tblCase tc ON tc.CaseID = it.CaseID
Where au.MonitorStatusID = 120 AND tc.BypassMonitoring = 0




2. Displays by Department the quantity of Randomly Captured items over the past 30, 60 and 90 days separated by Message Direction as well as Message Type.

/* **************************************
*
*  Apply Against: Compliance Customer database
*  Versions: 6, 7, 2007
*
*  Description: This query calculates the amount of Random Sampled (Direction and Type)
*               placed into a review set by Department.  This is sorted by Department                
*
********************************************/

-- Setting Table variables
DECLARE @first int
DECLARE @second int
DECLARE @third int
DECLARE @forth int
DECLARE @30Email int
DECLARE @30IM int
DECLARE @30Bloomberg int
DECLARE @30Fax int
DECLARE @fifth nvarchar(250)
DECLARE @thirtydays nvarchar(30)
DECLARE @sixtydays nvarchar(30)
DECLARE @nintydays nvarchar(30)

-- Set values to table variables
SET @thirtydays = 'Last 30 days'
SET @sixtydays = 'Between 30 and 60 days'
SET @nintydays = 'Between 60 and 90 days'

-- Setting Function variables
-- DECLARE @Turns int
DECLARE @NegDate1 int
DECLARE @NegDate2 int
DECLARE @Counter int
DECLARE @RecRow nvarchar(30)

CREATE TABLE #RCTotals (
RecNum varchar(25),
RCTotal int,
RCInternal int,
RCExtInbound int,
RCExtOutbound int,
RCEmail int,
RCIM int,
RCBloomberg int,
RCFax int,
RCCaseName nvarchar(250))

INSERT INTO #RCTotals (RecNum)
VALUES (@thirtydays)
INSERT INTO #RCTotals (RecNum)
VALUES (@sixtydays)
INSERT INTO #RCTotals (RecNum)
VALUES (@nintydays)

--Cursor installation
DECLARE My_Department CURSOR FOR
SELECT CaseID, [Name], FolderType
FROM tblCase
WHERE FolderType = 330
AND [Name] NOT IN ('Discovery System Case', 'Compliance System Case', 'Lite Discovery System Case')

OPEN My_Department
DECLARE @CaseID int, @DeptName nvarchar(250), @FolderType int

FETCH NEXT FROM My_Department INTO @CaseID, @DeptName, @FolderType
WHILE @@FETCH_STATUS = 0

BEGIN

SET @fifth = @DeptName
UPDATE #RCTotals SET RCCaseName = @fifth
WHERE RecNum = @thirtydays


SET @Counter = 3
WHILE @Counter > 0
BEGIN
SET @Counter = @Counter -1


IF @Counter = 2
BEGIN
SET @negDate1 = -1
SET @negDate2 = 0
SET @RecRow = @thirtydays
END
IF @Counter = 1
BEGIN
SET @negDate1 = -2
SET @negDate2 = -1
SET @RecRow = @sixtydays
END
IF @Counter = 0
BEGIN
SET @negDate1 = -3
SET @negDate2 = -2
SET @RecRow = @nintydays
END


SET @first =
(SELECT COUNT (*) AS 'Total Captured' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND CaptureType = 2
AND (DATEADD(month, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(month, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))


SET @second =
(SELECT COUNT (*) AS 'Internal Only' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND CaptureType = 2
AND Direction = 1
AND (DATEADD(month, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(month, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))

SET @third =
(SELECT COUNT (*) AS 'External Outbound' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND CaptureType = 2
AND Direction = 2
AND (DATEADD(month, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(month, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))

SET @forth =
(SELECT COUNT (*) AS 'External Outbound' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND CaptureType = 2
AND Direction = 3
AND (DATEADD(month, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(month, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))

SET @30Email =
(SELECT COUNT (*) AS 'Total Captured' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND [Type] = 1
AND CaptureType = 2
AND (DATEADD(month, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(month, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))

SET @30IM =
(SELECT COUNT (*) AS 'Total Captured' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND [Type] = 2
AND CaptureType = 2
AND (DATEADD(month, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(month, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))

SET @30Bloomberg =
(SELECT COUNT (*) AS 'Total Captured' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND [Type] = 3
AND CaptureType = 2
AND (DATEADD(month, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(month, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))

SET @30Fax =
(SELECT COUNT (*) AS 'Total Captured' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND [Type] = 4
AND CaptureType = 2
AND (DATEADD(month, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(month, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))


UPDATE #RCTotals  
SET RCTotal = @first
WHERE RecNum = @RecRow
UPDATE #RCTotals
SET RCExtOutbound = @forth
WHERE RecNum = @RecRow
UPDATE #RCTotals
SET RCInternal = @second
WHERE RecNum = @RecRow
UPDATE #RCTotals
SET RCExtInbound = @third
WHERE RecNum = @RecRow
UPDATE #RCTotals
SET RCEmail = @30Email
WHERE RecNum = @RecRow
UPDATE #RCTotals
SET RCIM = @30IM
WHERE RecNum = @RecRow
UPDATE #RCTotals
SET RCBloomberg = @30Bloomberg
WHERE RecNum = @RecRow
UPDATE #RCTotals
SET RCFax = @30Fax
WHERE RecNum = @RecRow

END

SELECT RecNum AS 'Random Capture Counts', RCTotal AS 'Total Captured',
RCInternal AS 'Internal Only', RCExtInbound AS ' External Inbound Only',
RCExtOutbound AS 'External Outbound Only', RCCaseName AS 'Department',
RCEmail AS 'Exchange Email', RCIM AS 'IMsg', RCBloomberg AS 'Bloomberg', RCFax AS 'Fax'
FROM #RCTotals

FETCH NEXT FROM My_Department INTO @CaseID, @DeptName, @FolderType
END

DROP TABLE #RCTotals

CLOSE My_Department
DEALLOCATE My_Department


3. Displays by Department the quantity of Randomly Captured items over the past day, 3 days and 7 days separated by Message Direction as well as Message Type.

/* **************************************
*
*  Apply Against: Compliance Customer database
*  Versions: 6, 7, 2007
*
*  Description: This query calculates the amount of Random Sampled (Direction and Type)
*               placed into a review set by Department.  This is sorted by Department                
*
********************************************/


-- Setting Table variables
DECLARE @first int
DECLARE @second int
DECLARE @third int
DECLARE @forth int
DECLARE @30Email int
DECLARE @30IM int
DECLARE @30Bloomberg int
DECLARE @30Fax int
DECLARE @fifth nvarchar(250)
DECLARE @Onedays nvarchar(30)
DECLARE @Threedays nvarchar(30)
DECLARE @Sevendays nvarchar(30)

-- Set values to table variables
SET @Onedays = 'Yesterday'
SET @Threedays = 'Last 3 days'
SET @Sevendays = 'Last 7 days'

-- Setting Function variables
-- DECLARE @Turns int
DECLARE @NegDate1 int
DECLARE @NegDate2 int
DECLARE @Counter int
DECLARE @RecRow nvarchar(30)

CREATE TABLE #RCTotals (
RecNum varchar(25),
RCTotal int,
RCInternal int,
RCExtInbound int,
RCExtOutbound int,
RCEmail int,
RCIM int,
RCBloomberg int,
RCFax int,
RCCaseName nvarchar(250))

INSERT INTO #RCTotals (RecNum)
VALUES (@Onedays)
INSERT INTO #RCTotals (RecNum)
VALUES (@Threedays)
INSERT INTO #RCTotals (RecNum)
VALUES (@Sevendays)

--Cursor installation
DECLARE My_Department CURSOR FOR
SELECT CaseID, [Name], FolderType
FROM tblCase
WHERE FolderType = 330
AND [Name] NOT IN ('Discovery System Case', 'Compliance System Case', 'Lite Discovery System Case')

OPEN My_Department
DECLARE @CaseID int, @DeptName nvarchar(250), @FolderType int

FETCH NEXT FROM My_Department INTO @CaseID, @DeptName, @FolderType
WHILE @@FETCH_STATUS = 0

BEGIN

SET @fifth = @DeptName
UPDATE #RCTotals SET RCCaseName = @fifth
WHERE RecNum = @Onedays


SET @Counter = 3
WHILE @Counter > 0
BEGIN
SET @Counter = @Counter -1


IF @Counter = 2
BEGIN
SET @negDate1 = -1
SET @negDate2 = 0
SET @RecRow = @Onedays
END
IF @Counter = 1
BEGIN
SET @negDate1 = -3
SET @negDate2 = 0
SET @RecRow = @Threedays
END
IF @Counter = 0
BEGIN
SET @negDate1 = -7
SET @negDate2 = 0
SET @RecRow = @Sevendays
END


SET @first =
(SELECT COUNT (*) AS 'Total Captured' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND CaptureType = 2
AND (DATEADD(day, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(day, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))


SET @second =
(SELECT COUNT (*) AS 'Internal Only' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND CaptureType = 2
AND Direction = 1
AND (DATEADD(day, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(day, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))

SET @third =
(SELECT COUNT (*) AS 'External Outbound' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND CaptureType = 2
AND Direction = 2
AND (DATEADD(day, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(day, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))

SET @forth =
(SELECT COUNT (*) AS 'External Outbound' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND CaptureType = 2
AND Direction = 3
AND (DATEADD(day, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(day, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))

SET @30Email =
(SELECT COUNT (*) AS 'Total Captured' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND [Type] = 1
AND CaptureType = 2
AND (DATEADD(day, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(day, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))

SET @30IM =
(SELECT COUNT (*) AS 'Total Captured' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND [Type] = 2
AND CaptureType = 2
AND (DATEADD(day, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(day, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))

SET @30Bloomberg =
(SELECT COUNT (*) AS 'Total Captured' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND [Type] = 3
AND CaptureType = 2
AND (DATEADD(day, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(day, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))

SET @30Fax =
(SELECT COUNT (*) AS 'Total Captured' FROM tblIntDiscoveredItems
WHERE CaseID = @CaseID
AND [Type] = 4
AND CaptureType = 2
AND (DATEADD(day, @negDate1,GETDATE())) < (CONVERT(varchar(10), CaptureDate, 102))
AND (DATEADD(day, @negDate2,GETDATE())) > (CONVERT(varchar(10), CaptureDate, 102)))


UPDATE #RCTotals  
SET RCTotal = @first
WHERE RecNum = @RecRow
UPDATE #RCTotals
SET RCExtOutbound = @forth
WHERE RecNum = @RecRow
UPDATE #RCTotals
SET RCInternal = @second
WHERE RecNum = @RecRow
UPDATE #RCTotals
SET RCExtInbound = @third
WHERE RecNum = @RecRow
UPDATE #RCTotals
SET RCEmail = @30Email
WHERE RecNum = @RecRow
UPDATE #RCTotals
SET RCIM = @30IM
WHERE RecNum = @RecRow
UPDATE #RCTotals
SET RCBloomberg = @30Bloomberg
WHERE RecNum = @RecRow
UPDATE #RCTotals
SET RCFax = @30Fax
WHERE RecNum = @RecRow

END

SELECT RecNum AS 'Random Capture Counts', RCTotal AS 'Total Captured',
RCInternal AS 'Internal Only', RCExtInbound AS ' External Inbound Only',
RCExtOutbound AS 'External Outbound Only', RCCaseName AS 'Department',
RCEmail AS 'Exchange Email', RCIM AS 'IMsg', RCBloomberg AS 'Bloomberg', RCFax AS 'Fax'
FROM #RCTotals

FETCH NEXT FROM My_Department INTO @CaseID, @DeptName, @FolderType
END

DROP TABLE #RCTotals

CLOSE My_Department
DEALLOCATE My_Department