Complicated UPDATE Statement

[lesleycampbell]

I'm using SQL Server 2008. I'm trying to update a temp table with values dependent on the max occurrence of another column. Please be patient with me in my explanation. Thank you!

Temp Table - #APPTRACKER
Columns - Reason, Remarks

Table - History
Columns - HistoryID, Date, Comment, Event

In the previous UPDATE the 'Reason' column only pulls data when the 'Event' is in ('a','b','c').

In this UPDATE, if the 'Reason' column is null, I want to pull the data from the 'Comment' column from the MAX date entry when the 'Event' is in 'RCT'.

If the 'Reason' column is not null, I want to pull the data from the 'Comment' column of the same history ID from the previous UPDATE.

If there is no historyID with the event of 'a','b','c','d', or 'RCT', then I want to leave the field blank.

 

[johnherman]

It looks like you've just about written the code already. I think this will work in Microsoft/TSQL. It can be done with 2 update statements which makes things clearer.

UPDATE Temp Table SET Reason =
(SELECT Comment FROM History WHERE Event IN ('RCT') AND HistoryID =
(SELECT TOP 1( HistoryID from History ORDER BY Date DESC)));

I am not sure how you identify the "previous update", presumably by some check on the date other than MAX ???

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile big data clouds)

 

[lesleycampbell]

Thank you! This is what I have and it is trying to return more than one value. Any suggestions?

UPDATE #APPTRACKERLC
SET Remarks = (SELECT HIST.Comment FROM HISTORY WHERE HIST.Event IN ('AC','CTN','I1','I2','OA',
'OC','OD','OE','OO','RCT') AND HIST.History_ID = (SELECT TOP 1 HIST.History_ID FROM HISTORY
WHERE ATF.EmployeeID = HIST.Employee_ID ORDER BY Actual_DateTime DESC))
FROM #APPTRACKERLC AS ATF, HISTORY AS HIST
WHERE ATF.OrderID = HIST.Order_ID

 

[johnherman]

Sorry. I have been away. Did you resolve this?

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile big data clouds)

 

[philhege]

SET Remarks = (SELECT HIST.Comment FROM HISTORY WHERE HIST.Event IN ('AC','CTN','I1','I2','OA',
'OC','OD','OE','OO','RCT') AND HIST.History_ID = (SELECT TOP 1 HIST.History_ID FROM HISTORY
WHERE ATF.EmployeeID = HIST.Employee_ID ORDER BY Actual_DateTime DESC))
FROM #APPTRACKERLC AS ATF, HISTORY AS HIST
WHERE ATF.OrderID = HIST.Order_ID

The first parenthetical SELECT will return a record for every event found in that list.

I think you need to move your parentheses around.

http://www.tek-tips.com/viewthread.cfm?qid=1749240

-----------
With business clients like mine, you'd be better off herding cats.