/** 
* Scan Ingestion Processing Time Percentile Report 
* A scan throughput report in precentile/end-2-end ingestion time reports 
* Default: limits results to latest 1000 PendingScan that active flag is true/hasn't been user modified 
* Parameters:
* Count limit: change the number after the TOP keyword, you can also remove it all together. (Default: TOP 1000)
* Order: switch DESC to ASC for earliest scans. (Default: ORDER BY createdDate DESC)
* Active
*/
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT 
status AS Status, 
COUNT(*) AS total,
AVG(twentyfifthPTime) AS  '25th % Processing Time (Sec)',
AVG(medianPTime) AS 'Median Processing Time (Sec)',
AVG(eightiethPTime) AS  '80th % Processing Time (Sec)',
AVG(ninetiethPTime) AS  '90th % Processing Time (Sec)',
AVG(ninetyfifthPTime) AS  '95th % Processing Time (Sec)'
FROM
(SELECT 
status,
PERCENTILE_CONT(0.5) 
        WITHIN GROUP (ORDER BY processingTime ASC)
        OVER (PARTITION BY status) AS medianPTime, 
		PERCENTILE_CONT(0.25) 
        WITHIN GROUP (ORDER BY processingTime ASC)
        OVER (PARTITION BY status) AS twentyfifthPTime,
PERCENTILE_CONT(0.80) 
        WITHIN GROUP (ORDER BY processingTime ASC)
        OVER (PARTITION BY status) AS eightiethPTime,
PERCENTILE_CONT(0.90) 
        WITHIN GROUP (ORDER BY processingTime ASC)
        OVER (PARTITION BY status) AS ninetiethPTime, 
PERCENTILE_CONT(0.95) 
        WITHIN GROUP (ORDER BY processingTime ASC)
        OVER (PARTITION BY status) AS ninetyfifthPTime

FROM 
(SELECT TOP 1000 -- this can be adjusted 
pendingScanStatus AS status,
createdDate, 
endTime,
(DATEDIFF(millisecond, CONVERT(DATETIME2, createdDate, 103), CONVERT(DATETIME2, modifiedDate, 103))/1000) AS processingTime
FROM NewPendingScan WHERE active = 1 ORDER BY createdDate DESC) ps)  report
GROUP BY status; 
GO

/* 
* Total start to finish time for all 'Completed' and 'active' Pending Scans 
* Result time unit: minutes
*/ 
SELECT
(DATEDIFF(millisecond,
			CONVERT(DATETIME2, oldestCreatedDate, 103),
			CONVERT(DATETIME2, latestEndDate, 103))/1000 / 60) AS totalTime
FROM
(SELECT 
 MIN(createdDate) AS oldestCreatedDate, MAX(modifiedDate) AS latestEndDate 
 FROM
(SELECT TOP 1000 * FROM NewPendingScan 
WHERE active = 1 AND pendingScanStatus = 'COMPLETED'
ORDER BY createdDate DESC) ps ) t ; 

/*
* Report for avg times of all scan processing phases.
* Times are in millisecond. 
*/ 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT 

--TODO: replace avg with mean 
AVG(CAST(DATEDIFF(millisecond, CONVERT(DATETIME2, ps.createdDate, 103), importing.sTime) AS BIGINT)) AS 'Queued for Import (Scan Files)',
AVG(
CASE 
WHEN ps.importType = 'FILE_UPLOAD' THEN CAST(DATEDIFF(millisecond, importing.sTime, queuedForProcessing.sTime) AS BIGINT)
ELSE 0 END) AS 'Import Time (Scan Files)',
-- Avg time to pull/import a RP PendingScan  
AVG(
CASE 
WHEN ps.importType = 'REMOTE_PROVIDER_IMPORT_TYPE' THEN CAST(DATEDIFF(millisecond, CONVERT(DATETIME2, ps.requestTime, 103), queuedForProcessing.sTime) AS BIGINT)
ELSE 0 END
) AS 'RP Scan Import Time', 

AVG(CAST(DATEDIFF(millisecond, queuedForProcessing.sTime, processing.sTime) AS BIGINT )) AS 'Queued for Processing',
AVG(CAST(DATEDIFF(millisecond, processing.sTime, queueingForIngestion.sTime) AS BIGINT)) AS 'Processing',
AVG(CAST(DATEDIFF(millisecond, queueingForIngestion.sTime, queuedForIngestion.sTime) AS BIGINT)) AS 'Queueing for Ingestion', 
--This might be closer in accuracy than (queuedForReconcile (minus) queuedForIngestion)
AVG(CAST(DATEDIFF(millisecond, queueingForIngestion.sTime, queuedForReconcile.sTime) AS BIGINT)) AS 'DB Ingestion', 
AVG(CAST(DATEDIFF(millisecond, queuedForReconcile.sTime, reconciling.sTime) AS BIGINT)) AS 'Queued for Reconcile',
AVG(CAST(DATEDIFF(millisecond, reconciling.sTime, completed.sTime) AS BIGINT)) AS 'Reconcile'

FROM 
(SELECT TOP 500 * FROM NewPendingScan 
WHERE active = 1 and pendingScanStatus = 'COMPLETED'
ORDER BY endTime DESC) ps 
INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'COMPLETED')  completed ON completed.pendingScanId = ps.id 
INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'RECONCILING') reconciling ON reconciling.pendingScanId = ps.id 
INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'QUEUED_FOR_RECONCILE') queuedForReconcile ON queuedForReconcile.pendingScanId = ps.id 
INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'QUEUED_FOR_INGESTION') queuedForIngestion ON queuedForIngestion.pendingScanId = ps.id 
INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'QUEUING_FOR_INGESTION') queueingForIngestion ON queueingForIngestion.pendingScanId = ps.id 
INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'PROCESSING') processing ON processing.pendingScanId = ps.id 
INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'QUEUED_FOR_PROCESSING') queuedForProcessing ON queuedForProcessing.pendingScanId = ps.id
LEFT JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'IMPORTING') importing ON importing.pendingScanId = ps.id 
LEFT JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'QUEUED_FOR_IMPORT') queuedForimport ON importing.pendingScanId = ps.id;
GO

SELECT rpe.name                 AS 'RP Connection',
       rpt.name                 AS 'Source Type',
       type                     AS 'Request Type',
       status                   AS 'Status',
       (DATEDIFF(millisecond,
                 CONVERT(DATETIME2, processingStartTimestamp, 103),
                 COALESCE(CONVERT(DATETIME2, finishedTimestamp, 103), GETDATE()))
           / (1000 * 60))       as 'Import Time (min)',
       requestTimestamp         AS 'Requested Time',
       processingStartTimestamp AS 'Request Import Start Time',
       finishedTimestamp        AS 'Request Import Finish Time',
       applicationId,
       remoteProviderApplicationId,
       error,
       errorMessage,
       request.id

FROM RemoteProviderImportRequest request
         LEFT JOIN RemoteProviderEntity rpe ON rpe.id = request.remoteProviderEntityId
         LEFT JOIN RemoteProviderType rpt ON rpt.id = rpe.remoteProviderTypeId
ORDER BY createdDate DESC;