How to generate Summary? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to generate Summary?

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2008-12-16 : 09:20:13

I've got 2 tables I need to search and provide a Summary. The tables both have similar information:

One is populated by a machine that is purchased through a vendor.

The other is designed to mirror the format of the vendor's table, but it is populated by our software.

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.).

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 company's 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 the two tables, multiple System_IDs, a 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?

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 10:41:22

[code]SELECT LEFT(Serial_Number,CHARINDEX(' ',Serial_Number)-1) AS Model,
SUM(ct.WO_Qty) AS Ordered,
COUNT(DISTINCT vt.Serial_Number)-SUM(ct.WO_Qty) AS remaining,
SUM(CASE WHEN vt.System_ID='Admin' THEN 1 ELSE 0 END) AS Admin,
SUM(CASE WHEN vt.System_ID='Machine_Stamp' THEN 1 ELSE 0 END) AS Machine_Stamp,
SUM(CASE WHEN vt.System_ID='Riviting' THEN 1 ELSE 0 END) AS _Riviting_,
SUM(CASE WHEN vt.System_ID='Welding' THEN 1 ELSE 0 END) AS Welding,
SUM(CASE WHEN vt.System_ID='PressureTank' THEN 1 ELSE 0 END) AS PressureTank,
SUM(CASE WHEN vt.System_ID='Assembly' THEN 1 ELSE 0 END) AS Assembly
FROM VendorTable vt
LEFT JOIN CompanyTable ct
ON ct.Serial_Number=vt.Serial_Number
AND ct.System_ID=vt.System_ID
AND ct.Test_Result LIKE '%Pass%'
GROUP BY LEFT(Serial_Number,CHARINDEX(' ',Serial_Number)-1)
[/code]

jp2code
Posting Yak Master

175 Posts

Posted - 2008-12-16 : 11:33:51

Amazing!

This needs a little tweaking on my end to make it work, but that is still fantastic! I had no idea how to approach this.

Can I buy you a beer?

Avoid Sears Home Improvement

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-16 : 11:37:25

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 11:44:25

quote:
Originally posted by jp2code

Amazing!

This needs a little tweaking on my end to make it work, but that is still fantastic! I had no idea how to approach this.

Can I buy you a beer?

Avoid Sears Home Improvement

Thanks for the beer

jp2code
Posting Yak Master

175 Posts

Posted - 2008-12-16 : 17:19:57

Just an FYI Update:

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.

Query:

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 WO=@WO) as 'Ordered',
  (select distinct WO_Qty from Parts_Data where WO=@WO) - 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 (ct.WO=@WO)
group by left(ct.Serial_Number, CharIndex(' ', ct.Serial_Number) - 1)

drop table #wo

Thanks again, visakh16!

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 00:01:08

hey dont use subqueries . this is enough


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',
  sum(distinct ct.WO_Qty) as 'Ordered',
  sum(distinct ct.WO_Qty) - Count(distinct ct.WO_Seq) as 'Remaining',
  Count(case when ct.System_ID like '%Admin%' and w.Serial_Number is not null then ct.Serial_Number else null end)  as 'Admin',
  Count(distinct case when vt.System_ID like '%Riviting%' and w.Serial_Number is not null  then vt.Serial_Number else null end)  as 'Riviting',
 Count(distinct case when  vt.System_ID like '%Welding%' and w.Serial_Number is not null  then vt.Serial_Number else null end)  as 'Welding',
  Count(case when ct.System_ID like '%Assembly%' and w.Serial_Number is not null then ct.Serial_Number else null end)  as 'Assembly'
from Parts_Data ct
left join Vendor_Data vt
ON ct.Serial_Number=vt.Serial_Number
AND ct.System_ID=vt.System_ID
left join #wo w
ON w.Serial_Number=ct.Serial_Number
where (Len(RTrim(ct.Serial_Number))=15) and (ct.WO=@WO)
group by left(ct.Serial_Number, CharIndex(' ', ct.Serial_Number) - 1)

drop table #wo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources