CREATE FUNCTION [dbo].[APPOINTMENT_GAPS_SINGLE] 
(@DATES DATES,
 @LICENSE LICENSE)
 RETURNS TABLE
 AS
RETURN

WITH Gaps AS (
        SELECT ID_EMPLOYEE_GUID, ts, [Type]
            ,e=CASE [Type] WHEN 1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY ID_EMPLOYEE_GUID, [Type] ORDER BY TIME_UNTILL) END
            ,s=CASE [Type] WHEN -1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY ID_EMPLOYEE_GUID, [Type] ORDER BY TIME_FROM) END
        FROM APPOINTMENT_OCCUPIED(@DATES, @LICENSE)
        CROSS APPLY (
            VALUES (1, TIME_FROM), (-1, TIME_UNTILL)) a([Type], ts)
        ),
    C2 AS (
        SELECT Gaps.
            ,se=ROW_NUMBER() OVER (PARTITION BY ID_EMPLOYEE_GUID ORDER BY ts, [Type] DESC)
        FROM Gaps),
    C3 AS (
        SELECT ID_EMPLOYEE_GUID, ts
            ,grpnm=FLOOR((ROW_NUMBER() OVER (PARTITION BY ID_EMPLOYEE_GUID ORDER BY ts)-1)  2 + 1)
        FROM C2
        WHERE COALESCE(s-(se-s)-1, (se-e)-e) = 0),
    MinMax AS (
        SELECT ID_EMPLOYEE_GUID, TIME_FROM=MIN(ts), TIME_UNTILL=MAX(ts)
        FROM C3
        GROUP BY ID_EMPLOYEE_GUID, grpnm)

-- Convert the Islands from MinMax into gaps (method by Dwain.C)
-- httpswww.simple-talk.comsqlt-sql-programmingthe-sql-of-gaps-and-islands-in-sequences
SELECT ID_EMPLOYEE_GUID, TIME_FROM=MIN([date]), TIME_UNTILL=MAX([date])
FROM (

		SELECT ID_EMPLOYEE_GUID, [date], rn=ROW_NUMBER() OVER (PARTITION BY ID_EMPLOYEE_GUID ORDER BY [date])2

		FROM (
			-- Normally this would be a CROSS APPLY VALUES but this works in SQL 2005
			SELECT ID_EMPLOYEE_GUID, [date]=TIME_FROM
			FROM MinMax
			UNION ALL
			SELECT ID_EMPLOYEE_GUID, [date]=TIME_UNTILL
			FROM MinMax
			 ) a
	 ) a
--cross apply (values(StartDate), (EndDate)) b ([date])) b
--inner join vEMPLOYEE as e on e.ID_GUID = a.ID_EMPLOYEE_GUID
--where e.IND_WEBSITE = 'Y'
--where
-- id_employee_guid = '0B13951D-5E31-42FA-952A-E2A56F5C8001'

--and
 --[date] between '2182016' and '2292016'

GROUP BY ID_EMPLOYEE_GUID, rn
HAVING COUNT() = 2
and DATEDIFF(mi, MIN([date]), MAX([date])) = 5
and DATEPART(weekday, MIN([date])) = DATEPART(weekday, MAX([date]))
and DATEDIFF(hh, MIN([date]), MAX([date])) = 23


--   and DATEDIFF(hh, MIN([date]), MAX([date])) = 14
--   and DATEPART(weekday, MIN([date])) = DATEPART(weekday, MAX([date]))
--   and DATEDIFF(mi, MIN([date]), MAX([date])) = 5

--;