SQL Summary Report w/ 2 tables

[joe_pool_is]

I've 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 vendor's table.

Vendor's 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 don't 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 I'd 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