Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

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?
0
HLRosenberger
Asked:
HLRosenberger
1 Solution
 
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 ITCommented:
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
Patrick MatthewsCommented:
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
 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now