locked
Counts per user per event per day RRS feed

  • Question

  • User-2099569815 posted

    Hi there experts,

    I need to count user login logout event per day as well as total event use had per day

    Table data is like this:

    Userid. Username Eventid Typeofevent Date time

    123. Test1. 234. Login. 2/21/2016 06:06:59
    123. Test1. 234. Unk. 2/21/2016 06:07:01
    123. Test1. 234. None. 2/21/2016. 07:08:10
    123. Test1. 234. Logout. 2/21/2016. 07:30:10
    123. Test1. 254. Login. 2/21/2016. 08:10:10
    123. Test1. 254. Logout. 2/21/2016. 10:10:10
    123. Test1. 264. Login. 2/21/2016. 11:00:00
    123. Test1. 264. Mg. 2/21/2016. 11:10:10
    123. Test1. 264. Uc. 2/21/2016. 11:20:20
    123. Test1. 264. Logout. 2/21/2016. 12:30:30

    Next user etc....



    I want the result like this:

    Userid. Username. Totalofevent.                                Date

    123. Test1.                 3 (based on 234,254,264).          2/21/2016
    123. Test1.                 5.                                                   2/22/2016

    123   Test1                    4                                                 2/23/2016
    Etc...

    How can get this result with the dataset I have here?

    Any help is appreciated.

    Thanks

    Tuesday, March 1, 2016 1:40 AM

Answers

  • User-158764254 posted

    The original test data in the opening post is still not quite making sense to me.

    If we used the test data that Weibo provided i'd use a pivot like this:

    SELECT userid, 
           username, 
           eventdate, 
           Sum([login])  AS LoginCount, 
           Sum([logout]) AS LogoutCount, 
           Count(*)      AS TotalEvents 
    FROM   (SELECT userid, 
                   username, 
                   Dateadd(dd, Datediff(dd, 0, happendatetime), 0) AS EventDate, 
                   login, 
                   logout 
            FROM   @tempp 
                   PIVOT ( Count([typeofevent]) 
                         FOR [typeofevent] IN ([Login], 
                                               [Logout]) ) AS pvt) AS data 
    GROUP  BY userid, 
              username, 
              eventdate 

    The output from that query is:

    Userid username EventDate LoginCount LogoutCount TotalEvents
    123 Test1 2/21/16 0:00 3 3 10
    123 Test1 2/23/16 0:00 1 1 2
    124 Test2 2/24/16 0:00 1 1 2

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 5, 2016 12:51 PM

