Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

SQL statement count logins by date

Hi, I am trying to run a SQL query to measure user performance. Each time a student logs into the intranet a new record with a date time stamp is created.

I want to run a query that will return the student ID their very first login date&time their latest login date&time and then a count of how many times they have logged in.

I have tried the below statement but it's just returning the dates incorrectly  and not the counts
SELECT *
FROM (SELECT Student_ID, Time_stamp,
      MAX(Time_stamp) OVER (PARTITION BY Student_ID) as MaxLoginDate
FROM Intranet_LogIn) x
WHERE Time_stamp = MaxLoginDate
 
 My results should look like this
Student_ID         FirstLogin                                             LatestLogin                                         LoginCounts
 
00223                    2013-12-24 17:05:35.000                                2014-01-08 13:12:12.000                10
78143                    2013-12-26 11:05:11.000                                2014-02-09 16:11:45.000                8
99233                    2013-12-27 13:05:23.000                                2014-02-10 12:12:21.000                3
00323                    2013-12-28 12:06:34.000                                2014-01-11 17:22:33.000                3
00314                    2013-12-29 12:07:45.000                                2014-03-11 13:36:12.000                4
0
jay_eire
Asked:
jay_eire
  • 2
  • 2
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
SELECT Student_ID, Time_stamp Last_Login_Time, cnt number_of_logins  
FROM ( SELECT Student_ID, Time_stamp
     , ROW_NUMBER() OVER (PARTITION BY Student_ID ORDER BY  Time_stamp DESC) as RN
     , COUNT(*) OVER  (PARTITION BY Student_ID ) as CNT
FROM Intranet_LogIn
  ) sq
WHERE sq.rn = 1 

Open in new window

0
 
Snarf0001Commented:
Any reason not to use a standard group by?

select Student_ID, min(Time_stamp) as FirstLogin, max(Time_stamp) as LastLogin, count(1) as TotalLogins
from Intranet_LogIn
group by Student_ID

Open in new window

0
 
jay_eireAuthor Commented:
Thanks for the replies guys!

I did not know I could achieve the same result using group by.

Both solutions work!

OK to split the points?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok for me
0
 
Snarf0001Commented:
No problem.
0
 
jay_eireAuthor Commented:
Thanks again
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now