I'm trying to determine an id's status based on if they have had any activity in the past 3 months.
My data is a log of all activity and looks like:
I want to be able to structure my data so it looks like the following:
id LogIn Status
1 01Jan11 Active
1 03Jan11 Active
1 04Mar11 Active
2 25Dec10 Inactive
2 27Jan11 Inactive
I think i need a function that checks whether or not the id has a Login date in the past 3 months, if yes, then status for id is set to Active.
I'm pretty sure it has to be a separate dataset to do the calculations then do a Merge to the main file.
case when intnx('day',date(),-90) >= t2.lastlogin then 'Active' else 'Inactive' as Current_Status
from work.logins t1 inner join (select id, max(login) as LastLogin from work.logins group by id) t2 on t1.id=t2.id;
I'm trying to make DBaily's statements work, but havent gotten it to work yet.
I'm practically trying to figure out how long since todays date was their last Login, so if its been longer than 90 days than their status would be inactive across all rows with that persons id.