Query for report

[ronwmon]

I am working on a query that will produce a report for one of our customers.

Initially I created a table that contained two fields to isolate this customers accounts, basically an account list. The table contains the site and system number for each of the accounts for this customer. This table is called hes_list.

The query has to get information from multiple tables. The tables involved are:

Site
System
Zone
Alarm_Incident

The fields necessary for the report are:

System_no – from system and or hes_list tables
Site_name – from site table
Alarm_local_date – from alarm_incident table
Servtypt_id – from alarm_incident table
Alarm_zone_id – from alarm incident table
Comment – from zone table

Here is the code I wrote. It works but only gets data for one hes_list.system_no.

insert into hes_lasttrip
select top 1 hes_list.system_no, site.site_name, alarm_incident.alarm_local_date, alarm_incident.servtype_id, alarm_incident.alarm_zone_id, zone.comment
from site
left join hes_list on site.site_no = hes_list.site_no
join alarm_incident on hes_list.system_no = alarm_incident.system_no
join zone on hes_list.system_no = zone.system_no
order by alarm_incident.alarm_local_date desc

I thought by using a left join it would get every entry from hes_list.

The data does not have to be put in the table hes_lasttrip, this is something I thought might make it get all of the desired records.

Can anybody help me create the query to get the data connected to all of the system_no’s in hes_list?

Thanks in advance,

Ron--

 

[gmmastros]

It works but only gets data for one hes_list.system_no

I'm not surprised! That is exactly what Top 1 is supposed to do.

insert into hes_lasttrip
select    [!]top 1[/!] hes_list.system_no, site.site_name, alarm_incident.alarm_local_date, alarm_incident.servtype_id, alarm_incident.alarm_zone_id, zone.comment
from site
left join hes_list on site.site_no = hes_list.site_no
join alarm_incident on hes_list.system_no = alarm_incident.system_no
join zone on hes_list.system_no = zone.system_no
order by alarm_incident.alarm_local_date desc

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ronwmon]

Just so you understand, the alarm_incident table has many events and I only want the last one. The report I am trying to create is the last signal from each system.

That is why I am using "top 1." I get the data from the alarm_incident table then order it in reverse and take the top 1 which is the last entry.

I then need to go to the next system number in hes_list and repeat the process.

Thanks,

Ron--

 

[markros]

Take a look at these two blogs to resolve your problem:

Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2


PluralSight Learning Library