/*
Server:		ssunb008vm01.sunstore.ch
Format:		GCM 
Business:	TPPHAR
type:		DEVE
Version:	23.1.10015.00066

31.10.2022, TSC
*/
SET XACT_ABORT ON;
SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#bkps') IS NOT NULL
BEGIN
    DROP TABLE #bkps;
END;

SELECT DISTINCT
	CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SERVER,
    bs.[database_name],
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.backup_size,
    bs.[name] AS backupset_name,
    bs.[description],
    CASE
            WHEN bs.TYPE = 'D' THEN 'Full'
            WHEN bs.TYPE = 'I' THEN 'Differential'
            WHEN bs.TYPE = 'L' THEN 'Log'
            WHEN bs.TYPE = 'F' THEN 'File or filegroup'
            WHEN bs.TYPE = 'G' THEN 'Differential file'
            WHEN bs.TYPE = 'P' THEN 'Partial'
            WHEN bs.TYPE = 'Q' THEN 'Differential partial'
            ELSE 'Unknown' END AS backup_type
	,ROW_NUMBER()OVER(PARTITION BY bs.[database_name], bs.TYPE ORDER BY bs.backup_start_date) AS rid
  INTO      #bkps
  FROM      msdb.[dbo].backupset bs
 WHERE      (CONVERT(DATETIME, bs.backup_start_date, 102) >= DATEADD(DAY, -7, CURRENT_TIMESTAMP))
   AND bs.[name] LIKE '%commvault%'
   AND bs.[database_name] NOT IN ('model','msdb','master','tempdb','HCITools')
   /*AND (
	bs.database_name LIKE 'arizona%'
	OR bs.database_name LIKE 'activepos%'
   )
   */
;

WITH chkFull AS (
	SELECT b.SERVER
          ,b.[database_name]
          ,b.backup_start_date
          ,b.backup_finish_date
          ,b.backup_size
          ,b.backupset_name
          ,b.[description]
          ,b.backup_type
		  ,b.rid
		  ,hiera = 1
		  ,CAST(NULL AS INT) AS delta_min
	FROM #bkps b
	WHERE b.rid = 1

	UNION ALL 

	SELECT b.SERVER
          ,b.[database_name]
          ,b.backup_start_date
          ,b.backup_finish_date
          ,b.backup_size
          ,b.backupset_name
          ,b.[description]
          ,b.backup_type
		  ,b.rid
		  ,p.hiera + 1 AS hiera
		  ,DATEDIFF(MINUTE, p.backup_start_date, b.backup_start_date) AS delta_min
	FROM chkFull p
		INNER JOIN #bkps b 
			ON b.[database_name] = p.[database_name] 
			AND b.backup_type = p.backup_type 
			AND b.rid = p.rid + 1
)

SELECT 
	f.[database_name]
	, f.backup_type
	, SUM(CAST(CAST(f.delta_min AS NUMERIC(12,2)) / 60 AS NUMERIC(12,2))) / COUNT(1) AS mean_delta_hour
FROM chkFull f
WHERE 1=1
GROUP BY f.[database_name], f.backup_type
OPTION(MAXRECURSION 5000);


SELECT * 
FROM #bkps b
WHERE b.backup_type = 'log'
AND b.[database_name]='arizona'
ORDER BY b.[database_name] ASC, b.rid ASC
;