get the last active data

• sindhupavani123

  Right there with Babe

  Points: 754

  More actions

  January 25, 2018 at 7:09 am

  #332856

  I’m trying to get the last active data and insert that in the current row of the table. Create table latest_value ( year int, [name] varchar(50), last_active varchar(50) )

  go

  Insert into latest_value Select 2010, 'Avinash', 'active' Union All Select 2011, 'Avinash','active' Union All select 2010,'Rahul','active' Union All Select 2012, 'Avinash','inactive'

  SELECT * FROM latest_value

  year name last_active
  2010 Avinash active
  2011 Avinash active
  2010 Rahul active
  2012 Avinash inactive

  Desired output
  year name last_active Status
  2010 Rahul active 2010
  2012 Avinash inactive 2011
  

  Thanks in advance for the help!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 25, 2018 at 7:28 am

  #1977332

  Row_number partition by name order by year and filter for that = 1 (you'll need a subquery or CTE)

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 25, 2018 at 7:28 am

  #1977333

  sindhupavani123 - Thursday, January 25, 2018 7:09 AM

  I’m trying to get the last active data and insert that in the current row of the table. Create table latest_value ( year int, [name] varchar(50), last_active varchar(50) )

  go

  Insert into latest_value Select 2010, 'Avinash', 'active' Union All Select 2011, 'Avinash','active' Union All select 2010,'Rahul','active' Union All Select 2012, 'Avinash','inactive'

  SELECT * FROM latest_value

  year name last_active
  2010 Avinash active
  2011 Avinash active
  2010 Rahul active
  2012 Avinash inactive

  Desired output
  year name last_active Status
  2010 Rahul active 2010
  2012 Avinash inactive 2011
  

  Thanks in advance for the help!

  Your description doesn't appear to match your expected results - can you clarify?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• sindhupavani123

  Right there with Babe

  Points: 754

  More actions

  January 25, 2018 at 7:38 am

  #1977336

  Sorry
  I’m trying to get the last active year for the individual user and insert that year as a new column (Status) for that particular user.

  Create table latest_value(year int,[name] varchar(50),last_active varchar(50))
go
Insert into latest_valueSelect 2010, 'Avinash', 'active'
Union All
Select 2011, 'Avinash','active'
Union All
select 2010,'Rahul','active'
Union All
Select 2012, 'Avinash','inactive'

  SELECT *FROM latest_value

  year  name   last_active
2010  Avinash  active
2011  Avinash  active
2010  Rahul   active
2012  Avinash  inactive

  Desired output

  year  name   last_active Status
2010  Rahul   active    2010
2012  Avinash  inactive  2011

  I hope this make some sense?

  Thank you

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 25, 2018 at 7:51 am

  #1977341

  If I've seen correctly through your illogical naming convention and the fact that your column headings are in the wrong order in your desired results, this should work.  But only if you've posted on the wrong forum and you're not actually using SQL Server 2008.

  WITH LatestDates AS (
    SELECT
         year
    ,    name
    ,    last_active
    ,    ROW_NUMBER() OVER (PARTITION BY name ORDER BY year DESC) AS YearNo
    ,    ROW_NUMBER() OVER (PARTITION BY name ORDER BY last_active, year DESC) AS StatusNo
    FROM latest_value
    )
, LatestActive AS (
    SELECT
         year
    ,    name
    ,    last_active AS Status
    ,    YearNo
    ,    FIRST_VALUE(year) OVER (PARTITION BY name ORDER BY StatusNo ASC) AS last_active
    FROM LatestDates
    )
SELECT
     year
,    name
,    Status
,    last_active
FROM LatestActive
WHERE YearNo = 1;

  John

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 25, 2018 at 7:55 am

  #1977342

  Or maybe this:

  SELECT lv.*, la.*

  FROM (

  SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY [year] DESC)

  FROM #latest_value

  ) lv

  OUTER APPLY (

  SELECT [Status] = MAX([year])

  FROM #latest_value lvi

  WHERE lvi.name = lv.name AND lvi.last_active = 'active'

  ) la

  WHERE lv.rn = 1

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• sindhupavani123

  Right there with Babe

  Points: 754

  More actions

  January 25, 2018 at 8:38 am

  #1977356

  This worked! Thanks a lot for the help!