SEO

Hi me again, well now i think that i am really looser jejee, how can i
do this whitout pass the table 8 fucking times!!!, i'm sorry but i am
really angry because at this moment i cannot visualize the solution,
help me please:

SELECT
@MAX_HEPE_OUT1 = ISNULL(MAX(Hepe),0),
@MIN_HEPE_OUT1 = ISNULL(MIN(Hepe),0),
@AVG_HEPE_OUT1 = ISNULL(AVG(Hepe),0)
FROM #TMP2_UB_HEPE
WHERE
PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE_AUX AND
OutputSource = '1'

SELECT
@MAX_HEPE_OUT2 = ISNULL(MAX(Hepe),0),
@MIN_HEPE_OUT2 = ISNULL(MIN(Hepe),0),
@AVG_HEPE_OUT2 = ISNULL(AVG(Hepe),0)
FROM #TMP2_UB_HEPE
WHERE
PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE_AUX AND
OutputSource = '2'

SELECT
@MAX_HEPE_OUT3 = ISNULL(MAX(Hepe),0),
@MIN_HEPE_OUT3 = ISNULL(MIN(Hepe),0),
@AVG_HEPE_OUT3 = ISNULL(AVG(Hepe),0)
FROM #TMP2_UB_HEPE
WHERE
PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE_AUX AND
OutputSource = '3'

SELECT
@MAX_HEPE_OUT4 = ISNULL(MAX(Hepe),0),
@MIN_HEPE_OUT4 = ISNULL(MIN(Hepe),0),
@AVG_HEPE_OUT4 = ISNULL(AVG(Hepe),0)
FROM #TMP2_UB_HEPE
WHERE
PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE_AUX AND
OutputSource = '4'

SELECT
@MAX_HEPE_OUT5 = ISNULL(MAX(Hepe),0),
@MIN_HEPE_OUT5 = ISNULL(MIN(Hepe),0),
@AVG_HEPE_OUT5 = ISNULL(AVG(Hepe),0)
FROM #TMP2_UB_HEPE
WHERE
PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE_AUX AND
OutputSource = '5'

SELECT
@MAX_HEPE_OUT6 = ISNULL(MAX(Hepe),0),
@MIN_HEPE_OUT6 = ISNULL(MIN(Hepe),0),
@AVG_HEPE_OUT6 = ISNULL(AVG(Hepe),0)
FROM #TMP2_UB_HEPE
WHERE
PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE_AUX AND
OutputSource = '6'

SELECT
@MAX_HEPE_OUT7 = ISNULL(MAX(Hepe),0),
@MIN_HEPE_OUT7 = ISNULL(MIN(Hepe),0),
@AVG_HEPE_OUT7 = ISNULL(AVG(Hepe),0)
FROM #TMP2_UB_HEPE
WHERE
PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE_AUX AND
OutputSource = '7'

SELECT
@MAX_HEPE_OUT8 = ISNULL(MAX(Hepe),0),
@MIN_HEPE_OUT8 = ISNULL(MIN(Hepe),0),
@AVG_HEPE_OUT8 = ISNULL(AVG(Hepe),0)
FROM #TMP2_UB_HEPE
WHERE
PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE_AUX AND
OutputSource = '8'

Quit thinking "one row at time" and start thinking about whole sets.
Stop using the proprietary ISNULL() and use the Standard SQL
COALESCE(). You probably should not be using a # table, too.

SELECT outputsource, COALESCE(MAX (hepe), 0) AS max_hepe_out3,
COALESCE(MIN(hepe), 0) AS min_hepe_out3,
COALESCE(AVG(hepe), 0) AS avg_hepe_out3
FROM #tmp2_ub_hepe
WHERE pmm_datetime BETWEEN @dinit_date AND @dend_date_aux
AND outputsource IN ('1', '2', '3', '4', '5', '6', '7', '8')
GROUP BY outputsource;

Assuming that assigning the values to local variables is important,
something like the following might be used:

