dates

I have a record with two date fields.   I want to do:

SELECT    Min(Date1), MAX(date2)  FROM xxx

except date2 can be NULL, in which case I want the NULL value returned.  

How can I do this?   Maybe use a stored proc?
LVL 1
HLRosenbergerAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
SELECT MIN(t1.Date1) AS MinDate1,
    (SELECT TOP 1 t2.Date2
    FROM SomeTable t2
    WHERE t2.Date1 = MAX(t1.Date1)) AS Date2ForMaxDate1
FROM SomeTable t1
0
 
Patrick MatthewsCommented:
Your statement above will return the earliest non-null Date1, and the latest non-null Date2.  If all of the Date1 and/or Date2 values are null, then the MIN and MAX aggregate functions will likewise return null.

Is that what yu wanted?  If not, please explain.
0
 
Ariful AmbiaHead of MISCommented:
Yes u can make a sp

If  isnull(SELECT MAX(date2)  FROM xxx,0)=0
begin
SELECT  MAX(date2)  FROM xxx
End
Else
Begin
SELECT    Min(Date1), MAX(date2)  FROM xxx
End
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
HLRosenbergerAuthor Commented:
I did not explain correctly.  My apologies.

Suppose I have dates like this.

Date1                        Date2

1/2/2011                   1/24/2011
2/3/2011                   2/12/2011


For results I want:  Date1=1/2/2011 and Date2=2/12/2011.  This is MIN(Date1) and MAX(Date2)


Now suppose I have these dates:

1/2/2011                   1/24/2011
2/3/2011                   2/12/2011
3/11/2011                 NULL

For results I want   Date1=1/2/2011  and Date2=NULL.  

For Date2, I want the value - even if null - that is associated with the MAX(date1).

 
I do not know how to accomplish this.

0
 
TempDBACommented:
declare @d1 datetime
declare @d2 datetime

if exists (select 1 from xxx where date1 is null)
set @d1 = NULL
else
select @d1= min(date1) from xxx

if exists (select 1 from xxx where date2 is null)
set @d2 = NULL
else
select @d2=max(date2) from xxx

select @d1,@d2
0
 
HLRosenbergerAuthor Commented:
That's the cleanest and easiest solution
0
 
HLRosenbergerAuthor Commented:
I have a followup on this.  I need to return a second field in the Subquery where second_field is the value in the same record as MAX(t1.Date), like:

SELECT MIN(t1.Date1) AS MinDate1,
    (SELECT TOP 1 t2.Date2, second_field
    FROM SomeTable t2
    WHERE t2.Date1 = MAX(t1.Date1)) AS Date2ForMaxDate1
FROM SomeTable t1

But, this is invalid SQL.

 How can I do this?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.