calculating with variables statistical values. How?

Dear all,
I am trying to solve this but I can't work out.
Set @.DESV_INSERTED = (SELECT stdev(desv) FROM #DESVIATIONTYPICAL WHERE
desviation_id = @.desviation_id AND desv > 0)
SET @.AVERAGE = (SELECT AVG(DESV) FROM #DESVIATIONTYPICAL WHERE
desviation_id = @.desviation_id AND desv > 0)
SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
SET @.MINIMUM = SELECT (@.AVERAGE - @.AUXMIN)
SET @.MAXIMUM = SELECT (@.AVERAGE + @.AUXMIN)
And I obtain the following errors:
Server: Msg 156, Level 15, State 1, Procedure
up_WHS_PeriodWarningQControl_PROC, Line 850
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Procedure
up_WHS_PeriodWarningQControl_PROC, Line 852
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Procedure
up_WHS_PeriodWarningQControl_PROC, Line 853
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Procedure
up_WHS_PeriodWarningQControl_PROC, Line 855
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Procedure
up_WHS_PeriodWarningQControl_PROC, Line 856
Line 856: Incorrect syntax near '='.
Thanks in advance and best regards,Change:
SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
SET @.MINIMUM = SELECT (@.AVERAGE - @.AUXMIN)
SET @.MAXIMUM = SELECT (@.AVERAGE + @.AUXMIN)
To:
SET @.AUXMIN = 1.96 * @.DESV_INSERTED
SET @.MINIMUM = AVERAGE - @.AUXMIN
SET @.MAXIMUM = AVERAGE + @.AUXMIN
and you probably have a few more similar lines in that procedure _after_ the
code you have shown. The line numbers shown in the errors are the lines in
the body of the stored procedure, after the keyword 'AS', so they have an
offset comapred to the line number in the stored procedure declaration
(which includes CREATE PROC and the parameter declarations).
Jacco Schalkwijk
SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:30173799-B153-49B2-9223-4C4B282A2B27@.microsoft.com...
> Dear all,
> I am trying to solve this but I can't work out.
>
> Set @.DESV_INSERTED = (SELECT stdev(desv) FROM #DESVIATIONTYPICAL WHERE
> desviation_id = @.desviation_id AND desv > 0)
> SET @.AVERAGE = (SELECT AVG(DESV) FROM #DESVIATIONTYPICAL WHERE
> desviation_id = @.desviation_id AND desv > 0)
>
> SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
> SET @.MINIMUM = SELECT (@.AVERAGE - @.AUXMIN)
> SET @.MAXIMUM = SELECT (@.AVERAGE + @.AUXMIN)
>
> And I obtain the following errors:
>
> Server: Msg 156, Level 15, State 1, Procedure
> up_WHS_PeriodWarningQControl_PROC, Line 850
> Incorrect syntax near the keyword 'SELECT'.
> Server: Msg 156, Level 15, State 1, Procedure
> up_WHS_PeriodWarningQControl_PROC, Line 852
> Incorrect syntax near the keyword 'SELECT'.
> Server: Msg 156, Level 15, State 1, Procedure
> up_WHS_PeriodWarningQControl_PROC, Line 853
> Incorrect syntax near the keyword 'SELECT'.
> Server: Msg 156, Level 15, State 1, Procedure
> up_WHS_PeriodWarningQControl_PROC, Line 855
> Incorrect syntax near the keyword 'SELECT'.
> Server: Msg 170, Level 15, State 1, Procedure
> up_WHS_PeriodWarningQControl_PROC, Line 856
> Line 856: Incorrect syntax near '='.
>
> Thanks in advance and best regards,|||Enric,
SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
is invalid syntax. To use a SELECT expression as
a value, you need to surround the entire SELECT
expression in parenthesis:
SET @.AUXMIN = (SELECT (1.96 * @.DESV_INSERTED))
You did this correctly in the first to SET statement,
but not in the final three.
Steve Kass
Drew University
Enric wrote:

>Dear all,
>I am trying to solve this but I can't work out.
>
>Set @.DESV_INSERTED = (SELECT stdev(desv) FROM #DESVIATIONTYPICAL WHERE
> desviation_id = @.desviation_id AND desv > 0)
>SET @.AVERAGE = (SELECT AVG(DESV) FROM #DESVIATIONTYPICAL WHERE
> desviation_id = @.desviation_id AND desv > 0)
>
>SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
>SET @.MINIMUM = SELECT (@.AVERAGE - @.AUXMIN)
>SET @.MAXIMUM = SELECT (@.AVERAGE + @.AUXMIN)
>
>And I obtain the following errors:
>
>Server: Msg 156, Level 15, State 1, Procedure
>up_WHS_PeriodWarningQControl_PROC, Line 850
>Incorrect syntax near the keyword 'SELECT'.
>Server: Msg 156, Level 15, State 1, Procedure
>up_WHS_PeriodWarningQControl_PROC, Line 852
>Incorrect syntax near the keyword 'SELECT'.
>Server: Msg 156, Level 15, State 1, Procedure
>up_WHS_PeriodWarningQControl_PROC, Line 853
>Incorrect syntax near the keyword 'SELECT'.
>Server: Msg 156, Level 15, State 1, Procedure
>up_WHS_PeriodWarningQControl_PROC, Line 855
>Incorrect syntax near the keyword 'SELECT'.
>Server: Msg 170, Level 15, State 1, Procedure
>up_WHS_PeriodWarningQControl_PROC, Line 856
>Line 856: Incorrect syntax near '='.
>
>Thanks in advance and best regards,
>|||yeah, works.
I am feel very proud of your performance.
Enric
"Jacco Schalkwijk" wrote:

> Change:
> SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
> SET @.MINIMUM = SELECT (@.AVERAGE - @.AUXMIN)
> SET @.MAXIMUM = SELECT (@.AVERAGE + @.AUXMIN)
> To:
> SET @.AUXMIN = 1.96 * @.DESV_INSERTED
> SET @.MINIMUM = AVERAGE - @.AUXMIN
> SET @.MAXIMUM = AVERAGE + @.AUXMIN
> and you probably have a few more similar lines in that procedure _after_ t
he
> code you have shown. The line numbers shown in the errors are the lines in
> the body of the stored procedure, after the keyword 'AS', so they have an
> offset comapred to the line number in the stored procedure declaration
> (which includes CREATE PROC and the parameter declarations).
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:30173799-B153-49B2-9223-4C4B282A2B27@.microsoft.com...
>
>|||Thanks Steve,
Regards to all
"Steve Kass" wrote:

> Enric,
> SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
> is invalid syntax. To use a SELECT expression as
> a value, you need to surround the entire SELECT
> expression in parenthesis:
> SET @.AUXMIN = (SELECT (1.96 * @.DESV_INSERTED))
> You did this correctly in the first to SET statement,
> but not in the final three.
> Steve Kass
> Drew University
>
>
> Enric wrote:
>
>