Update Sequence - 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 2000 Forums

Transact-SQL (2000)

Update Sequence

Author

Topic

ladiaocb
Yak Posting Veteran

57 Posts

Posted - 2008-08-26 : 12:48:33

Need help with an update query. I have the following table:

tbl1

userid----monid----date-------score
1---------Null-----8/1/2008---100
1---------Null-----8/22/2008--98
1---------Null-----8/15/2008--88
1---------Null-----8/10/2008--90
2---------Null-----8/4/2008---100
2---------Null-----8/25/2008--75
2---------Null-----8/18/2008--99

I am trying to update the "monid" column to reflect the sequence number based on the date. For example:

userid----monid----date-------score
1---------1--------8/1/2008---100
1---------4--------8/22/2008--98
1---------3--------8/15/2008--88
1---------2--------8/10/2008--90
2---------1--------8/4/2008---100
2---------3--------8/25/2008--75
2---------2--------8/18/2008--99

Any help is greatly appreciated!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-26 : 13:16:26

DECLARE @TABLE TABLE (userid int,monid int,aDate datetime,Score int)

INSERT INTO @table
SELECT 1,Null,'8/1/2008',100 UNION ALL
SELECT 1,Null,'8/22/2008',98 UNION ALL
SELECT 1,Null,'8/15/2008',88 UNION ALL
SELECT 1,Null,'8/10/2008',90 UNION ALL
SELECT 2,Null,'8/4/2008',100 UNION ALL
SELECT 2,Null,'8/25/2008',75 UNION ALL
SELECT 2,Null,'8/18/2008',99

SELECT a.userid,a.adate,[MonoID] = count(*)
FROM
@table a
INNER JOIN
@table b
ON
a.userid = b.userid
and a.aDate <= b.aDate
group by a.userid,a.adate

order by 1,3 asc

Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-26 : 13:22:57

DECLARE @TABLE TABLE (userid int,monoid int,aDate datetime,Score int)

INSERT INTO @table
SELECT 1,Null,'8/1/2008',100 UNION ALL
SELECT 1,Null,'8/22/2008',98 UNION ALL
SELECT 1,Null,'8/15/2008',88 UNION ALL
SELECT 1,Null,'8/10/2008',90 UNION ALL
SELECT 2,Null,'8/4/2008',100 UNION ALL
SELECT 2,Null,'8/25/2008',75 UNION ALL
SELECT 2,Null,'8/18/2008',99

UPDATE @TABLE

SET MonoID =
b.MonoID
FROM
@table a
INNER JOIN
(
select a.userid,a.adate,[MonoID] = count(*)
from
@table a
inner join
@table b
on
a.userid = b.userid
and a.aDate <= b.aDate
group by a.userid,a.adate

) b
ON
a.userid = b.userid
and a.aDate = b.aDate

SELECT * FROM @table ORDER BY 1,2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 13:35:20

quote:
Originally posted by ladiaocb

Need help with an update query. I have the following table:

tbl1

userid----monid----date-------score
1---------Null-----8/1/2008---100
1---------Null-----8/22/2008--98
1---------Null-----8/15/2008--88
1---------Null-----8/10/2008--90
2---------Null-----8/4/2008---100
2---------Null-----8/25/2008--75
2---------Null-----8/18/2008--99

I am trying to update the "monid" column to reflect the sequence number based on the date. For example:

userid----monid----date-------score
1---------1--------8/1/2008---100
1---------4--------8/22/2008--98
1---------3--------8/15/2008--88
1---------2--------8/10/2008--90
2---------1--------8/4/2008---100
2---------3--------8/25/2008--75
2---------2--------8/18/2008--99

Any help is greatly appreciated!

Minor variation

UPDATE t
SET t.monid=tmp.Seq
FROM YourTable t
INNER JOIN 
(
SELECT (SELECT COUNT(*) 
        FROM YourTable 
        WHERE userid=a.userid 
        and date<a.date)+1 AS Seq,
        *
FROM YourTable a
)tmp
ON tmp.userid=t.userid
AND tmp.date=t.date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources