Samuele Locatelli
44 lines
1.5 KiB
Transact-SQL
44 lines
1.5 KiB
Transact-SQL
|
|
|
|
CREATE VIEW [dbo].[v_printStatistic]
|
|
AS
|
|
/*
|
|
vista a scopo di verifica tempi di stampa in millesecondi ultimi 6 mesi
|
|
*/
|
|
|
|
WITH cte
|
|
AS ( SELECT
|
|
TipoCart ,
|
|
prtName ,
|
|
DATEDIFF( ms , dtStart , dtEnd) AS TimePrint ,
|
|
MAX( DATEDIFF( ms , dtStart , dtEnd ))OVER( PARTITION BY TipoCart , prtName) AS Maxi_ms ,
|
|
MIN( DATEDIFF( ms , dtStart , dtEnd ))OVER( PARTITION BY TipoCart , prtName) AS Min_ms ,
|
|
AVG( DATEDIFF( ms , dtStart , dtEnd ))OVER( PARTITION BY TipoCart , prtName) AS Med_ms ,
|
|
COUNT( * )OVER( PARTITION BY TipoCart , prtName) AS NumCart
|
|
FROM dbo.PrintJobQueue
|
|
WHERE stato = 1 AND dtStart > GETDATE() - 180
|
|
)
|
|
, cte2 AS
|
|
( SELECT * ,
|
|
CASE WHEN TimePrint BETWEEN Maxi_ms - 500 AND Maxi_ms + 500 THEN 1 ELSE 0 END AS MaxiCount ,
|
|
CASE WHEN TimePrint BETWEEN Min_ms - 100 AND Min_ms + 100 THEN 1 ELSE 0 END AS MinCount
|
|
FROM cte
|
|
)
|
|
SELECT
|
|
TipoCart ,
|
|
prtName ,
|
|
NumCart ,
|
|
Med_ms ,
|
|
Maxi_ms ,
|
|
SUM( MaxiCount) AS MaxiCount ,
|
|
Min_ms ,
|
|
SUM( MinCount) AS MinCount
|
|
FROM cte2
|
|
GROUP BY
|
|
TipoCart ,
|
|
prtName ,
|
|
Maxi_ms ,
|
|
Min_ms ,
|
|
Med_ms ,
|
|
NumCart;
|