0

Please login or register to vote for this query.

(click on this box to dismiss)

http://stackoverflow.com/questions/7605842/query-for-time-attendance-min-and-max-hours-group-by-day/

Q&A for SharePoint enthusiasts

declare @myTable table (eventime datetime)
insert @myTable 
select           CAST('2011-09-01 10:29:37' AS datetime) 
union all select CAST('2011-09-01 10:50:04' AS datetime)
union all select CAST('2011-09-01 17:10:45' AS datetime)
union all select CAST('2011-09-30 08:15:10' AS datetime)
union all select CAST('2011-09-30 11:40:44' AS datetime)
union all select CAST('2011-09-30 15:50:40' AS datetime)
union all select CAST('2011-09-30 17:50:11' AS datetime)

SELECT MinDate.Day as Day,
       MinDate.Hour as FirstLogin,
       MaxDate.Hour as LastLogin
FROM
    (
        SELECT MIN(eventime) as Hour, CAST(eventime AS Date) as Day
        FROM @myTable 
        GROUP BY CAST(eventime AS Date)
    ) AS MinDate
    INNER JOIN
    (
        SELECT MAX(eventime) as Hour, CAST(eventime AS Date) as Day
        FROM @myTable 
        GROUP BY CAST(eventime AS Date)
    ) AS MaxDate
    ON MinDate.Day = MaxDate.Day

fork query

created sep 30 11 Scorpi0