Find the min and max effective date for when a value changed

• Tom_Hogan

  Hall of Fame

  Points: 3874

  More actions

  August 6, 2010 at 2:07 pm

  #224387

  We have a table that stores all changes to a table based on some triggers. We're looking to do some reporting on when a specific value changes. I'd like to store only the changes and their effective dates is another table for ease of reporting. I'd like to take input like this

  -- create temp table to hold results

  IF object_id ( 'tempdb..#contract_history', 'u' ) IS NOT NULL

  DROP TABLE #contract_history

  CREATE TABLE #contract_history (

  contract_name VARCHAR(25) NOT NULL

  , contract_type CHAR(3) NULL

  , change_start_time DATETIME

  , change_end_time DATETIME

  )

  INSERT INTO #contract_history (

  contract_name

  , contract_type

  , change_start_time

  , change_end_time

  )

  SELECT 'CONTRACT1', '', '2008-08-29 23:02:43.860', '2008-10-10 22:36:23.527'

  UNION ALL

  SELECT 'CONTRACT1', 'A', '2008-10-10 22:36:24.603', '2008-11-13 22:34:44.050'

  UNION ALL

  SELECT 'CONTRACT1', 'M', '2008-11-13 22:34:44.100', '2008-11-26 22:39:46.770'

  UNION ALL

  SELECT 'CONTRACT1', 'M', '2008-11-26 22:39:46.960', '2009-03-19 08:58:40.910'

  UNION ALL

  SELECT 'CONTRACT1', 'M', '2009-03-19 08:58:43.253', '2009-03-19 13:13:29.890'

  UNION ALL

  SELECT 'CONTRACT1', 'M', '2009-03-19 13:13:32.297' ,'2009-03-30 22:50:58.050'

  UNION ALL

  SELECT 'CONTRACT1', 'M', '2009-03-30 22:50:58.283', '2009-09-11 06:17:36.543'

  UNION ALL

  SELECT 'CONTRACT1', 'A', '2009-09-11 06:17:38.230', '2009-09-11 22:18:47.087'

  UNION ALL

  SELECT 'CONTRACT1', 'M', '2009-09-11 22:18:48.367', '2009-10-28 22:43:25.760'

  UNION ALL

  SELECT 'CONTRACT1', 'M', '2009-10-28 22:43:26.713', '2010-04-28 23:09:02.077'

  UNION ALL

  SELECT 'CONTRACT1', 'M', '2010-04-28 23:09:03.060', NULL

  and get this output:

  CONTRACT1 2008-08-29 23:02:43.8602008-10-10 22:36:23.527

  CONTRACT1A 2008-10-10 22:36:24.6032008-11-13 22:34:44.050

  CONTRACT1M 2008-11-13 22:34:44.1002009-09-11 06:17:36.543

  CONTRACT1A 2009-09-11 06:17:38.2302009-09-25 22:18:47.087

  CONTRACT1M 2009-09-25 22:18:48.367NULL

  Any ideas of how I can get this without using a cursor? I've played around with ranking and max dates and so far I'm stumped.

  Thanks.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  August 6, 2010 at 4:25 pm

  #1203815

  Would something along the following lines help you? (the "trick" is to build subgroups by contract_type as shown with the subgrp aliased column)

  ;

  WITH cte AS

  (

  SELECT

  *,

  ROW_NUMBER() OVER(ORDER BY contract_name,change_start_time ) -

  ROW_NUMBER() OVER(PARTITION BY contract_name,contract_type ORDER BY change_start_time ) AS subgrp

  FROM #contract_history

  )

  SELECT

  contract_name,

  contract_type,

  MIN(change_start_time),

  CASE WHEN MAX(ISNULL(change_end_time,'29991231'))<'29991231' THEN MAX(ISNULL(change_end_time,'29991231')) ELSE NULL END

  FROM cte

  GROUP BY contract_name,contract_type,subgrp

  ORDER BY contract_name,MIN(change_start_time)

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Tom_Hogan

  Hall of Fame

  Points: 3874

  More actions

  August 9, 2010 at 7:01 am

  #1204136

  This works great. Thanks a lot.

  The "trick" was what had me stumped.