Last value entered based on last modified date

• Adelphi

  SSC Journeyman

  Points: 91

  More actions

  September 13, 2013 at 4:49 am

  #277972

  I need to get the last modified value for each project and each owner

  -- SAMPLE DATA

  DECLARE @Audit TABLE (

  [ID] varchar(50),

  [Owner] varchar(50),

  [Modified on] datetime,

  [Value] money

  )

  SET DATEFORMAT dmy

  INSERT INTO @Audit

  SELECT 'ProjA','OwnerA','20/05/2011 17:45','1256'

  UNION ALL SELECT 'ProjA','OwnerA','11/07/2011 16:22','2540'

  UNION ALL SELECT 'ProjB','OwnerB','21/06/2011 09:32','25610'

  UNION ALL SELECT 'ProjB','OwnerB','21/06/2011 09:37','30000'

  UNION ALL SELECT 'ProjB','OwnerB','21/06/2011 09:40','45000';

  -- SOLUTION

  WITH AuditData AS (

  SELECT *,

  RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Modified On] DESC),

  max([Modified on]) OVER(PARTITION BY ID) AS [Max Modified On]

  FROM @Audit

  )

  SELECT [ID], [Owner], [Modified on], [Value], [Max Modified On]

  FROM AuditData AS AD

  I need the following result:

  [ID], [Owner], [Modified on], [Value], [Max Modified On]

  ProjAOwnerA2011-07-11 16:22:00.0002540.002011-07-11 16:22:00.000

  ProjBOwnerB2011-06-21 09:40:00.00045000.002011-06-21 09:40:00.000

  Instead I get the following:

  ProjAOwnerA2011-05-20 17:45:00.0001256.002011-07-11 16:22:00.000

  ProjAOwnerA2011-07-11 16:22:00.0002540.002011-07-11 16:22:00.000

  ProjBOwnerB2011-06-21 09:32:00.00025610.002011-06-21 09:40:00.000

  ProjBOwnerB2011-06-21 09:37:00.00030000.002011-06-21 09:40:00.000

  ProjBOwnerB2011-06-21 09:40:00.00045000.002011-06-21 09:40:00.000

  What am I doing wrong?

  Your help is appreciated.

  Many Thanks

  A

• Koen Verbeeck

  SSC Guru

  Points: 259155

  More actions

  September 13, 2013 at 5:09 am

  #1650015

  WITH AuditData AS (

  SELECT *,

  RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Modified On] DESC),

  max([Modified on]) OVER(PARTITION BY ID) AS [Max Modified On]

  FROM @Audit

  )

  SELECT [ID], [Owner], [Modified on], [Value], [Max Modified On]

  FROM AuditData AS AD

  WHERE RN = 1;

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  September 13, 2013 at 5:09 am

  #1650016

  Or this maybe:

  with MaxDates as (select id, MaxMod = max([Modified on]) from @Audit a group by id)

  select a.id,a.Owner, a.[Modified on],a.Value

  from @Audit a

  join MaxDates m on a.id = m.id and a.[Modified on] = m.MaxMod

  ---