Solved

Help with inner join and subquery

Posted on 2010-08-20
12
322 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
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 2

Expert Comment

by:MatrixDweller
Comment Utility
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
Comment Utility
or this
0
 
LVL 2

Expert Comment

by:MatrixDweller
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:harris_jribaudo
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry that should have been

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

 ...fat finger Friday
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 75 total points
Comment Utility
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
Comment Utility
ScottPletcher,

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

Expert Comment

by:ScottPletcher
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now