Lat Login details check with sql query

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

Hi frnds,
I am Updating User last Login details in User Table. I need to get user last 10 days whoever not logged in.

UserId           UserName           LastLoginDate
  1               Rajesh             10-Dec-2013
  2               Kumar              08-Dec-2013
  3               Vijay              08-Dec-2013
  4               Karthi             09-Dec-2013
  5               Prabhu             11-Dec-2013

I need output as follows
for example current date is 12-Dec-2013 for upto last 10 days

User_Not_Login_Day_Count      Last_Login_User_Count
          1                          1
          2                          1
          3                          1
          4                          2
          5                          0
          .                          .
          .                          .
          .                          .
          10                         0

Posted 11-Dec-13 23:54pm

itsureshuk

Updated 12-Dec-13 0:22am

Nelek

v3

Add a Solution

Comments

Varsha Ramnani 12-Dec-13 5:56am

What have you tried so far....

ZurdoDev 12-Dec-13 8:17am

Where are you stuck?

Corporal Agarn 12-Dec-13 13:23pm

Have you tried DATEADD?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 10434230 · Answer 1 · 2013-12-13T02:23:00

Below is the SQL:

C#

DECLARE @MyCounter int;
SET @MyCounter = 1;

IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N'tempdb..#LastLoginDetailsTempTable')
)
BEGIN
DROP TABLE #LastLoginDetailsTempTable
END

CREATE TABLE #LastLoginDetailsTempTable(
User_Not_Login_Day_Count int, Last_Login_User_Count int)

WHILE @MyCounter < 11
BEGIN
INSERT INTO #LastLoginDetailsTempTable(User_Not_Login_Day_Count, Last_Login_User_Count)
	SELECT @MyCounter as User_Not_Login_Day_Count, COUNT(UserId) as Last_Login_User_Count
	FROM LoginDetails AS T1
	WHERE 
	  (SELECT MIN(DATEDIFF(day,LastLoginDate, GETDATE())) 
	   FROM LoginDetails AS T2
	   WHERE T2.UserID=T1.UserId) = @MyCounter
SET @MyCounter = @MyCounter + 1;
END

select * from #LastLoginDetailsTempTable

Maciej Los · Answer 2 · 2013-12-13T03:05:00

All you need to do is to create query to fetch users which are logged by last 10 days and exclude them from entire list of users.

There are few ways to achieve that:

using NOT IN (T-SQL)[^] clause
using NOT EXISTS[^] (Subqueries with EXISTS[^]) statement
using EXCEPT and INTERSECT[^] statement
using proper JOIN[^] stetement

SQL

SELECT u.*
FROM MyUsers AS u
WHERE UserID NOT IN (
    SELECT UserID
    FROM Logs
    WHERE LogDate BETWEEN DATEADD(d,-10, GETDATE()) AND GETDATE()
)

For further information, please see: Visual Representation of SQL Joins[^]

Lat Login details check with sql query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0