SHill SHill - 4 months ago 9
SQL Question

I am trying to write a query to track use of terminals. I want to show all terminals that have not had activity for 1 year

I am trying to write a query to track the use of PC terminals. I want to show all PC terminals that have not had activity for 1 year. This is the query that I am trying to use and it returns 0 results even though I know I have terminals that have not been used. The dates are in as date/time formats (2016-06-22 14:38:12.000)

select distinct
t.Terminalid,
t.TerminalName
from
Terminal as t
Inner Join
Pos_Payments as p
on p.TerminalId = t.TerminalId
where
TransactionDateTime < '7/29/2015' and TransactionDateTime !> '7/30/2015'
order by t.TerminalID

Answer

Use group by and max():

select t.Terminalid, t.TerminalName
from Terminal t Inner Join 
     Pos_Payments p 
     on p.TerminalId = t.TerminalId
group by t.Terminalid, t.TerminalName
having max(TransactionDateTime) < '2015-07-29'
order by t.TerminalID ;

Note the use of the ISO standard YYYY-MM-DD date format.

If you want this based on the current date:

having max(TransactionDateTime) < dateadd(year, -1, getdate())

(And you might want to cast that to date to get rid of the time component.)

Comments