SET NOCOUNT ON;

DECLARE @retention INT = 7,
        @destination_table VARCHAR(500) = 'whoisactive_interim',
        @destination_database sysname = 'ADS',
        @schema VARCHAR(MAX),
        @SQL NVARCHAR(4000),
        @parameters NVARCHAR(500),
        @exists BIT,
        @minuteLimit INTEGER = 3;

SET @destination_table = @destination_database + '.dbo.' + @destination_table;

----- CREATE THE INTERIM TABLE TO COLLECT THE OUTPUT FROM USP
IF OBJECT_ID(@destination_table) IS NULL
    BEGIN;
        EXEC [dbo].[sp_WhoIsActive] @get_transaction_info = 1,
                                @get_outer_command = 1,
                                @get_plans = 1,
                                @return_schema = 1,
                                @schema = @schema OUTPUT;
        SET @schema = REPLACE(@schema, '<table_name>', @destination_table);
        EXEC ( @schema );
    END;

----- CREATE INDEX ON COLLECTION_TIME

SET @SQL
    = 'USE ' + QUOTENAME(@destination_database)
      + '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0';
SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT';
EXEC sys.sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;

IF @exists = 0
    BEGIN;
        SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';
        EXEC ( @SQL );
    END;

----- COLLECT ACTIVITY INTO LOGGING TABLE
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,
                        @get_outer_command = 1,
                        @get_plans = 1,
                        @destination_table = @destination_table;

--------- PURGE DATA NEWER THAN n MINUTES
SET @SQL
    = 'DELETE FROM ' + @destination_table + ' WHERE [start_time] > DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE() )-' + CAST(@minuteLimit AS NVARCHAR(MAX)) + ', 0)';
EXEC ( @SQL );


----- MERGE RESULTS INTO FINAL TABLE
MERGE [ADS].[dbo].[whoisactive_scd2] AS TARGET
USING
    (
    SELECT
        [dd hh:mm:ss.mss]
        ,[session_id]
        ,[sql_text]
        --,[sql_command]
        ,[login_name]
        ,[wait_info]
        ,[tran_log_writes]
        ,[CPU]
        ,[tempdb_allocations]
        ,[tempdb_current]
        ,[blocking_session_id]
        ,[reads]
        ,[writes]
        ,[physical_reads]
        --,[query_plan]
        ,[used_memory]
        ,[status]
        ,[tran_start_time]
        ,[open_tran_count]
        ,[percent_complete]
        ,[host_name]
        ,[database_name]
        ,[program_name]
        ,[start_time]
        ,[login_time]
        ,[request_id]
        ,[collection_time]
    FROM [ADS].[dbo].[whoisactive_interim]
    )
    AS SOURCE
    ON CAST(TARGET.[session_id] AS NVARCHAR(MAX))       = CAST(SOURCE.[session_id] AS NVARCHAR(MAX))
        AND CAST(TARGET.[sql_text] AS NVARCHAR(MAX))    = CAST( SOURCE.[sql_text]  AS NVARCHAR(MAX))
        AND TARGET.[login_name]     = SOURCE.[login_name]
        AND TARGET.[program_name]   = SOURCE.[program_name]
WHEN MATCHED
        THEN UPDATE SET
            TARGET.[dd hh:mm:ss.mss]        = SOURCE.[dd hh:mm:ss.mss]
            --, TARGET.[sql_command]            = SOURCE.[sql_command]
            , TARGET.[wait_info]            = SOURCE.[wait_info]
            , TARGET.[tran_log_writes]      = SOURCE.[tran_log_writes]
            , TARGET.[CPU]                  = SOURCE.[CPU]
            , TARGET.[tempdb_allocations]   = SOURCE.[tempdb_allocations]
            , TARGET.[tempdb_current]       = SOURCE.[tempdb_current]
            , TARGET.[blocking_session_id]  = SOURCE.[blocking_session_id]
            , TARGET.[reads]                = SOURCE.[reads]
            , TARGET.[writes]               = SOURCE.[writes]
            , TARGET.[physical_reads]       = SOURCE.[physical_reads]
            --, TARGET.[query_plan]         = SOURCE.[query_plan]
            , TARGET.[used_memory]          = SOURCE.[used_memory]
            , TARGET.[status]               = SOURCE.[status]
            , TARGET.[tran_start_time]      = SOURCE.[tran_start_time]
            , TARGET.[open_tran_count]      = SOURCE.[open_tran_count]
            , TARGET.[percent_complete]     = SOURCE.[percent_complete]
            , TARGET.[host_name]            = SOURCE.[host_name]
            , TARGET.[database_name]        = SOURCE.[database_name]
            , TARGET.[start_time]           = SOURCE.[start_time]
            , TARGET.[login_time]           = SOURCE.[login_time]
            , TARGET.[request_id]           = SOURCE.[request_id]
            , TARGET.[collection_time]      = SOURCE.[collection_time]
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([dd hh:mm:ss.mss],[session_id],[sql_text]
        ,[login_name],[wait_info],[tran_log_writes],[CPU]
        ,[tempdb_allocations],[tempdb_current],[blocking_session_id]
        ,[reads],[writes],[physical_reads],[used_memory]
        ,[status],[tran_start_time],[open_tran_count],[percent_complete]
        ,[host_name],[database_name],[program_name],[start_time]
        ,[login_time],[request_id],[collection_time])
    VALUES (SOURCE.[dd hh:mm:ss.mss], SOURCE.[session_id], SOURCE.[sql_text]
        , SOURCE.[login_name], SOURCE.[wait_info]
        , SOURCE.[tran_log_writes], SOURCE.[CPU], SOURCE.[tempdb_allocations]
        , SOURCE.[tempdb_current], SOURCE.[blocking_session_id], SOURCE.[reads]
        , SOURCE.[writes], SOURCE.[physical_reads]
        , SOURCE.[used_memory], SOURCE.[status], SOURCE.[tran_start_time]
        , SOURCE.[open_tran_count], SOURCE.[percent_complete], SOURCE.[host_name]
        , SOURCE.[database_name], SOURCE.[program_name], SOURCE.[start_time]
        , SOURCE.[login_time], SOURCE.[request_id], SOURCE.[collection_time])
    OUTPUT $action          AS [action]
;

----- PURGE INTERIM TABLE
SET @SQL
    = 'DELETE FROM ' + @destination_table;
EXEC ( @SQL );