Help on SQL syntax

Dear Anyone,
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
Annette
You can use AVG() aggregate function with GROUP BY clause, for example:
SELECT SAMPLE.CORRECTED_COL_DATE, AVG(SAMPLE.PRODUCT_GRADE)
FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS, PRODUCT
WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND
PRODUCT.NAME = SAMPLE.PRODUCT AND
PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND
PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME LIKE '%SD'))
AND
RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'
GROUP BY SAMPLE.CORRECTED_COL_DATE
ORDER BY CORRECTED_COL_DATE
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Annette Malini" <anonymous@.discussions.microsoft.com> wrote in message
news:731d01c48423$63065e20$a501280a@.phx.gbl...
> Dear Anyone,
> I am no expert with SQL server but I am trying obtain
> some data from this server using SQL syntax, I would like
> to average out the data by correction_col_date below, can
> anyone help please!
> SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
> SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
> SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
> SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
> PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
> RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
> RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
> RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
> PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
> SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
> SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
> PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
> SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
> SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
> SAMPLE.PRODUCT_GRADE AND
> PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
> PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
> RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
> ((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
> LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
> RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
> CORRECTED_COL_DATE
> Thanks,
> Annette
>

Help on SQL syntax

Dear Anyone,
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME = SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
AnnetteYou can use AVG() aggregate function with GROUP BY clause, for example:
SELECT SAMPLE.CORRECTED_COL_DATE, AVG(SAMPLE.PRODUCT_GRADE)
FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS, PRODUCT
WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND
PRODUCT.NAME = SAMPLE.PRODUCT AND
PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND
PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME LIKE '%SD'))
AND
RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'
GROUP BY SAMPLE.CORRECTED_COL_DATE
ORDER BY CORRECTED_COL_DATE
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Annette Malini" <anonymous@.discussions.microsoft.com> wrote in message
news:731d01c48423$63065e20$a501280a@.phx.gbl...
> Dear Anyone,
> I am no expert with SQL server but I am trying obtain
> some data from this server using SQL syntax, I would like
> to average out the data by correction_col_date below, can
> anyone help please!
> SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
> SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
> SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
> SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
> PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
> RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
> RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
> RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
> PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
> SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
> SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
> PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME => SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION => SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE => SAMPLE.PRODUCT_GRADE AND
> PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
> PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
> RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
> ((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
> LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
> RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
> CORRECTED_COL_DATE
> Thanks,
> Annette
>

Help on SQL syntax

Dear Anyone,
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
AnnetteYou can use AVG() aggregate function with GROUP BY clause, for example:
SELECT SAMPLE.CORRECTED_COL_DATE, AVG(SAMPLE.PRODUCT_GRADE)
FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS, PRODUCT
WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND
PRODUCT.NAME = SAMPLE.PRODUCT AND
PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND
PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME LIKE '%SD'))
AND
RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'
GROUP BY SAMPLE.CORRECTED_COL_DATE
ORDER BY CORRECTED_COL_DATE
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Annette Malini" <anonymous@.discussions.microsoft.com> wrote in message
news:731d01c48423$63065e20$a501280a@.phx.gbl...
> Dear Anyone,
> I am no expert with SQL server but I am trying obtain
> some data from this server using SQL syntax, I would like
> to average out the data by correction_col_date below, can
> anyone help please!
> SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
> SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
> SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
> SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
> PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
> RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
> RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
> RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
> PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
> SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
> SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
> PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
> SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
> SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
> SAMPLE.PRODUCT_GRADE AND
> PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
> PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
> RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
> ((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
> LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
> RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
> CORRECTED_COL_DATE
> Thanks,
> Annette
>

Help on SQL Syantax

Dear Anyone,
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
Annette
post relevent table structures, sample data and expected result set.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Help needed to generate a "sort code"

I have been puzzled by this over the last 2 days, maybe an SQL expert here can help me out.

I have the following table:
==============
Pref
==============
UID int
SMOKE bit
LIVESMOKE bit
PET bit
LIVEPET bit
==============

I have a record with the following preferences:
smoke: 1
live with smoker: 1
pet: 0
live with pet: 0

how can I write an SQL statement to pull up all rows in the table sorted by the number of matching preferences to the row indicated above?

My initial thought was to xnor the pref which will return 1 on each matching pref but then I still need to count the number of on bit on the xnor result.

Have anyone done this before? any idea or suggestion?order
by case when SMOKE = 1 then 1 else 0 end
+case when LIVESMOKE = 1 then 1 else 0 end
+case when PET = 0 then 1 else 0 end
+case when LIVEPET = 0 then 1 else 0 end
desc|||I'm not used to the BIT datatype. So you can't do this then?

order by SMOKE+LIVESMOKE+PET+LIVEPET

i.e. BITs are not implicitly converted to INTEGERS by an expression like that?|||not implicitly, no, you actually have to use CAST on them

however, that won't help here, as the problem was to find rows with most matches to the particular combination 1, 1, 0, 0|||I should have read more carefully!

Help me please ! My question for an expert !

Hi !
I have been worked with VC++, MS SQL SERVER, Transact-SQL for
3 years. I made an axtended stored procedure (xp_test) which returns
an recordset.
From Query Analizer, I can see the recordest : exec xp_test

I want to make an User Defined Function - MyTest which return
the recordset that it is returned by xp_test after its execution.
Something like that :

CREATE function dbo.MyTest ( )
RETURNS @.table ...
AS
BEGIN
exec master.. xp_test table1 output -- can I do this ?

RETURN table1
END

Table and table1 are the same design.

Thank you very much !Yes, in your function you need to do a:INSERT INTO @.table EXECUTE master.dbo.xp_test -- and any parametersBEWARE: Non-deterministic code of any kind in your extended stored procedure can cause serious problems. Although it is highly unlikely, it is possible to even stop the SQL service!!!

-PatP|||For me it doesn't work :

INSERT INTO @.table EXECUTE master.dbo.xp_test

Originally posted by Pat Phelan
Yes, in your function you need to do a:INSERT INTO @.table
EXECUTE master.dbo.xp_test -- and any parametersBEWARE: Non-deterministic code of any kind in your extended stored procedure can cause serious problems. Although it is highly unlikely, it is possible to even stop the SQL service!!!

-PatP|||So it runs, but no rows are inserted and no error is raised ?

-PatP|||I was under the impression that you can not use execute inside a UDF except for calling extended stored procedures. Will have to check that Out ... Need to get back to the Holy Book