SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON

IF OBJECT_ID('tempdb..#result') IS NOT NULL
    DROP TABLE #result
GO

declare @startPoint datetime2 = (SELECT MAX(slnxjob_postingtime) FROM slnx_job)
declare @start datetime2 = DATEADD( month,-1, @startPoint);
declare @end datetime2 = @startPoint;

CREATE table #result (id int not null identity(1,1) PRIMARY KEY, category sysname, report sysname, name sysname, cnt int, tmsec int, chk int, stamp time not null default getdate())
declare @counter datetime2 = getdate();

--------------------------------------------------------------------------------------------------------------------------------------- Device Reports
-----------------------------------------------------------------
BEGIN
    DECLARE @xml varbinary(MAX) = CAST(N'<root>'+(SELECT * from 
    dbo.fn_device_reports(null , null , null) 
    ORDER BY 1, 2 ASC FOR XML RAW)+N'</root>' AS varbinary(MAX));
    WITH cte AS ( SELECT X.C.value(N'fn:local-name(.)', N'nvarchar(255)') as name FROM (SELECT CAST(@xml AS XML) AS XML_DATA) DATA CROSS APPLY DATA.XML_DATA.nodes(N'//*') as X(C) ) 
        INSERT INTO #result (category, report, name, cnt, chk) SELECT 'Device Reports', 'Device List', 'fn_device_reports', COUNT(*), CHECKSUM(@xml) from cte
    UPDATE #result SET tmsec = DATEDIFF(second, @counter, getdate() ) WHERE id= (SELECT MAX(id) FROM #result);SET @counter = getdate();
END

BEGIN
    DECLARE @xml1 varbinary(MAX) = CAST(N'<root>'+(SELECT * from 
    dbo.fn_device_category_reports(null , null , null, null) 
    ORDER BY 1, 2 ASC FOR XML RAW)+'</root>' AS varbinary(MAX));
    WITH cte AS ( SELECT X.C.value(N'fn:local-name(.)', N'nvarchar(255)') as name FROM (SELECT CAST(@xml1 AS XML) AS XML_DATA) DATA CROSS APPLY DATA.XML_DATA.nodes(N'//*') as X(C) ) 
        INSERT INTO #result (category, report, name, cnt, chk) SELECT 'Device Reports', 'Device Configuration by Group', 'fn_device_category_reports', COUNT(*), CHECKSUM(@xml1) from cte
    UPDATE #result SET tmsec = DATEDIFF(second, @counter, getdate() ) WHERE id= (SELECT MAX(id) FROM #result);SET @counter = getdate();
END

----..................  hundreds reports are here

INSERT INTO #result (category, report, name, cnt, tmsec, chk) SELECT 'Total', 'Total', 'Total', (SELECT SUM(cnt) FROM #result), (SELECT DATEDIFF(second, (SELECT MIN(stamp)
 FROm #result), (SELECT MAX(stamp) FROM #result))), (SELECT CHECKSUM_AGG(COALESCE(chk, 0)) FROM #result)

SELECT id, LEFT(name, 64), cnt, tmsec, chk FROM #result ORDER BY id ASC
GO

create function dbo.fn_device_reports( @p1 int, @p2 int, @p3 int)
RETURNS TABLE
WITH SCHEMABINDING
RETURN
SELECT  1 as col, 2 as num, 3 as val
union all
SELECT  4, 5, 6
GO

create function dbo.fn_device_category_reports( @p1 int, @p2 int, @p3 int, @p4 int)
RETURNS TABLE
WITH SCHEMABINDING
RETURN
SELECT  1 as col, 2 as num, 3 as val, 4 as [desc]
union all
SELECT  4, 5, 6, 7
GO