computed column SQL 2008 if statement

I have a duration column in SQL that is (datediff(minute,[InTime],[OutTime])). Then I sum the status during that duration to get the % time spent in each status. My question is the most current status does not have an OutTime yet and therefore no duration, thus it is being excluded from the %.

What I would like to do is use Getdate() as the OutTime so that when a query is ran it is always recalculated to the current time. Key is that OutTime needs to remain null.

I was thinking (if(OutTime is null, datediff(minute,[InTime],GetDate()), datediff(minute,[InTime],[OutTime])))

I get an error validating field when I do that. I guess I could throw it in a view and query that instead.
bhiebAsked:
Who is Participating?
 
Walter RitzelSenior Software EngineerCommented:
Use this:
 (datediff(minute,[InTime],IsNull([OutTime],GetDate())))
0
 
bhiebAuthor Commented:
Perfect, so as long as I don't make this persistent it will recalc every time it is called?
0
 
Walter RitzelSenior Software EngineerCommented:
Yes.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Wild Guess>  
Same expression you have now, but use an ISNULL function to replace null values with the GETDATE:

Declare @dt datetime = GETDATE()
SELECT ... 
datediff(minute,[InTime],ISNULL([OutTime], @dt)) as duration

Open in new window


A case can also be made to remove all rows WHERE OutTime IS NULL from the query, as it is somewhat of a false statement as who knows how long the duration will be of what's in progress..
0
 
JestersGrindCommented:
You should be able to use ISNULL.  

DATEDIFF(MINUTE, [InTime], ISNULL([OutTime], GETDATE()))

Open in new window


Hope this helps,

Greg
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.