Reputation: 30445
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))
Upvotes: 2
Views: 3240
Reputation: 5135
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
Upvotes: 3
Reputation: 8040
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.
Upvotes: 1