Home Forums SQL Server 2005 Development Converting sequential time records into IN and OUT times RE: Converting sequential time records into IN and OUT times

  • Got part of the way there... must have done something silly, because the math looks backwards... I created some sample records to play with... hopefully it's enough.

    SELECT EmpID, InOutDate

    ,MIN(InOutTimeStamp) AS InStamp

    ,MAX(InOutTimeStamp) AS OutStamp

    ,DATEDIFF(n,MAX(InOutTimeStamp),MIN(InOutTimeStamp)) AS MinsWorked

    FROM

    (SELECT EmpID

    , InOutDate

    , InOutTimeStamp

    , ROW_NUMBER() OVER (PARTITION BY EmpID, InOutDate ORDER BY EmpID, InOutDate) AS rn

    FROM

    (

    SELECT EmpID

    , InOutTimeStamp

    , CAST(InOutTimeStamp AS DATE) AS InOutDate

    FROM

    (SELECT 1 AS EmpID,'2-1-2014 8:30' AS InOutTimeStamp

    UNION ALL

    SELECT 1, '2-1-2014 18:30'

    UNION ALL

    SELECT 1, '2-2-2014 7:30'

    UNION ALL

    SELECT 1, '2-2-2014 15:30'

    UNION ALL

    SELECT 2, '2-1-2014 6:30'

    UNION ALL

    SELECT 2, '2-1-2014 19:30') x

    ) y

    ) z

    GROUP BY EmpID, InOutDate

    ORDER BY EmpID, InOutDate;