0

Could you help me out with the following query?

My goal:

For each day and for each employee select minimum datetime and the maximum datetime and get the time difference in minutes

My query :

SELECT 
   PersonalId, Name, LastName, 
   Min(TmsPrick) as MinDatetime, 
   Max(TmsPrick) as MaxDatetime, 
   DATEDIFF(mi, MIN(TmsPrick), MAX(TmsPrick)) AS Expr1
FROM 
   prickTable
GROUP BY 
   DATEPART(dy, TmsPrick), PersonalId, name, lastname, TmsPrick
ORDER BY 
   PersonalId, TmsPrick

Note: DATEPART(dy, TmsPrick) = Day of year

My expected output

  PersonalId | Name      | LastName   | MinDatetime       | MaxDatetime        | Expr1
  ------------------------------------------------------------------------------------
   000001    | A         | AC         |2014-01-05 07:12:00|2014-01-05 16:00:00 | 528
   000001    | A         | AC         |2014-01-06 07:08:00|2014-01-05 16:00:00 | 532
   000001    | A         | AC         |2014-01-07 07:11:00|2014-01-07 16:00:00 | 529
   000002    | B         | BD         |2014-01-05 07:11:00|2014-01-05 16:00:00 | 529

The output I'm getting:

  PersonalId | Name      | LastName   | MinDatetime       | MaxDatetime        | Expr1
  ------------------------------------------------------------------------------------
   000001    | A         | AC         |2014-01-05 07:12:00|2014-01-05 16:00:00 | 1053168
   000001    | A         | AC         |2014-01-06 07:08:00|2014-01-05 16:00:00 | 532
   000001    | A         | AC         |2014-01-07 07:11:00|2014-01-07 16:00:00 | 529
   000002    | B         | BD         |2014-01-05 07:11:00|2014-01-05 16:00:00 | 1053169

Any idea why my output shows such differences?

Thanks

Note: The values in Tmsprick may vary from several a day to 2 in a week. The table contains values from over 3 years.

User999999
  • 2,500
  • 7
  • 37
  • 63
  • GROUP BY DATEPART(dy,TmsPrick) ?? – Miller Feb 03 '14 at 14:40
  • 2
    Well, for starters, why aren't you using the same columns on which you calculated the `MAX` and `MIN` date?. For those you are using `TmsPrick` and for the difference in minutes you are using `tijdstip` – Lamak Feb 03 '14 at 14:41
  • @Lamak. Good point. I altered the columnnames from dutch somewhat more english. But i seem to have forgotten some names. Will change em directly – User999999 Feb 03 '14 at 14:44
  • Your output does not correspond to the query. The query is aggregating by `tmsprick`, so the `min()` and `max()` values should be the same. – Gordon Linoff Feb 03 '14 at 14:44
  • unfortunately this is the output i'm actually getting. I know there should be a more easy way of becoming the same result. But somehow it is unknown to me. – User999999 Feb 03 '14 at 14:47
  • You have way too many items in your GROUP BY. – Aaron Bertrand Feb 03 '14 at 15:12

2 Answers2

3

Well, without seeing the source data it's hard to know for sure, but you're using different date columns in the MAX/MIN and DATEDIFF:

SELECT 
    PersonalId, 
    Name, 
    LastName, 
    Min(TmsPrick) as MinDatetime, 
    Max(TmsPrick) as MaxDatetime , 
    DATEDIFF(mi, MIN(tijdstip), MAX(tijdstip)) AS Expr1
                     ^-- should be TmsPrick?
D Stanley
  • 149,601
  • 11
  • 178
  • 240
1

I suspect that the query that you want is:

SELECT PersonalId, Name, LastName,
       Min(TmsPrick) as Mintime, Max(TmsPrick) as Maxtime, 
       DATEDIFF(mi, MIN(TmsPrick), MAX(TmsPrick)) AS Expr1
FROM prickTable
GROUP BY PersonalId, name, lastname, cast(TmsPrick as date)
ORDER BY PersonalId, max(TmsPrick);

This will give the the "elapsed" time on each day, because the first and last "tmsprick".

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786