How to get a time for the dates?

Question

Table1

ID Date Time Functionkey

001 23-02-2009, 08:00:00 A
001 23-02-2009, 12:00:00 A
001 23-02-2009, 13:00:00 B
001 23-02-2009, 14:00:00 B
001 23-02-2009, 16:00:00 C
001 23-02-2009, 17:00:00 C
001 23-02-2009, 19:00:00 D
001 23-02-2009, 21:00:00 D
001 24-02-2009 10:00:00 A
001 24-02-2009 18:00:00 D
001 25-02-2009 09:00:00 A
001 25-02-2009 12:00:00 B
001 25-02-2009 18:00:00 D
....,

I want to get min(time) where function key = 'A', max(time) where function key = 'B', min(time) where function key ='c'. max(time) where function key = 'D' Query:

   SELECT 
      ID, Date, 
      CASE WHEN function = 'A' THEN min(time) END as Intime1,
      CASE WHEN function = 'B' THEN max(time) END as Outtime1, 
      CASE WHEN function = 'C' THEN min(time) END as Intime2,  
      CASE WHEN function = 'D' THEN max(time) END as Outtime2 
   FROM dbo.table1 
   GROUP BY id, date, function

Getting Output

ID Date, Intime1, Outtime1, Intime2, Outtime2

001 23-02-2009 08:00:00, null, null, null
001 23-02-2009 null, 14:00:00, null, null
001 23-02-2009 null, null, 16:00:00, null
001 23-02-2009 null, null, null, 21:00:00

...,

I need output in one line, How to make a query

ID Date, Intime1, Outtime1, Intime2, Outtime2

001 23-02-2009 08:00:00, 14:00:00. 16:00:00, 21:00:00 
001 24-02-2009 09:00:00  null, null, 18:00:00
001 25-02-2009 09:00:00, 12:00:00, null, 18:00:00

...,

How to modify my query?

Answer 1

One thing you really should consider, is to store your date/time as a single column formatted as a unix timestamp, it makes handling date/time a lot easier.

Answer 2

Try the following:

SELECT  ID, 
        Date, 
        min(time) AS Intime, 
        max(time) AS Outtime 
FROM table1 
GROUP BY id, date

Answer 3

Try something like:

SELECT T1.ID, T1.Date, MIN(T1.Time) As Intime, MAX(T2.Time) AS Outtime
FROM Table1 T1
JOIN Table1 T2 ON T1.ID=T2.ID AND T1.Date=T2.Date
WHERE T1.Function = 'A' AND T2.Function='D'
GROUP BY T1.ID, T1.Date

Edit: Regarding your comment you just need to change the condition for table T1 with the function for the minimum (see the MIN(T1.Time) above) and the condition for table T2 with the function for the maximum (see MAX(T2.Time) above), so it should be:

SELECT T1.ID, T1.Date, MIN(T1.Time) As Intime, MAX(T2.Time) AS Outtime
FROM Table1 T1
JOIN Table1 T2 ON T1.ID=T2.ID AND T1.Date=T2.Date
WHERE T1.Function = 'C' AND T2.Function='D'
GROUP BY T1.ID, T1.Date

I hope I understood correctly from the comment what you need.

Edit 2: OK, I got it now. You need to do the same self join two more times like this:

SELECT 
 T1.ID, T1.Date, 
 MIN(T1.Time) AS Intime1,
 MAX(T2.Time) AS Outtime1,
 MIN(T3.Time) AS Intime2,
 MAX(T4.Time) AS Outtime2
FROM Table1 T1
JOIN Table1 T2
 ON T1.ID=T2.ID AND T1.Date=T2.Date
JOIN Table1 T3
 ON T1.ID=T3.ID AND T1.Date=T3.Date
JOIN Table1 T4
 ON T1.ID=T4.ID AND T1.Date=T4.Date
WHERE 
 T1.Func = 'A' 
 AND T2.Func='B'
 AND T3.Func='C'
 AND T4.Func='D'
GROUP BY
 T1.ID, T1.Date

Edit 3: To account for possible missing values try the next query. The only value that needs to be present is that for the function 'A'. The rest can be absent:

SELECT 
 T1.ID, T1.Date, 
 MIN(T1.Time) AS Intime1,
 MAX(T2.Time) AS Outtime1,
 MIN(T3.Time) AS Intime2,
 MAX(T4.Time) AS Outtime2
FROM Table1 T1
LEFT JOIN Table1 T2
 ON T1.ID=T2.ID AND T1.Date=T2.Date AND T2.Func = 'B'
LEFT JOIN Table1 T3
 ON T1.ID=T3.ID AND T1.Date=T3.Date AND T3.Func = 'C'
LEFT JOIN Table1 T4
 ON T1.ID=T4.ID AND T1.Date=T4.Date AND T4.Func = 'D'
WHERE 
 T1.Func = 'A' 
GROUP BY
 T1.ID, T1.Date