All replies

  • User-219423983 posted

    Hi arial12,

    I have created a demo as below and you could have a look. In the demo, I use “count(*)/2” to get the total login and logout count. Besides, for example, if there are 13 records for the login and logout, it would just return 6 as the Totalofevent. For this or other needs, you could modify it by using case when then according to your need.

    Declare @tempp table 
    (
    	UserId int, 
    	UserName varchar(20), 
    	EventId int, 
    	TypeOfEvent varchar(20), 
    	HappenDateTime datetime
    )
    insert into @tempp values ( 123, 'Test1', 234, 'Login', '2/21/2016 06:06:59')
    insert into @tempp values ( 123, 'Test1', 234, 'Unk', '2/21/2016 06:07:01')
    insert into @tempp values ( 123, 'Test1', 234, 'None', '2/21/2016 07:08:10')
    insert into @tempp values ( 123, 'Test1', 234, 'Logout', '2/21/2016 07:30:10')
    insert into @tempp values ( 123, 'Test1', 254, 'Login', '2/21/2016 08:10:10')
    insert into @tempp values ( 123, 'Test1', 254, 'Logout', '2/21/2016 10:10:10')
    insert into @tempp values ( 123, 'Test1', 264, 'Login', '2/21/2016 11:00:00')
    insert into @tempp values ( 123, 'Test1', 264, 'Mg', '2/21/2016 11:10:10')
    insert into @tempp values ( 123, 'Test1', 264, 'Uc', '2/21/2016 11:20:20')
    insert into @tempp values ( 123, 'Test1', 264, 'Logout', '2/21/2016 12:30:30')
    
    insert into @tempp values ( 124, 'Test2', 254, 'LogIn', '2/24/2016 12:30:30')
    insert into @tempp values ( 124, 'Test2', 264, 'Logout', '2/24/2016 12:30:30')
    
    insert into @tempp values ( 123, 'Test1', 254, 'LogIn', '2/23/2016 12:30:30')
    insert into @tempp values ( 123, 'Test1', 264, 'Logout', '2/23/2016 12:30:30')
    
    
    select * from @tempp
    
    select UserId,UserName, (count(*)/2) as Totalofevent, cast(HappenDateTime as date) as [Date]
    from @tempp
    where TypeOfEvent in ('Login','Logout')
    group by  cast(HappenDateTime as date),UserId,UserName
    order by UserId, [Date]
    

    I want the result like this:
    Userid. Username. Totalofevent. Date
    123. Test1. 3 (based on 234,254,264). 2/21/2016
    123. Test1. 5. 2/21/2016
    Etc...

    For your need, it’s a little confused for me. If it based on the 234,254,264, it would return 5 of ‘2/21/2016’, not 3. The rule of ‘Totalofevent” value 5 in the second line is not clear, you’d better share more thing for it. If you want to get the total result of two different rules, you’d better use union all to combine the two results into one and then use “order by UserId, [Date]” to order them.

    Best Regards,

    Weibo Zhang

    Tuesday, March 1, 2016 9:28 AM
  • User-2099569815 posted

    Thanks Weibo,

    Total number of event for 2/21/2016 is three, second row I meant for another day it could be 5 or1 or 6 but I put the same date. I had corrected in the original post.

    first report just want to count total number of event per day and second report with login logout info.

    Tuesday, March 1, 2016 2:25 PM
  • User-808054615 posted

    Hi,

    Try something like this:

    select
        Userid,
        Username,
        count(distinct EventId) as Totalofevent,
        cast([MyDateTimeColumn] as date) as [Date]
    from MyTable
    group by
        Userid,
        Username,
        cast([MyDateTimeColumn] as date) 
    

    Hope this help

    Tuesday, March 1, 2016 5:17 PM
  • User-219423983 posted

    Hi arial12,

    Total number of event for 2/21/2016 is three

    first report just want to count total number of event per day and second report with login logout info.

    If you want to count the total number of per day, maybe the “2/21/2016” should be 10. But, I’m sorry I don’t find the related information in your modified description from the original post.

    For the second report, you could check my first reply that just count the records which “TypeOfEvent” equals 'Login' or 'Logout' and then reduce by half to get the total count of login logout. Then, you could check gimimex’s code whether it’s what you need. If some of the above replies are helpful to you, you could mark them as answers to close this thread. Or, you could share your code and maybe it could help us to know how you want to achieve your need.

    Best Regards,

    Weibo Zhang

    Thursday, March 3, 2016 1:42 PM
  • User-158764254 posted

    The original test data in the opening post is still not quite making sense to me.

    If we used the test data that Weibo provided i'd use a pivot like this:

    SELECT userid, 
           username, 
           eventdate, 
           Sum([login])  AS LoginCount, 
           Sum([logout]) AS LogoutCount, 
           Count(*)      AS TotalEvents 
    FROM   (SELECT userid, 
                   username, 
                   Dateadd(dd, Datediff(dd, 0, happendatetime), 0) AS EventDate, 
                   login, 
                   logout 
            FROM   @tempp 
                   PIVOT ( Count([typeofevent]) 
                         FOR [typeofevent] IN ([Login], 
                                               [Logout]) ) AS pvt) AS data 
    GROUP  BY userid, 
              username, 
              eventdate 

    The output from that query is:

    Userid username EventDate LoginCount LogoutCount TotalEvents
    123 Test1 2/21/16 0:00 3 3 10
    123 Test1 2/23/16 0:00 1 1 2
    124 Test2 2/24/16 0:00 1 1 2

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 5, 2016 12:51 PM
  • User-595703101 posted

    Hello,

    I hope following SQL Select query can help

    /*
    create table eventtable (
    Userid int,
    Username varchar(10),
    Eventid int,
    Typeofevent varchar(19),
    Date date,
    time time
    )
    
    insert into eventtable select 123,'Test1',234,'Login','2/21/2016','06:06:59'
    insert into eventtable select 123,'Test1',234,'Unk','2/21/2016','06:07:01'
    insert into eventtable select 123,'Test1',234,'None','2/21/2016','07:08:10'
    insert into eventtable select 123,'Test1',234,'Logout','2/21/2016','07:30:10'
    insert into eventtable select 123,'Test1',254,'Login','2/21/2016','08:10:10'
    insert into eventtable select 123,'Test1',254,'Logout','2/21/2016','10:10:10'
    insert into eventtable select 123,'Test1',264,'Login','2/21/2016','11:00:00'
    insert into eventtable select 123,'Test1',264,'Mg','2/21/2016','11:10:10'
    insert into eventtable select 123,'Test1',264,'Uc','2/21/2016','11:20:20'
    insert into eventtable select 123,'Test1',264,'Logout','2/21/2016','12:30:30'
    
    insert into eventtable select 124,'Test2',264,'Logout','2/21/2016','12:30:30'
    */
    
    select
    	Userid, Username, Date, Totalofevent = count(distinct Eventid)
    from eventtable
    group by Userid, Username, Date

    Output is as follows

    Userid      Username   Date       Totalofevent
    ----------- ---------- ---------- ------------
    123         Test1      2016-02-21 3
    124         Test2      2016-02-21 1

    Sunday, March 6, 2016 8:14 PM