updating multiple dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

updating multiple dates

Author

Topic

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2011-03-14 : 23:21:54

I have a price table with the following layout:


ArtNo	StartDate	EndDate		Price
-----   ----------      ----------      ------
123	2010-12-14			208.75
123	2011-01-04			211.68
123	2011-02-08			212.80
123	2011-01-04			211.98
234	2010-10-15			208.75
234	2011-01-14			211.68
234	2011-03-12			212.80
543	2011-01-08			 21.18
543	2011-02-01			 22.36

I need to update each row for every ArtNo with an End Date that is the day prior to the next StartDate with the exception of the latest Start Date which needs to be a NULL value.

So after the Update the table should look like this:


ArtNo	StartDate	EndDate		Price
-----   ----------      ----------      ------
123	2010-12-14	2011-01-03	208.75
123	2011-01-04	2011-02-07	211.68
123	2011-02-08	2011-03-01	212.80
123	2011-03-02			211.98
234	2010-10-15	2011-01-13	168.75
234	2011-01-14	2011-03-11	171.68
234	2011-03-12			170.80
543	2011-01-08	2011-01-31	 21.18
543	2011-02-01			 22.36

Any direction that you can give me would be helpful.Thanks. hf

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-15 : 01:16:27

One way:
Create table #Test
(
ArtNo int,
StartDate datetime,
EndDate datetime,
Price numeric(9,2))

Insert into #Test
Select 123 ,'2010-12-14',null, 208.75 Union all
Select 123 ,'2011-01-04',null, 211.68 Union all
Select 123 ,'2011-02-08',null, 212.80 Union all
Select 123 ,'2011-03-04',null, 211.98 Union all
Select 234 ,'2010-10-15',null, 208.75 Union all
Select 234 ,'2011-01-14',null, 211.68 Union all
Select 234 ,'2011-03-12',null, 212.80 Union all
Select 543 ,'2011-01-08',null, 21.18 Union all
Select 543 ,'2011-02-01',null, 22.36

