September 29, 2011 at 4:01 pm
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.
September 29, 2011 at 4:18 pm
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
September 29, 2011 at 4:29 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply