Log in Register FAQ Memberlist Search ContactReview Forum Index
A GoldMine Discussion and Support Community

ContactReview Forum Index » GoldMine 8 - 2019 (Premium Edition) » SQL Query - all contacts where last history is by PAUL
Post new topic  Reply to topic View previous topic :: View next topic 
SQL Query - all contacts where last history is by PAUL
PostPosted: Sat Jun 20, 2015 11:45 am Points: 0 Reply with quote
Paul Laufer
GoldBox Guru
Joined: 27 Oct 2008
Posts: 125




Having a brain cramp on this one for some reason. Looking to write a WHERE portion for a SQL query to find all contacts where the newest history record on the contact is by a particular user... say, PAUL.

Anyone done that before?

_________________
Paul Laufer
Telesage Solutions
www.tsage.com
View user's profile Send private message Visit poster's website

PostPosted: Mon Jun 22, 2015 11:07 am Points: 0 Reply with quote
DougCastell
GoldMine Guru
Joined: 15 Jun 2006
Posts: 1639
Location: Los Angeles, CA




here's one saved in my GoldMine that shows the last history item ONLY where it's a CALL in the last 6 months by DOUG. Maybe that'll get you there?

Code:
SELECT
contact1.company ,contact1.contact, ch.resultcode, CONVERT(VARCHAR(10),ch.ondate,101) LcontDate, ch.REF
FROM
contact1, conthist ch (NOLOCK)
WHERE
ch.accountno=contact1.accountno
AND srectype ='C'
AND ch.recid IN
(SELECT TOP 1 recid FROM conthist
WHERE
(accountno=ch.accountno)
AND conthist.ondate between getdate()-180 and getdate()
AND srectype ='C'
AND userid = 'DOUG'
ORDER BY ondate desc, recid DESC)
ORDER BY ch.resultcode desc, LcontDate desc

_________________
Doug Castell

GoldMine Sales and Support:
http://www.castellcomputers.com/
office: (310)601-4738
View user's profile Send private message Visit poster's website AIM Address Yahoo Messenger MSN Messenger ICQ Number

PostPosted: Tue Jun 23, 2015 4:50 pm Points: 0 Reply with quote
Paul Laufer
GoldBox Guru
Joined: 27 Oct 2008
Posts: 125




Thanks Doug, looks promising. Will give it a shot and report back.

_________________
Paul Laufer
Telesage Solutions
www.tsage.com
View user's profile Send private message Visit poster's website

PostPosted: Tue Jun 23, 2015 5:13 pm Points: 0 Reply with quote
Paul Laufer
GoldBox Guru
Joined: 27 Oct 2008
Posts: 125




Hey, I think I got it!

Code:
select c1.accountno from CONTACT1 c1 where
(select top 1 userid from CONTHIST ch where ch.ACCOUNTNO = c1.ACCOUNTNO
order by ONDATE desc, ONTIME desc, recid desc) = 'PAUL'

_________________
Paul Laufer
Telesage Solutions
www.tsage.com
View user's profile Send private message Visit poster's website

SQL Query - all contacts where last history is by PAUL
  ContactReview Forum Index » GoldMine 8 - 2019 (Premium Edition)
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
All times are GMT - 7 Hours  
Page 1 of 1  

  
  
 Post new topic  Reply to topic  


Brought to you by Castell Computers, Doug Castell, Admin
RSS Feed
Powered by phpBB © 2001-2004 phpBB Group
Theme created by Vjacheslav Trushkin