--

DECLARE @StartDate AS DATETIME = '2016-01-01'
;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
,CTE0 AS
(
	SELECT *, DATEADD(d,Number,@StartDate) st FROM Series 
)
,CTE1
AS
(
	SELECT * , CASE WHEN StartDateTime > st then CAST(st AS DATE) else CAST(DATEADD(d,-1,CAST(StartDateTime AS DATE)) AS DATE) END starts
		     , CASE WHEN EndDateTime > et then CAST(et AS DATE) else CAST(DATEADD(d,-1,CAST(EndDateTime AS DATE)) AS DATE) END Ends
	FROM
	(
		SELECT EmployeeType,StartDateTime , EndDateTime , DATEADD(day, DATEDIFF(day, 0, CAST(StartDateTime AS DATE)), '07:00:00') st
		, DATEADD(day, DATEDIFF(day, 0, CAST(EndDateTime AS DATE)), '07:00:00') et
		FROM Employees1
	)k
)
SELECT EmployeeType, CAST(b.st AS DATE) WorkDate
			,SUM(CASE WHEN starts = b.st THEN 
				CASE WHEN a.st > StartDateTime THEN DATEDIFF(hh,StartDateTime,a.st) ELSE 
					CASE WHEN starts <> Ends THEN
							DATEDIFF(hh,StartDateTime,CASE WHEN a.et < EndDateTime THEN a.et ELSE EndDateTime END) 
					ELSE
							DATEDIFF(hh,StartDateTime,EndDateTime)
					END
				END
			ELSE
				CASE WHEN a.et < EndDateTime THEN DATEDIFF(hh,a.et,EndDateTime) ELSE DATEDIFF(hh,a.StartDateTime,a.et)END  
			END) Hours 
FROM CTE1 a
INNER JOIN CTE0 b ON b.st BETWEEN a.starts AND a.Ends
GROUP BY EmployeeType,Number,CAST(b.st AS DATE)
ORDER BY EmployeeType DESC,Number

--

--


;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3   
)
SELECT DISTINCT PawanName , CASE WHEN cnt = dcnt AND cnt >= 1 THEN chrs else Pawan_slug_name END Pawan_slug_name  FROM 
(
	SELECT * , COUNT(SUBSTRING(Ints,Number,1)) OVER (PARTITION BY PawanName) cnt , COUNT(SUBSTRING(Ints,Number,1)) OVER (PARTITION BY PawanName,SUBSTRING(Ints,Number,1)) dcnt FROM 
	(
		SELECT * , SUBSTRING(Pawan_slug_name,0,CHARINDEX('-',Pawan_slug_name,0)) chrs
		, SUBSTRING(Pawan_slug_name,CHARINDEX('-',Pawan_slug_name,0)+1,DATALENGTH(Pawan_slug_name)) Ints  FROM RemoveDuplicateIntsFromNames
	)k
	CROSS APPLY
	( 
		SELECT DISTINCT number FROM
		Series WHERE number > 0 AND number <= DATALENGTH(k.Ints) 
	)x
)m
ORDER BY PawanName 

--