Help with Convert and Having/Where Aggregate Query

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)

Help with Convert and Having/Where Aggregate Query

Author

Topic

brickerz
Starting Member

3 Posts

Posted - 2012-05-16 : 13:10:03

I'm trying to use a query to update a row with details that need to be updated and convert.

My thoughts were the following but I couldn't get this or any other iterations to work:

update table
set LastRev = (MAX(RevNum) - RevNum having key = key)
from table;

Current:
RevNum-LastRev--KEY
40-----NULL-----123
41-----NULL-----123
42-----NULL-----123
43-----NULL-----123
44-----NULL-----123
45-----C--------123
Expected:
Rev #--LastRev--KEY
40-----0005-----123
41-----0004-----123
42-----0003-----123
43-----0002-----123
44-----0001-----123
45-----C--------123

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-05-16 : 13:48:49

If revnum is always sequential, this code code be cleaner.


DECLARE @Table TABLE (RevNum int,LastRev varchar(10),[key] int)
INSERT INTO @Table
VALUES
(40,NULL,123),
(41,NULL,123),
(42,NULL,123),
(43,NULL,123),
(44,NULL,123),
(45,'C',123)



UPDATE t2

SET t2.LastRev = CASE WHEN t2.Rown = 0 THEN t2.LastRev ELSE RIGHT('0000' +CONVERT(varchar(10),t2.maxrev-t2.Revnum),4)  END
FROM
(
SELECT t.*
     ,[Rown]=  ROW_NUMBER() OVER(partition by [key]  order by RevNum desc)-1
     ,[MaxRev]    =  MAX(RevNum) OVER(Partition by [key]) 
 FROM @Table t
)t2


SELECT *
FROM @table

Jim

Everyday I learn something that somebody else already knew

brickerz
Starting Member

3 Posts

Posted - 2012-05-16 : 13:57:31

Hi Jim,

Thanks for the reply. I see my question was chopped(mentally or touchpadly) off after I saw your reply indicating to specify values. There are roughly 7051 rows in this table, this was more of a sample of the data.

What I was trying to do is update each row where the revnum is not the max(revnum) for that specific key, and then subtract the revnum of the current line from the max(revnum) for that key and store that number as #### in the LastRev column.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-05-16 : 14:37:36

Doesn't my query produce the results you want?

Jim

Everyday I learn something that somebody else already knew

brickerz
Starting Member

3 Posts

Posted - 2012-05-16 : 14:42:39

quote:
Originally posted by jimf

Doesn't my query produce the results you want?

Yes, will I have to put all of the values in for it to work through a whole table though?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-05-16 : 14:54:20

I took the sample table you provided to make the query. You'll have to change the name of @table to whatever you actual table name is. Actually, I'd populate a temp table (#table) with you original data and update that first, and then if the results are correct run my code against your actual table.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources