Find the earliest datetime entry & then update

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

Find the earliest datetime entry & then update

Author

Topic

colinw
Starting Member

8 Posts

Posted - 2007-03-12 : 13:21:11

Finding the earliest datetime entry, and then updating the database on these reults.

I need to query a table of data that has multiple datetime entries in it relating to individual customer records. So one customer could have 1 entry, or it could have 10 entries. I need to be able to identify the earliest of these records, and then on this earliest record update a field value. Example:

My Fields in the table are as follows.
Log_ID, Cust_Ref_No, ActionDateTime, Action_Code

The Log_ID is the primary key, and I need to update the Action_Code field on only the earliest entry against a customer record i.e.

12345,ABCDEF,01/01/2007 00:00:01.000,6
12346,ABCDEF,01/01/2007 09:00:00.000,6
12347,ABCDEF,01/01/2007 17:00:00.000,2

In the above I need to change the first record Action_Code from a 6 to a 1, but leave all other records unaffected.

All help greatly appreciated, Thanks : o )

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 13:31:56

This should do it

UPDATE yourtable
SET Action_Code = 1
FROM yourtable
INNER JOIN (SELECT Log_ID, min(ActionDateTime) AS ActionDateTime
    FROM yourtable) a ON yourtable.Log_ID = a.Log_ID AND yourtable.ActionDateTime = a.ActionDateTime
WHERE yourtable.Action_Code = 6

colinw
Starting Member

8 Posts

Posted - 2007-03-12 : 14:05:16

I'm getting the following error when running this:
Column 'Report_Action_Log.LOG_ID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Any ideas?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 14:07:04

Sorry, forgot the GROUP BY

UPDATE yourtable
SET Action_Code = 1
FROM yourtable
INNER JOIN (SELECT Log_ID, min(ActionDateTime) AS ActionDateTime
    FROM yourtable
    GROUP BY Log_ID) a ON yourtable.Log_ID = a.Log_ID AND yourtable.ActionDateTime = a.ActionDateTime
WHERE yourtable.Action_Code = 6

colinw
Starting Member

8 Posts

Posted - 2007-03-12 : 14:34:52

Fantastic - this seems to work - i am so appriciative, unfortunatly i need to expand on this a bit (sorry - i know you must curse me). The updates are also dependent on a set of other critera in another table. Below is the full script I have written based on your model:

UPDATE Report_Action_Log
SET Report_Action_Log.ActionType = 1
FROM Report_Action_Log
INNER JOIN (SELECT Action_Log_ID, MIN(ActionDate) AS ActionDate
FROM Report_Action_Log
GROUP BY Action_Log_ID) a ON Report_Action_Log.Action_Log_ID = a.Action_Log_ID
AND Report_Action_Log.ActionDate = a.ActionDate
INNER JOIN customer_policy_details
ON Report_Action_Log.policynumber = Customer_Policy_Details.policynumber
WHERE Customer_Policy_Details.policy_status_id = '3ajpul67'
AND Customer_Policy_Details.live = 1
AND Report_Action_Log.policynumber like '%mc0%'
AND Report_Action_Log.ActionType = 6

I think this does not work correctly as the second set of linkage is not relating to the first set, as i only got 3 results when run, and i would have expected a few hundred.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 14:45:59

Have you already run the query once and updated the data so that now there are almost no first rows with their Action_Code still equal to 6?

colinw
Starting Member

8 Posts

Posted - 2007-03-12 : 14:51:16

No, this was the results of the first run. I have some sample data records i am using to check the results against, and these have not updated after the script ran, and still show a code 6 against the earliest entry.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 14:52:51

You'll need to give some example data of rows that are not being updated. Be sure to include the sample rows from Customer_Policy_Details too.

colinw
Starting Member

8 Posts

Posted - 2007-03-12 : 15:01:20

Report_Action_Log Table
Fields:
Action_Log_ID,POlicy_Details_ID, History_ID, PolicyNumber, PolicyType, UserName, ActionDate, ActionType

A908F0F3A11F415BBC94EE3F88711B65
90BC016293E841A187E25373D7BA4995 1 STTY15MC01 25 afgadmin 2006-01-04 13:58:22.000 6
9150A6AE63C94322B143BCD882C18085
90BC016293E841A187E25373D7BA4995 1 STTY15MC01 25 ksg 2006-01-04 11:51:22.000 6
80CD91D7ECCB4A5AA1CE834C482DC1D7
90BC016293E841A187E25373D7BA4995 1 STTY15MC01 25 trc 2006-01-04 11:55:45.000 6

Customer_Policy_Details Table
Fields:
POlicy_Details_ID, History_id, Live, Policy_Status_ID, PolicyNumber
90BC016293E841A187E25373D7BA4995 1 1 3AJPUL77 STTY15MC01

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 15:20:26

Your example has a Policy_Status_ID of 3AJPUL77, and the query you gave before is using 3AJPUL67, if I change that to 3AJPUL77, then all the rows match.

colinw
Starting Member

8 Posts

Posted - 2007-03-13 : 05:49:26

You've got better eyes than me - here's me doubting the script I’d written, and it's just down to me not checking correct data. The script works perfectly - thanks for all your help - your skill and patients with me has been greatly appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources