<div>
try this<br />
<br />
select distinct client_id from ctrack <br />
where client_id not in (select distinct client_id from ctrack where datediff(day, Last_Login_Time, getdate()) &lt;&nbsp;180)<br />

</div>


<div>
Looks kind of over-complicated - and wrong ...<br />
<br />
select client_id from ctrack where getdate()-Last_Login_Time &gt;&nbsp;180;<br />
<br />
For getdate() you can use another date, if you like.
</div>


<div>
Let me explain:<br />
<br />
select distinct client_id from ctrack<br />
where client_id not in (select distinct client_id from ctrack where datediff(day, Last_Login_Time, getdate()) &lt;&nbsp;180)<br />
<br />
-&gt; Show list of clients that has login previously, but didn't login for the pass 180 days.<br />
<br />
select client_id from ctrack where getdate()-Last_Login_Time &gt;&nbsp;180<br />
<br />
-&gt; Shows all clients that login &gt;180 days before<br />
If client A did login yesterday, and has previously login 1 year ago, his name will be appeal. Is that what you want?<br />
<br />
<br />

</div>


<div>
The key is &quot;Last login&quot;, so there is only one record per Client_ID.
</div>


<div>
cpleong99,<br />
<br />
the last time login column will be updated. so there will be no duplicate data. the query need to display the clients that hasn't login for the last 6 months. do your query need a change?
</div>


<div>
Rrooter,<br />
<br />
with your sql i don't get the clients that haven't login at all. they have to be also part. see record 3! it don't display it<br />
<br />
declare @days_back as int<br />
set @days_back = 180<br />
<br />
select client_id <br />
from ctrack <br />
where Last_Login_Time &lt;&nbsp;dateadd(day, -(@days_back ), getdate())<br />
<br />
<br />
client_id &nbsp; &nbsp; &nbsp;Last_Login_Time<br />
1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/8/2007 10:07:43 AM<br />
2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;11/7/2007 9:45:44 AM<br />
3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&lt;null&gt;
</div>


<div>
You could also try this too to check for null:<br />
<br />
declare @days_back as int<br />
set @days_back = 180<br />
<br />
select client_id <br />
from ctrack <br />
where isnull(Last_Login_Time, '1/1/2000') &lt;&nbsp;dateadd(day, -(@days_back ), getdate())
</div>


<div>
<br />
lots of variations on a common theme...<br />
<br />
select client_id, last_login_time<br />
from ctrack<br />
where isnull(last_login_time,'01<wbr />/01/1900')<wbr /> &lt;&nbsp;getdate() - 180
</div>


<div>
<div class="content wysiwyg-content">
hi, i have a table named ctrack in which i keep track of a client's last login time<br />
<br />
i am in need of a query where i can use the number of days like (180) that can pull out the clients that haven't login for the last 6 months<br />
the date count must begin from the actual date (today). if i can put the date from where to begin and how many days afetr i want to view (180) would be fine<br />
<br />
ctrack<br />
<br />
client_id &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Last_Login_Time<br />
1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3/8/2007 10:07:43 AM<br />
2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11/7/2007 9:45:44 AM<br />
3 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;null&gt;<br />
4 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11/7/2007 12:34:12 PM<br />
5 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11/7/2007 12:10:23 PM<br />
6 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11/7/2007 4:35:19 PM<br />
7 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9/16/2005 11:25:39 AM<br />
8 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4/26/2007 2:15:56 PM<br />
9 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10/9/2007 4:37:40 PM<br />
10 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8/6/2003 9:19:52 AM<br />
11 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11/7/2007 8:28:11 AM<br />
12 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10/30/2007 8:14:46 AM<br />
13 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7/28/2003 2:56:47 PM
</div>
</div>


hi, i have a table named ctrack in which i keep track of a client's last login time

i am in need of a query where i can use the number of days like (180) that can pull out the clients that haven't login for the last 6 months
the date count must begin from the actual date (today). if i can put the date from where to begin and how many days afetr i want to view (180) would be fine

ctrack

client_id	Last_Login_Time
1		3/8/2007 10:07:43 AM
2		11/7/2007 9:45:44 AM
3		<null>
4		11/7/2007 12:34:12 PM
5		11/7/2007 12:10:23 PM
6		11/7/2007 4:35:19 PM
7		9/16/2005 11:25:39 AM
8		4/26/2007 2:15:56 PM
9		10/9/2007 4:37:40 PM
10		8/6/2003 9:19:52 AM
11		11/7/2007 8:28:11 AM
12		10/30/2007 8:14:46 AM
13		7/28/2003 2:56:47 PM

6 months (date) query

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.