SQL Server 2005 derived field w/ logic

Question

i have three tables Pproject, ProjectMilestone and ReleaseSchedule.

i want to add a derived field to the project table called EndDate.

the value of this new field called EndDate should be the larger of the two values ProjectMilestone.EndDate and ReleaseSchedule.EndDate.

the SQL logic to get the two data points of EndDate within both those tables is like this:

select EndDate from ProjectMilestone where MilestoneCID = 77 and projectId = project.projectId

select EndDate from ReleaseSchedule where MilestoneCID = 77 releaseId = project.releaseId

so i basically need the derived field to be the larger of these 2 values and if neither value exist to have 'N/A'

i am at this point trying to get the case statement to work...

ALTER TABLE Project ADD EndDate AS (CASE WHEN ProjectMilestone.EndDate < ReleaseSchedule.EndDate THEN ProjectMilestone.EndDate ELSE ReleaseSchedule.EndDate END)

how do i get N/A if both are null?

thanks all

Answer 1

This is the sort of thing that would probably be better handled in your client application, but you can do it in T-SQL as below.

All outputs from the CASE statement have to be of the same data type so you have to cast the dates to char or varchar.

ALTER TABLE Project 
ADD EndDate AS 
ISNULL((CASE 
  WHEN ProjectMilestone.EndDate < ReleaseSchedule.EndDate 
    THEN CAST(ProjectMilestone.EndDate AS VARCHAR(50))
  ELSE 
    CAST(ReleaseSchedule.EndDate AS VARCHAR(50)) 
  END), 'N/A')

Alternatively, you could use CONVERT instead of CAST and then specify a date style to get prettier dates.