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