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)
Terminal as t
Pos_Payments as p
on p.TerminalId = t.TerminalId
TransactionDateTime < '7/29/2015' and TransactionDateTime !> '7/30/2015'
order by t.TerminalID
group by and
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.)