July 1, 2008 at 8:23 am
I am looking to create an UPDATE statement that changes the value of a column from a select group of rows in a table. The table in question contains records for customers whom are asked specific questions and give specific answers but over time they may change there answer so there can be multiple records for one question for one customer they are differentiated by the Observation Date (OBDATE). My requirement is as follows, find the most recent answer to a specific question for a specific customer and update the OBDATE with a value I select. Now this may be impossible to do this way, but maybe someone can give me some feedback to guide me in the right direction. Here is the SQL:
UPDATE Observe
SET Observe.Ob_Date = '2008-06-30 00:00:00'
WHERE EXISTS
(SELECT MAX(Observe.Ob_Date) AS OBDATE, Quest_Current.Qu_Ref, Quest_Current.Qu_Text, Customer.cu_last, Customer.cu_first
FROM Observe INNER JOIN
Quest_Current ON Observe.Ob_Qu_Recnum = Quest_Current.Qu_Recnum INNER JOIN
Customer ON Observe.Ob_cu_Recnum = Customer.cu_recnum INNER JOIN
CustomerStatus ON Customer.cu_recnum = CustomerStatus.Rs_Cu_Recnum INNER JOIN
CustomerStatusCode ON CustomerStatus.Cs_CsCode_Recnum = CustomerStatusCode.CsCode_Recnum
WHERE (CustomerStatus.Cs_EndDate >= CONVERT(DATETIME, '3000-01-01 00:00:00', 102)) AND (CustomerStatusCode.CsCode_Text = 'Active') AND
(Quest_Current.Qu_Ref >= 'CP') AND (Quest_Current.Qu_Ref < 'CQ')
GROUP BY Quest_Current.Qu_Ref, Quest_Current.Qu_Text, Customer.cu_last, Customer.cu_first)
July 1, 2008 at 8:56 am
try:
UPDATE d SET Observe.Ob_Date = '2008-06-30 00:00:00'
FROM Observe d
INNER JOIN
(SELECT MAX(Observe.Ob_Date) AS OBDATE, Quest_Current.Qu_Ref, Quest_Current.Qu_Text, Customer.cu_last, Customer.cu_first
FROM Observe INNER JOIN
Quest_Current ON Observe.Ob_Qu_Recnum = Quest_Current.Qu_Recnum INNER JOIN
Customer ON Observe.Ob_cu_Recnum = Customer.cu_recnum INNER JOIN
CustomerStatus ON Customer.cu_recnum = CustomerStatus.Rs_Cu_Recnum INNER JOIN
CustomerStatusCode ON CustomerStatus.Cs_CsCode_Recnum = CustomerStatusCode.CsCode_Recnum
WHERE (CustomerStatus.Cs_EndDate >= CONVERT(DATETIME, '3000-01-01 00:00:00', 102))
AND (CustomerStatusCode.CsCode_Text = 'Active')
AND (Quest_Current.Qu_Ref >= 'CP')
AND (Quest_Current.Qu_Ref < 'CQ')
GROUP BY Quest_Current.Qu_Ref, Quest_Current.Qu_Text, Customer.cu_last, Customer.cu_first) s
ON s.Qu_Ref = d.Qu_Ref
AND s.Qu_Text = d.Qu_Text
AND s.cu_last = d.cu_last
AND s.cu_first = d.cu_first
* Noel
July 1, 2008 at 9:10 am
The basic way to do this is to select the customer, the question, and the max date in a derived table or CTE, then join to that on all three fields. That will give you the most recent record.
When you join to that, you can include other columns from the main table/query. It's a way to get around having to group by all the columns.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2008 at 3:18 pm
noel,
Thanks for the reply, I tried your example but it didn't work, possibly because in the ON section of the statment d is referencing columnns that do not exist in the table Observe?
Any ideas?
noeld (7/1/2008)
try:UPDATE d SET Observe.Ob_Date = '2008-06-30 00:00:00'
FROM Observe d
INNER JOIN
(SELECT MAX(Observe.Ob_Date) AS OBDATE, Quest_Current.Qu_Ref, Quest_Current.Qu_Text, Customer.cu_last, Customer.cu_first
FROM Observe INNER JOIN
Quest_Current ON Observe.Ob_Qu_Recnum = Quest_Current.Qu_Recnum INNER JOIN
Customer ON Observe.Ob_cu_Recnum = Customer.cu_recnum INNER JOIN
CustomerStatus ON Customer.cu_recnum = CustomerStatus.Rs_Cu_Recnum INNER JOIN
CustomerStatusCode ON CustomerStatus.Cs_CsCode_Recnum = CustomerStatusCode.CsCode_Recnum
WHERE (CustomerStatus.Cs_EndDate >= CONVERT(DATETIME, '3000-01-01 00:00:00', 102))
AND (CustomerStatusCode.CsCode_Text = 'Active')
AND (Quest_Current.Qu_Ref >= 'CP')
AND (Quest_Current.Qu_Ref < 'CQ')
GROUP BY Quest_Current.Qu_Ref, Quest_Current.Qu_Text, Customer.cu_last, Customer.cu_first) s
ON s.Qu_Ref = d.Qu_Ref
AND s.Qu_Text = d.Qu_Text
AND s.cu_last = d.cu_last
AND s.cu_first = d.cu_first
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply