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?
|||SQL Server 2005|||
Dave
-- -
-- 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
