Concatenate Columns - Evaluate Nulls

[tqeonline]

Hello everyone!

So I have a table that has an ID, STATUS, and TIME... sometimes the times are null...

Say the table had this data:
ID STATUS TIME
1 Closed 2010-08-01 03:00:00.000
1 Submitted 2010-08-01 01:00:00.000
1 Deferred 2010-08-01 02:00:00.000
2 Submitted 2010-08-01 01:00:00.000
2 Deferred NULL

What I need it to return is this:
ID START TIME END TIME
1 2010-08-01 01:00:00.000 2010-08-01 03:00:00.000
2 2010-08-01 01:00:00.000 DATE.TIME.NOW

What that does is evaluates the MIN time, then determines the MAX TIME. However, with the Max time I need it to fill in the NULLs with Date.Time.Now.

So say there is a Closed and Deferred
Closed - 2010-08-01 01:00:00.000
Deferred - NULL

The null would enter Date.time.now and I would want that to be returned. Not the closed.

I can't figure it out. I saw some Case structure but it didn't add it as a new column. I guess I need to evaluate Deferred and Closed times together and then pick the max.

Ideas?

 

[drlex]

On the basis you want the text 'DATE.TIME.NOW' rather than the evaluated datetime:

create table #test ([ID] int, [STATUS] varchar(10), [TIME] datetime)
Insert into #test
Select 1 ,'Closed', '2010-08-01 03:00:00.000'
UNION Select 1 ,'Submitted', '2010-08-01 01:00:00.000'
UNION Select 1 ,'Deferred',    '2010-08-01 02:00:00.000'
UNION Select 2 ,'Submitted', '2010-08-01 01:00:00.000'
UNION Select 2 ,'Deferred',  NULL

select ID, min ([TIME]), max(ISNULL(CONVERT(varchar(25),[TIME],121),'DATE.TIME.NOW')) from #test
group by ID 

drop table #test

soi là, soi carré

 

[tqeonline]

Well what I need it to do is this.

If I give it a date that is NOT today... then put in that date and 11:59:59.999PM and if it is today then add the current time.

Ex.
Date Given - 8-1-2010 then return 2010-08-01 11:59:59.999
Date Given - Today (9/23/2010) then return 2010-09-23 07:53:21.123 <- (the current time)

So that's just if its null. Then I need it to compare the MAX time against the Deferred, Closed and Null times.

So it would say like MAX(END TIME)... given that the Deferred, Closed and Nulls are the END TIME.