×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Goldmine SQL Query help.

Goldmine SQL Query help.

Goldmine SQL Query help.

(OP)
version 8.5.2.8.

I have little experience in SQL.
I am trying to write a query that will allow me to generate a list of any record that has been modified by a specific user in a given time.

The results need to include the custid, the date it was modified, and the user that did so.

My attempts have returned gibberish.

Can this be done?

Thanks for the help
Eds.

RE: Goldmine SQL Query help.

There's a BUILD button/wizard on the SQL query window that might help you get where you're going -- even without SQL knowledge.

Otherwise, something like this might be a good start:

CODE --> SQL

select * from contact1 where lastuser='JOHN' and lastdate>='3/15/2016' 

Adjust the selected fields list, username, date, etc. as needed.

Doug Castell
Castell Computers
www.castellcomputers.com

RE: Goldmine SQL Query help.

(OP)

Thanks for the reply.
Looking at your script I see only one date. I really need to set a range, like oct3-23.

I had already tried the query builder in goldmine, this is the script it gave me:

SELECT NT.* FROM NOTES NT WHERE (NT.USERID = 'goldmine') AND ((dateadd( day, datediff(day, 0, NT.CREATEDDATE), 0) >= '2016-10-03' AND dateadd( day, datediff(day, 0, NT.CREATEDDATE), 0) <= '2016-11-01') OR (dateadd( day, datediff(day, 0, NT.MODIFIEDDATE), 0) >= '2016-10-03' AND dateadd( day, datediff(day, 0, NT.MODIFIEDDATE), 0) <= '2016-11-01'))

I could make that work with several runs on different criteria, the only issue is that it does not return the customer account name or custid. So I have no way of knowing what account it is.These are the only headers it returns




Thanks for your help.
Eds

RE: Goldmine SQL Query help.

Yeah, the downside of the SQL query builder is that it writes somewhat overly-complex code -- sort of how Microsoft Word generates ridiculously complicated HTML. It's functional, but...

Also, it looks like your wizard=built query is on the NOTES table -- probably not the right choice.

Unfortunately, I don't know what your custom 'custid' field name is under the hood, so I can't really add it to the query example here, but you can get a date range like this:

CODE --> SQL

select * from contact1 where lastuser='DOUG' and lastdate between '3/15/2016' and '4/15/2016' 

Finally, if this continues to be a problem, maybe a quick remote session could help you get what you need here. I do offer consulting services and work with clients around the world via remote screen sharing tools like Join.Me, Teamviewer, etc.

Doug Castell
Castell Computers
www.castellcomputers.com

RE: Goldmine SQL Query help.

(OP)
I appreciate all your help. Thanks.

Is there anyway to just add the company name or preferably the custid [KEY 5] to the query that goldmine wrote?

SELECT NT.* FROM NOTES NT WHERE (NT.USERID = 'goldmine') AND ((dateadd( day, datediff(day, 0, NT.CREATEDDATE), 0) >= '2016-10-03' AND dateadd( day, datediff(day, 0, NT.CREATEDDATE), 0) <= '2016-11-01') OR (dateadd( day, datediff(day, 0, NT.MODIFIEDDATE), 0) >= '2016-10-03' AND dateadd( day, datediff(day, 0, NT.MODIFIEDDATE), 0) <= '2016-11-01'))

RE: Goldmine SQL Query help.

The query GoldMine wrote is based on the notes in the notes tab. I'm not sure this is your intention as you originally were looking at the last-modified date, right?

You could add those contact fields, however, this way:

CODE --> sql

SELECT c1.company, c1.contact, c1.key5, NT.* FROM NOTES NT, contact1 c1 WHERE nt.accountno=c1.accountno and (NT.USERID = 'goldmine') AND ((dateadd( day, datediff(day, 0, NT.CREATEDDATE), 0) >= '2016-10-03' AND dateadd( day, datediff(day, 0, NT.CREATEDDATE), 0) <= '2016-11-01') OR (dateadd( day, datediff(day, 0, NT.MODIFIEDDATE), 0) >= '2016-10-03' AND dateadd( day, datediff(day, 0, NT.MODIFIEDDATE), 0) <= '2016-11-01')) 

I wouldn't however, approach it this way for multiple reasons.

Doug Castell
Castell Computers
www.castellcomputers.com

RE: Goldmine SQL Query help.

try this, instead:

CODE --> sql

select company, contact, key5, lastuser, lastdate from contact1 where lastuser='DOUG' and lastdate between '3/15/2016' and '4/15/2016' 

Doug Castell
Castell Computers
www.castellcomputers.com

RE: Goldmine SQL Query help.

(OP)
That is awesome.
Thanks.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close