SELECT
@MAX_HEPE_OUT1 = COALESCE( MAX( CASE when Outputsource = 1 then Hepe
else <minvalue> end ),0),
@MIN_HEPE_OUT1 = COALESCE( MIN( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0),
@AVG_HEPE_OUT1 = COALESCE( AVG( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0)
@MAX_HEPE_OUT2 = COALESCE( MAX( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0),
@MIN_HEPE_OUT2 = COALESCE( MIN( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0),
@AVG_HEPE_OUT2 = COALESCE( AVG( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0)
@MAX_HEPE_OUT3 = COALESCE( MAX( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0),
@MIN_HEPE_OUT3 = COALESCE( MIN( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0),
@AVG_HEPE_OUT3 = COALESCE( AVG( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0)
@MAX_HEPE_OUT4 = COALESCE( MAX( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0),
@MIN_HEPE_OUT4 = COALESCE( MIN( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0),
@AVG_HEPE_OUT4 = COALESCE( AVG( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0)
@MAX_HEPE_OUT5 = COALESCE( MAX( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0),
@MIN_HEPE_OUT5 = COALESCE( MIN( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0),
@AVG_HEPE_OUT5 = COALESCE( AVG( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0)
@MAX_HEPE_OUT6 = COALESCE( MAX(CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0),
@MIN_HEPE_OUT6 = COALESCE( MIN( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0),
@AVG_HEPE_OUT6 = COALESCE( AVG( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0)
@MAX_HEPE_OUT7 = COALESCE( MAX( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0),
@MIN_HEPE_OUT7 = COALESCE( MIN( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0),
@AVG_HEPE_OUT7 = COALESCE( AVG( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0)
@MAX_HEPE_OUT8 = COALESCE( MAX( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0),
@MIN_HEPE_OUT8 = COALESCE( MIN( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0),
@AVG_HEPE_OUT8 = COALESCE( AVG( CASE when Outputsource = 1 then
Hepeelse <minvalue> end ),0)
FROM #TMP2_UB_HEPE
WHERE
PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE_AUX

On 23 abr, 13:46, b...@sybase.com wrote:
> Assuming that assigning the values to local variables is important,
> something like the following might be used:
>
> SELECT
> @MAX_HEPE_OUT1 = COALESCE( *MAX( CASE when Outputsource = 1 then Hepe
> else <minvalue> end ),0),
> @MIN_HEPE_OUT1 = *COALESCE( *MIN( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0),
> @AVG_HEPE_OUT1 = COALESCE( *AVG( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0)
> @MAX_HEPE_OUT2 = COALESCE( *MAX( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0),
> @MIN_HEPE_OUT2 = COALESCE( *MIN( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0),
> @AVG_HEPE_OUT2 = COALESCE( *AVG( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0)
> @MAX_HEPE_OUT3 = COALESCE( *MAX( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0),
> @MIN_HEPE_OUT3 = COALESCE( *MIN( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0),
> @AVG_HEPE_OUT3 = COALESCE( *AVG( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0)
> @MAX_HEPE_OUT4 = COALESCE( *MAX( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0),
> @MIN_HEPE_OUT4 = COALESCE( *MIN( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0),
> @AVG_HEPE_OUT4 = COALESCE( *AVG( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0)
> @MAX_HEPE_OUT5 = COALESCE( *MAX( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0),
> @MIN_HEPE_OUT5 = COALESCE( *MIN( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0),
> @AVG_HEPE_OUT5 = COALESCE( *AVG( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0)
> @MAX_HEPE_OUT6 = COALESCE( *MAX(CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0),
> @MIN_HEPE_OUT6 = COALESCE( *MIN( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0),
> @AVG_HEPE_OUT6 = COALESCE( *AVG( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0)
> @MAX_HEPE_OUT7 = COALESCE( *MAX( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0),
> @MIN_HEPE_OUT7 = COALESCE( *MIN( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0),
> @AVG_HEPE_OUT7 = COALESCE( *AVG( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0)
> @MAX_HEPE_OUT8 = COALESCE( *MAX( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0),
> @MIN_HEPE_OUT8 = COALESCE( *MIN( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0),
> @AVG_HEPE_OUT8 = COALESCE( *AVG( CASE when Outputsource = 1 then
> Hepeelse <minvalue> end ),0)
> FROM #TMP2_UB_HEPE
> WHERE
> PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE_AUX

Assigning the values to local variables is VERY important, but i dont
understand this, you mean that i have put a MIN(CASE when X else Y
end),0)
where
X = Outputsource = 1,2,3......8
Y = ?????

Sorry but please give me more details, thanks!!

On Apr 23, 4:28 pm, Jorge Reyes <jorg_re...@hotmail.com> wrote:
> On 23 abr, 13:46, b...@sybase.com wrote:
>
>
>
> > Assuming that assigning the values to local variables is important,
> > something like the following might be used:
>
> > SELECT
> > @MAX_HEPE_OUT1 = COALESCE( MAX( CASE when Outputsource = 1 then Hepe
> > else <minvalue> end ),0),
> > @MIN_HEPE_OUT1 = COALESCE( MIN( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0),
> > @AVG_HEPE_OUT1 = COALESCE( AVG( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0)
> > @MAX_HEPE_OUT2 = COALESCE( MAX( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0),
> > @MIN_HEPE_OUT2 = COALESCE( MIN( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0),
> > @AVG_HEPE_OUT2 = COALESCE( AVG( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0)
> > @MAX_HEPE_OUT3 = COALESCE( MAX( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0),
> > @MIN_HEPE_OUT3 = COALESCE( MIN( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0),
> > @AVG_HEPE_OUT3 = COALESCE( AVG( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0)
> > @MAX_HEPE_OUT4 = COALESCE( MAX( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0),
> > @MIN_HEPE_OUT4 = COALESCE( MIN( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0),
> > @AVG_HEPE_OUT4 = COALESCE( AVG( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0)
> > @MAX_HEPE_OUT5 = COALESCE( MAX( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0),
> > @MIN_HEPE_OUT5 = COALESCE( MIN( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0),
> > @AVG_HEPE_OUT5 = COALESCE( AVG( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0)
> > @MAX_HEPE_OUT6 = COALESCE( MAX(CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0),
> > @MIN_HEPE_OUT6 = COALESCE( MIN( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0),
> > @AVG_HEPE_OUT6 = COALESCE( AVG( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0)
> > @MAX_HEPE_OUT7 = COALESCE( MAX( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0),
> > @MIN_HEPE_OUT7 = COALESCE( MIN( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0),
> > @AVG_HEPE_OUT7 = COALESCE( AVG( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0)
> > @MAX_HEPE_OUT8 = COALESCE( MAX( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0),
> > @MIN_HEPE_OUT8 = COALESCE( MIN( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0),
> > @AVG_HEPE_OUT8 = COALESCE( AVG( CASE when Outputsource = 1 then
> > Hepeelse <minvalue> end ),0)
> > FROM #TMP2_UB_HEPE
> > WHERE
> > PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE_AUX
>
> Assigning the values to local variables is VERY important, but i dont
> understand this, you mean that i have put a MIN(CASE when X else Y
> end),0)
> where
> X = Outputsource = 1,2,3......8
> Y = ?????
>
> Sorry but please give me more details, thanks!!

Sorry, I did get in a bit too much of a hurry. The query should have
had incrementing Outputsource values for each set of 3 variables, and
the else values should be NULLs

SELECT
@MAX_HEPE_OUT1 = COALESCE( MAX( CASE when Outputsource = 1 then Hepe
else NULL end ),0),
@MIN_HEPE_OUT1 = COALESCE( MIN( CASE when Outputsource = 1 then
Hepeelse NULL end ),0),
@AVG_HEPE_OUT1 = COALESCE( AVG( CASE when Outputsource = 1 then
Hepeelse NULL end ),0)
@MAX_HEPE_OUT2 = COALESCE( MAX( CASE when Outputsource = 2 then
Hepeelse NULL end ),0),
@MIN_HEPE_OUT2 = COALESCE( MIN( CASE when Outputsource = 2 then
Hepeelse NULL end ),0),
@AVG_HEPE_OUT2 = COALESCE( AVG( CASE when Outputsource = 2 then
Hepeelse NULL end ),0)
@MAX_HEPE_OUT3 = COALESCE( MAX( CASE when Outputsource = 3 then
Hepeelse NULL end ),0),
@MIN_HEPE_OUT3 = COALESCE( MIN( CASE when Outputsource = 3 then
Hepeelse NULL end ),0),
@AVG_HEPE_OUT3 = COALESCE( AVG( CASE when Outputsource = 3 then
Hepeelse NULL end ),0)
@MAX_HEPE_OUT4 = COALESCE( MAX( CASE when Outputsource = 4 then
Hepeelse NULL end ),0),
@MIN_HEPE_OUT4 = COALESCE( MIN( CASE when Outputsource = 4 then
Hepeelse NULL end ),0),
@AVG_HEPE_OUT4 = COALESCE( AVG( CASE when Outputsource = 4 then
Hepeelse NULL end ),0)
@MAX_HEPE_OUT5 = COALESCE( MAX( CASE when Outputsource = 5 then
Hepeelse NULL end ),0),
@MIN_HEPE_OUT5 = COALESCE( MIN( CASE when Outputsource = 5 then
Hepeelse NULL end ),0),
@AVG_HEPE_OUT5 = COALESCE( AVG( CASE when Outputsource = 5 then
Hepeelse NULL end ),0)
@MAX_HEPE_OUT6 = COALESCE( MAX(CASE when Outputsource = 6 then
Hepeelse NULL end ),0),
@MIN_HEPE_OUT6 = COALESCE( MIN( CASE when Outputsource = 6 then
Hepeelse NULL end ),0),
@AVG_HEPE_OUT6 = COALESCE( AVG( CASE when Outputsource = 6 then
Hepeelse NULL end ),0)
@MAX_HEPE_OUT7 = COALESCE( MAX( CASE when Outputsource = 7 then
Hepeelse NULL end ),0),
@MIN_HEPE_OUT7 = COALESCE( MIN( CASE when Outputsource = 7 then
Hepeelse NULL end ),0),
@AVG_HEPE_OUT7 = COALESCE( AVG( CASE when Outputsource = 7 then
Hepeelse NULL end ),0)
@MAX_HEPE_OUT8 = COALESCE( MAX( CASE when Outputsource = 8 then
Hepeelse NULL end ),0),
@MIN_HEPE_OUT8 = COALESCE( MIN( CASE when Outputsource = 8 then
Hepeelse NULL end ),0),
@AVG_HEPE_OUT8 = COALESCE( AVG( CASE when Outputsource = 8 then
Hepeelse NULL end ),0)
FROM #TMP2_UB_HEPE
WHERE
PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE_AUX