SQL Summary Report w/ 2 tables

[joe_pool_is]

Ive got 2 tables I need to search and provide a Work Order Summary on. The tables both have similar information:

• One is populated by a machine that is purchased through a vendor.
• One is popluated by our software and is designed to mirror the format of the vendors table.

Vendors Table Design:
Serial_Number|System_ID |Test_Result
varchar(20) |varchar(50)|varchar(255)

A Serial_Number consists of a tracking number tacked on to a Part_Number (i.e. Part_Number ABC could have Serial_Number ABC 001 to ABC 999). A System_ID is the name of the station where the operation happens (Admin, Machine_Stamp, Riviting, Welding, PressureTank, Assembly, etc.). A Test_Result would indicate if the part passed or failed a particular station (There are about 15 other fields to indicate the machine operators, dates, pressures/temperatures used, etc; but they are not part of the Summary).

Our Table Design (used for Admin and Assembly System_IDs):
Serial_Number|System_ID |Test_Result |Work_Order|WO_Qty|WO_Seq
varchar(20) |varchar(50)|varchar(255)|char(10) |int |int

Work_Order is the work order number, WO_Qty represents the number of items that are in the work order, and WO_Seq represents each item in the work order. So, for a work order with 3 items, the WO_Seq would be 1, 2, and 3.

I am attempting to write a Summary given a particular Work_Order number.
The desired output by Management is as follows:

|_Model_|_Ordered_|_Remaining_|__Admin__|_Machine_Stamp_|_Riviting_|_Welding_|_PressureTank_|_Assembly_|
|  ABC  |   25    |     0     |    50   |       50      |    50    |    50   |      50      |    50    |
|  ADA  |   50    |    15     |    35   |       29      |    28    |    17   |       9      |     7    |

Given a Work_Order, I need to Count the values of Test_Result that include the word pass for each System_ID (i.e. Count(Test_Result like %pass%) where System_ID=SysID), but I dont know how to do this for two tables, multiple System_IDs, a given Work_Order field that only appears in our table, and have the output display on a single line.

Would someone mind showing me how to do something like this? Is it possible?

My other option is to query all the information in one table using the Work_Order, store this in a DataTable, query the other table for each of the Serial_Numbers returned from the first query, then write a routine to filter it all. But this brute force method seems wasteful, and Id like to know how to build better SQL queries.

 

[joe_pool_is]

Just an FYI Update: This query problem has been solved.

The query I used is below. If anyone sees anything that is particularly time consuming in this technique or something that could be improved upon, I invite your comments.

declare @WO nchar(10)
set @WO=463202
select distinct(Serial_Number)
into #wo
from Parts_Data
where ([WorkOrder_Number]=@WO)
select
  Left(ct.Serial_Number, CharIndex( , ct.Serial_Number) - 1) as Model,
  (select distinct WO_Qty from Parts_Data where [EMAIL="WO=@WO"]WO=@WO[/EMAIL]) as Ordered,
  (select distinct WO_Qty from Parts_Data where [EMAIL="WO=@WO"]WO=@WO[/EMAIL]) - Count(distinct ct.WO_Seq) as Remaining,
  (select Count(Serial_Number) from Parts_Data where System_ID like %Admin% and Serial_Number in (Select Serial_Number from #wo)) as Admin,
  (select Count(distinct Serial_Number) from Vendor_Data where System_ID like %Riviting% and Serial_Number in (Select Serial_Number from #wo)) as Riviting,
  (select Count(distinct Serial_Number) from Vendor_Data where System_ID like %Welding% and Serial_Number in (Select Serial_Number from #wo)) as Welding,
  (select Count(Serial_Number) from Parts_Data where System_ID like %Assembly% and Serial_Number in (Select Serial_Number from #wo)) as Assembly
from Parts_Data ct
where (Len(RTrim(ct.Serial_Number))=15) and ([EMAIL="ct.WO=@WO"]ct.WO=@WO[/EMAIL])
group by left(ct.Serial_Number, CharIndex( , ct.Serial_Number) - 1)
drop table #wo