This query is using 4 tables (incident, facility, incident_status , and incident_status _update). I have attached a picture of the results. The results consists of a Ticket Number, a status (string), and a concatenated date and time column.
What I am trying to do is MAX on the Date_Time field for each group of Ticket_Number. I have referenced the columns that should be showing after a successfull MAX has taken place on the attachment with red arrows.
Below is the code that produces the attached results.jpg:
Below is the code that has a (non-working) Select Max statement within a Select statement:
The error I am getting is:
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Date_Time'.
What I am trying to do is MAX on the Date_Time field for each group of Ticket_Number. I have referenced the columns that should be showing after a successfull MAX has taken place on the attachment with red arrows.
Below is the code that produces the attached results.jpg:
Code:
SELECT i.incident_ticket_number_tx AS Ticket_Number, u.incident_status_update_status_tx AS Status_Update, CONVERT(nvarchar(10),
u.incident_status_update_status_dt) + ' ' + CONVERT(nvarchar(5), u.incident_status_update_status_time_tx) AS Date_Time
FROM incident AS i INNER JOIN
facility AS f ON i.incident_facility_id = f.facility_id INNER JOIN
incident_status AS s ON i.incident_status_id = s.incident_status_id INNER JOIN
incident_status_update AS u ON i.incident_id = u.incident_status_update_incident_id
Code:
SELECT i.incident_ticket_number_tx AS Ticket_Number, u.incident_status_update_status_tx AS Status_Update, CONVERT(nvarchar(10),
u.incident_status_update_status_dt) + ' ' + CONVERT(nvarchar(5), u.incident_status_update_status_time_tx) AS Date_Time
FROM
incident AS i INNER JOIN
facility AS f ON i.incident_facility_id = f.facility_id INNER JOIN
incident_status AS s ON i.incident_status_id = s.incident_status_id INNER JOIN
incident_status_update AS u ON i.incident_id = u.incident_status_update_incident_id,
(Select Max(CONVERT(nvarchar(10), s.incident_status_update_status_dt) + ' ' + CONVERT(nvarchar(5), s.incident_status_update_status_time_tx)) as Date_Time_Max, i.incident_ticket_number_tx as Ticket_Number from incident_status_update s, incident i group by i.incident_ticket_number_tx) as results_max
Where Date_Time = results_max.Date_Time_Max
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Date_Time'.
Comment