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

Transact-SQL (2005)

Query Help

Author

Topic

kalyur
Starting Member

7 Posts

Posted - 2008-12-31 : 15:00:54

I have a situation where the following data is in a table

ID Start Date
1 Dec 30 2008 2:47PM
2 Dec 30 2008 2:49PM
3 Dec 30 2008 2:49PM
4 Dec 30 2008 2:53PM
5 Dec 30 2008 2:58PM
6 Dec 30 2008 2:49PM

What I am trying to do is write a clean up script that checks the last record(ID=6)Start Date and if it's lesser than the previous Start Date (ie ID=5) update record ID = 6 with the previous Start Date(ID=5).

Any help you can provide on this is appreciated.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-12-31 : 15:12:09

This works, but it isn't very slick

DECLARE @Table Table (id int identity(1,1),Startdate datetime)

INSERT INTO @Table

SELECT 'Dec 30 2008 2:47PM' UNION ALL
SELECT 'Dec 30 2008 2:49PM' UNION ALL
SELECT 'Dec 30 2008 2:49PM' UNION ALL
SELECT 'Dec 30 2008 2:53PM' UNION ALL
SELECT 'Dec 30 2008 2:58PM' UNION ALL
SELECT 'Dec 30 2008 2:49PM'

DECLARE @maxId int
DECLARE @maxdate datetime
SET @MaxId = (select max(id) from @table)
SET @Maxdate = (select startdate from @table where id = @maxid-1)

select @maxid,@maxdate
UPDATE @table
SET Startdate =
CASE WHEN @maxdate >= Startdate THEN @maxdate END
WHERE id = @maxid

select * from @table

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 10:27:08

Slicker maybe?

-- Prepare sample data
DECLARE @Sample TABLE
	(
		ID INT,
		Startdate DATETIME
	)

INSERT	@Sample
SELECT	1, 'Dec 30 2008 2:47PM' UNION ALL
SELECT	2, 'Dec 30 2008 2:49PM' UNION ALL
SELECT	3, 'Dec 30 2008 2:49PM' UNION ALL
SELECT	4, 'Dec 30 2008 2:53PM' UNION ALL
SELECT	5, 'Dec 30 2008 2:58PM' UNION ALL
SELECT	6, 'Dec 30 2008 2:49PM' 

-- Display current values
SELECT	*
FROM	@Sample

-- Do the update
UPDATE		s
SET		s.StartDate = d.mSD
FROM		@Sample AS s
INNER JOIN	(
			SELECT TOP 2	MAX(ID) OVER () AS mID,
					MAX(StartDate) OVER () AS mSD
			FROM		@Sample
			ORDER BY	ID DESC
		) AS d ON d.mID = s.ID
WHERE		s.StartDate < d.mSD

-- Display current values
SELECT	*
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 10:32:10

For update of complete table, see this

-- Prepare sample data
DECLARE @Sample TABLE
	(
		ID INT PRIMARY KEY CLUSTERED,
		StartDate DATETIME
	)

INSERT	@Sample
SELECT	1, 'Dec 30 2008 2:47PM' UNION ALL
SELECT	2, 'Dec 30 2008 2:49PM' UNION ALL
SELECT	3, 'Dec 30 2008 2:49PM' UNION ALL
SELECT	4, 'Dec 30 2008 2:53PM' UNION ALL
SELECT	5, 'Dec 30 2008 2:58PM' UNION ALL
SELECT	6, 'Dec 30 2008 2:49PM' UNION ALL
SELECT	7, 'Dec 30 2008 2:49PM' UNION ALL
SELECT	8, 'Dec 31 2008 3:55PM'

-- Display current values
SELECT	*
FROM	@Sample

-- Do the update
DECLARE	@sd DATETIME

UPDATE	@Sample
SET	@sd = StartDate =	CASE
					WHEN StartDate < @sd THEN @sd
					ELSE StartDate
				END

-- Display current values
SELECT	*
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 10:34:28

friends or colleagues?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=117044

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources