Goldmine SQL Query help.

[cuetipper]

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.

 

[goner]

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:

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

http://www.castellcomputers.com

 

[cuetipper]

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

 

[goner]

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:

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

http://www.castellcomputers.com

 

[cuetipper]

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'))

 

[goner]

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:

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

http://www.castellcomputers.com

 

[goner]

try this, instead:

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

http://www.castellcomputers.com

 

[cuetipper]

That is awesome.
Thanks.