Analyst seeks help with one table query

[bpbull]

SQL Server 2003 - SQL Query Analyzer. We have a database that stores service tickets and a table called non_part_usage. In this table, we have one field called request_id, one field called line_code_type and one field called quantity (there are more, but these are all we need for this question). Each request_id represents a service ticket. We have two line code types: L for Labor and T for Travel. The quantity field is a number of hours. One service ticket can have multiple Labor entries and multiple Travel entries (if technician went back to site 3 times, we would have 3 labor entries and 3 travel entries).

The output I am trying to get would like like this:

Request ID...........To tal Labor Hours.......... ..Total Travel Hours

I can write a query to get either total labor hours or total travel hours but I cannot figure out how to create and output both columns in one query. The individual queries are as follows:

SELECT request_id, sum(quantity) as Total_Labor_Hou rs
FROM non_part_usage
WHERE line_code_type = 'L'

SELECT request_id, sum(quantity) as Total_Travel_Ho urs
FROM non_part_usage
WHERE line_code_type = 'T'

The bosses will ask for this information a dozen different ways, but they always want to see the request id, the total number of labor hours and the total number of travel hours - and they want to read them across the report.

Any help will be greatly appreciated as this is a request I receive daily and one I desperately want to be able to fulfill. Our programmers are all tied up and my query skills are limited to the fundamentals. (Right now I list every line with the line code type, output to Excel and then pivot by request and sum on the line code type).

With sincere appreciation,
Brian

[mabubakarpk]

Dear Try this one



SELECT tmp.request_id, sum(tmp.Total_L abor_Hours) as Total_Labor_Hou rs
,sum(tmp.Total_ Travel_Hours) as Total_Travel_Ho urs

From (

Select request_id,quan tity as Total_Labor_Hou rs,0 as Total_Travel_Ho urs FROM non_part_usage WHERE line_code_type = 'L'

Union ALL

Select request_id,0 as Total_Labor_Hou rs,quantity as Total_Travel_Ho urs FROM non_part_usage WHERE line_code_type = 'T'
) as tmp

group by tmp.request_id

Regards,

[azimmer]

SQL Server 2003 - SQL Query Analyzer. We have a database that stores service tickets and a table called non_part_usage. In this table, we have one field called request_id, one field called line_code_type and one field called quantity (there are more, but these are all we need for this question). Each request_id represents a service ticket. We have two line code types: L for Labor and T for Travel. The quantity field is a number of hours. One service ticket can have multiple Labor entries and multiple Travel entries (if technician went back to site 3 times, we would have 3 labor entries and 3 travel entries).

The output I am trying to get would like like this:

Request ID...........To tal Labor Hours.......... ..Total Travel Hours

I can write a query to get either total labor hours or total travel hours but I cannot figure out how to create and output both columns in one query. The individual queries are as follows:

SELECT request_id, sum(quantity) as Total_Labor_Hou rs
FROM non_part_usage
WHERE line_code_type = 'L'

SELECT request_id, sum(quantity) as Total_Travel_Ho urs
FROM non_part_usage
WHERE line_code_type = 'T'

The bosses will ask for this information a dozen different ways, but they always want to see the request id, the total number of labor hours and the total number of travel hours - and they want to read them across the report.

Any help will be greatly appreciated as this is a request I receive daily and one I desperately want to be able to fulfill. Our programmers are all tied up and my query skills are limited to the fundamentals. (Right now I list every line with the line code type, output to Excel and then pivot by request and sum on the line code type).

With sincere appreciation,
Brian

One way is to set up a UNION query:

SELECT X.request_id, max(X.Total_Labor_Hours), max(X.Total_Travel_Hours)
FROM
(
SELECT request_id, sum(quantity) as Total_Labor_Hours, NULL as Total_Travel_Hours
FROM non_part_usage
WHERE line_code_type = 'L'
GROUP BY request_id
 
UNION
 
SELECT request_id, NULL as Total_Labor_Hours, sum(quantity) as Total_Travel_Hours
FROM non_part_usage
WHERE line_code_type = 'T'
GROUP BY request_id
) as X
GROUP BY X.request_id
 

[azimmer]

We did it independently.. . :-)

[bpbull]

Awesome job! Greatly appreciated! Worked like a charm.

Thank You,
bpbull

Dear Try this one



SELECT tmp.request_id, sum(tmp.Total_L abor_Hours) as Total_Labor_Hou rs
,sum(tmp.Total_ Travel_Hours) as Total_Travel_Ho urs

From (

Select request_id,quan tity as Total_Labor_Hou rs,0 as Total_Travel_Ho urs FROM non_part_usage WHERE line_code_type = 'L'

Union ALL

Select request_id,0 as Total_Labor_Hou rs,quantity as Total_Travel_Ho urs FROM non_part_usage WHERE line_code_type = 'T'
) as tmp

group by tmp.request_id

Regards,

[bpbull]

You folks are awesome and your response time is phenomenal. Both solutions worked great and will be the foundations of many queries to come. I googled all over and could not find this solution...than ks for being here and for taking the time to help a novice!

Sincerely,
bpbull

One way is to set up a UNION query:

SELECT X.request_id, max(X.Total_Labor_Hours), max(X.Total_Travel_Hours)
FROM
(
SELECT request_id, sum(quantity) as Total_Labor_Hours, NULL as Total_Travel_Hours
FROM non_part_usage
WHERE line_code_type = 'L'
GROUP BY request_id
 
UNION
 
SELECT request_id, NULL as Total_Labor_Hours, sum(quantity) as Total_Travel_Hours
FROM non_part_usage
WHERE line_code_type = 'T'
GROUP BY request_id
) as X
GROUP BY X.request_id