Date query for report to find active / inactive users logging in within last 6 months

Posted on 2007-11-29
Last Modified: 2010-04-21
I have a DotNet site that I am creating some reports for with SQL Reporting Services 2005 and I need to build a query that will show me the following:

1. Active Users (logging into site within last 6 months)
2. Inactive Users (have not logged into site within last 6 months)

This is from the standard aspnet_Users log table.  The date field to query is called "LastActivityDate".

TIA for any expert knowledge!
Question by:dstjohnjr
  • 2
  • 2
LVL 25

Expert Comment

ID: 20378759
select * from aspnet_Users
where DateAdd(m, -6, getdate() <= LastActivityDate

select * from aspnet_Users
where DateAdd(m, -6, getdate() > LastActivityDate

Author Comment

ID: 20378861
Hmmm.... That doesn't appear to be it.

select * from aspnet_Users
where DateAdd(m, -6, getdate() <= LastActivityDate)

First off, I needed to add a closing paren.

Next, I'm getting this error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '<'.

Is that the right operator for Less than or equal to?

Thanks for your help!
LVL 25

Accepted Solution

imitchie earned 500 total points
ID: 20379069
crikey! my mind is getting blinded by the brackets... sorry

select * from aspnet_Users
where DateAdd(m, -6, getdate()) <= LastActivityDate

select * from aspnet_Users
where DateAdd(m, -6, getdate()) > LastActivityDate

Author Closing Comment

ID: 31411834
Sorry this is so late.  Thanks for the excellent expert help!

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Error in WHERE Clause 5 42
Counting connections to SQL Server through C# 3 29
SQL Server stored proc 2 13
TSQL - How to perform an IF… SELECT? 2 0
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

896 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

11 Experts available now in Live!

Get 1:1 Help Now