--- 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 = 91
DECLARE @surveyXML NVARCHAR(MAX)

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



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

--------- Insert your SQL statements here 
-- Gives full and Non-full backup Info about each agent for past 6 weeks.

DECLARE	@lastDays	INT = 7
DECLARE	@nPeriods	INT = 6
DECLARE @startDate	DATE
DECLARE @endDate	DATE = GETUTCDATE()
SET @endDate   = DATEADD(DAY, 1, @endDate)
SET @startDate = DATEADD(DAY, -@lastDays, @endDate)

DECLARE @tmpStart	INT = dbo.GetUnixTime(@startDate)
DECLARE @tmpEnd		INT = dbo.GetUnixTime(@endDate)
DECLARE @weekNo		INT = @nPeriods + 1

DECLARE @TblWeek TABLE (weekNo INT, startTime INT, endTime INT, startDate DATE, endDate DATE)

WHILE @weekNo > 0
BEGIN
	INSERT INTO @TblWeek SELECT @weekNo, @tmpStart, @tmpEnd, @startDate, @endDate
	SET @tmpEnd  = @tmpStart
	SET @endDate = @startDate
	SET @startDate = DATEADD(DAY, -@lastDays, @endDate)
	SET @tmpStart  = dbo.GetUnixTime(@startDate)
	SET	@weekNo = (@weekNo - 1)
END
SELECT @tmpStart = MIN(startTime) FROM @TblWeek
SELECT @tmpEnd   = MAX(endTime)   FROM @TblWeek

DECLARE @TblSize TABLE (weekNo INT, endDate DATE, appTypeId INT, appTypeName VARCHAR(256), fullSize BIGINT, nonFullSize BIGINT, fullJobCount INT,nonFullJobCount INT)


INSERT	INTO @TblSize
SELECT weekNo,endDate,appType,appTypeName,
		SUM(FullAppSize),
		SUM(NonFullAppSize),
		SUM(fullJobCount),
		SUM(nonFullJobCount) 
FROM
	(SELECT	W.weekNo, W.endDate AS endDate,J.appType, 
			CASE AAT.osGroup+' '+AAT.App 
				WHEN '' THEN I.name
				ELSE AAT.osGroup+' '+AAT.App
				END AS appTypeName,
			(CASE WHEN J.bkpLevel IN (1, 1024, 32768) THEN J.totalUncompBytes ELSE 0 END) AS FullAppSize,
			(CASE WHEN J.bkpLevel IN (2, 4, 256) THEN J.totalUncompBytes ELSE 0 END) AS NonFullAppSize,
			(CASE WHEN J.bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 0 END) AS fullJobCount,
			(CASE WHEN J.bkpLevel IN (2, 4, 256) THEN 1 ELSE 0 END) AS nonFullJobCount
	FROM	@TblWeek W
			INNER JOIN JMBkpStats J WITH (NOLOCK) ON J.servEndDate > W.startTime AND J.servEndDate <= W.endTime
			INNER JOIN APP_iDAType I WITH (NOLOCK) ON I.type = J.appType 
			INNER JOIN APP_Application A WITH (NOLOCK) ON A.id = J.appId
			INNER JOIN App_AppTypeGroups AAT WITH (NOLOCK) ON J.appType=AAT.appTypeId
	WHERE	J.status IN (1, 3, 14) AND (J.status <> 3 OR I.isCWEjobValid = 1) AND J.commCellId = 2 
		AND J.bkpLevel IN (1, 2, 4, 256, 1024, 32768)
		AND J.servEndDate > @tmpStart AND J.servEndDate <= @tmpEnd
		AND A.appTypeId < 600
		AND A.appTypeId NOT IN (72, 85, 127, 84, 107, 121, 122)
		AND (A.appTypeId NOT IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) OR A.subClientStatus&8 = 0)
		AND A.subclientStatus&(16|32) = 0
	) A
GROUP BY A.weekNo,A.endDate,A.appType,A.appTypeName

INSERT	INTO @TblSize
SELECT	W.weekNo, W.endDate, A.appTypeId, A.appTypeName, 0, 0, 0, 0
FROM	@TblWeek W
		CROSS JOIN (SELECT DISTINCT appTypeId, appTypeName FROM @TblSize) A
		LEFT OUTER JOIN @TblSize S ON S.weekNo = W.weekNo AND S.appTypeId = A.appTypeId
WHERE	S.appTypeId IS NULL

SET @surveyXML = (
	SELECT	weekNo AS 'weekNo',
			DATEADD(DAY, -1, endDate) AS 'endDate',					
			dbo.NormalizeForXML(appTypeName) AS 'AgentType',									
			appTypeId AS 'AgentTypeId',	
			fullSize AS 'FullAppSize',	
			nonFullSize AS 'NonFullAppSize',				
			fullJobCount AS 'FullJobCount',
			nonFullJobCount AS 'NonFullJobCount'
	FROM	@TblSize  	
	FOR XML RAW('Agent'), ROOT('ProtectedDataPerAgent6WeekInfo')
)

SET @surveyXML = ISNULL(@surveyXML,'<ProtectedDataPerAgent6WeekInfo />')

--------- 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 ---------