I have a table Queries that keeps track of every query made to a database with a date_time stamp and the computer name.
There can be 1000s of rows for the same computer, each one with a different date_time stamp.
If the computer has not made a query in 60 days, then it needs to be identified.
I know how to check each record to see if it has been longer than 60 days for that specific query record.
I do not know how to consider all the records for that specific computer to determine if the SMALLEST date_time stamp is greater than 60 days and if so, to show it in the results.
Please forgive as I am new to SQL queries. Thanks.
I tried:
select computer_name
from queries
where date_time > 20240508000000
I only want to show a computer_name IF it does not have a single record that is less than 60 days old - because that means it has not been used for a query in 2 months and needs to be identified.
The above example is wrong in that it will show ALL records that are greater than 60 days.
Without testing, and assuming the timestamp is in data-type datetime or similar, I suggest this. The idea is to get the youngest timestamp for every computer by use of group by (aggregation), and then filter for those whose youngest-timestamp is more than 60 days old compared to now (GETDATE()).
After additional information became available I updated the solution. I do not know the data type that produces values like 202407080000US or 20240508000000, so i use a rather crude way to convert to data type DATE, ignoring time parts. I also don't know, which part of this input is month and which is day, so check that.
;WITH prep AS(
SELECT
computer_name
,date = CONVERT(DATE, LEFT(CONVERT(NVARCHAR(100), date_time), 8))
FROM queries
)
SELECT
computer_name
,youngest = MAX(date)
FROM prep
GROUP BY computer_name
HAVING MAX(date) <= DATEADD(DAY, -60, CONVERT(DATE,GETDATE()))
-- This works, too, but it is better to keep
-- the column as much as possible outside of functions:
--HAVING DATEDIFF(DAY, MAX(date), CONVERT(DATE,GETDATE())) > 60