UserID     Name
1          Paul
2          John

UserID     LoginDate
1          22/04/2012
1          25/09/2011
1          03/03/2011
2          27/02/2011
2          20/03/2011

UserID      Name     LoginDate
2           John     20/03/2011

uerid     logindate
1         2011/03/03
2         2011/03/20

Select
	US.userID,
	userTitle,
	userFirstName,
	userLastName,
	userEmail,
	MAX(ULO.userLoginDate) AS userLoginDate
from
	dbo.tbl_Users US
left join
	dbo.tbl_UserLogins AS ULO ON ULO.userLogin_userID = US.userID
WHERE 
	userLogin_magJournalCode = 'GP'
AND
	US.userID NOT IN 
	(
		SELECT DISTINCT
			U.userID
		FROM 
			dbo.tbl_Users U
		left join
			dbo.tbl_UserLogins AS UL ON UL.userLogin_userID = U.userID
		WHERE
			UL.userLoginDate >= dateadd(month, -12, getdate())
	)
GROUP BY
	US.userID,
	userTitle,
	userFirstName,
	userLastName,
	userEmail,
	ULO.userLoginDate

Select
	US.userID,
	userTitle,
	userFirstName,
	userLastName,
	userEmail,
	--ULO.userLoginDate
	MAX(cast(ULO.userLoginDate as datetime)) AS userLoginDate
	
from
	dbo.tbl_Users US
inner join
	dbo.tbl_UserLogins AS ULO ON ULO.userLogin_userID = US.userID
WHERE 
	ULO.userLogin_magJournalCode = 'GP'
AND
	(US.useremail NOT LIKE '%haynet%' AND US.useremail NOT LIKE '%haymarket%')
AND
	US.userID NOT IN 
	(
		SELECT DISTINCT
			U.userID
		FROM 
			dbo.tbl_Users U
		inner join
			dbo.tbl_UserLogins AS UL ON UL.userLogin_userID = U.userID
		WHERE
			UL.userLoginDate >= dateadd(month, -12, getdate())
	)
GROUP BY
	US.userID,
	userTitle,
	userFirstName,
	userLastName,
	userEmail
ORDER BY
	userLastName,
	userFirstName

SELECT
	US.userID,
	US.userTitle,
	US.userFirstName,
	US.userLastName,
	US.userEmail,
	ULO.userLastLoginDate
FROM
	dbo.tbl_Users US
LEFT JOIN
	(
		SELECT	userLogin_userID, MAX(userLoginDate) AS userLastLoginDate
		FROM	dbo.tbl_UserLogins 
		WHERE	userLogin_magJournalCode = 'GP'
		GROUP BY userLogin_userID
	) AS ULO 	ON ULO.userLogin_userID = US.userID
WHERE 	
	(US.useremail NOT LIKE '%haynet%' AND US.useremail NOT LIKE '%haymarket%')
AND	
	(
		ULO.userLogin_userID	IS NULL
	OR	ULO.userLastLoginDate	< dateadd(month, -12, getdate())
	)
ORDER BY
	US.userLastName,
	US.userFirstName

;WITH cteSource(UserID, LoginDate)
AS (
	SELECT		UserID,
			MAX(LoginDate) AS LoginDate
	FROM		(
				SELECT	UserID,
					LoginDate
				FROM	dbo.Logins

				UNION ALL

				SELECT	UserID,
					NULL AS LoginDate
				FROM	dbo.Users
			) AS d
	GROUP BY	UserID
	HAVING		MAX(LoginDate) < DATEADD(YEAR, -1, GETDATE())
			OR MAX(LoginDate) IS NULL
)
SELECT		c.UserID,
		u.Name,
		c.LoginDate
FROM		cteSource AS c
INNER JOIN	dbo.Users AS u ON u.UserID = c.UserID

;WITH cteSource(UserID, LoginDate)
AS (
	SELECT		UserID,
			MAX(LoginDate) AS LoginDate
	FROM		dbo.Logins
	GROUP BY	UserID
	HAVING		MAX(LoginDate) < DATEADD(YEAR, -1, GETDATE())
			OR MAX(LoginDate) IS NULL
)
SELECT		c.UserID,
		u.Name,
		c.LoginDate
