Solved

# dates

Posted on 2011-10-31
300 Views
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
Question by:HLRosenberger

LVL 92

Expert Comment

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

LVL 4

Expert Comment

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

LVL 1

Author Comment

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

LVL 25

Expert Comment

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

LVL 92

Accepted Solution

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

LVL 1

Author Closing Comment

That's the cleanest and easiest solution
0

LVL 1

Author Comment

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.