IF OBJECT_ID('DSYNC_OFFLINE') IS NULL
BEGIN
    CREATE TABLE DSYNC_OFFLINE (
        ID BIGINT IDENTITY(1,1) PRIMARY KEY
        ,CLIENT_ID INT NOT NULL FOREIGN KEY REFERENCES DSYNC_CLIENT(ID)
        ,START_OFF DATETIME NOT NULL DEFAULT GETDATE()
        ,START_ON DATETIME
    )
END

GO

IF OBJECT_ID('F_DSYNC_OFFLINE') IS NOT NULL 
	DROP FUNCTION F_DSYNC_OFFLINE 
GO 

CREATE FUNCTION F_DSYNC_OFFLINE (@DT_INI DATETIME,@DT_FIN DATETIME)
RETURNS TABLE
AS RETURN
SELECT	C.ID AS CLIENT_ID
		,C.CID AS CD_FILIAL
		,C.CNAME
		,C.CHOST
		,CONVERT(DATETIME,CONVERT(VARCHAR,CO.START_OFF,112)) AS DT_OFF
		,DATEPART(HOUR,CO.START_OFF) AS HR_OFF
		,COUNT(0) QT_OFF
		,AVG(DATEDIFF(MINUTE,CO.START_OFF,ISNULL(CO.START_ON,GETDATE())))	AS AVG_OFF
		,MAX(DATEDIFF(MINUTE,CO.START_OFF,ISNULL(CO.START_ON,GETDATE())))	AS MAX_OFF
		,SUM(DATEDIFF(MINUTE,CO.START_OFF,ISNULL(CO.START_ON,GETDATE())))	AS TOT_OFF
FROM	DSYNC_CLIENT C
INNER	JOIN DSYNC_OFFLINE CO
ON		C.ID = CO.CLIENT_ID
WHERE	CONVERT(DATETIME,CONVERT(VARCHAR,CO.START_OFF,112)) BETWEEN @DT_INI AND @DT_FIN
		OR CO.START_ON IS NULL
GROUP	BY C.ID,C.CID,C.CNAME,C.CHOST,CONVERT(VARCHAR,CO.START_OFF,112),DATEPART(HOUR,CO.START_OFF)