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

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

iamthenuts
Starting Member

2 Posts

Posted - 2011-02-28 : 07:22:07

I am 5 fields in a table: Docid, Date,SortDate, AttachPID and AttachLvl. My overall obejective is to update the sortdate field with the first date in the date field when the AttachPID changes.

AttachPID AttchLvl SortDate Date Docid
77, 0, null, 01/02/2011
77, 1, null, 01/01/2011
77, 1, null, 01/01/2011
78 0, null, 20/02/2011
78 1, null, 01/01/2011

What I want to end up with is:

77, 0, 01/02/2011, 01/02/2011
77, 1, 01/02/2011, 01/01/2011
77, 1, 01/02/2011, 01/01/2011
78 0, 20/02/2011, 20/02/2011
78 1, 20/02/2011, 01/01/2011

I would be grateful for any help.

Thank you

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-28 : 07:26:48

do you want us to help you in updating the field "SortDate" on the basis of "Date" as per required output format?

Cheers
MIK

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-28 : 07:38:11

A relation (table, view etc) is an unordered set.

Assuming you want the latest date for each AttachPID, then the following will work with SQL2005 and above.


-- *** Test Data ***
CREATE TABLE #t
(
	AttachPID int NOT NULL
	,AttchLvl int NOT NULL
	,SortDate datetime NULL
	,[Date] datetime NOT NULL
)
INSERT INTO #t
SELECT 77, 0, NULL, '20110201'
UNION ALL SELECT 77, 1, NULL, '20110101'
UNION ALL SELECT 77, 1, NULL, '20110101'
UNION ALL SELECT 78, 0, NULL, '20110220'
UNION ALL SELECT 78, 1, NULL, '20110101'
-- *** End Test Data ***

;WITH tMax
AS
(
	SELECT *
		,MAX([Date]) OVER (PARTITION BY AttachPID) AS MaxDate
	FROM #t
)
UPDATE tMax
SET SortDate = MaxDate

-- select * from #t

iamthenuts
Starting Member

2 Posts

Posted - 2011-02-28 : 09:07:43

Sorry I should of been clearer. Yes I want to update each sortdate with the first date when the AttachPID changes. Therefore the sortdates will be all the same for each series of AttachPID. However it will each value will be the first date on the series

So the first date for:AttachPID 77 = 01/02/2011. Their are 3 AttachPID for 77. Therefore, the sort date will be 01/02/2011, 01/02/2011, 01/02/2011. hope this makes sense.

Thank you.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-28 : 09:22:31

quote:
hope this makes sense.

No, it does not.

As previously mentioned, a table is an unordered set.

The only order we have to go on is date.
Do you want the minimum date after AttachPID changes?
(ie For AttachID 78 this would be 01/01/2011 not 20/02/2011)
The previous example gave the maximum date after AttachPID changes.

Do you have an IDENTITY column in your table to order by?
etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources