в результате экспериментов получилось примерно так
Код:
SELECT CAST(FLOOR(CAST(m1.Date AS FLOAT)) AS DATETIME) AS Expr1, m1.date AS vhod, m3.date AS vihod, (DATEDIFF(mi, '2009-25-11 08:30:44', m1.date)) AS opozdanie, (DATEDIFF (mi,'2009-25-11 18:30:44',m3.date)) AS dosuhod
FROM MessageLog m1 JOIN MessageLOG m3
ON CAST(FLOOR(CAST(m1.Date AS FLOAT)) AS DATETIME) = CAST(FLOOR(CAST(m3.Date AS FLOAT)) AS DATETIME)
WHERE
m1.userid=3
AND
m3.userid=3
AND
m1.date IN (SELECT MIN(m2.Date) AS Expr2
FROM MessageLog AS m2
WHERE CAST(FLOOR(CAST(m1.Date AS FLOAT)) AS DATETIME) = CAST(FLOOR(CAST(m2.Date AS FLOAT)) AS DATETIME) and userid=3)
AND
m3.date IN (SELECT MAX(m4.Date) AS Expr4
FROM MessageLog AS m4
WHERE CAST(FLOOR(CAST(m3.Date AS FLOAT)) AS DATETIME) = CAST(FLOOR(CAST(m4.Date AS FLOAT)) AS DATETIME) and userid=3)
Admin: Пользуемся тегами!
Теперь не могу совладать с получением кол-ва часов проведенных сотрудником на работе за каждый день