CODEWITHSUNDEEP
sqlsql-servertransactionstimestamp
Glews
Glews

Reputation: 87

Select rows from a table where a timestamp has not been made since the past 60 days

I'm working on a project in sql managment studio with a sql server 2012 and I would like to get a list with user numbers whom hasn't made a transaction in the last 60 days. The data is from 2 tables (user and transaction) where one table hold the user number and user id and the other table holds the transaction timestamp and user id. The solutions i have right now is:

SELECT a.usernumber
FROM [user] a left join [transaction] b on a.id = b.user_id 
WHERE b.timestamp <= (SELECT getdate()-60) and a.usernumber is not null

The problem is that now it will return all users whom made a transaction more than 60 days ago, but they can also have made a transaction in the last 60 days. So what is a good solution for this problem?

Upvotes: 0

Views: 253

Answers (2)

IVNSTN
IVNSTN

Reputation: 9318

There is no need to prefix getdate() call with select. But it's better to compute arguments which don't depend on every row before select statement. Your goal may be defined with other words: Show me users for whom there is no transaction newer than 60 days old.

Let's translate it to sql statements straightforward:

declare @oldestdate datetime

set @oldestdate = dateadd(dd, -60, getdate())

select u.username
from [user] u
where not exists
  (
    select 1 
    from [transaction] t
    where t.user_id = u.user_id
      and t.timestamp > @oldestdate
  )

Upvotes: 0

Andrey Korneyev
Andrey Korneyev

Reputation: 26876

You can group your results by usernumber, calculate max(b.timestamp) and select only records having latest timestamp before your needed date:

select a.usernumber
from [user] a 
    left join [transaction] b on a.id = b.user_id 
where a.usernumber is not null
group by a.usernumber
having max(b.timestamp) <= (SELECT getdate()-60)

Upvotes: 2

Related Questions