SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


DROP PROCEDURE  IF EXISTS dbo.stp_StatsCall_filt;
GO

/*-- =============================================
-- Author:		S.E.L.
-- Description:	Estrazione dati statistiche call filtrati

EXEC dbo.stp_StatsCall_filt '2024-01-01', GETDATE(), ''

-- Mod. date: 2024.05.31
-- =============================================*/
CREATE PROCEDURE [dbo].[stp_StatsCall_filt]
(
     @DtFrom	DATETIME	
    ,@DtTo	    DATETIME
    ,@SearchVal	NVARCHAR(250)
)
AS
BEGIN

    SET NOCOUNT ON;
    SET XACT_ABORT ON;    

WITH cteDati AS
(
	SELECT  YEAR(DataRif) AS YearRef, CodInst, CodApp, SUM(NumCall) AS TotCall
	FROM    LogCall
    WHERE   DataRif BETWEEN @DtFrom and @DtTo
        AND (@SearchVal = '' OR TargetUrl LIKE '%'+@SearchVal+'%')
	GROUP BY YEAR(DataRif), CodInst, CodApp
)

SELECT	*
FROM	cteDati
ORDER BY YearRef DESC, TotCall DESC

END


GO