Hi, Update the target table based on bug date Here is the example Just creating a dummy source table
declare @tblSource table(bugdate datetime)
insert into @tblSource
select '2009-04-12' union all
select '2009-04-19' union all
select '2009-04-26' union all
select '2009-05-03' union all
select '2009-05-10' union all
select '2009-05-17' union all
select '2009-05-24' union all
select '2009-05-31' union all
select ' 2009-06-07'
Your target table schema
create table tblTarget (id int,bugdate date,weekno int,bugno int,startdayofweek datetime)
insert into tblTarget
select 111,'2009-04-12',null,3,null union all
select 111,'2009-04-19',null,1,null union all
select 111,'2009-04-26',null,1,null union all
select 111,'2009-05-03',null,1,null union all
select 111,'2009-05-10',null,9,null union all
select 111,'2009-05-17',null,9,null union all
select 111,'2009-05-24',null,4,null union all
select 111,'2009-05-31',null,2,null union all
select 111,' 2009-06-07',null,4,null
select * from tblTarget
Output (Before updation)
id bugdate weekno bugno startdayofweek
111 2009-04-12 NULL 3 NULL
111 2009-04-19 NULL 1 NULL
111 2009-04-26 NULL 1 NULL
111 2009-05-03 NULL 1 NULL
111 2009-05-10 NULL 9 NULL
111 2009-05-17 NULL 9 NULL
111 2009-05-24 NULL 4 NULL
111 2009-05-31 NULL 2 NULL
111 2009-06-07 NULL 4 NULL
Next execute the query
;with cte as
(
select bugdate,
DATEADD(wk, DATEDIFF(wk, 6,bugdate), 6) as startdayofweek,
Datepart(wk,bugdate) + ((Datepart(year,bugdate) - 2008) * 52) as WeekNumber
from @tblSource
)
update tblTarget
set tblTarget.weekno = c.WeekNumber,tblTarget.startdayofweek = c.startdayofweek
from cte c
where c.bugdate = tblTarget.bugdate
Output(After Updation)
id bugdate weekno bugno startdayofweek
111 2009-04-12 68 3 2009-04-12 00:00:00.000
111 2009-04-19 69 1 2009-04-19 00:00:00.000
111 2009-04-26 70 1 2009-04-26 00:00:00.000
111 2009-05-03 71 1 2009-05-03 00:00:00.000
111 2009-05-10 72 9 2009-05-10 00:00:00.000
111 2009-05-17 73 9 2009-05-17 00:00:00.000
111 2009-05-24 74 4 2009-