SQL First Time In Last Time Out (Transact-SQL..preferrably)

Question

I have the following table which contains all the time in and time out of people:

 CREATE TABLE test (
       timecardid INT
     , trandate DATE
     , employeeid INT
     , trantime TIME
     , Trantype VARCHAR(1)
     , Projcode VARCHAR(3)
)

The task is to get all the earliest trantime with trantype A (perhaps using MIN) and the latest trantime with trantype Z (Using Max), all of which in that trandate (ie. trantype A for july 17 is 8:00 AM and trantype Z for july 17 is 7:00PM).

the problem is, the output should be in the same format as the table where it's coming from, meaning that I have to leave this data and filter out the rest (that aren't the earliest and latest in/out for that date, per employee)

My current solution is to use two different select commands to get all earliest, then get all the latest. then combine them both.

I was wondering though, is there a much simpler, single string solution?

Thank you very much.

EDIT (I apologize, here is the sample. Server is SQL Server 2008):

 Timecardid | Trandate | employeeid | trantime | trantype | Projcode 
      1      2013-04-01      1        8:00:00        A       SAMPLE1
      2      2013-04-01      1        9:00:00        A       SAMPLE1
      3      2013-04-01      2        7:00:00        A       SAMPLE1
      4      2013-04-01      2        6:59:59        A       SAMPLE1
      5      2013-04-01      1       17:00:00        Z       SAMPLE1
      6      2013-04-01      1       17:19:00        Z       SAMPLE1
      7      2013-04-01      2        17:00:00       Z       SAMPLE1

      8      2013-04-02      1        8:00:00        A       SAMPLE1
      9      2013-04-02      1        9:00:00        A       SAMPLE1
     10      2013-04-02      2        7:00:58        A       SAMPLE1
     11      2013-04-02      2       18:00:00        Z       SAMPLE1
     12      2013-04-02      2       18:00:01        Z       SAMPLE1
     13      2013-04-02      1       20:00:00        Z       SAMPLE1

Expected Results (the earliest in and the latest out per day, per employee, in a select command):

  Timecardid | Trandate | employeeid | trantime | trantype | Projcode 
      1      2013-04-01      1        8:00:00        A       SAMPLE1 
      4      2013-04-01      2        6:59:59        A       SAMPLE1   
      6      2013-04-01      1       17:19:00        Z       SAMPLE1
      7      2013-04-01      2        17:00:00       Z       SAMPLE1
      8      2013-04-02      1        8:00:00        A       SAMPLE1
     10      2013-04-02      2        7:00:58        A       SAMPLE1
     12      2013-04-02      2       18:00:01        Z       SAMPLE1
     13      2013-04-02      1       20:00:00        Z       SAMPLE1

Thank you very much

Answer 1

I would use ROW_NUMBER to sort out the rows you want to select:

;with Ordered as (
  select *,
    ROW_NUMBER() OVER (PARTITION BY Trandate,employeeid,trantype
        ORDER BY trantime ASC) as rnEarly,
    ROW_NUMBER() OVER (PARTITION BY Trandate,employeeid,trantype
        ORDER BY trantime DESC) as rnLate
  from
    Test
)
select * from Ordered
where
  (rnEarly = 1 and trantype='A') or
  (rnLate = 1 and trantype='Z')
order by TimecardId

(SQLFiddle)

It produces the results you've requested, and I think it's quite readable. The reason that trantype is included in the PARTITION BY clauses is so that A and Z values receive separate numbering.

Answer 2

Perhaps this is what you're looking for:

select 
    t.* 
from 
    test t
where 
    trantime in (
        (select min(trantime) from test t1 where t1.trandate = t.trandate and trantype = 'A'),
        (select max(trantime) from test t2 where t2.trandate = t.trandate and trantype = 'Z')
    )

Changing my answer to account for the "per employee" requirement:

;WITH EarliestIn AS
(
    SELECT trandate, employeeid, min(trantime) AS EarliestTimeIn
    FROM test 
    WHERE trantype = 'A'
    GROUP BY trandate, employeeid 
), 
LatestOut AS
(
    SELECT trandate, employeeid, max(trantime) AS LatestTimeOut
    FROM test 
    WHERE trantype = 'Z'
    GROUP BY trandate, employeeid 
)
SELECT * 
FROM test t
WHERE 
    EXISTS (SELECT * FROM EarliestIn WHERE t.trandate = EarliestIn.trandate AND t.employeeid = EarliestIn.employeeid AND t.trantime = EarliestIn.EarliestTimeIn) 
    OR EXISTS (SELECT * FROM LatestOut WHERE t.trandate = LatestOut.trandate AND t.employeeid = LatestOut.employeeid AND t.trantime = LatestOut.LatestTimeOut)

Answer 3

If you're using SQL server 2012, you can use LAG/LEAD to find the max and min rows in a fairly concise way;

WITH cte AS (
  SELECT *,
       LAG(timecardid)  OVER (PARTITION BY trandate,employeeid,trantype ORDER BY trantime)  lagid,
       LEAD(timecardid) OVER (PARTITION BY trandate,employeeid,trantype ORDER BY trantime) leadid
  FROM test
)
SELECT timecardid,trandate,employeeid,trantime,trantype,projcode
FROM cte
WHERE trantype='A' AND lagid  IS NULL
   OR trantype='Z' AND leadid IS NULL;

An SQLfiddle to test with.

Answer 4

Assuming timecardid column is PK or unique, and if I understand it correctly, I would do something like

DECLARE @date DATE
SET @date = '2013-07-01'

SELECT
    T0.*
FROM
    (SELECT DISTINCT employeeid FROM test) E
    CROSS APPLY (
        SELECT TOP 1
            T.timecardid
        FROM
            test T
        WHERE
            T.trandate = @date
            AND T.Trantype = 'A'
            AND T.employeeid = E.employeeid
        ORDER BY T.trantime
        UNION ALL
        SELECT TOP 1
            T.timecardid
        FROM
            test T
        WHERE
            T.trandate = @date
            AND T.Trantype = 'Z'
            AND T.employeeid = E.employeeid
        ORDER BY T.trantime DESC
    ) V
    JOIN test T0 ON T0.timecardid = V.timecardid

Appropriate indexes should be set for the table, if you aware of performance.