Filtering Login Dates - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Filtering Login Dates

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-08-15 : 06:36:20

Hi All

I need to find the distinct login counts based on the following login date ranges from today's date...

0-6 months
7-12 months
13-24 months
25-36 months
37-48 months
49+ months

The tricky part (for me anyway) is that i need unique userids per range.

e.g.
i can easily do a distinct count of the 0-6 months with the following...

SELECT DISTINCT u.userid,
                Dateadd(dd, Datediff(dd, 0, ul.userlogindate), 0) AS userLoginDate
FROM   u.f_sub_site ss
       INNER JOIN tbl_users u
               ON u.userid = ss.userid
       INNER JOIN tbl_userlogins ul
               ON u.userid = ul.userlogin_userid
WHERE  ss.magjournalcode IN ( 'GP', 'MM2' )
       AND ul.userlogindate > Dateadd(month, -6, '2012-08-15 00:00:00.000')  -- last 6 months from today i could use getdate() here also to get todays date
ORDER  BY ul.userlogindate DESC

This then gives me approx 10000 distinct userids which is fine.

Now when i do the next range 7-12 months i need to exclude any userids that were in the previous 0-6 month range.
and then subsequently when i do the 13-24 month range i need to exclude any userids that appeared in either the 0-6 or 7-12 month ranges and so on up to 49+

I'm not quite sure how to exclude the userids that have appeared in earlier ranges.

Hope that makes sense!?

Any ideas welcome.

I'm thinking that a WHERE NOT EXISTS might work

====
Paul

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-15 : 07:35:36

[code];WITH cteSource(UserID, LoginDate)
AS (
SELECT DISTINCT u.userid,
DATEADD(DAY, DATEDIFF(DAY, '19000101', ul.userlogindate), '19000101') AS userLoginDate
FROM u.f_sub_site AS ss
INNER JOIN tbl_users AS u ON u.userid = ss.userid
INNER JOIN tbl_userlogins AS ul ON u.userid = ul.userlogin_userid
WHERE ss.magjournalcode IN ('GP', 'MM2')
AND ul.userlogindate >= DATEADD(DAY, DATEDIFF(DAY, '19000701', GETDATE()), '19000101')
), cteGroup(Delta)
(
SELECT CASE
WHEN DATEDIFF(MONTH, LoginDate, GETDATE()) BETWEEN 0 AND 6 THEN '00-06'
WHEN DATEDIFF(MONTH, LoginDate, GETDATE()) BETWEEN 7 AND 12 THEN '07-12'
WHEN DATEDIFF(MONTH, LoginDate, GETDATE()) BETWEEN 13 AND 24 THEN '13-24'
WHEN DATEDIFF(MONTH, LoginDate, GETDATE()) BETWEEN 25 AND 36 THEN '25-36'
WHEN DATEDIFF(MONTH, LoginDate, GETDATE()) BETWEEN 37 AND 48 THEN '37-48'
ELSE '49+'
END + ' months'
FROM cteSource
)
SELECT Delta,
COUNT(*) AS Item
FROM cteGroup
GROUP BY Delta
ORDER BY Delta;[/code]

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

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-08-15 : 07:55:13

Thanks for the reply.

Wasn't expecting anything looking like that.

When i try this it comes back with...

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '('.

====
Paul

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-15 : 08:27:43

With permission of SwePeso ;)

;WITH cteSource(UserID, LoginDate)
AS (
SELECT DISTINCT u.userid,
DATEADD(DAY, DATEDIFF(DAY, '19000101', ul.userlogindate), '19000101') AS userLoginDate
FROM u.f_sub_site AS ss
INNER JOIN tbl_users AS u ON u.userid = ss.userid
INNER JOIN tbl_userlogins AS ul ON u.userid = ul.userlogin_userid
WHERE ss.magjournalcode IN ('GP', 'MM2')
AND ul.userlogindate >= DATEADD(DAY, DATEDIFF(DAY, '19000701', GETDATE()), '19000101')
), cteGroup(Delta)
AS (
SELECT CASE
WHEN DATEDIFF(MONTH, LoginDate, GETDATE()) BETWEEN 0 AND 6 THEN '00-06'
WHEN DATEDIFF(MONTH, LoginDate, GETDATE()) BETWEEN 7 AND 12 THEN '07-12'
WHEN DATEDIFF(MONTH, LoginDate, GETDATE()) BETWEEN 13 AND 24 THEN '13-24'
WHEN DATEDIFF(MONTH, LoginDate, GETDATE()) BETWEEN 25 AND 36 THEN '25-36'
WHEN DATEDIFF(MONTH, LoginDate, GETDATE()) BETWEEN 37 AND 48 THEN '37-48'
ELSE '49+'
END + ' months'
FROM cteSource
)
SELECT Delta,
COUNT(*) AS Item
FROM cteGroup
GROUP BY Delta
ORDER BY Delta;

--------------------------
http://connectsql.blogspot.com/

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-08-15 : 09:08:33

Thanks for the reply

The AS does the trick but it returns only


Delta          Item
00-06 months   92543

and no other month ranges?

====
Paul

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-15 : 09:39:15

[code];WITH cteSource(UserID, LoginDate)
AS (
SELECT DISTINCT u.userid,
DATEADD(DAY, DATEDIFF(DAY, '19000101', ul.userlogindate), '19000101') AS userLoginDate
FROM u.f_sub_site AS ss
INNER JOIN tbl_users AS u ON u.userid = ss.userid
INNER JOIN tbl_userlogins AS ul ON u.userid = ul.userlogin_userid
WHERE ss.magjournalcode IN ('GP', 'MM2')
--AND ul.userlogindate >= DATEADD(DAY, DATEDIFF(DAY, '19000701', GETDATE()), '19000101')
), cteGroup(Delta)
AS (
SELECT CASE
WHEN DATEDIFF(MONTH, LoginDate, GETDATE()) BETWEEN 0 AND 6 THEN '00-06'
WHEN DATEDIFF(MONTH, LoginDate, GETDATE()) BETWEEN 7 AND 12 THEN '07-12'
WHEN DATEDIFF(MONTH, LoginDate, GETDATE()) BETWEEN 13 AND 24 THEN '13-24'
WHEN DATEDIFF(MONTH, LoginDate, GETDATE()) BETWEEN 25 AND 36 THEN '25-36'
WHEN DATEDIFF(MONTH, LoginDate, GETDATE()) BETWEEN 37 AND 48 THEN '37-48'
ELSE '49+'
END + ' months'
FROM cteSource
)
SELECT Delta,
COUNT(*) AS Item
FROM cteGroup
GROUP BY Delta
ORDER BY Delta;[/code]

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

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-08-15 : 11:18:33

Perfect - Thank you both and SwePeso in particular

====
Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources