When Does Minimum not Mean Minimum?

• Neil Burton

  SSC-Insane

  Points: 22920

  More actions

  July 23, 2014 at 2:33 am

  #309439

  I've got an interesting little problem that's really puzzling me. I have two tables like those below and I'm trying to find the minimum date processed for each history id.

  create table #EventHeader(

  HistoryIDint primary keynot null

  ,DateTimeHappened datetimenot null

  )

  create table #EventDetail

  (

  HistoryIDint

  ,DetailIDint primary key not null

  ,DateTimeProcessed datetimenot null

  ,ProcessedBy varchar(20)not null

  )

  insert into #EventHeader

  select 100001, '2014-07-23 08:50:00.123' union all

  select 100002, '2014-07-23 08:51:00.123' union all

  select 100003, '2014-07-23 08:52:00.123' union all

  select 100004, '2014-07-23 08:53:00.123' union all

  select 100005, '2014-07-23 08:54:00.123' union all

  select 100006, '2014-07-23 08:55:00.123' union all

  select 100007, '2014-07-23 08:56:00.123' union all

  select 100008, '2014-07-23 08:57:00.123' union all

  select 100009, '2014-07-23 08:58:00.123' union all

  select 100010, '2014-07-23 08:59:00.123'

  insert into #EventDetail

  select 100001,200001, '2014-07-23 08:50:07.123','System1' union all

  select 100001,200002, '2014-07-23 08:50:08.123','System2' union all

  select 100001,200003, '2014-07-23 08:54:09.123','APerson' union all

  select 100002,200004, '2014-07-23 08:51:07.123','System1' union all

  select 100002,200005, '2014-07-23 08:51:08.123','System2' union all

  select 100002,200006, '2014-07-23 08:57:09.123','APerson' union all

  select 100003,200007, '2014-07-23 08:52:08.123','System1' union all

  select 100003,200008, '2014-07-23 08:52:07.123','System2' union all

  select 100003,200009, '2014-07-23 08:59:09.123','APerson' union all

  select 100004,200010, '2014-07-23 08:53:07.123','System1' union all

  select 100004,200011, '2014-07-23 09:00:09.123','APerson' union all

  select 100005,200012, '2014-07-23 08:54:07.123','System1' union all

  select 100006,200013, '2014-07-23 08:55:08.123','System1' union all

  select 100007,200014, '2014-07-23 08:56:09.123','System1' union all

  select 100008,200015, '2014-07-23 08:57:07.123','System1' union all

  select 100009,200016, '2014-07-23 08:58:08.123','System1' union all

  select 100010,200017, '2014-07-23 08:59:09.123','System1'

  select distinct

  eh.HistoryID

  ,min(ed.DateTimeProcessed) over (partition by eh.HistoryId)

  from

  #EventHeader eh

  join #EventDetail ed on ed.HistoryID = eh.HistoryID

  drop table #EventHeader, #EventDetail

  What's happening is that for a couple of events it's not bringing back the minimum but the second minimum. So for event 100001 it will return 2014-07-23 08:50:08.123 instead of 2014-07-23 08:50:07.123. I'm stumped by this because it's only three rows out of thirteen million and I can't for the life of me see what's wrong. Any suggestions?

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• Neil Burton

  SSC-Insane

  Points: 22920

  More actions

  July 23, 2014 at 3:28 am

  #1731673

  I've solved it and it was pretty obvious in the end. My where predicate was excluding the rows with the earlier times from the query because I was only interested in certain event types. If the minimum time associated with a HistoryID was the wrong event type I'd exclude it. :Headdesk:

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• HanShi

  SSC-Dedicated

  Points: 33506

  More actions

  July 23, 2014 at 3:46 am

  #1731679

  Thanks for providing the excellent sample code. 😀

  Unfortunate I can't reproduce your problem. When I execute the code it is displaying all the correct minimum values in field ed.DateTimeProcessed. I ran the code on SQL2008 R2 SP2...

  EDIT:

  Oh, I see you allready solved it :w00t:

  Well done!

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

• Grant Fritchey

  SSC Guru

  Points: 398694

  More actions

  July 23, 2014 at 5:26 am

  #1731700

  Thanks for posting your solution. Now when anyone searches for this same problem and finds it, they'll know what you did to solve it. That really helps.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Neil Burton

  SSC-Insane

  Points: 22920

  More actions

  July 23, 2014 at 5:28 am

  #1731701

  Grant Fritchey (7/23/2014)

  ---

  Thanks for posting your solution. Now when anyone searches for this same problem and finds it, they'll know what you did to solve it. That really helps.

  If it saves them half the time it took me, they'll get most of a day back! Very annoying when I realised what I'd done.

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537