SQL Computed Column - Compare Dates and Return String

[zektheiguana]

I'm a complete SQL Noob so if this is ridiculously easy, I'll apologize now!

I'm trying to set a column "Status" in a record based on column DueDate compared to the current date as well as the contents of another column called DateClosed.

So in non-SQL syntax, what I'm looking at is

IF DueDate > CurrentDate AND DateClosed = Null then Status = 'Overdue' ELSE
IF DueDate < CurrentDate AND DateClosed = Null then Status = 'Open'
ELSE
Status = "Closed"

I've tried using a CASE statement in both the Computed Column field and setting it up with a CASE statement in a user defined function, but I guess I'm not getting the syntax...I'm using VS 2010

Any help would be very much appreciated!!

 

[gmmastros]

I think you have your logic backwards. If the DueDate is less than today, that means the due date already occurred and is therefore overdue.

To create a computed column, you probably want something like this:

Alter Table YourTableNameHere Add Status As
Case When DueDate < GetDate() And DateClosed Is NULL Then 'Overdue'
     When DueDate > GetDate() And DateClosed Is NULL Then 'Open'
     Else 'Closed'
     End

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[zektheiguana]

Yes, you're correct I had my < and > swapped. That worked, I knew it had to be easy but my mind wasn't wrapping around it.

Thanks for your help!

 

[gmmastros]

You're welcome.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom