| SQL Query - all contacts where last history is by PAUL | |
Posted: Sat Jun 20, 2015 11:45 am |
Points: 0 |
|
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? |
|
|
|
|
| | |
Posted: Mon Jun 22, 2015 11:07 am |
Points: 0 |
|
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 |
|
|
|
|
|
| | |
Posted: Tue Jun 23, 2015 4:50 pm |
Points: 0 |
|
Paul Laufer |
GoldBox Guru |
|
|
Joined: 27 Oct 2008 |
Posts: 125 |
|
|
|
|
|
|
|
|
Thanks Doug, looks promising. Will give it a shot and report back. |
|
|
|
|
Posted: Tue Jun 23, 2015 5:13 pm |
Points: 0 |
|
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' |
|
|
|
|
|
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
|
|
|
Brought to you by Castell Computers, Doug Castell, Admin
Powered by phpBB © 2001-2004 phpBB Group Theme created by Vjacheslav Trushkin
|