jamppi
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the grade. Good luck with your project. -Jim
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