translate Nested IIF statement with minus to CASE WHEN SQL Server

[Mittle]

Can someone please help. need help with translating this IIF's statement to SQL Server CASE Statement . I dont what am meant to do to the minus part of the statement

I understand the part that reads CASE WHEN latestbaselineDate is NULL Then OriginalBaselineDate ELSE LatesbaselineDate

END as
[Milestone - Baseline Date],
Milestone.[Actual-ForecastDate] AS [Milestone - Forecast Date],



this is the IIF Statement .
---------------------------------------------------------------------------------------------------------------------------------------------
IIf([LatestBaselineDate] Is Null,[OriginalBaselineDate],[LatestBaselineDate]) AS [Milestone - Baseline Date],
Milestone.[Actual-ForecastDate] AS [Milestone - Forecast Date], [Actual-ForecastDate]-
(IIf([LatestBaselineDate] Is Null,[OriginalBaselineDate],[LatestBaselineDate])) AS [Milestone - Variance (Days)],
Milestone.Status AS [Milestone - Status], Milestone.MilestoneNotes AS [Milestone - Comments], Milestone.Source AS [Milestone - Category], Milestone.[Critical Path] AS [Delivery Critical Path]

 

[plog]

I dont what am meant to do to the minus part of the statement

There is no subtraction operation occuring. Brackets delimit field names. So "Milestone - Baseline Date" is the field name.

Further, no CASE necessary--use COALESCE to work around nulls:

COALESCE (Transact-SQL) - SQL Server

Transact-SQL reference for COALESCE, which returns the value of the first expression that does not evaluate to NULL.

docs.microsoft.com

 

[plog]

I apologize, there is a subtraction operation:

...
[Actual-ForecastDate]-
(IIf([LatestBaselineDate] Is Null,[OriginalBaselineDate],[LatestBaselineDate])) AS [Milestone - Variance (Days)]
...

Since that is operating on Dates you should use the DateDiff function in its place:

DATEDIFF (Transact-SQL) - SQL Server

Transact-SQL reference for the DATEDIFF function. Returns the numerical difference between a start and end date based on datepart.

docs.microsoft.com

 

[Isaac]

Should be transferred to SQL Server forum

'Milestone - Baseline Date' = case
                                when LatestBaselineDate is null then originalbaselinedate
                                else LatestBaselineDate
                            end,
'Milestone - Forecast Date' = Milestone.[Actual-ForecastDate],

'Milestone - Variance (Days)' = [Actual-ForecastDate] - (case
                                                            when [LatestBaselineDate] is null then [OriginalBaselineDate]
                                                            else [LatestBaselineDate]
                                                        end),
'Milestone - Status' =     Milestone.Status,
'Milestone - Comments' = Milestone.MilestoneNotes,
'Milestone - Category' = Milestone.Source         
'Delivery Critical Path' = Milestone.[Critical Path]

 

[arnelgp]

SELECT
CASE WHEN [LatestBaseLineDate] IS NULL THEN [OriginalBaseLineDate] 
    ELSE [LatestBaselineDate]  END AS [Milestone - Baseline Date],
Milestone.[Actual-ForecastDate] As [Milestone - Forecast Date],
CASE WHEN [LatestBaseLineDate] IS NULL THEN DateDiff(day,[Actual-ForecastDate],[OriginalBaseLineDate]) 
    ELSE DateDiff(day,[Actual-ForecastDate],[LatestBaselineDate]) END AS [Milestone - Variance (Days)],
Milestone.Status As [Milestone - Status],
Milestone.MilestoneNotes As [Milestone - Comments],
Milestone.Source As [Milestone - Category],
Milestone.[Critical Path] As [Delivery Critical Path]
FROM Milestone;

 

[Galaxiom]

Take the special characters out of the column names and it will be a lot easier to read.

 

[Galaxiom]

Should be transferred to SQL Server forum

'Milestone - Baseline Date' = case
                                when LatestBaselineDate is null then originalbaselinedate
                                else LatestBaselineDate
                            end,
'Milestone - Forecast Date' = Milestone.[Actual-ForecastDate],

'Milestone - Variance (Days)' = [Actual-ForecastDate] - (case
                                                            when [LatestBaselineDate] is null then [OriginalBaselineDate]
                                                            else [LatestBaselineDate]
                                                        end),
'Milestone - Status' =     Milestone.Status,
'Milestone - Comments' = Milestone.MilestoneNotes,
'Milestone - Category' = Milestone.Source        
'Delivery Critical Path' = Milestone.[Critical Path]

Not quite. Single quotes are text delimiters and double quotes are object name delimiters (with quoted identifiers ON) in TSQL.

 

[Isaac]

Not quite. Single quotes are text delimiters and double quotes are object name delimiters (with quoted identifiers ON) in TSQL.

it's been about a year or more now since I worked in SQL server and I've gotten Rusty

however, I think you're wrong in suggesting it cannot be written as I written it. single quotes are used around column aliases all the time.

Taken from https://www.sqlservertutorial.net/sql-server-basics/sql-server-alias/

SELECT first_name + ' ' + last_name AS 'Full Name' FROM sales.customers ORDER BY first_name;

In fact I've always used single quotes for column aliases
Edit - you are right, but only if quoted identifier is set to on as you mentioned.
Apparently I've done this when it is set to off, during which time it's perfectly acceptable.

 

[Mittle]

SELECT
CASE WHEN [LatestBaseLineDate] IS NULL THEN [OriginalBaseLineDate]
    ELSE [LatestBaselineDate]  END AS [Milestone - Baseline Date],
Milestone.[Actual-ForecastDate] As [Milestone - Forecast Date],
CASE WHEN [LatestBaseLineDate] IS NULL THEN DateDiff(day,[Actual-ForecastDate],[OriginalBaseLineDate])
    ELSE DateDiff(day,[Actual-ForecastDate],[LatestBaselineDate]) END AS [Milestone - Variance (Days)],
Milestone.Status As [Milestone - Status],
Milestone.MilestoneNotes As [Milestone - Comments],
Milestone.Source As [Milestone - Category],
Milestone.[Critical Path] As [Delivery Critical Path]
FROM Milestone;

fantastic, this works perfect and infact another member gave a different version using Coalesce . they both work perfect .really appreciate the prompt help of people one this Forum.