Table update problem

• adam-639168

  Default port

  Points: 1444

  More actions

  September 29, 2011 at 4:01 pm

  #248662

  Hello, I have a table with account numbers and startdates. The same account number could have multiple startdates. There is a new column called EndDate that needs to be updated.

  e.g. Account A has 2 startdates (8/1/11 and 9/1/11). Since 9/1/11 start date is the most recent, the assumption is that it is still open so there is no end date. But for the 8/1/11 startdate, the end date should be 9/1/11 (most recent startdate) minus 1 (8/31/11).

  Seeing table data may make my jumbled explanation clearer :w00t:

  "EndDate_Goal" is the date I need in the EndDate column.

  create table #temp (id int, account int,startdate datetime, enddate datetime, enddate_goal datetime)

  insert into #temp values (1,123,'9/22/11',null,null)

  insert into #temp values (22,123,'8/15/11',null,'9/21/11')

  insert into #temp values (33,123,'8/1/11',null,'8/14/11')

  insert into #temp values (476,987,'7/15/11',null,null)

  insert into #temp values (2242,987,'7/1/11',null,'7/14/11')

  Thanks in advance for your help,

  Adam.

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  September 29, 2011 at 4:18 pm

  #1388649

  This may be?

  ; with cte as

  (

  select *

  , rn = ROW_NUMBER() over( partition by account order by startdate desc)

  from #temp

  )

  select outertable.id , outertable.account , outertable.startdate , crsapp.startdate enddate_goal

  from cte outertable

  outer apply

  ( select DATEADD( dd, -1 , innertable.startdate ) startdate

  from cte innertable

  where outertable.account = innertable.account

  and innertable.rn = outertable.rn -1 ) crsapp

• Andre Guerreiro

  SSCertifiable

  Points: 7319

  More actions

  September 29, 2011 at 4:29 pm

  #1388650

  You beat me to it, ColdCoffee.

  Here's my code anyway:

  SELECT t1.startdate, t1.enddate_goal, t2.date_goal

  FROM (SELECT ROW_NUMBER() OVER (PARTITION BY account ORDER BY startdate) AS NId, startdate, enddate_goal, account

  FROM #temp) AS t1

  LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY account ORDER BY startdate)-1 AS NId, account, startdate-1 AS date_goal

  FROM #temp) AS t2 ON t1.NId = t2.NId AND t1.account = t2.account;

  Best regards,

  Best regards,

  Andre Guerreiro Neto

  Database Analyst
  http://www.softplan.com.br
  MCITPx1/MCTSx2/MCSE/MCSA