DECLARE @ShiftMaster TABLE
	(
		ShiftID INT NOT NULL,
		Name VARCHAR(50) NOT NULL,
		StartTime SMALLDATETIME NOT NULL,
		EndTime SMALLDATETIME NOT NULL
	)

INSERT	@ShiftMaster
	(
		ShiftID,
		Name,
		StartTime,
		EndTime
	)
SELECT	1, 'G', '09:30', '22:00' UNION ALL
SELECT	7, 'F', '00:00', '08:00' UNION ALL
SELECT	8, 'S', '08:00', '16:00' UNION ALL
SELECT	9, 'T', '16:00', '23:59'

DECLARE @CheckInTime TABLE
	(
		EmpID INT NOT NULL,
		InTime SMALLDATETIME NOT NULL,
		OutTime SMALLDATETIME NOT NULL
	)

INSERT	@CheckInTime 
	(
		EmpID,
		InTime,
		OutTime
	)
SELECT	1, '00:50', '08:50' UNION ALL
SELECT	2, '09:45', '14:45' UNION ALL
SELECT	3, '14:20', '22:45' UNION ALL
SELECT	4, '08:30', '16:45'

;WITH cteTimings(EmpID, ShiftID, Name, InTime, OutTime)
AS (
	SELECT		cit.EmpID,
			sm.ShiftID,
			sm.Name,
			CASE
				WHEN cit.InTime < sm.StartTime THEN sm.StartTime
				ELSE cit.InTime
			END AS InTime,
			CASE
				WHEN cit.OutTime > sm.EndTime THEN sm.EndTime
				ELSE cit.OutTime
			END AS OutTime
	FROM		@ShiftMaster AS sm
	INNER JOIN	@CheckInTime AS cit ON cit.InTime <= sm.EndTime
				AND cit.OutTime >= sm.StartTime
)
SELECT	EmpID,
	ShiftID,
	Name,
	WorkMinutes
FROM	(
		SELECT	EmpID,
			ShiftID,
			Name,
			DATEDIFF(MINUTE, InTime, OutTime) AS WorkMinutes,
			RANK() OVER (PARTITION BY EmpID ORDER BY DATEDIFF(MINUTE, InTime, OutTime) DESC) AS RecID
		FROM	cteTimings
	) AS d
WHERE	RecID = 1

quote:

---

Originally posted by Peso

---

;WITH cteTimings(EmpID, ShiftID, Name, InTime, OutTime)
AS (
	SELECT	cit.EmpID,
			sm.ShiftID,
			sm.Name,
			--When the shift name is general(G) then we should take CheckIn InTime. (Because the CheckIn and CheckOut of an employee comes with in the General shift time)
			CASE
				WHEN cit.InTime < sm.StartTime AND sm.Name != 'G' THEN sm.StartTime
				WHEN cit.InTime > sm.StartTime AND sm.Name != 'G' THEN cit.InTime
				ELSE cit.InTime
			END AS InTime,
			CASE
				WHEN cit.OutTime > sm.EndTime AND sm.Name != 'G' THEN sm.EndTime
				WHEN cit.OutTime < sm.EndTime AND sm.Name != 'G' THEN cit.OutTime
				ELSE cit.OutTime
			END AS OutTime
	FROM		@ShiftMaster AS sm
	INNER JOIN	@CheckInTime AS cit ON
			    (cit.InTime <= sm.EndTime
				AND cit.OutTime >= sm.StartTime)
)

SELECT EmpID,
	ShiftID,
	Name,
	WorkMinutes
FROM	(
		SELECT	EmpID,
			ShiftID,
			Name,
			DATEDIFF(MINUTE, InTime, OutTime) AS WorkMinutes,
			--When the start and end time falls in between the general shift we have to show general shift.
			--So we are adding ORDER BY ShiftID and in the table G should be the first record in the ShiftMaster table
			RANK() OVER (PARTITION BY EmpID ORDER BY ShiftID, DATEDIFF(MINUTE, InTime, OutTime) DESC) AS RecID
		FROM cteTimings
	) AS d
WHERE RecID = 1

quote:

---

Originally posted by Peso

Oh, those were the days. No harddrive, only 2 diskettes 5.25" (360kb)......

---

N 56°04'39.26"
E 12°55'05.63"

---