-- ***********************************************
-- I need to get to this
-- ***********************************************

Employee Number		Clock In		Clock Out		Hours Worked
-----------------	---------------------	---------------------	-------------
AD101			2012-10-05 07:36:00	2012-10-05 12:05:00	4.483333

-- ***********************************************
-- Not this
-- ***********************************************

Employee Number		Clock In		Clock Out		Hours Worked
-----------------	---------------------	---------------------	-------------
AD101			2012-10-05 12:07:00	2012-10-05 12:05:00	

-- ***********************************************
-- Notice that the Clock In should be 07:36:00 not 12:07:00
--   This would also get me to the correct Hours Worked
-- ***********************************************

-- ==== Create and Populate 3 Row Test_Table with Sample Data

CREATE TABLE [Test_Table]
       (     [ID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED -- PK
           , [Date] [smalldatetime] NOT NULL              -- Date using SmallDateTime Type YYYY-MM-DD HH:MM:00
           , [Employee Number] [varchar](50) NOT NULL     -- Employee can contain alphanumeric
           , [Clock In or Out] [varchar](50) NOT NULL   ) -- Record Type; "Clock In" or "Clock Out" Record Only

SET DATEFORMAT DMY

SET IDENTITY_INSERT [Test_Table] ON

INSERT INTO [Test_Table]
       (    [ID]
          , [Date]
          , [Employee Number]
          , [Clock In or Out]            )
SELECT '3','Oct  5 2012  7:36AM','AD101','Clock In'  UNION ALL
SELECT '4','Oct  5 2012 12:05PM','AD101','Clock Out' UNION ALL
SELECT '5','Oct  5 2012 12:07PM','AD101','Clock In'

-- ==== End Create and Populate

-- ***********************************************
-- Select ALL from Test_Table
-- ***********************************************

SELECT       [ID]
           , [Date]
           , [Employee Number]
           , [Clock In or Out]
FROM         [Test_Table]
ORDER BY     [Date]

ID	Date			Employee Number		Clock In or Out
--	--------------------	-----------------	---------------
3	2012-10-05 07:36:00	AD101			Clock In
4	2012-10-05 12:05:00	AD101			Clock Out
5	2012-10-05 12:07:00	AD101			Clock In

-- ***********************************************
-- Select from Test_Table with conditions
-- ***********************************************

SELECT     [Employee Number]
             , [Clock In]
             , [Clock Out]
             , (CASE WHEN CAST([Clock In] as SmallDateTime) = '1900-01-01 00:00:00'
                 THEN ''
                 WHEN CAST([Clock Out] as SmallDateTime) = '1900-01-01 00:00:00'
                 THEN ''
                 WHEN CAST([Clock In] as SmallDateTime) < CAST([Clock Out] as SmallDateTime)
                 THEN CAST(CONVERT(DECIMAL,DATEDIFF(MINUTE,CAST([Clock In] as SmallDateTime),CAST([Clock Out] as SmallDateTime)))/60 as Varchar)
                 ELSE '' END) [Hours Worked]
FROM (
            SELECT     [Employee Number]
                     , MAX(CASE WHEN [Clock In or Out] = 'Clock In'  -- I believe this is the area where I am having the problem
                                THEN CAST([Date] as SmallDateTime)   -- I have tried MIN(CASE WHEN... but that does not seem to work for me either
                                ELSE '' END) [Clock In]
                     , MAX(CASE WHEN [Clock In or Out] = 'Clock Out'
                                THEN CAST([Date] as SmallDateTime)
                                ELSE '' END) [Clock Out]
FROM (
        SELECT     [Employee Number]
                     , [Clock In or Out]
                     , CAST(CONVERT(VARCHAR(10),[Date],111) as Date) [Clock In or Out Date]
                     , [Date]
FROM    [Test_Table]
      ) TableA
GROUP BY           [Employee Number]
                 , [Clock In or Out Date]
      ) TableB

-- ***********************************************
-- Output is not what I was trying for.
--     Notice that the Clock In Time is 12:07:00
--        I need it to be the other record which is 07:36:00
-- ***********************************************

Employee Number		Clock In		Clock Out		Hours Worked
-----------------	---------------------	---------------------	-------------
AD101			2012-10-05 12:07:00	2012-10-05 12:05:00	

-- ***********************************************
-- This is what I was expecting, but have been unable to get to
-- ***********************************************

Employee Number		Clock In		Clock Out		Hours Worked
-----------------	---------------------	---------------------	-------------
AD101			2012-10-05 07:36:00	2012-10-05 12:05:00	4.483333

SELECT 
	[Employee Number],
	X.[Dated],
	MIN(CASE [Clock In or Out] WHEN 'Clock In' THEN CONVERT(DATETIME,[Date]) ELSE CONVERT(DATETIME,'31 dec 2199') END) AS [Clock In],
	MAX(CASE [Clock In or Out] WHEN 'Clock In' THEN 0 ELSE [Date] END) AS [Clock Out]
FROM [Test_Table]
CROSS APPLY (
	-- This just works out the date part without the time
	SELECT DATEADD(DAY,datediff(DAY,0,[Date]),0) AS [Dated]
	) AS X
GROUP BY 
	[Employee Number],
	X.[Dated]