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

Converting a MySQL multiple column subselect to SQL Server

I am adding SQL Server support to an application that currently uses
MySQL 4.1.
I have a table that looks like this in SQL Server 8.0:
CREATE TABLE dbo.sales_estimates
(
ID int NOT NULL,
YearMonth datetime NULL,
CountryCode char(3) NULL,
StoreCode int NULL,
SalesEstimate decimal(18, 0) NULL,
UserID int NULL,
DateTimeStamp datetime NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.sales_estimates ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
(
ID
) ON [PRIMARY]
GO
It contains multiple sales estimates for stores, eg different users can
enter their own SalesEstimate for each store's monthly sales.
I want to select the most recent sales estimate for each store for a
given month.
In MySQL 4.1 I can do this by with the folling nested selects:
select YearMonth, CountryCode, StoreCode, SalesEstimate from
store_estimates
where (YearMonth,CountryCode, StoreCode, DateTimeStamp)
in (select YearMonth, State, StoreCode , max(DateTimeStamp)
from store_estimates
where YearMonth ='2006-01-1'
group by YearMonth, State, StoreCode)
I'd like to write a similar statement for SQL Server (version 8.0) if
this is possible, but it appears that I can't have multiple rows in
subselects. Ideally I'd like to find a simpler query that works for
both DBs.SQL Server doesn't support the syntax, I believe it's referred
to as 'row constructors'
You can do this instead
select a.YearMonth, a.CountryCode, a.StoreCode, a.SalesEstimate
from store_estimates a
inner join (select YearMonth, State, StoreCode , max(DateTimeStamp)
from store_estimates
where YearMonth ='2006-01-1'
group by YearMonth, State, StoreCode) b(YearMonth,CountryCode,
StoreCode, DateTimeStamp)
on a.YearMonth=b.YearMonth and a.CountryCode=b.CountryCode
and a.StoreCode=b.StoreCode and a.DateTimeStamp=b.DateTimeStamp
You can also achieve the same results with an EXISTS clause.

Converting a file into multiple tables

This file format has multiple levels (X12).
One level could have one or more instances of the
next level contained within it. Kind of like XML,
except that some sections have no end tags, and the
ones that do have end tags actually have a _different_
tag for the end. (ISA ...IEA or GS ... GE)

It's easy enough to read a line at a time, see what
type it is, and insert its parts into the appropriate
table. Keeping track of the keys of the parent level
for relationships.

But I'm wandering whether there's some (not impossibly
complex) more efficient method with SQL and/or DTS.

--
Wes Groleau

If you put garbage in a computer nothing comes out but garbage.
But this garbage, having passed through a very expensive machine,
is somehow ennobled and none dare criticize it.Take a look at SQLXML Bulk Load
(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Wes Groleau" <groleau+news@.freeshell.orgwrote in message
news:K7x6h.10720$l%2.2462@.trnddc05...

Quote:

Originally Posted by

This file format has multiple levels (X12).
One level could have one or more instances of the
next level contained within it. Kind of like XML,
except that some sections have no end tags, and the
ones that do have end tags actually have a _different_
tag for the end. (ISA ...IEA or GS ... GE)
>
It's easy enough to read a line at a time, see what
type it is, and insert its parts into the appropriate
table. Keeping track of the keys of the parent level
for relationships.
>
But I'm wandering whether there's some (not impossibly
complex) more efficient method with SQL and/or DTS.
>
--
Wes Groleau
>
If you put garbage in a computer nothing comes out but garbage.
But this garbage, having passed through a very expensive machine,
is somehow ennobled and none dare criticize it.

|||Dan Guzman wrote:

Quote:

Originally Posted by

Take a look at SQLXML Bulk Load
(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

He'd also need an EDI to XML translator. (I recognize those
damnable start/end tags.) Google indicates that several
translators exist; anyone want to offer a recommendation?|||You're right about the EDI to XML translator - I misread Wes's post. of
course, SQLXML can't consume EDI directly.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ed Murphy" <emurphy42@.socal.rr.comwrote in message
news:PCH6h.708$Fg.683@.tornado.socal.rr.com...

Quote:

Originally Posted by

Dan Guzman wrote:
>

Quote:

Originally Posted by

>Take a look at SQLXML Bulk Load
>(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

>
He'd also need an EDI to XML translator. (I recognize those
damnable start/end tags.) Google indicates that several
translators exist; anyone want to offer a recommendation?

|||Dan Guzman wrote:

Quote:

Originally Posted by

You're right about the EDI to XML translator - I misread Wes's post. of
course, SQLXML can't consume EDI directly.

Of course, I could easily write something to
convert it to XML that SQL server can read.
But then I could just as easily convert it
directly into INSERT statements. I'm just
wondering whether DTS or anything else is faster.

I already have a tool that loads the entire
file into an array of lines and provides various
query functions for other apps to access it.

But I'd like to put multiple files in the database
instead of having to select one file at a time.

By the way, whatever the technique is, it could
probably also handle GEDCOM files.

--
Wes Groleau

There ain't no right wing,
there ain't no left wing.
There's only you and me and we just disagree.
(apologies to Jim Krueger)|||Ed Murphy wrote:

Quote:

Originally Posted by

Dan Guzman wrote:

Quote:

Originally Posted by

>Take a look at SQLXML Bulk Load
>(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

>
He'd also need an EDI to XML translator. (I recognize those
damnable start/end tags.) Google indicates that several
translators exist; anyone want to offer a recommendation?

I think I figured out a solution (haven't tried it yet).

Comments on this idea welcome (I'm kind of new to SQL):

The X12 files and GEDCOM files (maybe HL7, too?) have
multiple levels. Generally, each "level X" record
may own more than one record on level X+1

So if a file has (data elem delims changed to spaces)
....
CLP A B C
SVC X Y Z
SVC 1 2 3
CLP D E F
SVC P Q R
SVC 5 6 7
....
then the first pass through the file could create rows

.... A B C X Y Z ...
.... A B C 1 2 3 ...
.... D E F P Q R ...
.... D E F 5 6 7 ...

Next, one query could SELECT DISTINCT to give

.... A B C
.... D E F

while another could SELECT for

.... A X Y Z ...
.... A 1 2 3 ...
.... D P Q R ...
.... D 5 6 7 ...

and the same strategy could be used on each adjacent pair of levels.

Right ?

--
Wes Groleau

He that is good for making excuses, is seldom good for anything else.
-- Benjamin Franklin|||It's true that you can transform EDI and GEDCOM files directly into
relational format. I think the reason XML is commonly used as an
intermediate format is that XML is perfect for hierarchical data and you can
leverage a high-performance XML import utility like SQLXML without writing
additional code. Although it will take a while, I expect XML will
eventually replace both EDI and GEDCOM formats. You'll be a step ahead if
you can process XML too.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Wes Groleau" <groleau+news@.freeshell.orgwrote in message
news:uJv7h.211$9e.25@.trnddc02...

Quote:

Originally Posted by

Ed Murphy wrote:

Quote:

Originally Posted by

>Dan Guzman wrote:

Quote:

Originally Posted by

>>Take a look at SQLXML Bulk Load
>>(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

>>
>He'd also need an EDI to XML translator. (I recognize those
>damnable start/end tags.) Google indicates that several
>translators exist; anyone want to offer a recommendation?

>
I think I figured out a solution (haven't tried it yet).
>
Comments on this idea welcome (I'm kind of new to SQL):
>
The X12 files and GEDCOM files (maybe HL7, too?) have
multiple levels. Generally, each "level X" record
may own more than one record on level X+1
>
So if a file has (data elem delims changed to spaces)
...
CLP A B C
SVC X Y Z
SVC 1 2 3
CLP D E F
SVC P Q R
SVC 5 6 7
...
then the first pass through the file could create rows
>
... A B C X Y Z ...
... A B C 1 2 3 ...
... D E F P Q R ...
... D E F 5 6 7 ...
>
Next, one query could SELECT DISTINCT to give
>
... A B C
... D E F
>
while another could SELECT for
>
... A X Y Z ...
... A 1 2 3 ...
... D P Q R ...
... D 5 6 7 ...
>
and the same strategy could be used on each adjacent pair of levels.
>
Right ?
>
--
Wes Groleau
>
He that is good for making excuses, is seldom good for anything else.
-- Benjamin Franklin

|||Dan Guzman wrote:

Quote:

Originally Posted by

It's true that you can transform EDI and GEDCOM files directly into
relational format. I think the reason XML is commonly used as an
intermediate format is that XML is perfect for hierarchical data and you
can leverage a high-performance XML import utility like SQLXML without
writing additional code. Although it will take a while, I expect XML
will eventually replace both EDI and GEDCOM formats. You'll be a step
ahead if you can process XML too.

OK, I do know how to read and write XML. But can an XML file
be formatted so that the utility will create multiple tables
with the appropriate foreign keys to relate them?

I got the impression when I was reading about it that one
XML file makes one table and vice versa.

--
Wes Groleau
----

"Thinking I'm dumb gives people something to
feel smug about. Why should I disillusion them?"
-- Charles Wallace
(in _A_Wrinkle_In_Time_)|||Wes Groleau wrote:

Quote:

Originally Posted by

OK, I do know how to read and write XML. But can an XML file
be formatted so that the utility will create multiple tables
with the appropriate foreign keys to relate them?
>
I got the impression when I was reading about it that one
XML file makes one table and vice versa.

The impression was wrong. I studied the MS KB article cited
earlier, and I can easily make such XML files. Only, the process
of transforming the file into XML is similar to the process used
by bulk load to turn the XML into records. So I suspect it would
add a little speed if I went directly to records.

--
Wes Groleau

Words of the Wild Wes(t) = http://ideas.lang-learn.us/WWWsqlsql

Convert rows to delimited string

Does RS have any function that would enable me to convert multiple
rows into a comma-delimited string? My report is very long due to multiple
instances of a child record that is represented by a single word. If I could
append
the column values it would significantly compress the report.
Example where the tables are Cust and Order and joined on Order.CustId:
Change from this:
CustId CustName OrderColors
-- -- --
100 Anderson Red
100 Anderson Green
100 Anderson Yellow
To this:
CustId CustName OrderColors
-- -- --
100 Anderson Red, Green, YellowRS doesn't have such a function. I would do this at the data source. If you
use SQL Server 2005, if I am not wrong, there is a custom aggregate sample
that does exactly this.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OOFXEMS9FHA.3492@.TK2MSFTNGP10.phx.gbl...
> Does RS have any function that would enable me to convert multiple
> rows into a comma-delimited string? My report is very long due to multiple
> instances of a child record that is represented by a single word. If I
> could append
> the column values it would significantly compress the report.
> Example where the tables are Cust and Order and joined on Order.CustId:
> Change from this:
> CustId CustName OrderColors
> -- -- --
> 100 Anderson Red
> 100 Anderson Green
> 100 Anderson Yellow
> To this:
> CustId CustName OrderColors
> -- -- --
> 100 Anderson Red, Green, Yellow
>
>