Selecting max/min value from more than one fields

Question

In the following query the start/finish columns are datetime fields.

How should I modify this query to get two more columns, one with the min date and one with the max date (of all the 6 datetime fields and all the rows) repeated in each row.

Alternatively how could I create a new query returning only these 2 (min/max) dates, for the same resultset of course?

Thanks a lot! (I would like answers for both SQL Server 2005 and Sybase ASE 12.5.4)

select  erg_mst.code, 
        erg_types.perigrafh, 
        erg_mst.FirstBaseStart, 
        erg_mst.FirstBaseFinish,
        erg_mst.LastBaseStart, 
        erg_mst.LastBaseFinish ,
        erg_mst.ActualStart, 
        erg_mst.ActualFinish 
from    erg_mst inner join 
        erg_types  on erg_mst.type = erg_types.type_code  
where   erg_mst.activemodule = 'co' 
and     (
            FirstBaseStart <> NULL OR 
            FirstBaseFinish <> NULL OR  
            LastBaseStart <> NULL OR 
            LastBaseFinish <> NULL OR 
            ActualStart <> NULL OR 
            ActualFinish <> NULL 
        )  
order by    isnull(FirstBaseStart,isnull(LastBaseStart,ActualStart))

Answer 1

I can think of two solutions, but both will need to take on board Lucer's comment to use IS NOT NULL, rather than <> NULL.

1. Create a two user defined function to return the max and minimum values - ok, but assumes you have access to do it.
2. Use a series of UNION'ed selects, each one selecting one of the six columns and then use this as the inner nested SELECT where you then use SELECT MAX(), MIN() from that.

Answer 2

See below for a SQL Server 2005 code sample using Miles D's suggestion of using a series of UNION'ed selects (sorry, I don't know Sybase syntax):

select min(AllDates) as MinDate, max(AllDates) as MaxDate
from
(
select erg_mst.FirstBaseStart as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     FirstBaseStart IS NOT NULL
union all
select erg_mst.FirstBaseFinish as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     FirstBaseFinish IS NOT NULL
union all
select erg_mst.LastBaseStart as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     LastBaseStart IS NOT NULL
union all
select erg_mst.LastBaseFinish as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     LastBaseFinish IS NOT NULL
union all
select erg_mst.ActualStart as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     ActualStart IS NOT NULL
union all
select erg_mst.ActualFinish as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     ActualFinish IS NOT NULL
) #Temp