Problem is I need to include these in a query that will be selecting multiple columns along with these.

dirtyshorellc (8/9/2012)

---

Hello,

I am new to SQL and have been asked to perform a query of our Dispatch database. Although I am able to pull most of the information I need I am having trouble with the following.

The following queries work seperately on their own:

SELECT unit_status_datetime AS UnitLeftSceneDateTime
FROM unit_status_hist
WHERE cad_command_code = 'EH'

SELECT unit_status_datetime AS UnitLeftSceneDateTime
FROM unit_status_hist
WHERE cad_command_code = 'AH'

Problem is I need to include these in a query that will be selecting multiple columns along with these.

Is there a way to specify multiple aliases for a single column a retrive the data I need?

Thank you for your time!
Chris Abbott

SELECT unit_status_datetime AS UnitLeftSceneDateTime
FROM unit_status_hist
WHERE cad_command_code = 'EH' OR cad_command_code = 'AH';

SELECT unit_status_datetime AS UnitLeftSceneDateTime
FROM unit_status_hist
WHERE cad_command_code in ('EH','AH');

dirtyshorellc (8/9/2012)

---

My appologies.

What I am doing is creating a query that will simply take certain columns from a table and export them to a csv file for export to another server. The export is for EMS calls and the data will include all of the unit status information for the incident. All of the other columns that I need to include in this query I'm able to retrieve with no issue. The problem is that I only need 2 types of data from the column I listed and I need to be able to seperate the retrived data and create a seperate column for each data type in the export file.

Here is what I have so far:

SELECT
incident_num AS IncidentNumber, incident_date AS IncidentDateTime, e911_time AS PsapCallDateTime,
rec_time AS DispatchNotifiedDateTime, disp_time AS UnitNotifiedDispatchDateTime, enr_time AS UnitEnRouteDateTime,
arv_time AS UnitArrivedSceneDateTime,
-- This is where the CAD_COMMAND_CODE for UnitLeftScene and PatientArrivedDest fields will live.


clr_time AS UnitBackInServiceDateTime, location AS StreetAddress, address AS StreetAddress2,
city_code AS City, call_type AS EmdCardNumber, disposition_code AS DestinationCode

FROM
cfs_incident_core

As you can see I am aliasing each column of the table to match the expected format for the other server.

Also I seem to have made a mistake in my first post. Each query should have a different Alias. The query for the 'AH' data should have been aliased as PatientArrivedDestDateTime. Again my apologies!

Also I'm using MS SQL SVR 2000.

SELECT unit_status 
    UnitLeftSceneDateTime=MAX(CASE WHEN cad_command_code = 'EH' THEN unit_status_datetime END) 
    PatientArrivedDestDateTime=MAX(CASE WHEN cad_command_code = 'AH' THEN unit_status_datetime END) 
FROM unit_status_hist
GROUP BY unit_status