CODEWITHSUNDEEP
sqlsql-server
Techy
Techy

Reputation: 2654

get the weekly distinct users count within two dates

I have a log table PRTL_UserAccessLog which has columns userID,datetime. I need to get the weekly distinct counts of logged users between two custom dates like the following

from date:01 Dec 2017
todate:31 dec 2017

My week start date should be from sunday. I have created the following query to get the result

    SET DATEFIRST 7
    SELECT DISTINCT 
       'week '+ CAST(DATEPART(WEEK, Datetime)AS NVARCHAR(10)) AS weeknumber,
   --I need to get the distinct count of users within this week as weekloggedcount
    FROM 
        dbo.PRTL_UserAccessLog
    WHERE
        Datetime > '2017-12-01' AND Datetime < '2017-12-31'
        AND usertypeid=1
    ORDER BY
       weeknumber

The result should be like this:

**Weeknumber**   **weeklogcount**
  Week48               10
  Week49               50

Upvotes: 0

Views: 241

Answers (1)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can try this.

SET DATEFIRST 7
SELECT DISTINCT 
   'week '+ CAST(DATEPART(WEEK, Datetime)AS NVARCHAR(10)) AS weeknumber,
    COUNT(DISTINCT userID ) weeklogcount
FROM 
    dbo.PRTL_UserAccessLog
WHERE
    Datetime > '2017-12-01' AND Datetime < '2017-12-31'
    AND usertypeid=1
GROUP BY 'week '+ CAST(DATEPART(WEEK, Datetime)AS NVARCHAR(10))
ORDER BY weeknumber

Upvotes: 2

Related Questions