How to get the maximum of time the employee falls in which shift.

Hi,
DECLARE @CheckInTime AS TABLE(InTime DATETIME)

INSERT INTO @CheckInTime
SELECT '1900-01-01 11:50:00.000' UNION ALL
SELECT '1900-01-01 06:45:00.000' UNION ALL
SELECT '1900-01-01 16:20:00.000' UNION ALL
SELECT '1900-01-01 21:30:00.000'

SELECT * FROM @CheckInTime


DECLARE @ShiftMaster AS TABLE([ID] [int],
[Name] [varchar](50) NOT NULL,
[StartTime] [datetime] NOT NULL,
[EndTime] [datetime] NOT NULL)

INSERT INTO @ShiftMaster
SELECT 1, 'G', '1900-01-01 09:30:00.000', '1900-01-01 22:00:00.000' UNION ALL
SELECT 7, 'F', '1900-01-01 06:00:00.000', '1900-01-01 14:00:00.000' UNION ALL
SELECT 8, 'S', '1900-01-01 14:00:00.000', '1900-01-01 21:30:00.000' UNION ALL
SELECT 9, 'T', '1900-01-01 21:30:00.000', '1900-01-01 02:30:00.000'

SELECT * FROM @ShiftMaster


The table @CheckInTime conatins the In time of an employee. The @ShiftMaster contains the shift.
How to get the maximum of time the employee falls in which shift.

Thanks,
Babu Kumarasamy.

Thanks for the quick reply Frank Kalis.
Now I changed the table structure.
DECLARE @CheckInTime AS TABLE(EmpID INT, InTime DATETIME)INSERT
INTO @CheckInTime SELECT
1, '1900-01-01 11:50:00.000' UNION ALLSELECT
2, '1900-01-01 06:45:00.000' UNION ALLSELECT
3, '1900-01-01 16:20:00.000' UNION ALLSELECT
4, '1900-01-01 21:30:00.000'SELECT
* FROM @CheckInTimeDECLARE
@ShiftMaster AS TABLE([ID] [int],[Name] [varchar]
(50) NOT NULL,[StartTime] [datetime]
NOT NULL,[EndTime] [datetime]
NOT NULL)INSERT
INTO @ShiftMasterSELECT
1, 'G', '1900-01-01 09:30:00.000', '1900-01-01 22:00:00.000' UNION ALLSELECT
7, 'F', '1900-01-01 06:00:00.000', '1900-01-01 14:00:00.000' UNION ALLSELECT
8, 'S', '1900-01-01 14:00:00.000', '1900-01-01 21:30:00.000' UNION ALLSELECT
9, 'T', '1900-01-01 21:30:00.000', '1900-01-01 02:30:00.000'SELECT
* FROM @ShiftMaster
Thanks,
Babu Kumarasamy

Sorry FrankKalis. once again I need changed the Table structure.
Thanks for the reply.DECLARE
@CheckInTime AS TABLE(EmpID INT, InTime DATETIME, OutTime DATETIME)INSERT
INTO @CheckInTime SELECT
1, '1900-01-01 00:50:00.000', '1900-01-01 08:50:00.000' UNION ALLSELECT
2, '1900-01-01 09:45:00.000', '1900-01-01 14:45:00.000' UNION ALLSELECT
3, '1900-01-01 14:20:00.000', '1900-01-01 22:45:00.000' UNION ALLSELECT
4, '1900-01-01 08:30:00.000', '1900-01-01 06:45:00.000'--SELECT * FROM @CheckInTimeDECLARE
@ShiftMaster AS TABLE([ShiftID] [int],[Name] [varchar]
(50) NOT NULL,[StartTime] [datetime]
NOT NULL,[EndTime] [datetime]
NOT NULL)INSERT
INTO @ShiftMasterSELECT
1, 'G', '1900-01-01 09:30:00.000', '1900-01-01 22:00:00.000' UNION ALLSELECT
7, 'F', '1900-01-01 00:00:00.000', '1900-01-01 08:00:00.000' UNION ALLSELECT
8, 'S', '1900-01-01 08:00:00.000', '1900-01-01 16:00:00.000' UNION ALLSELECT
9, 'T', '1900-01-01 16:00:00.000', '1900-01-01 23:59:00.000'There is no relationship between @ShiftMaster and @CheckInTime.
Here emp 1 should comes in F shift. He also falls in S shift. But he works more than F shift when compared to S shift
Here emp 2 should comes in G shift
Here emp 3 should comes in T shift (because he works more time in T shift)
Here emp 4 should comes in S shift
I wants to get (ShiftID) in which shift he works for more time.
Thanks,
Babu Kumarasamy.