Converting Access cross-tab query to SQL Server

I have an Access cross-tab query that conditionally breaks up a field into multiple groups and averages the results in each group and I am having trouble doing this in SQL Server. The SQL Server query I have below works but does not find the averages by node A, B, RA, or RB.

Is there a better way to do this than what I have below?

How would I do this in SQL Server?

In the Access query I use:

right( [Serial No], iif( len([Serial No])-3>=0, len([Serial No])-3, len([Serial No]))

Table:

[Work Order No] [Serial No] [Parameter Name] [Type] [Min] [Max] [Value] [Pass/Fail]

M1000 001A "Test Name" "System1" 0 100 50 P

M1000 001B "Test Name" "System1" 0 100 40 P

M1000 002A "Test Name" "System1" 0 100 45 P

M1000 002B "Test Name" "System1" 0 100 70 P

M1000 002RA "Test Name" "System1" 0 100 30 P

M1000 002RB "Test Name" "System1" 0 100 20 P

M1001 001A "Test Name" "System1" 0 100 50 P

M1001 001B "Test Name" "System1" 0 100 30 P

The Query Output should be:

[Work Order No] [Node] [Min] [Max] [Avg Value]

M1000 A 0 100 47.5

M1000 B 0 100 55

M1000 RA 0 100 30

M1000 RB 0 100 20

M1001 A 0 100 50

M1001 B 0 100 30

Access Query:

TRANSFORM Avg([Value]) AS AvgOfValue

SELECT [Work Order No], [Min], [Max], RIGHT( [Serial No], iif(len([Serial No])-3>=0,

len([Serial No])-3, len([Serial No]))) AS [Node], max([DateTime]) as [MaxOfDateTime]

FROM [Inspection Header] INNER JOIN [Inspection Data]

ON [Inspection Header].[Work Order No] = [Inspection Data].[Work Order No]

AND [Inspection Header].[Serial No] = [Inspection Data].[Serial No]

WHERE [Parameter Name] = "Test Name" AND [Pass/Fail] = "P"

GROUP BY [Work Order No], [Min], [Max], RIGHT([Serial No],iif(len([Serial No])-3>=0,

len([Serial No])-3, len([Serial No]))), [Pass/Fail]

PIVOT [Parameter Name]

Incomplete SQL Server Query:

set ANSI_NULLS OFF

set QUOTED_IDENTIFIER OFF

GO

ALTER procedure [dbo].[dt_AvgTest]

as

begin

select b.[Work Order No], b.[TN], c.[PF], d.[Min], d.[Max]

from (((select a.[Work Order No]

, avg(case a.[Parameter Name] when "Test Name" then [Value] end) as [TN]

from dbo.[Inspection Data] as a group by a.[Work Order No]) b

inner join (select [Work Order No], min([Pass/Fail]) as PF

from dbo.[Inspection Data] group by [Work Order No] ) c on c.[Work Order No] = b.[Work Order No] )

inner join (select [Work Order No], [Min], [Max]

from dbo.[Inspection Data] where [Parameter Name] = "Test Name" group by [Work Order No], [Min], [Max] ) d

on d.[Work Order No] = c.[Work Order No] )

where c.[PF] = 'P'

end

Sam:

Is your server running SQL Server 2000 or SQL Server 2005?

Dave

|||SQL Server 2005|||

-- -
-- 1. I am guessing based on the proposed output data that
-- the "Serial No" field must be broken apart to get the
-- [Node data]. This is a poor choice for data design.
-- This means that this field is not "atomic" and if
-- possible should be divided into two separate columns.
-- 2. I am assuming here that the "Parameter Name" column
-- contains quote characters as part of the target data.
-- 3. I am assuming here that the "Type" column contains
-- quote characters as part of the target data.
-- 4. I have chosen to display the "average value" field as
-- a numeric (9,1) column. It is unclear what the
-- precision of this field needs to be based on the
-- proposed output; it is likely that this display
-- column needs adjustment.
-- 5. The use of the 10-Level REPLACE function to remove
-- the numeric portion of the "Serial No" field to
-- obtain the "Node" portion of this field is a guess
-- that the "numeric" portion of the field is NOT a part
-- of the "Node". If this guess is not correct then
-- an alternate method of obtaining this field is
-- necessary.
-- 6. The "Min" and "Max" field are aggregated as a
-- precaution in case these fields should ever be
-- different for different line items. If these are
-- not aggregated an additional line will be generated
-- whenever these items might otherwise differ.
--
-- Questions:
-- 1. Can the "Serial No" field be broken down into
-- two separate fields?
-- 2. What are the rules for obtaining the "Node" data?
-- 3. What is the output specification for the "Value"
-- field.
-- -

select [Work Order No],
left (replace(replace(replace(replace(replace(replace(replace
(replace(replace(replace
([Serial No],'0',''),'1',''),'2',''),'3',''),'4',''),
'5',''),'6',''),'7',''),'8',''),'9',''), 10) as [Node],
min ([Min]) as [Min],
max ([Max]) as [Max],
convert (numeric (9,1), avg (convert (numeric (9,1), [value])))
as [Value]
from [Inspection Data]
where [Pass/Fail] = 'P'
and [Parameter Name] = '"Test Name"'
group by [Work Order No],
left (replace(replace(replace(replace(replace(replace(replace
(replace(replace(replace
([Serial No],'0',''),'1',''),'2',''),'3',''),'4',''),
'5',''),'6',''),'7',''),'8',''),'9',''), 10)
order by [Work Order No],
left (replace(replace(replace(replace(replace(replace(replace
(replace(replace(replace
([Serial No],'0',''),'1',''),'2',''),'3',''),'4',''),
'5',''),'6',''),'7',''),'8',''),'9',''), 10)

-- -- Sample Output

-- Work Order No Node Min Max Value
-- - -- -
-- M1000 A 0 100 47.5
-- M1000 B 0 100 55.0
-- M1000 RA 0 100 30.0
-- M1000 RB 0 100 20.0
-- M1001 A 0 100 50.0
-- M1001 B 0 100 30.0

|||

This worked great!

You are right about breaking the Serial Number down into two separate fields.

Thank you very much.

Sam