FROM		cteSource AS c
INNER JOIN	dbo.Users AS u ON u.UserID = c.UserID

quote:

---

Originally posted by khtan

SELECT
	US.userID,
	US.userTitle,
	US.userFirstName,
	US.userLastName,
	US.userEmail,
	ULO.userLastLoginDate
FROM
	dbo.tbl_Users US
LEFT JOIN
	(
		SELECT	userLogin_userID, MAX(userLoginDate) AS userLastLoginDate
		FROM	dbo.tbl_UserLogins 
		WHERE	userLogin_magJournalCode = 'GP'
		GROUP BY userLogin_userID
	) AS ULO 	ON ULO.userLogin_userID = US.userID
WHERE 	
	(US.useremail NOT LIKE '%haynet%' AND US.useremail NOT LIKE '%haymarket%')
AND	
	(
		ULO.userLogin_userID	IS NULL
	OR	ULO.userLastLoginDate	< dateadd(month, -12, getdate())
	)
ORDER BY
	US.userLastName,
	US.userFirstName

---

KH
Time is always against us

---

;WITH cteSource(UserID, LoginDate)
AS (
	SELECT		userLogin_userID,
			MAX(userLoginDate) AS userLoginDate
	FROM		dbo.tbl_UserLogins
	GROUP BY	userLogin_userID
	HAVING		MAX(userLoginDate) < DATEADD(YEAR, -1, GETDATE())
--			OR MAX(userLoginDate) IS NULL
)
SELECT		c.UserID,
		u.userTitle,
		u.userFirstName,
		u.userLastName,
		u.userEmail,
		c.LoginDate
FROM		cteSource AS c
INNER JOIN	dbo.tbl_Users AS u ON u.UserID = c.UserID

;WITH cteSource(UserID, LoginDate, JournalCode)
AS (
	SELECT		userLogin_userID,
				MAX(userLoginDate) AS userLoginDate,
				userLogin_magJournalCode
	FROM		dbo.tbl_UserLogins
	GROUP BY	userLogin_userID,
	userLogin_magJournalCode
	
	HAVING		MAX(userLoginDate) < DATEADD(YEAR, -1, GETDATE())
	and
		userLogin_magJournalCode = 'GP'
--			OR MAX(userLoginDate) IS NULL
)
SELECT		c.UserID,
			u.userTitle,
			u.userFirstName,
			u.userLastName,
			u.userEmail,
			c.LoginDate
FROM		cteSource AS c
INNER JOIN	dbo.tbl_Users AS u ON u.UserID = c.UserID
where
useremail NOT LIKE '%haynet%' AND useremail NOT LIKE '%haymarket%'
order by
	userLastname,
	userFirstName

quote:

---

Originally posted by KnooKie

this is very interesting.

where would i specify my WHERE clause(s) in it?

i.e. these two from their respective tables...
tbl_UserLogins: userLogin_magJournalCode = 'GP'
tbl_users: useremail NOT LIKE '%haynet%' AND useremail NOT LIKE '%haymarket%'


modified for my field names...

;WITH cteSource(UserID, LoginDate)
AS (
	SELECT		userLogin_userID,
			MAX(userLoginDate) AS userLoginDate
	FROM		dbo.tbl_UserLogins
	GROUP BY	userLogin_userID
	HAVING		MAX(userLoginDate) < DATEADD(YEAR, -1, GETDATE())
--			OR MAX(userLoginDate) IS NULL
)
SELECT		c.UserID,
		u.userTitle,
		u.userFirstName,
		u.userLastName,
		u.userEmail,
		c.LoginDate
FROM		cteSource AS c
INNER JOIN	dbo.tbl_Users AS u ON u.UserID = c.UserID

====
Paul

---

quote:

---

Originally posted by KnooKie

i wonder if the slightly higher count is to do with the difference between these...

DATEADD(YEAR, -1, GETDATE()

dateadd(month, -12, getdate()

====
Paul

---