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.
Who is Participating?
Walter RitzelSenior Software EngineerCommented:
Use this:
bhiebAuthor Commented:
Perfect, so as long as I don't make this persistent it will recalc every time it is called?
Walter RitzelSenior Software EngineerCommented:
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()
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..
You should be able to use ISNULL.  


Open in new window

Hope this helps,

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.