May 7, 2010 at 4:59 am
Guys.. following is the dml script alng with sample data..
if object_id('tempdb..#forecastperiods') is not null
drop table #forecastperiods
if object_id('tempdb..#estprj') is not null
drop table #estprj
if object_id('tempdb..#forecastprj') is not null
drop table #forecastprj
create table #forecastperiods(prjid int,periodid int null)
create table #estprj (prjid int,periodid int)
create table #forecastprj (prjid int,periodid int)
insert into #forecastperiods select 1,10
union all
select 2,null
union all
select 3,22
insert into #estprj
select 1,1
union all
select 1,2
union all
select 1,3
union all
select 2,1
union all
select 2,2
union all
select 2,3
union all
select 3,1
union all
select 3,2
union all
select 3,3
insert into #forecastprj (prjid,periodid) select prjid,periodid from #estprj
each prjid will have 3 sets of rows in forecastprj ..
now i want to udapte periodid in #forecastprj table based onthe value period id from #forecastperiods with the logic that..if period is 10 for prjid =1 then 3 rows in the table should be updated with value 10,11,12 ..
I tried with below update query with tally table..but update doesnot work..is there anything which am overlooking???
update b
set b.periodid = (a.periodid+n-1)
from #forecastprj b
inner join #forecastperiods a
on b.prjid = a.prjid
inner join #estprj c
on c.prjid = a.prjid
cross join tally
where a.periodid is not null
and n in( 1,2,3)
expected out put when i say select * from #forecastprj is
1 10
1 11
1 12
2 1
2 2
2 3
3 22
3 23
3 24
---Thanks in advance..
May 7, 2010 at 5:27 am
When I get an update/insert to work I usually try to select the restults I want first. For your query this line prevents prjid 2 from being updated at all
where a.periodid is not null
so removing that, you then have to deal with a NULL in a.periodid
SELECT DISTINCT b.prjid, COALESCE(a.periodid+n-1, n)
from #forecastprj b
inner join #forecastperiods a
on b.prjid = a.prjid
inner join #estprj c
on c.prjid = a.prjid
cross join tally
where n in( 1,2,3)
having got the query right, proceed to update
UPDATE B
SET b.periodid = COALESCE(a.periodid+n-1, n)
from #forecastprj b
inner join #forecastperiods a
on b.prjid = a.prjid
inner join #estprj c
on c.prjid = a.prjid
cross join tally
where n in( 1,2,3)
Does this give your expected results?
May 7, 2010 at 5:28 am
This should give you the expected output without a Tally table
UPDATE fprj
SET fprj.periodid = ISNULL( fper.periodid, 1 ) + fprj.rownum - 1
FROM (
SELECT ROW_NUMBER() OVER( PARTITION BY prjid ORDER BY periodid ) rownum, periodid, prjid
FROM #forecastprj
) fprj
INNER JOIN #forecastperiods fper ON fprj.prjid = fper.prjid
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 7, 2010 at 6:13 am
Kingston..yours code works like a champion...thanks a lot..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply