none
Query user logins using distinct and report back daily, weekly and monthly usage

    Question

  • I have a simple SQL Query, that I'm looking to report back site usage hourly, daily, weekly and monthly.
    I require the query to use Distinct as the nature of the application will create several entries to the table upon a each new login to the site.

    Here is what I have so far:

    SELECT  DISTINCT OPRID,
    CONVERT(varchar(12), LOGINDTTM, 112) 'Date'
    from   PSACCESSLOG_HIST
    where  OPRID NOT IN ('AUTOPROC', 'PSAPPS', 'PHSADBA', 'PTWEBSERVER')
    ORDER BY Date

    This will give the following:

    OPRID     LOGIPADDRESS     LOGINDTTM      LOGOUTDTTM
    dadams     10.1.1.5        20130612        20130612
    jblake     10.1.1.5        20130614        20130614

     

    First I do need to group the data as mentioned above by day. This is what I'm looking for, for this part:

    LOGINDATE-TIME    TOTAL-LOGINS
    20130612           25
    20130613           35
    20130614           45

    Appreciate replies  

    Saturday, July 20, 2013 10:47 PM

Answers

  • Try:

    SELECT
    	CONVERT(varchar(12), LOGINDTTM, 112) AS [LOGINDATE-TIME]
    	,COUNT(DISTINCT OPRID) AS [TOTAL-LOGINS]
    FROM dbo.PSACCESSLOG_HIST
    WHERE OPRID NOT IN ('AUTOPROC', 'PSAPPS', 'PHSADBA', 'PTWEBSERVER')
    GROUP BY CONVERT(varchar(12), LOGINDTTM, 112)
    ORDER BY [LOGINDATE-TIME];


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, July 20, 2013 10:56 PM

All replies

  • Try:

    SELECT
    	CONVERT(varchar(12), LOGINDTTM, 112) AS [LOGINDATE-TIME]
    	,COUNT(DISTINCT OPRID) AS [TOTAL-LOGINS]
    FROM dbo.PSACCESSLOG_HIST
    WHERE OPRID NOT IN ('AUTOPROC', 'PSAPPS', 'PHSADBA', 'PTWEBSERVER')
    GROUP BY CONVERT(varchar(12), LOGINDTTM, 112)
    ORDER BY [LOGINDATE-TIME];


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, July 20, 2013 10:56 PM
  • thanks that did the trick!
    Sunday, July 21, 2013 3:49 PM