[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Help with inner join and subquery

Posted on 2010-08-20
12
Medium Priority
?
384 Views
Last Modified: 2012-05-10
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:



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)

Open in new window

0
Comment
Question by:harris_jribaudo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33486001
something like:

SELECT o.UserID, o.FirstName +  ' ' + o.LastName As FullName, o.Email, g.LoginDateTime 
, ( select max(i.LOGINDATETIME) from dbo.LoginHistory i where i.userID = g.UserID AND LOGINDATETIME
NOT BETWEEN DATEADD(d,-35,GETDATE()) AND GETDATE() ) MaxLogin
FROM dbo.Users o
INNER JOIN dbo.LoginHistory g on o.UserID = g.UserID

Open in new window

0
 
LVL 2

Expert Comment

by:MatrixDweller
ID: 33486145
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

Open in new window

0
 

Author Comment

by:harris_jribaudo
ID: 33486563
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.


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)

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 2

Expert Comment

by:MatrixDweller
ID: 33486600
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

Open in new window

0
 
LVL 2

Expert Comment

by:MatrixDweller
ID: 33486663
or this
0
 
LVL 2

Expert Comment

by:MatrixDweller
ID: 33486684
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

Open in new window

0
 

Author Comment

by:harris_jribaudo
ID: 33487624
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.



0
 
LVL 2

Expert Comment

by:MatrixDweller
ID: 33487772
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
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

Open in new window

0
 
LVL 2

Expert Comment

by:MatrixDweller
ID: 33487785
Sorry that should have been

AND DATEDIFF(day, g.LOGINDATETIME, GETDATE()) > 35

 ...fat finger Friday
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 300 total points
ID: 33489066
Your original second query is not correct.

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

Open in new window

0
 

Author Comment

by:harris_jribaudo
ID: 33500803
ScottPletcher,

The last query worked successfully. Thanks for the updated query.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 33502424
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.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question