I'm trying to write some SQL that will pull out the most recent login of a user on our site, and then filter that to be in the date range of between 60-90 days ago. Accounts are made inactive after 90 days so we send a warning email (just to add flavour to this query).

I can do both halves of this, getting most recent and getting a range, but am having trouble putting them together.

The query pulls from two tables, as the table with log in dates only has a user ID, so I inner join the table that has user ID and username (email address), tables below and each has more columns than listed.

Table 1 = "time.log"

Table 2 = "users.id"

Current code

SELECT MAX(t.log_time) AS 'Date',
u.username AS 'Email'

FROM time.log t
    INNER JOIN users.id u ON t.user_id = u.user_id   

WHERE t.log_time BETWEEN Convert(DATE,GETDATE()-90) AND Convert(DATE,GETDATE()-60)

GROUP BY u.username

The result I am getting is the top log in date in that range, rather than it being the range of users who last logged in during that period. That makes sense when you read the query through but I can't seem to filter it after the GROUP BY has occurred.

I am using SQL Server Management Studio incase that is relevant - if you can't tell I am a SQL newbie.

I also tried using a second SELECT sequence but I can't get the query to execute at all (it seems to want something after the bracket but I don't know what.

SELECT MAX(t.log_time) AS 'Date',
u.username AS 'Email'

FROM time.log t
    INNER JOIN users.id u ON t.user_id = u.user_id   

INNER JOIN(   
    SELECT MAX(t2.log_time) 
    FROM time.log t2
    INNER JOIN users.id u2 ON t2.user_id = u2.user_id
    GROUP BY u2.username) ON 
    
    t.user_id = t2.user_id AND t.log_time = t2.log_time

Help very much appreciated.

CodePudding user response：

Run this in SSMS and apply to your scenario

declare @t table (the_name varchar, the_date date)
insert into @t
select 'a', '01-jan-2022' union
select 'a', '02-jan-2022' union
select 'b', '03-jan-2022' union
select 'b', '04-jan-2022'

select * from @t

select *, row_number() over(partition by the_name order by the_date desc) as the_number 
from @t

select * from (
    select *, row_number() over(partition by the_name order by the_date desc) as the_number 
    from @t
) z
where the_number = 1
and the_date between '01-jan-2022' and '02-jan-2022'

CodePudding user response：

You can simply add having clause to check if the maximum date is contained in the desired range.

Create Schema [time]
Create Table [time].[log] (log_time Date, [user_id] Int, [application] VarChar(100))

Create Schema [users]
Create Table [users].[id] ([user_id] Int, username VarChar(100), name VarChar(100))

Insert Into [users].[id] ([user_id], username, name)
Values (123, '[email protected]', 'John Smith'),
       (124, '[email protected]', 'Mary Cole')

Insert Into [time].[log] (log_time, [user_id], [application])
Values ('20211201', 123, 'web'),
       ('20211210', 123, 'web'),
       ('20211215', 123, 'web'),
       ('20220201', 124, 'web')

Declare @Date_From Date = Convert(DATE,GETDATE()-90)
Declare @Date_To Date = Convert(DATE,GETDATE()-60)

Select Max(t.log_time) As 'Date', Max(u.name) As 'Name', Max(u.username) As 'Email'
From [time].[log] t Inner Join users.id u On (t.[user_id] = u.[user_id])   
Group by u.[user_id]
Having Max(t.log_time) Between @Date_From And @Date_To

dbfiddle