Link to home
Start Free TrialLog in
Avatar of jamppi
jamppiFlag for Sweden

asked on

sql query, finding inactive users for 1 week and 1 month

Hi!

i need to find inactive users for 1 week and for 1 month from Getdate().

table layouts.

Customer table.
DeviceId, Custname,,,,,,,,,,,,

session table
Sessions_Id, DeviceId, on_date_time, Off_Date_Time, Complete,,,,,,,,,,,,,

need it to output

custname  deviceId
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

for a week:
select custname, deviceId
from Customer t1
left outer join session t2
on t1.device_id = t2.device_id and t2.on_date_time < getdate() - 7

for a month:
select custname, deviceId
from Customer t1
left outer join session t2
on t1.device_id = t2.device_id and t2.on_date_time < dateadd(month, getdate(), -1)

if you want it in a single query:


select custname, deviceId,
 case when t2.device_id is null then 'Never logged in'
         when on_date_time > dateadd(month, getdate(), -1) then 'inactive for more than a month'
         when t2.on_date_time < getdate() - 7 then 'Inactive for more than a week' end
from Customer t1
left outer join session t2
on t1.device_id = t2.device_id
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the grade.  Good luck with your project.  -Jim