Update T
Set EndDate = Dateadd(day , -1, ST.StartDate)
From
(Select Row_number() over (Partition by ArtNo Order By StartDate) Srno , * from #Test ) as T
inner join
(Select Row_number() over (Partition by ArtNo Order By StartDate) Srno, * from #Test ) as ST
On T.Srno + 1 = ST.Srno and T.ArtNo = ST.Artno

Select * from #Test order by ArtNO,StartDate

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2011-03-15 : 14:48:04

Thank you very much - that works perfectly.
I have a similar scenario for a table that contains cost:


ArtNo	SuppNo	StartDate	EndDate		Price
-----   ------  ----------      ----------      ------
123	abc	2010-12-14			208.75
123	abc	2011-01-04			211.68
123	bcd	2011-02-08			212.80
123	bcd	2011-01-04			211.98
234	gfe	2010-10-15			208.75
234	gfe	2011-01-14			211.68
234	qaz     2011-03-12			212.80
543	wsc     2011-01-08			 21.18
543	edc     2011-02-01			 22.36

Here I also need to update the End Dates with the exception of the row with the latest StartDate but for the combination of ArtNo and SuppNo. It is possible that I get the same ArtNo from multiple suppliers and they will have their respective Start and End Dates.
So the expected result would be:


ArtNo	SuppNo	StartDate	EndDate		Cost
-----   ------  ----------      ----------      ------
123	abc	2010-12-14	2011-01-04       88.75
123	abc	2011-01-04			 91.68
123	bcd	2011-02-08	2011-03-03	 92.80
123	bcd	2011-03-04			 91.98
234	gfe	2010-10-15	2011-01-13	 78.75
234	gfe	2011-01-14			 81.68
234	qaz     2011-03-12			 82.80
543	wsc     2011-01-08			 11.18
543	edc     2011-02-01			 13.36

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-15 : 16:06:17

quote:
Originally posted by HenryFulmer

Thank you very much - that works perfectly.

You are welcome

quote:

Here I also need to update the End Dates with the exception of the row with the latest StartDate but for the combination of ArtNo and SuppNo. It is possible that I get the same ArtNo from multiple suppliers and they will have their respective Start and End Dates.
So the expected result would be:

Please give some try.
Hint: (Partition by ArtNo,SuppNo order By StartDate)
If you don't get expected result then post what you tried and either me or somebody in the forum will help you out.

I can give you answer but that way you will not learn.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2011-03-15 : 16:19:58

@pk_bohra: thanks for your assistance!
Here is what I tried and it appears to work. Is this the correct way to do it?


UPDATE t1
SET EndDate = Dateadd(day , -1, t2.StartDate)
FROM
(SELECT Row_number() over (PARTITION BY ArtNo,SuppNo ORDER BY StartDate) Srno , * FROM #Teste ) AS t1
INNER JOIN 
(SELECT Row_number() over (PARTITION BY ArtNo,SuppNo ORDER By StartDate) Srno, * FROM #Teste ) AS t2
ON t1.Srno + 1 = t2.Srno and (t1.ArtNo = t2.Artno AND t1.SuppNo = t2.SuppNo)

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-15 : 17:16:29

quote:
Originally posted by HenryFulmer

@pk_bohra: thanks for your assistance!
Here is what I tried and it appears to work. Is this the correct way to do it?

UPDATE t1
SET EndDate = Dateadd(day , -1, t2.StartDate)
FROM
(SELECT Row_number() over (PARTITION BY ArtNo,SuppNo ORDER BY StartDate) Srno , * FROM #Teste ) AS t1
INNER JOIN
(SELECT Row_number() over (PARTITION BY ArtNo,SuppNo ORDER By StartDate) Srno, * FROM #Teste ) AS t2
ON t1.Srno + 1 = t2.Srno and (t1.ArtNo = t2.Artno AND t1.SuppNo = t2.SuppNo)

Really? That's updateable?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2011-03-15 : 18:16:24

Originally posted by X002548

quote:

Really? That's updateable?

Why? Am I doing something incorrectly?


Create table #Teste
(
ArtNo int,
SuppNo char(10),
StartDate datetime,
EndDate datetime,
Cost numeric(9,2))

Insert into #Tester
Select 123,'abc'	,'2010-12-14',null,	 208.75 Union all
Select 123,'abc'	,'2011-01-04',null,	 211.68 Union all
Select 123,'bcd'	,'2011-02-08',null,	 212.80 Union all
Select 123,'bcd'	,'2011-03-04',null,	 211.98 Union all
Select 234,'abc'	,'2010-10-15',null,	 208.75 Union all
Select 234,'abc'	,'2011-01-14',null,	 211.68 Union all
Select 234,'qaz'	,'2011-03-12',null,	 212.80 Union all
Select 543,'efb'	,'2011-01-08',null,	 21.18 Union all
Select 543,'ert'	,'2011-02-01',null,	 22.36

UPDATE t1
SET EndDate = Dateadd(day , -1, t2.StartDate)
FROM
(SELECT Row_number() over (PARTITION BY ArtNo,SuppNo ORDER BY StartDate) Srno , * FROM #Teste ) AS t1
INNER JOIN 
(SELECT Row_number() over (PARTITION BY ArtNo,SuppNo ORDER By StartDate) Srno, * FROM #Teste ) AS t2
ON t1.Srno + 1 = t2.Srno and (t1.ArtNo = t2.Artno AND t1.SuppNo = t2.SuppNo)

SELECT * FROM #Teste

My result set looks like this:


ArtNo	SuppNo	        StartDate	EndDate	       Price
123	abc       	2010-12-14 	2011-01-03 	208.75
123	abc       	2011-01-04 	NULL	        211.68
123	bcd       	2011-02-08 	2011-03-03 	212.80
123	bcd       	2011-03-04 	NULL	        211.98
234	abc       	2010-10-15 	2011-01-13 	208.75
234	abc       	2011-01-14 	NULL	        211.68
234	qaz       	2011-03-12 	NULL	        212.80
543	efb       	2011-01-08 	NULL	         21.18
543	ert       	2011-02-01 	NULL	         22.36

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-16 : 04:44:42

quote:
Originally posted by X002548

quote:
Originally posted by HenryFulmer

@pk_bohra: thanks for your assistance!
Here is what I tried and it appears to work. Is this the correct way to do it?

UPDATE t1
SET EndDate = Dateadd(day , -1, t2.StartDate)
FROM
(SELECT Row_number() over (PARTITION BY ArtNo,SuppNo ORDER BY StartDate) Srno , * FROM #Teste ) AS t1
INNER JOIN
(SELECT Row_number() over (PARTITION BY ArtNo,SuppNo ORDER By StartDate) Srno, * FROM #Teste ) AS t2
ON t1.Srno + 1 = t2.Srno and (t1.ArtNo = t2.Artno AND t1.SuppNo = t2.SuppNo)

Really? That's updateable?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Are you still working in version 2000?

Madhivanan

Failing to plan is Planning to fail

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2011-03-16 : 07:21:01

I'm working in 2008 R2.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources