At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.
Help with inner join and subquery
I refreshing my memory on inner joins and group by, and having difficulty combining the two queries below. Please advise.
I want to obtain employees in our application that have not logged in for at least 35 days, then return the max value for LoginDateTime since I dont need to see all values greater than 35 days per UserID
Table 1 - entitled Users containing following columns:
UserID - PK(int) , not null
DefaultRoleID - int, null
LastName - varchar(50), not null
FirstName - varchar(50), null
Password - varchar(50), null
Email - varchar(100), not null
ADAccount (varchar(50), null
PasswordDate (smalldatetime, null)
Table 2 - entitled LoginHistory containing following columns
UserID - PK, FK, int, not null
LoginDateTime (PK, datetime, not null)
I created two queries, which return results, but I need to combine the two queries.
SELECT o.UserID, o.FirstName + ' ' + o.LastName As FullName, o.Email, g.LoginDateTime FROM dbo.Users o
INNER JOIN dbo.LoginHistory g on o.UserID = g.UserID
SELECT UserID, MAX(LOGINDATETIME)as InactiveDates FROM dbo.LoginHistory WHERE LOGINDATETIME
NOT BETWEEN DATEADD(d,-35,GETDATE()) AND GETDATE() GROUP BY UserID
Results:
I want to obtain employees in our application that have not logged in for at least 35 days, then return the max value for LoginDateTime since I dont need to see all values greater than 35 days per UserID
Table 1 - entitled Users containing following columns:
UserID - PK(int) , not null
DefaultRoleID - int, null
LastName - varchar(50), not null
FirstName - varchar(50), null
Password - varchar(50), null
Email - varchar(100), not null
ADAccount (varchar(50), null
PasswordDate (smalldatetime, null)
Table 2 - entitled LoginHistory containing following columns
UserID - PK, FK, int, not null
LoginDateTime (PK, datetime, not null)
I created two queries, which return results, but I need to combine the two queries.
SELECT o.UserID, o.FirstName + ' ' + o.LastName As FullName, o.Email, g.LoginDateTime FROM dbo.Users o
INNER JOIN dbo.LoginHistory g on o.UserID = g.UserID
SELECT UserID, MAX(LOGINDATETIME)as InactiveDates FROM dbo.LoginHistory WHERE LOGINDATETIME
NOT BETWEEN DATEADD(d,-35,GETDATE()) AND GETDATE() GROUP BY UserID
Results:
UserID FullName LoginDateTime
----------- ----------------------------------------------------------------------------------------------------- -----------------------
39 System Administrator 2007-10-15 08:39:29.257
39 System Administrator 2007-10-15 08:46:11.003
39 System Administrator 2007-10-15 09:02:44.650
39 System Administrator 2007-10-15 10:05:58.757
39 System Administrator 2007-10-16 07:47:38.590
39 System Administrator 2007-10-17 10:44:55.803
39 System Administrator 2007-10-17 10:51:07.397
39 System Administrator 2007-10-17 11:10:21.147
39 System Administrator 2007-10-17 12:49:46.527
39 System Administrator 2007-10-17 13:42:12.350
39 System Administrator 2007-11-01 09:45:43.867
39 System Administrator 2007-12-17 14:32:14.963
39 System Administrator 2008-10-07 09:03:28.047
39 System Administrator 2008-10-07 10:33:51.060
39 System Administrator 2008-10-07 11:49:00.090
39 System Administrator 2008-10-07 12:00:13.963
39 System Administrator 2008-10-07 12:01:53.650
39 System Administrator 2008-10-07 12:02:39.403
39 System Administrator 2008-10-07 12:05:13.000
39 System Administrator 2008-10-07 12:07:21.813
39 System Administrator 2008-10-08 16:19:10.657
39 System Administrator 2008-10-08 16:29:36.183
39 System Administrator 2008-10-08 17:34:45.580
39 System Administrator 2008-10-09 14:39:08.807
39 System Administrator 2008-10-13 09:42:45.843
39 System Administrator 2008-10-13 09:43:44.390
39 System Administrator 2008-10-13 13:39:37.000
39 System Administrator 2008-10-13 13:45:35.563
39 System Administrator 2008-10-13 13:51:55.580
39 System Administrator 2008-10-13 13:57:17.227
39 System Administrator 2008-10-13 14:00:27.960
39 System Administrator 2008-10-13 14:54:58.853
39 System Administrator 2008-10-13 14:56:08.260
39 System Administrator 2008-10-21 10:25:59.097
39 System Administrator 2008-10-21 10:26:52.147
39 System Administrator 2008-10-22 21:31:52.057
39 System Administrator 2008-10-22 21:35:24.697
39 System Administrator 2008-10-22 21:37:26.540
39 System Administrator 2008-10-22 21:39:44.027
39 System Administrator 2008-10-22 21:41:32.353
39 System Administrator 2008-10-22 22:55:55.290
39 System Administrator 2008-10-22 23:02:14.637
39 System Administrator 2008-10-23 09:07:48.227
39 System Administrator 2008-10-23 13:57:29.017
39 System Administrator 2008-10-24 07:56:05.473
39 System Administrator 2008-10-24 08:29:54.757
39 System Administrator 2008-10-24 08:31:29.570
39 System Administrator 2008-10-24 08:42:33.640
39 System Administrator 2008-10-24 08:56:27.103
39 System Administrator 2008-10-24 09:12:03.817
39 System Administrator 2008-10-24 09:21:00.597
39 System Administrator 2008-10-24 09:33:52.897
39 System Administrator 2008-10-24 10:09:17.140
39 System Administrator 2008-10-24 13:45:00.323
39 System Administrator 2008-10-24 15:24:02.300
39 System Administrator 2008-10-24 15:24:42.833
39 System Administrator 2008-11-03 08:07:47.090
39 System Administrator 2008-11-04 20:26:52.603
39 System Administrator 2008-11-04 20:32:11.870
39 System Administrator 2008-11-04 20:34:56.590
39 System Administrator 2008-11-05 12:20:21.297
39 System Administrator 2008-11-06 09:31:06.553
39 System Administrator 2008-11-06 10:16:34.180
39 System Administrator 2008-11-06 14:19:33.827
39 System Administrator 2008-11-08 16:58:39.670
39 System Administrator 2008-11-10 08:11:17.300
39 System Administrator 2008-11-10 08:40:04.810
39 System Administrator 2008-11-10 11:11:55.770
39 System Administrator 2008-11-10 11:14:15.867
39 System Administrator 2008-11-10 14:48:22.210
39 System Administrator 2008-11-10 14:51:22.310
39 System Administrator 2008-11-12 14:04:19.443
39 System Administrator 2008-11-18 16:39:05.053
39 System Administrator 2008-11-20 09:06:45.617
39 System Administrator 2008-11-21 14:48:27.070
39 System Administrator 2008-11-21 14:52:19.110
39 System Administrator 2009-02-02 13:43:03.597
39 System Administrator 2009-02-04 09:45:39.140
39 System Administrator 2009-02-04 10:12:03.660
39 System Administrator 2009-02-06 09:21:46.030
39 System Administrator 2009-02-06 12:54:26.923
39 System Administrator 2009-02-06 12:56:06.333
39 System Administrator 2009-02-16 13:08:31.180
39 System Administrator 2009-02-16 13:32:06.907
39 System Administrator 2009-02-24 13:34:10.673
39 System Administrator 2009-03-06 07:03:17.487
39 System Administrator 2009-04-02 08:22:37.830
39 System Administrator 2009-04-06 09:46:10.643
39 System Administrator 2009-04-06 09:52:00.963
39 System Administrator 2009-04-06 09:56:25.127
39 System Administrator 2009-04-07 12:04:40.463
39 System Administrator 2009-04-07 14:03:19.020
39 System Administrator 2009-04-08 08:13:06.260
39 System Administrator 2009-04-08 11:03:07.537
39 System Administrator 2009-04-08 11:40:37.470
39 System Administrator 2009-04-09 08:24:52.057
39 System Administrator 2009-04-15 11:28:37.057
39 System Administrator 2009-04-20 18:47:41.150
39 System Administrator 2009-06-04 07:30:31.883
39 System Administrator 2009-06-05 11:31:29.767
39 System Administrator 2009-06-05 13:05:35.290
39 System Administrator 2009-06-15 10:40:15.860
39 System Administrator 2009-06-15 13:14:59.150
39 System Administrator 2009-06-15 13:30:49.963
39 System Administrator 2009-06-15 13:35:20.673
39 System Administrator 2009-06-15 13:39:54.617
39 System Administrator 2009-06-17 08:34:14.207
39 System Administrator 2009-06-17 10:46:18.667
39 System Administrator 2009-06-18 10:20:55.503
39 System Administrator 2009-06-23 13:43:37.317
39 System Administrator 2009-06-26 13:23:08.280
39 System Administrator 2009-07-02 08:01:54.390
39 System Administrator 2009-07-07 07:22:46.370
39 System Administrator 2009-07-07 12:50:52.467
39 System Administrator 2009-07-07 15:32:19.133
39 System Administrator 2009-07-07 15:44:55.733
39 System Administrator 2009-07-09 13:32:58.880
39 System Administrator 2009-07-10 11:21:57.560
39 System Administrator 2009-07-10 11:49:27.453
39 System Administrator 2009-07-13 15:04:48.403
39 System Administrator 2009-07-13 16:14:58.187
39 System Administrator 2009-07-14 09:36:11.660
39 System Administrator 2009-07-14 13:18:49.510
39 System Administrator 2009-07-17 14:34:55.463
39 System Administrator 2009-07-17 14:40:39.923
39 System Administrator 2009-07-20 11:34:49.023
39 System Administrator 2009-07-20 13:06:33.753
39 System Administrator 2009-07-22 10:31:38.370
39 System Administrator 2009-07-23 09:16:00.033
39 System Administrator 2009-07-23 12:57:04.637
39 System Administrator 2009-07-24 09:22:13.350
39 System Administrator 2009-07-27 10:03:43.607
39 System Administrator 2009-07-27 17:27:21.083
39 System Administrator 2009-07-27 17:30:58.530
39 System Administrator 2009-07-28 13:58:50.467
39 System Administrator 2009-07-29 08:30:40.277
39 System Administrator 2009-07-29 15:08:47.770
39 System Administrator 2009-07-29 15:29:35.900
39 System Administrator 2009-07-30 09:22:39.230
39 System Administrator 2009-07-30 09:26:29.767
39 System Administrator 2009-07-30 10:25:21.460
39 System Administrator 2009-07-30 11:25:45.863
39 System Administrator 2009-07-31 12:16:29.633
39 System Administrator 2009-08-03 11:43:10.530
39 System Administrator 2009-08-05 11:03:00.353
39 System Administrator 2009-08-19 09:05:27.127
39 System Administrator 2009-08-21 11:07:27.663
39 System Administrator 2009-08-23 14:51:36.010
39 System Administrator 2009-08-24 14:58:21.697
39 System Administrator 2009-08-24 15:04:58.330
39 System Administrator 2009-08-24 16:00:16.140
39 System Administrator 2009-08-24 16:01:25.817
39 System Administrator 2009-08-25 10:03:28.837
39 System Administrator 2009-08-27 09:20:13.153
39 System Administrator 2009-09-02 13:13:58.297
39 System Administrator 2009-09-02 13:35:59.737
39 System Administrator 2009-09-02 13:43:20.250
39 System Administrator 2009-09-03 08:12:32.563
39 System Administrator 2009-09-03 10:03:00.230
39 System Administrator 2009-09-03 10:55:05.140
39 System Administrator 2009-09-03 11:01:07.340
39 System Administrator 2009-09-03 11:17:19.607
39 System Administrator 2009-09-03 14:52:57.080
39 System Administrator 2009-09-16 10:20:56.693
39 System Administrator 2009-09-18 15:34:54.940
39 System Administrator 2009-09-18 15:36:42.627
39 System Administrator 2009-09-18 15:36:52.737
39 System Administrator 2009-09-21 09:21:51.593
39 System Administrator 2009-09-22 23:33:03.990
39 System Administrator 2009-09-24 14:31:39.793
39 System Administrator 2009-10-02 14:17:01.060
39 System Administrator 2009-10-05 15:15:24.290
39 System Administrator 2009-10-07 14:33:59.933
39 System Administrator 2009-10-09 11:11:35.713
39 System Administrator 2009-10-12 10:29:25.680
39 System Administrator 2009-10-12 10:40:07.820
39 System Administrator 2009-10-12 11:47:37.163
39 System Administrator 2009-10-14 16:44:27.400
39 System Administrator 2009-10-14 16:48:43.620
39 System Administrator 2009-10-14 17:52:29.133
39 System Administrator 2009-10-15 19:24:52.520
39 System Administrator 2009-10-18 17:53:25.620
39 System Administrator 2009-10-18 18:36:38.153
39 System Administrator 2009-10-19 13:39:36.943
39 System Administrator 2009-10-21 14:55:46.693
39 System Administrator 2009-10-22 13:37:09.947
39 System Administrator 2009-10-22 15:32:28.203
39 System Administrator 2009-10-22 16:27:28.673
39 System Administrator 2009-10-23 10:03:35.907
39 System Administrator 2009-10-23 10:26:31.467
39 System Administrator 2009-10-26 11:26:03.887
39 System Administrator 2009-10-28 10:10:15.650
39 System Administrator 2009-10-28 11:52:55.510
39 System Administrator 2009-11-19 15:35:18.403
39 System Administrator 2009-11-20 14:22:01.637
39 System Administrator 2009-12-07 15:03:40.270
39 System Administrator 2009-12-16 08:36:01.213
39 System Administrator 2009-12-16 14:14:00.217
39 System Administrator 2009-12-17 12:56:37.533
39 System Administrator 2009-12-17 14:49:13.407
39 System Administrator 2010-01-24 18:54:54.233
39 System Administrator 2010-01-27 15:20:31.080
39 System Administrator 2010-01-28 16:05:53.853
39 System Administrator 2010-01-28 16:40:18.733
39 System Administrator 2010-02-02 17:38:33.867
39 System Administrator 2010-02-04 15:11:00.800
39 System Administrator 2010-02-10 16:04:19.470
39 System Administrator 2010-02-12 09:22:09.110
39 System Administrator 2010-02-18 08:48:04.860
39 System Administrator 2010-02-18 09:15:44.707
39 System Administrator 2010-02-18 12:55:38.247
39 System Administrator 2010-02-22 09:36:58.290
39 System Administrator 2010-02-22 12:07:05.383
39 System Administrator 2010-03-05 16:18:15.043
39 System Administrator 2010-03-08 09:10:34.920
39 System Administrator 2010-03-08 09:22:49.153
39 System Administrator 2010-03-08 10:03:44.657
39 System Administrator 2010-03-08 10:19:27.400
39 System Administrator 2010-03-08 11:45:29.483
39 System Administrator 2010-03-08 12:33:56.667
39 System Administrator 2010-03-08 12:37:58.237
39 System Administrator 2010-03-11 12:56:34.080
39 System Administrator 2010-03-12 14:53:49.330
39 System Administrator 2010-03-12 15:05:12.927
39 System Administrator 2010-03-15 10:37:22.473
39 System Administrator 2010-03-24 12:30:09.737
39 System Administrator 2010-03-24 12:53:09.553
39 System Administrator 2010-03-24 16:45:43.907
39 System Administrator 2010-03-29 10:54:02.623
39 System Administrator 2010-03-29 10:56:38.923
39 System Administrator 2010-03-29 12:22:23.257
39 System Administrator 2010-03-29 14:58:14.173
39 System Administrator 2010-03-30 09:06:21.303
39 System Administrator 2010-03-30 10:31:51.920
39 System Administrator 2010-04-02 08:26:28.057
39 System Administrator 2010-04-02 14:04:33.250
39 System Administrator 2010-04-07 10:50:51.160
39 System Administrator 2010-04-08 16:18:25.563
39 System Administrator 2010-04-13 08:55:08.830
39 System Administrator 2010-04-13 08:59:51.823
39 System Administrator 2010-04-13 09:02:47.860
39 System Administrator 2010-04-13 09:24:57.580
39 System Administrator 2010-04-13 10:05:06.673
39 System Administrator 2010-04-13 16:56:18.913
39 System Administrator 2010-04-15 16:23:58.300
39 System Administrator 2010-04-16 10:42:11.200
39 System Administrator 2010-04-18 15:53:08.657
39 System Administrator 2010-04-19 17:34:21.057
39 System Administrator 2010-04-20 12:43:14.410
39 System Administrator 2010-04-20 14:48:37.100
39 System Administrator 2010-04-21 10:48:55.180
39 System Administrator 2010-04-22 16:05:56.227
39 System Administrator 2010-04-22 16:06:51.570
39 System Administrator 2010-04-27 09:06:24.007
39 System Administrator 2010-04-28 08:34:40.810
39 System Administrator 2010-04-28 08:38:11.330
39 System Administrator 2010-04-28 08:42:06.460
39 System Administrator 2010-04-28 08:56:25.890
39 System Administrator 2010-04-28 09:02:46.133
39 System Administrator 2010-04-28 09:09:12.953
39 System Administrator 2010-04-28 22:22:01.427
39 System Administrator 2010-04-29 14:06:33.297
39 System Administrator 2010-04-29 14:18:18.750
39 System Administrator 2010-05-03 13:34:41.047
39 System Administrator 2010-05-04 08:50:50.183
39 System Administrator 2010-05-04 08:58:26.180
39 System Administrator 2010-05-04 09:05:14.753
39 System Administrator 2010-05-05 09:36:12.397
39 System Administrator 2010-05-06 12:28:36.227
39 System Administrator 2010-05-07 14:47:46.590
39 System Administrator 2010-05-11 08:36:42.040
39 System Administrator 2010-05-11 09:26:09.523
39 System Administrator 2010-05-11 10:00:03.277
39 System Administrator 2010-05-13 15:28:49.223
39 System Administrator 2010-05-17 07:44:43.850
39 System Administrator 2010-05-17 07:47:17.230
39 System Administrator 2010-05-17 08:08:35.027
39 System Administrator 2010-05-17 09:24:05.797
39 System Administrator 2010-05-21 13:36:06.353
39 System Administrator 2010-06-25 10:36:19.523
39 System Administrator 2010-06-29 10:11:23.150
39 System Administrator 2010-07-14 09:38:40.160
39 System Administrator 2010-07-14 14:19:17.363
39 System Administrator 2010-07-19 15:48:03.853
39 System Administrator 2010-07-29 14:22:21.500
39 System Administrator 2010-07-30 11:08:09.407
39 System Administrator 2010-07-30 13:30:17.030
39 System Administrator 2010-08-06 09:38:56.843
39 System Administrator 2010-08-09 10:24:24.363
39 System Administrator 2010-08-09 13:19:03.540
39 System Administrator 2010-08-10 13:52:41.837
40 Emdad Khondaker 2008-10-24 09:20:44.393
40 Emdad Khondaker 2008-10-24 09:31:27.500
41 Brent Goetz 2009-03-06 07:49:55.920
41 Brent Goetz 2010-04-28 08:31:24.647
42 Laura Mathis 2009-07-27 17:44:23.453
42 Laura Mathis 2009-07-28 18:20:32.490
42 Laura Mathis 2009-08-03 10:02:55.523
42 Laura Mathis 2009-08-03 11:45:43.530
42 Laura Mathis 2009-08-14 12:31:56.440
42 Laura Mathis 2009-08-18 15:54:30.093
42 Laura Mathis 2009-08-18 17:40:39.273
42 Laura Mathis 2009-08-21 11:10:05.883
42 Laura Mathis 2009-08-28 17:30:35.900
42 Laura Mathis 2009-08-31 15:00:42.433
42 Laura Mathis 2009-08-31 15:19:29.887
42 Laura Mathis 2009-08-31 16:46:07.183
42 Laura Mathis 2009-09-01 09:24:08.740
42 Laura Mathis 2009-09-01 09:35:45.537
42 Laura Mathis 2009-09-02 15:55:42.250
42 Laura Mathis 2009-09-03 10:07:05.640
42 Laura Mathis 2009-09-08 15:32:20.417
42 Laura Mathis 2009-09-09 09:48:53.207
42 Laura Mathis 2009-09-25 09:26:33.530
42 Laura Mathis 2009-09-28 10:22:16.073
44 Krish Rajagopal 2009-09-03 08:42:36.690
44 Krish Rajagopal 2009-09-03 10:12:32.673
44 Krish Rajagopal 2010-04-13 08:59:01.603
44 Krish Rajagopal 2010-04-13 09:03:40.173
44 Krish Rajagopal 2010-04-13 12:19:53.350
44 Krish Rajagopal 2010-04-13 14:17:39.703
44 Krish Rajagopal 2010-04-16 13:09:50.850
44 Krish Rajagopal 2010-04-27 11:14:53.633
44 Krish Rajagopal 2010-04-28 08:37:40.640
44 Krish Rajagopal 2010-04-28 08:39:32.080
44 Krish Rajagopal 2010-04-28 08:47:15.280
44 Krish Rajagopal 2010-04-28 08:59:59.753
44 Krish Rajagopal 2010-04-28 09:04:06.230
44 Krish Rajagopal 2010-04-28 22:09:31.360
44 Krish Rajagopal 2010-04-28 22:22:51.803
44 Krish Rajagopal 2010-04-29 09:10:21.793
45 Kevin Williams 2009-09-03 10:26:36.637
45 Kevin Williams 2009-09-04 14:14:27.427
45 Kevin Williams 2009-09-08 10:47:08.010
45 Kevin Williams 2009-09-09 10:07:39.880
45 Kevin Williams 2009-09-10 15:53:54.213
45 Kevin Williams 2009-09-15 10:34:43.680
45 Kevin Williams 2009-09-16 09:40:04.753
45 Kevin Williams 2009-09-16 17:22:35.787
(339 row(s) affected)
UserID InactiveDates
----------- -----------------------
39 2010-07-14 14:19:17.363
40 2008-10-24 09:31:27.500
41 2010-04-28 08:31:24.647
42 2009-09-28 10:22:16.073
44 2010-04-29 09:10:21.793
45 2009-09-16 17:22:35.787
(6 row(s) affected)
Asked:
6-
3
2- +1
1 Solution
Try this
SELECT o.UserID, o.FirstName + ' ' + o.LastName As FullName, o.Email as UserEmail, MAX(g.LOGINDATETIME)as LastInactiveDate
FROM dbo.Users o
INNER JOIN dbo.LoginHistory g on o.UserID = g.UserID AND g.LOGINDATETIME NOT BETWEEN DATEADD(d,-35,GETDATE()) AND GETDATE()
Group By o.UserID
ORDER BY g.LOGINDATETIME DESC
MatrixDweller,
I tried executing the above snippet, but it returns following SQL error:
Msg 8120, Level 16, State 1, Line 9
Column 'dbo.Users.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 9
Column 'dbo.Users.LastName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
The first query returns correct results, but I'm trying to group the UserID to return one MaxLoginDate per UserID.
I tried executing the above snippet, but it returns following SQL error:
Msg 8120, Level 16, State 1, Line 9
Column 'dbo.Users.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 9
Column 'dbo.Users.LastName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
The first query returns correct results, but I'm trying to group the UserID to return one MaxLoginDate per UserID.
UserID FullName LoginDateTime MaxLogin
----------- ----------------------------------------------------------------------------------------------------- ----------------------- -----------------------
39 System Administrator 2007-10-15 08:39:29.257 2010-07-14 14:19:17.363
39 System Administrator 2007-10-15 08:46:11.003 2010-07-14 14:19:17.363
39 System Administrator 2007-10-15 09:02:44.650 2010-07-14 14:19:17.363
39 System Administrator 2007-10-15 10:05:58.757 2010-07-14 14:19:17.363
39 System Administrator 2007-10-16 07:47:38.590 2010-07-14 14:19:17.363
39 System Administrator 2007-10-17 10:44:55.803 2010-07-14 14:19:17.363
39 System Administrator 2007-10-17 10:51:07.397 2010-07-14 14:19:17.363
39 System Administrator 2007-10-17 11:10:21.147 2010-07-14 14:19:17.363
39 System Administrator 2007-10-17 12:49:46.527 2010-07-14 14:19:17.363
39 System Administrator 2007-10-17 13:42:12.350 2010-07-14 14:19:17.363
39 System Administrator 2007-11-01 09:45:43.867 2010-07-14 14:19:17.363
39 System Administrator 2007-12-17 14:32:14.963 2010-07-14 14:19:17.363
39 System Administrator 2008-10-07 09:03:28.047 2010-07-14 14:19:17.363
39 System Administrator 2008-10-07 10:33:51.060 2010-07-14 14:19:17.363
39 System Administrator 2008-10-07 11:49:00.090 2010-07-14 14:19:17.363
39 System Administrator 2008-10-07 12:00:13.963 2010-07-14 14:19:17.363
39 System Administrator 2008-10-07 12:01:53.650 2010-07-14 14:19:17.363
39 System Administrator 2008-10-07 12:02:39.403 2010-07-14 14:19:17.363
39 System Administrator 2008-10-07 12:05:13.000 2010-07-14 14:19:17.363
39 System Administrator 2008-10-07 12:07:21.813 2010-07-14 14:19:17.363
39 System Administrator 2008-10-08 16:19:10.657 2010-07-14 14:19:17.363
39 System Administrator 2008-10-08 16:29:36.183 2010-07-14 14:19:17.363
39 System Administrator 2008-10-08 17:34:45.580 2010-07-14 14:19:17.363
39 System Administrator 2008-10-09 14:39:08.807 2010-07-14 14:19:17.363
39 System Administrator 2008-10-13 09:42:45.843 2010-07-14 14:19:17.363
39 System Administrator 2008-10-13 09:43:44.390 2010-07-14 14:19:17.363
39 System Administrator 2008-10-13 13:39:37.000 2010-07-14 14:19:17.363
39 System Administrator 2008-10-13 13:45:35.563 2010-07-14 14:19:17.363
39 System Administrator 2008-10-13 13:51:55.580 2010-07-14 14:19:17.363
39 System Administrator 2008-10-13 13:57:17.227 2010-07-14 14:19:17.363
39 System Administrator 2008-10-13 14:00:27.960 2010-07-14 14:19:17.363
39 System Administrator 2008-10-13 14:54:58.853 2010-07-14 14:19:17.363
39 System Administrator 2008-10-13 14:56:08.260 2010-07-14 14:19:17.363
39 System Administrator 2008-10-21 10:25:59.097 2010-07-14 14:19:17.363
39 System Administrator 2008-10-21 10:26:52.147 2010-07-14 14:19:17.363
39 System Administrator 2008-10-22 21:31:52.057 2010-07-14 14:19:17.363
39 System Administrator 2008-10-22 21:35:24.697 2010-07-14 14:19:17.363
39 System Administrator 2008-10-22 21:37:26.540 2010-07-14 14:19:17.363
39 System Administrator 2008-10-22 21:39:44.027 2010-07-14 14:19:17.363
39 System Administrator 2008-10-22 21:41:32.353 2010-07-14 14:19:17.363
39 System Administrator 2008-10-22 22:55:55.290 2010-07-14 14:19:17.363
39 System Administrator 2008-10-22 23:02:14.637 2010-07-14 14:19:17.363
39 System Administrator 2008-10-23 09:07:48.227 2010-07-14 14:19:17.363
39 System Administrator 2008-10-23 13:57:29.017 2010-07-14 14:19:17.363
39 System Administrator 2008-10-24 07:56:05.473 2010-07-14 14:19:17.363
39 System Administrator 2008-10-24 08:29:54.757 2010-07-14 14:19:17.363
39 System Administrator 2008-10-24 08:31:29.570 2010-07-14 14:19:17.363
39 System Administrator 2008-10-24 08:42:33.640 2010-07-14 14:19:17.363
39 System Administrator 2008-10-24 08:56:27.103 2010-07-14 14:19:17.363
39 System Administrator 2008-10-24 09:12:03.817 2010-07-14 14:19:17.363
39 System Administrator 2008-10-24 09:21:00.597 2010-07-14 14:19:17.363
39 System Administrator 2008-10-24 09:33:52.897 2010-07-14 14:19:17.363
39 System Administrator 2008-10-24 10:09:17.140 2010-07-14 14:19:17.363
39 System Administrator 2008-10-24 13:45:00.323 2010-07-14 14:19:17.363
39 System Administrator 2008-10-24 15:24:02.300 2010-07-14 14:19:17.363
39 System Administrator 2008-10-24 15:24:42.833 2010-07-14 14:19:17.363
39 System Administrator 2008-11-03 08:07:47.090 2010-07-14 14:19:17.363
39 System Administrator 2008-11-04 20:26:52.603 2010-07-14 14:19:17.363
39 System Administrator 2008-11-04 20:32:11.870 2010-07-14 14:19:17.363
39 System Administrator 2008-11-04 20:34:56.590 2010-07-14 14:19:17.363
39 System Administrator 2008-11-05 12:20:21.297 2010-07-14 14:19:17.363
39 System Administrator 2008-11-06 09:31:06.553 2010-07-14 14:19:17.363
39 System Administrator 2008-11-06 10:16:34.180 2010-07-14 14:19:17.363
39 System Administrator 2008-11-06 14:19:33.827 2010-07-14 14:19:17.363
39 System Administrator 2008-11-08 16:58:39.670 2010-07-14 14:19:17.363
39 System Administrator 2008-11-10 08:11:17.300 2010-07-14 14:19:17.363
39 System Administrator 2008-11-10 08:40:04.810 2010-07-14 14:19:17.363
39 System Administrator 2008-11-10 11:11:55.770 2010-07-14 14:19:17.363
39 System Administrator 2008-11-10 11:14:15.867 2010-07-14 14:19:17.363
39 System Administrator 2008-11-10 14:48:22.210 2010-07-14 14:19:17.363
39 System Administrator 2008-11-10 14:51:22.310 2010-07-14 14:19:17.363
39 System Administrator 2008-11-12 14:04:19.443 2010-07-14 14:19:17.363
39 System Administrator 2008-11-18 16:39:05.053 2010-07-14 14:19:17.363
39 System Administrator 2008-11-20 09:06:45.617 2010-07-14 14:19:17.363
39 System Administrator 2008-11-21 14:48:27.070 2010-07-14 14:19:17.363
39 System Administrator 2008-11-21 14:52:19.110 2010-07-14 14:19:17.363
39 System Administrator 2009-02-02 13:43:03.597 2010-07-14 14:19:17.363
39 System Administrator 2009-02-04 09:45:39.140 2010-07-14 14:19:17.363
39 System Administrator 2009-02-04 10:12:03.660 2010-07-14 14:19:17.363
39 System Administrator 2009-02-06 09:21:46.030 2010-07-14 14:19:17.363
39 System Administrator 2009-02-06 12:54:26.923 2010-07-14 14:19:17.363
39 System Administrator 2009-02-06 12:56:06.333 2010-07-14 14:19:17.363
39 System Administrator 2009-02-16 13:08:31.180 2010-07-14 14:19:17.363
39 System Administrator 2009-02-16 13:32:06.907 2010-07-14 14:19:17.363
39 System Administrator 2009-02-24 13:34:10.673 2010-07-14 14:19:17.363
39 System Administrator 2009-03-06 07:03:17.487 2010-07-14 14:19:17.363
39 System Administrator 2009-04-02 08:22:37.830 2010-07-14 14:19:17.363
39 System Administrator 2009-04-06 09:46:10.643 2010-07-14 14:19:17.363
39 System Administrator 2009-04-06 09:52:00.963 2010-07-14 14:19:17.363
39 System Administrator 2009-04-06 09:56:25.127 2010-07-14 14:19:17.363
39 System Administrator 2009-04-07 12:04:40.463 2010-07-14 14:19:17.363
39 System Administrator 2009-04-07 14:03:19.020 2010-07-14 14:19:17.363
39 System Administrator 2009-04-08 08:13:06.260 2010-07-14 14:19:17.363
39 System Administrator 2009-04-08 11:03:07.537 2010-07-14 14:19:17.363
39 System Administrator 2009-04-08 11:40:37.470 2010-07-14 14:19:17.363
39 System Administrator 2009-04-09 08:24:52.057 2010-07-14 14:19:17.363
39 System Administrator 2009-04-15 11:28:37.057 2010-07-14 14:19:17.363
39 System Administrator 2009-04-20 18:47:41.150 2010-07-14 14:19:17.363
39 System Administrator 2009-06-04 07:30:31.883 2010-07-14 14:19:17.363
39 System Administrator 2009-06-05 11:31:29.767 2010-07-14 14:19:17.363
39 System Administrator 2009-06-05 13:05:35.290 2010-07-14 14:19:17.363
39 System Administrator 2009-06-15 10:40:15.860 2010-07-14 14:19:17.363
39 System Administrator 2009-06-15 13:14:59.150 2010-07-14 14:19:17.363
39 System Administrator 2009-06-15 13:30:49.963 2010-07-14 14:19:17.363
39 System Administrator 2009-06-15 13:35:20.673 2010-07-14 14:19:17.363
39 System Administrator 2009-06-15 13:39:54.617 2010-07-14 14:19:17.363
39 System Administrator 2009-06-17 08:34:14.207 2010-07-14 14:19:17.363
39 System Administrator 2009-06-17 10:46:18.667 2010-07-14 14:19:17.363
39 System Administrator 2009-06-18 10:20:55.503 2010-07-14 14:19:17.363
39 System Administrator 2009-06-23 13:43:37.317 2010-07-14 14:19:17.363
39 System Administrator 2009-06-26 13:23:08.280 2010-07-14 14:19:17.363
39 System Administrator 2009-07-02 08:01:54.390 2010-07-14 14:19:17.363
39 System Administrator 2009-07-07 07:22:46.370 2010-07-14 14:19:17.363
39 System Administrator 2009-07-07 12:50:52.467 2010-07-14 14:19:17.363
39 System Administrator 2009-07-07 15:32:19.133 2010-07-14 14:19:17.363
39 System Administrator 2009-07-07 15:44:55.733 2010-07-14 14:19:17.363
39 System Administrator 2009-07-09 13:32:58.880 2010-07-14 14:19:17.363
39 System Administrator 2009-07-10 11:21:57.560 2010-07-14 14:19:17.363
39 System Administrator 2009-07-10 11:49:27.453 2010-07-14 14:19:17.363
39 System Administrator 2009-07-13 15:04:48.403 2010-07-14 14:19:17.363
39 System Administrator 2009-07-13 16:14:58.187 2010-07-14 14:19:17.363
39 System Administrator 2009-07-14 09:36:11.660 2010-07-14 14:19:17.363
39 System Administrator 2009-07-14 13:18:49.510 2010-07-14 14:19:17.363
39 System Administrator 2009-07-17 14:34:55.463 2010-07-14 14:19:17.363
39 System Administrator 2009-07-17 14:40:39.923 2010-07-14 14:19:17.363
39 System Administrator 2009-07-20 11:34:49.023 2010-07-14 14:19:17.363
39 System Administrator 2009-07-20 13:06:33.753 2010-07-14 14:19:17.363
39 System Administrator 2009-07-22 10:31:38.370 2010-07-14 14:19:17.363
39 System Administrator 2009-07-23 09:16:00.033 2010-07-14 14:19:17.363
39 System Administrator 2009-07-23 12:57:04.637 2010-07-14 14:19:17.363
39 System Administrator 2009-07-24 09:22:13.350 2010-07-14 14:19:17.363
39 System Administrator 2009-07-27 10:03:43.607 2010-07-14 14:19:17.363
39 System Administrator 2009-07-27 17:27:21.083 2010-07-14 14:19:17.363
39 System Administrator 2009-07-27 17:30:58.530 2010-07-14 14:19:17.363
39 System Administrator 2009-07-28 13:58:50.467 2010-07-14 14:19:17.363
39 System Administrator 2009-07-29 08:30:40.277 2010-07-14 14:19:17.363
39 System Administrator 2009-07-29 15:08:47.770 2010-07-14 14:19:17.363
39 System Administrator 2009-07-29 15:29:35.900 2010-07-14 14:19:17.363
39 System Administrator 2009-07-30 09:22:39.230 2010-07-14 14:19:17.363
39 System Administrator 2009-07-30 09:26:29.767 2010-07-14 14:19:17.363
39 System Administrator 2009-07-30 10:25:21.460 2010-07-14 14:19:17.363
39 System Administrator 2009-07-30 11:25:45.863 2010-07-14 14:19:17.363
39 System Administrator 2009-07-31 12:16:29.633 2010-07-14 14:19:17.363
39 System Administrator 2009-08-03 11:43:10.530 2010-07-14 14:19:17.363
39 System Administrator 2009-08-05 11:03:00.353 2010-07-14 14:19:17.363
39 System Administrator 2009-08-19 09:05:27.127 2010-07-14 14:19:17.363
39 System Administrator 2009-08-21 11:07:27.663 2010-07-14 14:19:17.363
39 System Administrator 2009-08-23 14:51:36.010 2010-07-14 14:19:17.363
39 System Administrator 2009-08-24 14:58:21.697 2010-07-14 14:19:17.363
39 System Administrator 2009-08-24 15:04:58.330 2010-07-14 14:19:17.363
39 System Administrator 2009-08-24 16:00:16.140 2010-07-14 14:19:17.363
39 System Administrator 2009-08-24 16:01:25.817 2010-07-14 14:19:17.363
39 System Administrator 2009-08-25 10:03:28.837 2010-07-14 14:19:17.363
39 System Administrator 2009-08-27 09:20:13.153 2010-07-14 14:19:17.363
39 System Administrator 2009-09-02 13:13:58.297 2010-07-14 14:19:17.363
39 System Administrator 2009-09-02 13:35:59.737 2010-07-14 14:19:17.363
39 System Administrator 2009-09-02 13:43:20.250 2010-07-14 14:19:17.363
39 System Administrator 2009-09-03 08:12:32.563 2010-07-14 14:19:17.363
39 System Administrator 2009-09-03 10:03:00.230 2010-07-14 14:19:17.363
39 System Administrator 2009-09-03 10:55:05.140 2010-07-14 14:19:17.363
39 System Administrator 2009-09-03 11:01:07.340 2010-07-14 14:19:17.363
39 System Administrator 2009-09-03 11:17:19.607 2010-07-14 14:19:17.363
39 System Administrator 2009-09-03 14:52:57.080 2010-07-14 14:19:17.363
39 System Administrator 2009-09-16 10:20:56.693 2010-07-14 14:19:17.363
39 System Administrator 2009-09-18 15:34:54.940 2010-07-14 14:19:17.363
39 System Administrator 2009-09-18 15:36:42.627 2010-07-14 14:19:17.363
39 System Administrator 2009-09-18 15:36:52.737 2010-07-14 14:19:17.363
39 System Administrator 2009-09-21 09:21:51.593 2010-07-14 14:19:17.363
39 System Administrator 2009-09-22 23:33:03.990 2010-07-14 14:19:17.363
39 System Administrator 2009-09-24 14:31:39.793 2010-07-14 14:19:17.363
39 System Administrator 2009-10-02 14:17:01.060 2010-07-14 14:19:17.363
39 System Administrator 2009-10-05 15:15:24.290 2010-07-14 14:19:17.363
39 System Administrator 2009-10-07 14:33:59.933 2010-07-14 14:19:17.363
39 System Administrator 2009-10-09 11:11:35.713 2010-07-14 14:19:17.363
39 System Administrator 2009-10-12 10:29:25.680 2010-07-14 14:19:17.363
39 System Administrator 2009-10-12 10:40:07.820 2010-07-14 14:19:17.363
39 System Administrator 2009-10-12 11:47:37.163 2010-07-14 14:19:17.363
39 System Administrator 2009-10-14 16:44:27.400 2010-07-14 14:19:17.363
39 System Administrator 2009-10-14 16:48:43.620 2010-07-14 14:19:17.363
39 System Administrator 2009-10-14 17:52:29.133 2010-07-14 14:19:17.363
39 System Administrator 2009-10-15 19:24:52.520 2010-07-14 14:19:17.363
39 System Administrator 2009-10-18 17:53:25.620 2010-07-14 14:19:17.363
39 System Administrator 2009-10-18 18:36:38.153 2010-07-14 14:19:17.363
39 System Administrator 2009-10-19 13:39:36.943 2010-07-14 14:19:17.363
39 System Administrator 2009-10-21 14:55:46.693 2010-07-14 14:19:17.363
39 System Administrator 2009-10-22 13:37:09.947 2010-07-14 14:19:17.363
39 System Administrator 2009-10-22 15:32:28.203 2010-07-14 14:19:17.363
39 System Administrator 2009-10-22 16:27:28.673 2010-07-14 14:19:17.363
39 System Administrator 2009-10-23 10:03:35.907 2010-07-14 14:19:17.363
39 System Administrator 2009-10-23 10:26:31.467 2010-07-14 14:19:17.363
39 System Administrator 2009-10-26 11:26:03.887 2010-07-14 14:19:17.363
39 System Administrator 2009-10-28 10:10:15.650 2010-07-14 14:19:17.363
39 System Administrator 2009-10-28 11:52:55.510 2010-07-14 14:19:17.363
39 System Administrator 2009-11-19 15:35:18.403 2010-07-14 14:19:17.363
39 System Administrator 2009-11-20 14:22:01.637 2010-07-14 14:19:17.363
39 System Administrator 2009-12-07 15:03:40.270 2010-07-14 14:19:17.363
39 System Administrator 2009-12-16 08:36:01.213 2010-07-14 14:19:17.363
39 System Administrator 2009-12-16 14:14:00.217 2010-07-14 14:19:17.363
39 System Administrator 2009-12-17 12:56:37.533 2010-07-14 14:19:17.363
39 System Administrator 2009-12-17 14:49:13.407 2010-07-14 14:19:17.363
39 System Administrator 2010-01-24 18:54:54.233 2010-07-14 14:19:17.363
39 System Administrator 2010-01-27 15:20:31.080 2010-07-14 14:19:17.363
39 System Administrator 2010-01-28 16:05:53.853 2010-07-14 14:19:17.363
39 System Administrator 2010-01-28 16:40:18.733 2010-07-14 14:19:17.363
39 System Administrator 2010-02-02 17:38:33.867 2010-07-14 14:19:17.363
39 System Administrator 2010-02-04 15:11:00.800 2010-07-14 14:19:17.363
39 System Administrator 2010-02-10 16:04:19.470 2010-07-14 14:19:17.363
39 System Administrator 2010-02-12 09:22:09.110 2010-07-14 14:19:17.363
39 System Administrator 2010-02-18 08:48:04.860 2010-07-14 14:19:17.363
39 System Administrator 2010-02-18 09:15:44.707 2010-07-14 14:19:17.363
39 System Administrator 2010-02-18 12:55:38.247 2010-07-14 14:19:17.363
39 System Administrator 2010-02-22 09:36:58.290 2010-07-14 14:19:17.363
39 System Administrator 2010-02-22 12:07:05.383 2010-07-14 14:19:17.363
39 System Administrator 2010-03-05 16:18:15.043 2010-07-14 14:19:17.363
39 System Administrator 2010-03-08 09:10:34.920 2010-07-14 14:19:17.363
39 System Administrator 2010-03-08 09:22:49.153 2010-07-14 14:19:17.363
39 System Administrator 2010-03-08 10:03:44.657 2010-07-14 14:19:17.363
39 System Administrator 2010-03-08 10:19:27.400 2010-07-14 14:19:17.363
39 System Administrator 2010-03-08 11:45:29.483 2010-07-14 14:19:17.363
39 System Administrator 2010-03-08 12:33:56.667 2010-07-14 14:19:17.363
39 System Administrator 2010-03-08 12:37:58.237 2010-07-14 14:19:17.363
39 System Administrator 2010-03-11 12:56:34.080 2010-07-14 14:19:17.363
39 System Administrator 2010-03-12 14:53:49.330 2010-07-14 14:19:17.363
39 System Administrator 2010-03-12 15:05:12.927 2010-07-14 14:19:17.363
39 System Administrator 2010-03-15 10:37:22.473 2010-07-14 14:19:17.363
39 System Administrator 2010-03-24 12:30:09.737 2010-07-14 14:19:17.363
39 System Administrator 2010-03-24 12:53:09.553 2010-07-14 14:19:17.363
39 System Administrator 2010-03-24 16:45:43.907 2010-07-14 14:19:17.363
39 System Administrator 2010-03-29 10:54:02.623 2010-07-14 14:19:17.363
39 System Administrator 2010-03-29 10:56:38.923 2010-07-14 14:19:17.363
39 System Administrator 2010-03-29 12:22:23.257 2010-07-14 14:19:17.363
39 System Administrator 2010-03-29 14:58:14.173 2010-07-14 14:19:17.363
39 System Administrator 2010-03-30 09:06:21.303 2010-07-14 14:19:17.363
39 System Administrator 2010-03-30 10:31:51.920 2010-07-14 14:19:17.363
39 System Administrator 2010-04-02 08:26:28.057 2010-07-14 14:19:17.363
39 System Administrator 2010-04-02 14:04:33.250 2010-07-14 14:19:17.363
39 System Administrator 2010-04-07 10:50:51.160 2010-07-14 14:19:17.363
39 System Administrator 2010-04-08 16:18:25.563 2010-07-14 14:19:17.363
39 System Administrator 2010-04-13 08:55:08.830 2010-07-14 14:19:17.363
39 System Administrator 2010-04-13 08:59:51.823 2010-07-14 14:19:17.363
39 System Administrator 2010-04-13 09:02:47.860 2010-07-14 14:19:17.363
39 System Administrator 2010-04-13 09:24:57.580 2010-07-14 14:19:17.363
39 System Administrator 2010-04-13 10:05:06.673 2010-07-14 14:19:17.363
39 System Administrator 2010-04-13 16:56:18.913 2010-07-14 14:19:17.363
39 System Administrator 2010-04-15 16:23:58.300 2010-07-14 14:19:17.363
39 System Administrator 2010-04-16 10:42:11.200 2010-07-14 14:19:17.363
39 System Administrator 2010-04-18 15:53:08.657 2010-07-14 14:19:17.363
39 System Administrator 2010-04-19 17:34:21.057 2010-07-14 14:19:17.363
39 System Administrator 2010-04-20 12:43:14.410 2010-07-14 14:19:17.363
39 System Administrator 2010-04-20 14:48:37.100 2010-07-14 14:19:17.363
39 System Administrator 2010-04-21 10:48:55.180 2010-07-14 14:19:17.363
39 System Administrator 2010-04-22 16:05:56.227 2010-07-14 14:19:17.363
39 System Administrator 2010-04-22 16:06:51.570 2010-07-14 14:19:17.363
39 System Administrator 2010-04-27 09:06:24.007 2010-07-14 14:19:17.363
39 System Administrator 2010-04-28 08:34:40.810 2010-07-14 14:19:17.363
39 System Administrator 2010-04-28 08:38:11.330 2010-07-14 14:19:17.363
39 System Administrator 2010-04-28 08:42:06.460 2010-07-14 14:19:17.363
39 System Administrator 2010-04-28 08:56:25.890 2010-07-14 14:19:17.363
39 System Administrator 2010-04-28 09:02:46.133 2010-07-14 14:19:17.363
39 System Administrator 2010-04-28 09:09:12.953 2010-07-14 14:19:17.363
39 System Administrator 2010-04-28 22:22:01.427 2010-07-14 14:19:17.363
39 System Administrator 2010-04-29 14:06:33.297 2010-07-14 14:19:17.363
39 System Administrator 2010-04-29 14:18:18.750 2010-07-14 14:19:17.363
39 System Administrator 2010-05-03 13:34:41.047 2010-07-14 14:19:17.363
39 System Administrator 2010-05-04 08:50:50.183 2010-07-14 14:19:17.363
39 System Administrator 2010-05-04 08:58:26.180 2010-07-14 14:19:17.363
39 System Administrator 2010-05-04 09:05:14.753 2010-07-14 14:19:17.363
39 System Administrator 2010-05-05 09:36:12.397 2010-07-14 14:19:17.363
39 System Administrator 2010-05-06 12:28:36.227 2010-07-14 14:19:17.363
39 System Administrator 2010-05-07 14:47:46.590 2010-07-14 14:19:17.363
39 System Administrator 2010-05-11 08:36:42.040 2010-07-14 14:19:17.363
39 System Administrator 2010-05-11 09:26:09.523 2010-07-14 14:19:17.363
39 System Administrator 2010-05-11 10:00:03.277 2010-07-14 14:19:17.363
39 System Administrator 2010-05-13 15:28:49.223 2010-07-14 14:19:17.363
39 System Administrator 2010-05-17 07:44:43.850 2010-07-14 14:19:17.363
39 System Administrator 2010-05-17 07:47:17.230 2010-07-14 14:19:17.363
39 System Administrator 2010-05-17 08:08:35.027 2010-07-14 14:19:17.363
39 System Administrator 2010-05-17 09:24:05.797 2010-07-14 14:19:17.363
39 System Administrator 2010-05-21 13:36:06.353 2010-07-14 14:19:17.363
39 System Administrator 2010-06-25 10:36:19.523 2010-07-14 14:19:17.363
39 System Administrator 2010-06-29 10:11:23.150 2010-07-14 14:19:17.363
39 System Administrator 2010-07-14 09:38:40.160 2010-07-14 14:19:17.363
39 System Administrator 2010-07-14 14:19:17.363 2010-07-14 14:19:17.363
39 System Administrator 2010-07-19 15:48:03.853 2010-07-14 14:19:17.363
39 System Administrator 2010-07-29 14:22:21.500 2010-07-14 14:19:17.363
39 System Administrator 2010-07-30 11:08:09.407 2010-07-14 14:19:17.363
39 System Administrator 2010-07-30 13:30:17.030 2010-07-14 14:19:17.363
39 System Administrator 2010-08-06 09:38:56.843 2010-07-14 14:19:17.363
39 System Administrator 2010-08-09 10:24:24.363 2010-07-14 14:19:17.363
39 System Administrator 2010-08-09 13:19:03.540 2010-07-14 14:19:17.363
39 System Administrator 2010-08-10 13:52:41.837 2010-07-14 14:19:17.363
40 Emdad Khondaker 2008-10-24 09:20:44.393 2008-10-24 09:31:27.500
40 Emdad Khondaker 2008-10-24 09:31:27.500 2008-10-24 09:31:27.500
41 Jane Doe 2009-03-06 07:49:55.920 2010-04-28 08:31:24.647
41 Jane Doe 2010-04-28 08:31:24.647 2010-04-28 08:31:24.647
42 Laura Mathis 2009-07-27 17:44:23.453 2009-09-28 10:22:16.073
42 Laura Mathis 2009-07-28 18:20:32.490 2009-09-28 10:22:16.073
42 Laura Mathis 2009-08-03 10:02:55.523 2009-09-28 10:22:16.073
42 Laura Mathis 2009-08-03 11:45:43.530 2009-09-28 10:22:16.073
42 Laura Mathis 2009-08-14 12:31:56.440 2009-09-28 10:22:16.073
42 Laura Mathis 2009-08-18 15:54:30.093 2009-09-28 10:22:16.073
42 Laura Mathis 2009-08-18 17:40:39.273 2009-09-28 10:22:16.073
42 Laura Mathis 2009-08-21 11:10:05.883 2009-09-28 10:22:16.073
42 Laura Mathis 2009-08-28 17:30:35.900 2009-09-28 10:22:16.073
42 Laura Mathis 2009-08-31 15:00:42.433 2009-09-28 10:22:16.073
42 Laura Mathis 2009-08-31 15:19:29.887 2009-09-28 10:22:16.073
42 Laura Mathis 2009-08-31 16:46:07.183 2009-09-28 10:22:16.073
42 Laura Mathis 2009-09-01 09:24:08.740 2009-09-28 10:22:16.073
42 Laura Mathis 2009-09-01 09:35:45.537 2009-09-28 10:22:16.073
42 Laura Mathis 2009-09-02 15:55:42.250 2009-09-28 10:22:16.073
42 Laura Mathis 2009-09-03 10:07:05.640 2009-09-28 10:22:16.073
42 Laura Mathis 2009-09-08 15:32:20.417 2009-09-28 10:22:16.073
42 Laura Mathis 2009-09-09 09:48:53.207 2009-09-28 10:22:16.073
42 Laura Mathis 2009-09-25 09:26:33.530 2009-09-28 10:22:16.073
42 Laura Mathis 2009-09-28 10:22:16.073 2009-09-28 10:22:16.073
44 Krish Rajagopal 2009-09-03 08:42:36.690 2010-04-29 09:10:21.793
44 Krish Rajagopal 2009-09-03 10:12:32.673 2010-04-29 09:10:21.793
44 Krish Rajagopal 2010-04-13 08:59:01.603 2010-04-29 09:10:21.793
44 Krish Rajagopal 2010-04-13 09:03:40.173 2010-04-29 09:10:21.793
44 Krish Rajagopal 2010-04-13 12:19:53.350 2010-04-29 09:10:21.793
44 Krish Rajagopal 2010-04-13 14:17:39.703 2010-04-29 09:10:21.793
44 Krish Rajagopal 2010-04-16 13:09:50.850 2010-04-29 09:10:21.793
44 Krish Rajagopal 2010-04-27 11:14:53.633 2010-04-29 09:10:21.793
44 Krish Rajagopal 2010-04-28 08:37:40.640 2010-04-29 09:10:21.793
44 Krish Rajagopal 2010-04-28 08:39:32.080 2010-04-29 09:10:21.793
44 Krish Rajagopal 2010-04-28 08:47:15.280 2010-04-29 09:10:21.793
44 Krish Rajagopal 2010-04-28 08:59:59.753 2010-04-29 09:10:21.793
44 Krish Rajagopal 2010-04-28 09:04:06.230 2010-04-29 09:10:21.793
44 Krish Rajagopal 2010-04-28 22:09:31.360 2010-04-29 09:10:21.793
44 Krish Rajagopal 2010-04-28 22:22:51.803 2010-04-29 09:10:21.793
44 Krish Rajagopal 2010-04-29 09:10:21.793 2010-04-29 09:10:21.793
45 Kevin Williams 2009-09-03 10:26:36.637 2009-09-16 17:22:35.787
45 Kevin Williams 2009-09-04 14:14:27.427 2009-09-16 17:22:35.787
45 Kevin Williams 2009-09-08 10:47:08.010 2009-09-16 17:22:35.787
45 Kevin Williams 2009-09-09 10:07:39.880 2009-09-16 17:22:35.787
45 Kevin Williams 2009-09-10 15:53:54.213 2009-09-16 17:22:35.787
45 Kevin Williams 2009-09-15 10:34:43.680 2009-09-16 17:22:35.787
45 Kevin Williams 2009-09-16 09:40:04.753 2009-09-16 17:22:35.787
45 Kevin Williams 2009-09-16 17:22:35.787 2009-09-16 17:22:35.787
(339 row(s) affected)
Try doing this instead then
SELECT o.UserID, o.FirstName + ' ' + o.LastName As FullName, o.Email as UserEmail, MAX(g.LOGINDATETIME)as LastInactiveDate
FROM dbo.Users o
INNER JOIN dbo.LoginHistory g on o.UserID = g.UserID AND g.LOGINDATETIME NOT BETWEEN DATEADD(d,-35,GETDATE()) AND GETDATE()
Group By o.UserID, o.LastName, o.FirstName
ORDER BY g.LOGINDATETIME DESC
Sorry it didn't attach my snippet
SELECT DISTINCT o.UserID,
o.FirstName + ' ' + o.LastName As FullName,
o.Email as UserEmail,
g.LOGINDATETIME as LastInactiveDate
FROM dbo.Users o
INNER JOIN dbo.LoginHistory g on o.UserID = g.UserID AND g.LOGINDATETIME NOT BETWEEN DATEADD(d,-35,GETDATE()) AND GETDATE()
ORDER BY g.LOGINDATETIME DESC
the below statement returned error:
SELECT o.UserID, o.FirstName + ' ' + o.LastName As FullName, o.Email as UserEmail, MAX(g.LOGINDATETIME)as LastInactiveDate
FROM dbo.Users o
INNER JOIN dbo.LoginHistory g on o.UserID = g.UserID AND g.LOGINDATETIME NOT BETWEEN DATEADD(d,-35,GETDATE()) AND GETDATE()
Group By o.UserID, o.LastName, o.FirstName
ORDER BY g.LOGINDATETIME DESC
Msg 8120, Level 16, State 1, Line 1
Column 'dbo.Users.Email' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
while your last code snipped returned values
The last code snipped returns values, but it returns all records greater than 35 days from current date.
SELECT o.UserID, o.FirstName + ' ' + o.LastName As FullName, o.Email as UserEmail, MAX(g.LOGINDATETIME)as LastInactiveDate
FROM dbo.Users o
INNER JOIN dbo.LoginHistory g on o.UserID = g.UserID AND g.LOGINDATETIME NOT BETWEEN DATEADD(d,-35,GETDATE()) AND GETDATE()
Group By o.UserID, o.LastName, o.FirstName
ORDER BY g.LOGINDATETIME DESC
Msg 8120, Level 16, State 1, Line 1
Column 'dbo.Users.Email' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
while your last code snipped returned values
The last code snipped returns values, but it returns all records greater than 35 days from current date.
In the second query try:
AND g.LOGINDATETIME NOT BETWEEN DATEADD(d,-35,GETDATE()) AND GETDATE()
Try
AND DATEDIFF(day, GETDATE(), g.LOGINDATETIME) < 35
or in try this query
AND g.LOGINDATETIME NOT BETWEEN DATEADD(d,-35,GETDATE()) AND GETDATE()
Try
AND DATEDIFF(day, GETDATE(), g.LOGINDATETIME) < 35
or in try this query
SELECT o.UserID,
o.FirstName + ' ' + o.LastName As FullName,
o.Email as UserEmail,
MAX(g.LOGINDATETIME)as LastInactiveDate
FROM dbo.Users o
INNER JOIN dbo.LoginHistory g on o.UserID = g.UserID AND g.LOGINDATETIME NOT BETWEEN DATEADD(d,-35,GETDATE()) AND GETDATE()
Group By o.UserID, o.LastName, o.FirstName, o.Email
ORDER BY g.LOGINDATETIME DESC
Your original second query is not correct.
Please try query below.
Please try query below.
SELECT o.UserID, o.FirstName + ' ' + o.LastName As FullName, o.Email, g.LastLoginDate
FROM dbo.Users o
INNER JOIN (
SELECT UserID, MAX(LOGINDATETIME) AS LastLoginDate
FROM dbo.LoginHistory
GROUP BY UserID
HAVING MAX(LOGINDATETIME) < DATEADD(DAY, -35, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
) AS g on o.UserID = g.UserID
--ORDER BY FullName
Sorry for being so abrupt before, was very busy.
The problem with this query:
SELECT UserID, MAX(LOGINDATETIME)as InactiveDates
FROM dbo.LoginHistory
WHERE LOGINDATETIME NOT BETWEEN DATEADD(d,-35,GETDATE()) AND GETDATE() GROUP BY UserID
is that it will pick up even Users who have been active in the last day, because the query looks at *only* activities 35 or more days old, but does not do a check for more recent activity.
The problem with this query:
SELECT UserID, MAX(LOGINDATETIME)as InactiveDates
FROM dbo.LoginHistory
WHERE LOGINDATETIME NOT BETWEEN DATEADD(d,-35,GETDATE()) AND GETDATE() GROUP BY UserID
is that it will pick up even Users who have been active in the last day, because the query looks at *only* activities 35 or more days old, but does not do a check for more recent activity.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
Open in new window