4 Replies Latest reply on Aug 4, 2016 6:25 PM by schorn

    SQL Query

    schorn Rookie

      I need an sql query for GM users login data. I would like to export to excel so time in GM can easily be added unless the query can add time logged in.

        • 1. Re: SQL Query
          John Neighbors Expert

          Thomas,

           

          Know that the information you are looking for is stored in the USERLOG table, at the SQL level.

           

          Also, know that the WAY this data is stored in the USERLOG table can vary, depending on one specific GLOBAL setting in GoldMine.  This GLOBAL setting is the "Time Clock" setting on the Login tab of any GM user's OPTIONS.

           

           

          This query will give you some quick exposure to the data itself:

           

               select * from userlog

           

          Know that you can use the SQL cast() function to make the login and logout columns truly behave like date and time stamps, as illustrated here:

           

               select cast(login as datetime), cast(logout as datetime), * from userlog

           

          The DUR column is the # of minutes for that logged entry (that row of data in the userlog table), which may be for a single login/logout session OR may be totals for a day, depending on the "Time Clock" setting noted above.

           

          Regarding "unless the query can add time logged in"...MOST ASSUREDLY.  You should be able to manipulate as you see fit (with the correctly written SQL query, of course).

           

          Here is a query, just as an example, that totals the minutes for a given date range (using the SQL sum() function), calculates # of hours AND counts the number of logged entries for that same date range, grouping by GoldMine user ID:

           

               select userid, sum(dur) as 'Total Mins', cast(sum(dur)/60.0 as float) as 'Total Hours', count(*) as 'Count' from userlog where cast(login as datetime) between '7/1/2016' and '7/31/2016' group by userid

           

          Hope this feedback helps. Please let me know if I need to clarify anything.

          • 2. Re: SQL Query
            schorn Rookie

            John

             

            Thank you for your response. I have been able to get the data I need with the queries above. If our time clock settings are set to track each login, would the last query above still produce an accurate total?

            • 3. Re: SQL Query
              John Neighbors Expert

              Yes, that query should be accurate, for the date range in question, no matter which Time Clock setting is being used (other than the "Disable Time Clock" setting, of course).

               

              If you wanted DAILY totals for yourself, then you could add the login date to the query and group by clause, like this:

               

                   select userid, cast(login as datetime) as 'Date', sum(dur) as 'Total Mins', cast(sum(dur)/60.0 as float) as 'Total Hours', count(*) as 'Count' from userlog where cast(login as datetime) between '7/1/2016' and '7/31/2016' group by userid, cast(login as datetime)

               

              Glad it was helpful.

              • 4. Re: SQL Query
                schorn Rookie

                This was another really useful query. Thank you John.