--- Please follow the below comments to insert SQL statements.

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 22
DECLARE @surveyXML NVARCHAR(MAX)

--------- END - GENERATED CODE ---------



--------- BEGIN SURVEY QUERY ---------

--------- Insert your SQL statements here 
CREATE TABLE #tasks
(schedPolicyId INT, beingUsed INT,disable INT)

-- Get all schedule policies except the default System created policies
IF  EXISTS (SELECT id FROM APP_Client WHERE id = 2 AND releaseId <= 15)
BEGIN
	INSERT INTO #tasks
	SELECT T.taskId, 0,T.disabled FROM TM_Task T WITH (NOLOCK)
	INNER JOIN TM_SubTask ST WITH (NOLOCK) ON T.taskId = ST.taskId 
	WHERE T.taskType = 4 
	AND T.deleted = 0
	AND T.uninstalled = 0
	AND T.taskName NOT IN ('System Created (All  Agent Types)', 'System Created for DDB subclients', 
		'System Created for Content Index', 'System Created for Content Store', 'System Created for Edge Drive')
END
ELSE
BEGIN
	INSERT INTO #tasks
	SELECT T.taskId, 0,T.disabled FROM TM_Task T WITH (NOLOCK)
	INNER JOIN TM_SubTask ST WITH (NOLOCK) ON T.taskId = ST.taskId 
	WHERE T.taskType = 4 
	AND T.deleted = 0
	AND T.uninstalled = 0
	AND (T.flags & 4) <> 4
END

-- Mark the policy as being used if there is atleast one associated entity exists for that policy
UPDATE #tasks
SET beingUsed =1
WHERE EXISTS (SELECT 1 FROM TM_AssocEntity  WHERE taskId = schedPolicyId AND exclude = 0)

-- For Workflow policy use TM_AssocWorkFlowEntity
DECLARE @queryString NVARCHAR(MAX) = '
UPDATE #tasks
SET beingUsed =1
WHERE EXISTS (SELECT 1 FROM TM_AssocWorkFlowEntity  WHERE taskId = schedPolicyId AND exclude = 0)'


IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TM_AssocWorkFlowEntity')
BEGIN
EXEC sp_executesql @queryString
END

SET @surveyXML = (
SELECT DISTINCT 
		temp.schedPolicyId AS '@policyId',
		T.taskName  AS '@policyName',
		temp.beingUsed  AS '@beingUsed',
		temp.disable AS '@Disable'
	from #tasks temp
INNER JOIN TM_Task  T WITH (NOLOCK)
ON
T.taskId = temp.schedPolicyId
FOR XML PATH ('Sch_policy') )

DROP TABLE #tasks
--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)

SET NOCOUNT OFF

--------- END - GENERATED CODE ---------