Date column update as per other column

Question

Hi, I want to update date2 column on the condition of month_no column. month_no is changing after every 1 and 2 rows and if 3 comes after 1 and 2 then month will be same. Please see below. Help on this please.

create table #T1 (p1 char(2), pd char(4), date1 datetime, month_no int, date2 datetime)

insert into #T1 values ('A','12','2012-01-01',1,null)  --date2 will be '2012-01-01'
insert into #T1 values ('A','13','2012-01-10',2,null)  --date2 will be '2012-01-01'
insert into #T1 values ('A','14','2012-01-31',1,null)  --date2 will be '2012-02-01'
insert into #T1 values ('A','15','2012-02-15',2,null)  --date2 will be '2012-02-01'
insert into #T1 values ('A','16','2012-02-28',1,null)  --date2 will be '2012-03-01'
insert into #T1 values ('A','17','2012-03-10',2,null)  --date2 will be '2012-03-01'
insert into #T1 values ('A','18','2012-03-30',3,null)  --date2 will be '2012-03-01'  --this is same because if it comes after 1 and 2.
insert into #T1 values ('A','19','2012-04-10',1,null)  --date2 will be '2012-04-01'
insert into #T1 values ('A','20','2012-04-20',2,null)  --date2 will be '2012-04-01'

insert into #T1 values ('A','21','2012-05-01',1,null)  --date2 will be '2012-05-01'
insert into #T1 values ('A','22','2012-05-10',2,null)  --date2 will be '2012-05-01'
insert into #T1 values ('A','23','2012-05-30',3,null)  --date2 will be '2012-05-01'  --this is

same because if it comes after 1 and 2.

Select month_no int, date2 datetime from #T1

--Exptected results

month_no     date2

1                  2012-01-01

2                  2012-01-01

1                  2012-02-01

2                  2012-02-01

1                  2012-03-01

2                  2012-03-01

3                  2012-03-01

1                  2012-04-01

2                  2012-04-01

1                  2012-05-01

2                  2012-05-01

3                  2012-05-01

Answer 1

I think you need to add somehow a groupid to the table and then based on this update the date2

month,groupod

1          1  --Jan

2          1  --Jan

1          2--Feb

2          2---Mar

1          3--Mar

2          3---Mar

3          3--Mar

...............

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Hi Sultana,

I've managed to do it using the following:

create table #T1 (p1 char(2), pd char(4), date1 datetime, month_no int, date2 datetime)

insert into #T1 values ('A','12','2012-01-01',1,null)  --date2 will be '2012-01-01'
insert into #T1 values ('A','13','2012-01-10',2,null)  --date2 will be '2012-01-01'
insert into #T1 values ('A','14','2012-01-31',1,null)  --date2 will be '2012-02-01'
insert into #T1 values ('A','15','2012-02-15',2,null)  --date2 will be '2012-02-01'
insert into #T1 values ('A','16','2012-02-28',1,null)  --date2 will be '2012-03-01'
insert into #T1 values ('A','17','2012-03-10',2,null)  --date2 will be '2012-03-01'
insert into #T1 values ('A','18','2012-03-30',3,null)  --date2 will be '2012-03-01'  --this is same because if it comes after 1 and 2.
insert into #T1 values ('A','19','2012-04-10',1,null)  --date2 will be '2012-04-01'
insert into #T1 values ('A','20','2012-04-20',2,null)  --date2 will be '2012-04-01'
insert into #T1 values ('A','21','2012-05-01',1,null)  --date2 will be '2012-05-01'
insert into #T1 values ('A','22','2012-05-10',2,null)  --date2 will be '2012-05-01'
insert into #T1 values ('A','23','2012-05-30',3,null)  --date2 will be '2012-05-01'  --this is

;WITH CTE AS (

SELECT DATEADD(M, DENSE_RANK() OVER(ORDER BY (pd - month_no))-1, '2012-01-01') [date2update],
	   pd
FROM #T1

)

UPDATE #T1
SET date2 = C.date2update
FROM #T1 T
	 JOIN CTE C ON C.pd = T.pd

Select * from #T1

DROP TABLE #T1

Hope that helps...

---

Zach Stagers - http://www.scratchbox.co.uk

Answer 3

Thank you so much.