update record with fields from next record?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

update record with fields from next record??

Author

Topic

luissimoes
Starting Member

10 Posts

Posted - 2007-11-19 : 13:39:10

Hi,

Suppose my original table has the following fields...

ID, DATE, VALUE

and i want to transform this table to this structure:

ID, INITIAL DATE, ENDING DATE, VALUE

So i need to update my ending dates with the initial date of the next record within the same id...

I have tryied update setting ending date to min(initial date) were a.initial_date > b.initial_date but without success...

Did anyone did this before?

Best Regards,
Luis Simoes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 13:48:21

Post your table DDL, sample data and expected result

KH
[spoiler]Time is always against us[/spoiler]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-19 : 13:48:22

Post some sample data of what it looks like before the update and what it should look like after the update.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

luissimoes
Starting Member

10 Posts

Posted - 2007-11-19 : 14:30:21

Imagine the following fields:

Phase 1:

ID | INIT DATE | END DATE | VALUE
1 | 2005-01-01 | NULL | 1.0
1 | 2007-01-01 | NULL | 2.0

One of the queries must update the end dates acoordingly to get this result:

ID | INIT DATE | END DATE | VALUE
1 | 2005-01-01 | 2007-01-01 | 1.0
1 | 2007-01-01 | 2099-01-01 | 2.0

I did get this query just fine... like this:

update tableName
set END_DATE = (select ISNULL(min(t2.INIT_DATE), '2099-12-31')
from tableName t2
where t2.ID = tableName.ID
and t2.INIT_DATE > tableName.INIT_DATE )
WHERE END_DATE IS NULL

So whats my current problem? Imagine after the expected results i insert a new record in the middle like this:

ID | INIT DATE | END DATE | VALUE
1 | 2005-01-01 | 2007-01-01 | 1.0
1 | 2006-01-01 | NULL | 1.5
1 | 2007-01-01 | 2099-01-01 | 2.0

How can i update the table so it would based on the new inserted record remap dates like this:

ID | INIT DATE | END DATE | VALUE
1 | 2005-01-01 | 2006-01-01 | 1.0
1 | 2006-01-01 | 2007-01-01 | 1.5
1 | 2007-01-01 | 2099-01-01 | 2.0

Remember that i only want to update the fields that are previous to the inserted ones and not the entire table...

Best Regards
Luis Simoes

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-19 : 15:38:19

