By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,745 Members | 1,577 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,745 IT Pros & Developers. It's quick & easy.

How do I MAX a query within a query?

P: 34
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT     i.incident_ticket_number_tx AS Ticket_Number, u.incident_status_update_status_tx AS Status_Update, CONVERT(nvarchar(10), 
  2.                       u.incident_status_update_status_dt) + ' ' + CONVERT(nvarchar(5), u.incident_status_update_status_time_tx) AS Date_Time
  3. FROM         incident AS i INNER JOIN
  4.                       facility AS f ON i.incident_facility_id = f.facility_id INNER JOIN
  5.                       incident_status AS s ON i.incident_status_id = s.incident_status_id INNER JOIN
  6.                       incident_status_update AS u ON i.incident_id = u.incident_status_update_incident_id
  7.  
Below is the code that has a (non-working) Select Max statement within a Select statement:

Expand|Select|Wrap|Line Numbers
  1. SELECT     i.incident_ticket_number_tx AS Ticket_Number, u.incident_status_update_status_tx AS Status_Update, CONVERT(nvarchar(10), 
  2.                       u.incident_status_update_status_dt) + ' ' + CONVERT(nvarchar(5), u.incident_status_update_status_time_tx) AS Date_Time
  3. FROM
  4. incident AS i INNER JOIN
  5. facility AS f ON i.incident_facility_id = f.facility_id INNER JOIN
  6. incident_status AS s ON i.incident_status_id = s.incident_status_id INNER JOIN
  7. incident_status_update AS u ON i.incident_id = u.incident_status_update_incident_id,
  8. (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
  9. Where Date_Time = results_max.Date_Time_Max
  10.  
The error I am getting is:
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Date_Time'.
Attached Images
File Type: jpg results.jpg (52.2 KB, 92 views)
Dec 4 '13 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
You have a comma at the end of

Expand|Select|Wrap|Line Numbers
  1. i.incident_id = u.incident_status_update_incident_id,
  2.  
I will be able to help you more if you post some sample data and your intended output.

Good Luck!!!


~~ CK
Dec 13 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.