0

All I am trying to do is pull the most recent 'Scan' (hence 'LastScan') from each 'location' and then find out if any of them are 2 hours or more 'old'(which would make them overdue).

Select cast(max(entered_date) as int) as 'LastScan', location
FROM housing_activity
WHERE 'LastScan' <= Datediff(HH,-2,GETDATE())
Group by location

This is my error:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'LastScan' to data type int.
SkeerNC
  • 1
  • 1
  • In SqlServer, you should quote identifiers in `[ ]`, or possibly double quotes (`" "`). See https://stackoverflow.com/a/45865263/121309 – Hans Kesting Aug 27 '20 at 06:50
  • 1
    *Never* use single quotes for column names. You are making a mistake that should never happen (if you make that change, you'll get another error that `LastScan` is unknown, but at least that would make sense). – Gordon Linoff Aug 27 '20 at 12:32

1 Answers1

0

try this

; WITH cte AS (
SELECT
  location
  , LastScan = entered_date
  , RN = ROW_NUMBER() OVER (PARTITION BY location ORDER BY entered_date DESC)
FROM housing_activity 
)
SELECT 
  *
FROM cte
WHERE 
   RN = 1
   AND LastScan <= DateAdd(hour,-2,GETDATE())
K4M
  • 1,030
  • 3
  • 11
  • **Thank you for all of the help!** I had no idea about the single quotes. (Embarrassed) HUGE help. I used CTE and it pulls exactly the values that I need. When I copy the code into my stored procedure and save it, I have no problem. When I execute it, the error I get appears to come from when I try to insert into a table to send by email. Does something stand out as incorrect? I have searched @mail_body and cannot find an answer. THE ERROR--Conversion failed when converting date and/or time from character string. – SkeerNC Aug 27 '20 at 20:01
  • You need to ask that as a new question and provide more details. – K4M Aug 27 '20 at 20:48