applying aggregate function on computed column

• MuditGupta

  SSCrazy

  Points: 2596

  More actions

  October 20, 2008 at 2:19 pm

  #194916

  Hello

  I am trying to apply MIN function on computed Column. Wondering how can I do it.

  Code example:

  SELECT yr

  ,zone

  ,date

  ,code1

  , enterDate=

  CASE WHEN code1='XY' THEN date ELSE NULL

  ,ExitDate=

  CASE WHEN code1='ZX' THEN date ELSE NULL

  INTO #tmp

  FROMTableA

  Next, I want to get result set as YR,Zone, EnterDate,ExitDate

  For zone=Value1;

  there can be multiple entries of date. I want to get MIN value among available for 1 zone.

  Similarly,

  Zone=value1, I want MAX of ExitDate.

  I was inserting above mentioned code in tmp table.

  then I created another query :

  SELECT TMP.Yr,TMP.Zone,TMP.Code1,TMP.EnterDate

  FROM #tmp AS TMP

  WHERE

  TMP.EnterDate= (SELECT MIN(TMP2.EnterDate)

  FROM #tmp AS TMP2

  WHERE TMP2.yr=TMP.yr

  AND TMP2.Zone=TMP.Zone)

  ORTMP.ExitDate = (SELECT MAX(TMP2.ExitDate)

  FROM #tmp AS TMP2

  WHERE TMP2.yr=TMP.yr

  AND TMP2.Zone=TMP.Zone)

  But I don't think this is an effective way to do. AS first I run part-1 and then part-2.

  what can I do to make it happen in more effective manner?

  Thanks

• dmc-608719

  SSCarpal Tunnel

  Points: 4663

  More actions

  October 20, 2008 at 2:52 pm

  #887330

  Might be missing something... but can you do something like this?

  select z,

  min(case code1 when 'xy' then date else null end),

  max(case code1 when 'zx' then date else null end)

  from table

  where zone = 'zone1'

  group by zone