11/22/2024 8:55:38 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
Most recent history?
Posted: 24 Oct 06 12:35 PM
|
fiogf49gjkf0d Hello,
I hope everyone is doing well.
I would like to know how to create a query that will show me the last time a contact was touched, and show me what that item was - email, telephone call, note, etc.
I am able to get a query where it shows me ALL the history for a contact, but I would like it to show me only the most recent one. That way I can sort by that date, and know who is the oldest, and work forward by date in terms of contact, etc.
It would show something similar to the "SUMMARY" tab on the contact's view.
Please advise, as this has been a major problem for us for a long time.
Thank you |
|
|
|
Re: Most recent history?
Posted: 25 Oct 06 6:42 AM
|
fiogf49gjkf0d You have to create a SELECT statement that returns the historyid of the maximum create date for each contact history. The following SELECT statement will do this.
SELECT h.historyid FROM history h WHERE h.createdate = (SELECT MAX(z.createdate) FROM history z WHERE z.contactid = h.contactid)
You might want to add a time constraint to this because as your database grows, the longer it will take to execute the statement. When I ran it for all history it took SLX about 1 minute to pull back about 28,000 records. I added a time constraint of any history in the past 12 months as the next SELECT statement shows
SELECT h.historyid FROM history h WHERE h.createdate = (SELECT MAX(z.createdate) FROM history z WHERE z.contactid = h.contactid AND z.createdate >= DATEADD(mm, -12, GETDATE()))
To add this in a contact group add a condition for historyid, change the operator to IN and then paste one of the above SELECT statements in the 'Value is:' field and click OK.
John G. |
|
|
| |
|
Re: Most recent history?
Posted: 17 Nov 06 9:43 AM
|
fiogf49gjkf0d Just a small tweak on the query. I had used something similar but had trouble because there were multiple history records with the same createdate thus this would return more than 1 record for some contacts. Most of the time either way would work, because if you are just looking for the date you would get the same result either way. But I think this would restrict to 1 hist rec / contact.
SELECT h.historyid FROM history h WHERE h.historyid = (SELECT TOP 1 historyid FROM history z WHERE z.contactid = h.contactid ORDER BY createdate DESC)
Zeph |
|
|
|
Re: Most recent history?
Posted: 18 Nov 06 8:29 AM
|
fiogf49gjkf0d True. The TOP n clause will work but only for a SQL Server backend. I don't think this will work for Oracle. Not sure if there is a SQL-99 equivalant of the TOP n clause.
John G.
|
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|