[code]
update a
set
a.END_DATE = coalesce((select top 1 b.INIT_DATE
from tablename b
where b.ID = a.ID
and b.InitDate > a.InitDate
Order by a.InitDate asc)
,'2099-12-31')
from tableName a
[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-19 : 16:23:40

This should work for ya assuming you only want to update the dates in sequence for matching IDs (notice the same query is shown twice to show it works for both conditions you supplied):

DECLARE @Yak TABLE (ID INT, StartDate DATETIME, EndDate DATETIME, Val NUMERIC(3,1))

INSERT @Yak
SELECT 1, '20050101', NULL, 1.0
UNION ALL SELECT 1, '20070101', NULL, 2.0
UNION ALL SELECT 2, '20010101', NULL, 1.0
UNION ALL SELECT 2, '20020101', NULL, 2.0

SELECT *
FROM @Yak

UPDATE Yak
SET EndDate = COALESCE(
	(
		SELECT MIN(StartDate)
		FROM @Yak AS T
		WHERE T.ID = Yak.ID
		AND T.StartDate > Yak.StartDate
	), '99991231')
FROM @Yak AS Yak

SELECT *
FROM @Yak

INSERT @Yak
SELECT 1, '20060101', NULL,1.5


UPDATE Yak
SET EndDate = COALESCE(
	(
		SELECT MIN(StartDate)
		FROM @Yak AS T
		WHERE T.ID = Yak.ID
		AND T.StartDate > Yak.StartDate
	), '99991231')
FROM @Yak AS Yak

SELECT *
FROM @Yak

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 21:59:12

[code]DECLARE @Sample TABLE (ID int, InitDate datetime, EndDate datetime, Val NUMERIC(3,1))

INSERT @Sample
SELECT 1, '20050101', '20070101', 1.0 UNION ALL
SELECT 1, '20060101', NULL, 1.5 UNION ALL
SELECT 1, '20070101', '20990101', 2.0

;WITH SampleData([ID], InitDate, EndDate, Val, SeqNo) AS
(
SELECT ID, InitDate, EndDate, Val,
seq_no = row_number() OVER (PARTITION BY ID ORDER BY InitDate)
FROM @Sample s
)
UPDATE s
SET EndDate = coalesce(n.InitDate, c.EndDate, '99991231')
FROM @Sample s
INNER JOIN SampleData c
ON s.[ID] = c.[ID]
AND s.InitDate = c.InitDate
LEFT JOIN SampleData n
ON s.[ID] = n.[ID]
AND c.SeqNo = n.SeqNo - 1

SELECT *
FROM @Sample

/*
ID InitDate EndDate Val
----------- ----------- ----------- -----
1 2005-01-01 2006-01-01 1.0
1 2006-01-01 2007-01-01 1.5
1 2007-01-01 2099-01-01 2.0
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

luissimoes
Starting Member

10 Posts

Posted - 2007-11-20 : 06:02:02

Thanks for the answers, but have something in mind...
My table can have billions of rows... i cannot update all records... so i must only update records previous to the ones that are null in the ENDING DATE.

Best Regards

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-20 : 07:50:27

"My table can have billions of rows."
Really? That bad of a design? Perhaps you want a trigger then.
Any row that depends on the next row for data is a poor, poor design.
Unless, of course, this is a one time thing where you are NORMALIZING your data. Then just use one of the solutions provided.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

luissimoes
Starting Member

10 Posts

Posted - 2007-11-20 : 07:58:25

Well it is a complementary table to get some information for a datawarehouse factual table based on the record date... So this is the only way to get values where date is between x and y...

I cant use the above querys to only update the previous records of the ones with null on ending date?

Best Regards

luissimoes
Starting Member

10 Posts

Posted - 2007-11-21 : 06:43:03

No ideas? This is a very important thing for me todo... please give me some directions :)

Regards

cas_o
Posting Yak Master

154 Posts

Posted - 2007-11-21 : 08:37:56

Can't you just add:


WHERE end_date is null

to the end of Vinnie881's code ?

;-]... Quack Waddle

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-21 : 09:16:55

Why do you need to update the END_DATE in the first place ? You can always get END_DATE from query with slight modification on the solution i posted earlier.

DECLARE @Sample TABLE (ID int, InitDate datetime, EndDate datetime, Val NUMERIC(3,1))

INSERT @Sample
SELECT 1, '20050101', '20070101', 1.0 UNION ALL 
SELECT 1, '20060101', NULL, 	  1.5 UNION ALL 
SELECT 1, '20070101', '20990101', 2.0 

;WITH SampleData([ID], InitDate, EndDate, Val, SeqNo) AS
(
	SELECT	ID, InitDate, EndDate, Val,
		seq_no = row_number() OVER (PARTITION BY ID ORDER BY InitDate)
	FROM	@Sample s
)
SELECT	c.[ID], c.InitDate,
	EndDate	= coalesce(n.InitDate, c.EndDate, '99991231'),
	c.Val
FROM	@Sample s
	INNER JOIN SampleData c
	ON 	s.[ID]		= c.[ID]
	AND	s.InitDate	= c.InitDate
	LEFT JOIN SampleData n
	ON	s.[ID]		= n.[ID]
	AND	c.SeqNo		= n.SeqNo - 1

KH
[spoiler]Time is always against us[/spoiler]

luissimoes
Starting Member

10 Posts

Posted - 2007-11-21 : 11:22:26

I dont want to update the rows with end_date = null but the columns previous to the ones with null...

Because i have to update the previous carriage so that it would connect to the one inserted in the middle...

And yes i must materialize this table

Regards

luissimoes
Starting Member

10 Posts

Posted - 2007-11-22 : 07:13:03

Ok i have solved it by using a variable table for the previous and a straight update for the other ones.

Regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-22 : 07:22:19

quote:
i must materialize this table

Then when do you intend to run this UPDATE query ? Once a day ? Every time you have new record inserted or changed ? You do realized that this is not efficient at all even with trigger. Why is it not possible to obtain the END_DATE during a select query ? If you find it is not efficient to do it in SQL, then the more you should do this in your front end application. I believe this can be easily accomplish in whatever front end tool you are using.

KH
[spoiler]Time is always against us[/spoiler]

luissimoes
Starting Member

10 Posts

Posted - 2007-11-22 : 07:30:24

This is a Datawarehouse so we dont change frontends :) And the loading is a daily process.

Best Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources