# Users Not logged in within last 6 months

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

# Users Not logged in within last 6 months

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-04-18 : 07:33:07

I have this query that happily gives me users who have logged in within the last 6 months.

How can i now do a query to show users who have NOT logged in within the last 6 months (i.e. userLoginDate will be NULL) ?


USE HBPL_Users

SELECT DISTINCT
	U.userID,
	userTitle,
	userFirstName,
	userLastName,
	userEmail,
	userLoginDate,
	prod_code,
	prod_desc
FROM 
	wdis.f_prod AS Wprod
inner join
	wdis.f_sub_prod AS Wsp ON Wsp.f_prod_id = Wprod.f_prod_id
inner join
	wdis.f_sub_reader_num AS Wsr ON Wsp.sub_ref = Wsr.sub_ref
inner join
	wdis.f_user_reader_num AS Wurn ON Wsr.reader_num = Wurn.reader_num
inner join
	dbo.tbl_Users AS U ON U.userID = Wurn.userID
left join
	dbo.tbl_UserLogins AS UL ON UL.userLogin_userID = U.userID

WHERE 
	(prod_code = 'HOO' OR prod_code = 'HOW' OR prod_code ='HOP' OR prod_Code = 'HOT') 

	AND userLoginDate >= dateadd(month, -6, getdate())

ORDER BY 
	userEmail ASC, 
	userLastName ASC, 
	userFirstName ASC,
	userLoginDate ASC

====
Paul

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-18 : 07:39:35

Assuming that your tbl_Users has all the users, just do a query to find all the others as in:

select userID from dbo.tbl_Users
EXCEPT
-- Your original query below
SELECT DISTINCT
	U.userID
FROM 
	wdis.f_prod AS Wprod
inner join
	wdis.f_sub_prod AS Wsp ON Wsp.f_prod_id = Wprod.f_prod_id
inner join
	wdis.f_sub_reader_num AS Wsr ON Wsp.sub_ref = Wsr.sub_ref
inner join
	wdis.f_user_reader_num AS Wurn ON Wsr.reader_num = Wurn.reader_num
inner join
	dbo.tbl_Users AS U ON U.userID = Wurn.userID
left join
	dbo.tbl_UserLogins AS UL ON UL.userLogin_userID = U.userID

WHERE 
	(prod_code = 'HOO' OR prod_code = 'HOW' OR prod_code ='HOP' OR prod_Code = 'HOT') 

	AND userLoginDate >= dateadd(month, -6, getdate())

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-04-18 : 07:55:55

Thanks for the reply, yes tbl_Users has all the users, but i now get...

Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'FROM'.
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'FROM'.


SELECT
	userID

FROM dbo.tbl_Users

EXCEPT

SELECT DISTINCT
	U.userID
FROM 
	wdis.f_prod AS Wprod
inner join
	wdis.f_sub_prod AS Wsp ON Wsp.f_prod_id = Wprod.f_prod_id
inner join
	wdis.f_sub_reader_num AS Wsr ON Wsp.sub_ref = Wsr.sub_ref
inner join
	wdis.f_user_reader_num AS Wurn ON Wsr.reader_num = Wurn.reader_num
inner join
	dbo.tbl_Users AS U ON U.userID = Wurn.userID
left join
	dbo.tbl_UserLogins AS UL ON UL.userLogin_userID = U.userID

WHERE 
	(prod_code = 'HOO' OR prod_code = 'HOW' OR prod_code ='HOP' OR prod_Code = 'HOT') 

	AND userLoginDate >= dateadd(month, -6, getdate())

====
Paul

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-18 : 08:25:27

What version of SQL Server are you using? In any case, do the following in order:

1. Run this query - this is your original query except, I have removed the order by clause. This should run without any errors.

SELECT DISTINCT
	U.userID
FROM 
	wdis.f_prod AS Wprod
inner join
	wdis.f_sub_prod AS Wsp ON Wsp.f_prod_id = Wprod.f_prod_id
inner join
	wdis.f_sub_reader_num AS Wsr ON Wsp.sub_ref = Wsr.sub_ref
inner join
	wdis.f_user_reader_num AS Wurn ON Wsr.reader_num = Wurn.reader_num
inner join
	dbo.tbl_Users AS U ON U.userID = Wurn.userID
left join
	dbo.tbl_UserLogins AS UL ON UL.userLogin_userID = U.userID

WHERE 
	(prod_code = 'HOO' OR prod_code = 'HOW' OR prod_code ='HOP' OR prod_Code = 'HOT') 

	AND userLoginDate >= dateadd(month, -6, getdate())

2. Now run this code. The parts shown in red are changes from step 1:

SELECT UserId FROM dbo.tbl_Users
WHERE UserId NOT IN (
SELECT DISTINCT
	U.userID
FROM 
	wdis.f_prod AS Wprod
inner join
	wdis.f_sub_prod AS Wsp ON Wsp.f_prod_id = Wprod.f_prod_id
inner join
	wdis.f_sub_reader_num AS Wsr ON Wsp.sub_ref = Wsr.sub_ref
inner join
	wdis.f_user_reader_num AS Wurn ON Wsr.reader_num = Wurn.reader_num
inner join
	dbo.tbl_Users AS U ON U.userID = Wurn.userID
left join
	dbo.tbl_UserLogins AS UL ON UL.userLogin_userID = U.userID

WHERE 
	(prod_code = 'HOO' OR prod_code = 'HOW' OR prod_code ='HOP' OR prod_Code = 'HOT') 

	AND userLoginDate >= dateadd(month, -6, getdate())
)

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-04-18 : 08:52:26

SQL Server 2005.

Thanks i'll give that a go - it seemed to be my DISTINCT in the previous one that was causing a problem presumably because i only had it in one SELECT statement

====
Paul

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-18 : 09:43:40

[code]SELECT userLogin_userID
FROM dbo.tbl_UserLogins
WHERE userLoginDate IS NULL
OR userLoginDate < DATEADD(MONTH, -6, GETDATE())[/code]

N 56°04'39.26"
E 12°55'05.63"

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-04-18 : 10:28:54

Perfect Thanks

====
Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources