CODEWITHSUNDEEP
sqlsql-serversql-server-2008
dynamicuser
dynamicuser

Reputation: 1552

SQL MIN and MAX when only one row

I have 2 lines in my query that select the minimum and maximum values from a table. There is only ever going to be a max of 2 rows returned, however if there is only 1 returned, I don't want anything returned for the second one.

IE:

MAX(CASE WHEN bd.DayText = 'Tuesday' and bd.BookingDuration = 3 
and CONVERT(time(0), bd.StartTime) < CONVERT(time(0), '12:00:00') 
AND bd.NoOfHOurs < 5.5 and s.PrimarySchool = 1 THEN bd.ID ELSE NULL END)
"TuesdayHourlyAM",

MIN(CASE WHEN bd.DayText = 'Tuesday' and bd.BookingDuration = 3 
and CONVERT(time(0), bd.StartTime) < CONVERT(time(0), '12:00:00') 
AND bd.NoOfHOurs < 5.5 and s.PrimarySchool = 1 THEN bd.ID ELSE NULL END)
"TuesdayHourlyAM2",

So if theres 1 row returned, TuesdayHourlyAM2 should return null.

Any ideas?

Thanks

Upvotes: 0

Views: 357

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269653

Try wrapping it in a case statement:

(case when count(*) > 1
      then MIN(CASE WHEN bd.DayText = 'Tuesday' and bd.BookingDuration = 3 and
                         CONVERT(time(0), bd.StartTime) < CONVERT(time(0), '12:00:00') AND
                         bd.NoOfHOurs < 5.5 and s.PrimarySchool = 1
                    THEN bd.ID ELSE NULL
               END)
 end) as TuesdayHourlyAM2

Upvotes: 1

Related Questions