Don't shoot the messenger here, but...
The GM ID of the user that created the email address, the date they did such and the time of day they did such is all embedded within the CITY field in the email address's CONTSUPP record.
See if this query helps:
select isnull(contsupref,'')+isnull(address1,'') as 'E-mail Address'
, LEFT(city,8) as 'Who_Created_It'
, SUBSTRING(city,9,8) as 'Date_It_Was_Created'
, SUBSTRING(city,17,7) as 'Time_It_Was_Created'
where rectype = 'P' and U_CONTACT = 'E-MAIL ADDRESS'
order by isnull(contsupref,'')+isnull(address1,'')
Thank you for your help John. That's close enough to work with. Not being a SQL great, how would I have it pull the company name with that info?
Here is an example that includes not only the company name, but also the primary contact's name, city, state and zip. Also, just for another good example, I show how to only include ONLY email addresses that were added to GM between 2 dates (I use 1/1//2019 and 2/4/2019 as the date range in this example):
, isnull(contsupp.contsupref,'')+isnull(contsupp.address1,'') as 'E-mail Address'
, LEFT(contsupp.city,8) as 'Who_Created_It'
, SUBSTRING(contsupp.city,9,8) as 'Date_It_Was_Created'
, SUBSTRING(contsupp.city,17,7) as 'Time_It_Was_Created'
left join CONTACT1 on (CONTSUPP.ACCOUNTNO = CONTACT1.ACCOUNTNO)
where contsupp.rectype = 'P' and contsupp.U_CONTACT = 'E-MAIL ADDRESS'
AND cast(SUBSTRING(contsupp.city,9,8) as datetime) between '1/1/2019' and '2/4/2019'
order by isnull(contsupp.contsupref,'')+isnull(contsupp.address1,'')
Hope this feedback helps. Please let me know if I need to clarify anything.
Thank you so much for your help John Neighbors. That was exactly what I needed. Would have never found it. You are a rock star.
Glad it was helpful.