5 Replies Latest reply on Feb 5, 2019 7:24 AM by John Neighbors

    email addresses by the date they were entered

    JO'Bri Rookie

      Is there a way to grab email addresses by the date they were entered in GMPE 2015.2?

      In the details tab you can see the date entered but I cannot figure out how to pull a report or group based on the entered date of the email address.

      Any help would be greatly appreciated.

        • 1. Re: email addresses by the date they were entered
          John Neighbors SSMMVPGroup

          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'

              , city

              , accountno

              from contsupp

              where rectype = 'P' and U_CONTACT = 'E-MAIL ADDRESS'

              order by isnull(contsupref,'')+isnull(address1,'')

          • 2. Re: email addresses by the date they were entered
            JO'Bri Rookie

            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?

            • 3. Re: email addresses by the date they were entered
              John Neighbors SSMMVPGroup

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

               

              select CONTACT1.COMPANY

                  , CONTACT1.CONTACT

                  , CONTACT1.CITY

                  , CONTACT1.STATE

                  , CONTACT1.ZIP

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

                  , contsupp.city

                  , contsupp.accountno

                  from contsupp

                  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.

              • 4. Re: email addresses by the date they were entered
                JO'Bri Rookie

                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.