Update just the newest transaction

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

Update just the newest transaction

Author

Topic

amarundo
Starting Member

9 Posts

Posted - 2012-02-23 : 14:19:46

Hi,

I have a table with a list of transactions per customer with a code for the type of transaction. Each customer may have one or more transactions. To over simplify let's says the table is called HISTORY and these are the columns:
CUSTOMERID
TRANSACTIONID
TRANSACTIONCODE
TRANSACTIONDATETIME

What I want to do is to change the transaction code for the newest transaction for a certain code.

In English: for every CUSTOMERID that has as one of their transactions a TRANSACTIONCODE of 'Complain', update the TRANSACTIONCODE for the newest TRANSACTIONDATETIME to 'Resolved'

I hope it's clear.

Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-23 : 14:41:26

I guess the Transactionid is different for EVERY row grouped by Customerid...it would be easier with DDL and sample data.

update H1
set Transactioncode='Resolved'
from History H1
where H1.Transactiondatetime = (select max(Transactiondatetime) from History H2 where H2.Customerid = H1.Customerid)
and exists(select * from History H3 where H3.Customerid = H1.Customerid and H3.Transactioncode='Complain')

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-23 : 14:43:02

otherwise...maybe this

update H1
set Transactioncode='Resolved'
from History H1
where H1.Transactiondatetime = (select max(Transactiondatetime) from History H2 where H2.Customerid = H1.Customerid and H2.Transactionid = H1.Transactionid)
and exists(select * from History H3 where H3.Customerid = H1.Customerid and H3.Transactionid = H1.Transactionid and H3.Transactioncode='Complain')

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 14:44:02

[code]
UPDATE t
SET t.TRANSACTIONCODE ='Resolved'
FROM HISTORY t
INNER JOIN (SELECT CUSTOMERID,MAX(TRANSACTIONDATETIME) AS latest
FROM HISTORY
GROUP BY CUSTOMERID
HAVING SUM(CASE WHEN TRANSACTIONCODE ='Complain' THEN 1 ELSE 0 END) >0
)t1
ON t1.Latest= t.TRANSACTIONDATETIME
AND t1.CUSTOMERID = t.CUSTOMERID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources