Embed If Statement Inside Case

sql, sql-server, t-sql

Solution

You can't combine logic in that manner, but you can add a nested `CASE` your existing `CASE` statement:

SET @ReturnDate = (CASE WHEN NOT @PaymentDOM IS NULL THEN
    @ReturnDate + (@PaymentDOM - DATEPART(day, DateAdd(mm,1,@ReturnDate))) 
WHEN NOT @PaymentDays IS NULL THEN 
    DATEADD(Day,@PaymentDays,@ReturnDate)
WHEN NOT @PaymentSemiMonthlyOffset IS NULL THEN
    CASE WHEN @LDCCode = 40 AND @InvDateDayMonth > 11 AND @InvDateDayMonth < 26 THEN
        @ReturnDate + (@PaymentSemiMonthlyOffset - DATEPART(day, DateAdd(mm,1,@ReturnDate)))
    ELSE
        @ReturnDate + (@PaymentSemiMonthlyDays - DATEPART(day, DateAdd(mm,1,@ReturnDate)))
    END   
END)

Though, if this is in a stored procedure, I might just opt for simpler `IF/THEN/ELSE` structure instead of the `CASE`:

IF @PaymentDOM IS NULL
    SET @ReturnDate = @ReturnDate + (@PaymentDOM - DATEPART(day, DateAdd(mm, 1, @ReturnDate))) 
ELSE IF NOT @PaymentDays IS NULL
    SET @ReturnDate = DATEADD(Day,@PaymentDays,@ReturnDate)
ELSE IF NOT @PaymentSemiMonthlyOffset IS NULL
BEGIN
    IF @LDCCode = 40 AND @InvDateDayMonth > 11 AND @InvDateDayMonth < 26
        SET @ReturnDate = @ReturnDate + (@PaymentSemiMonthlyOffset - DATEPART(day, DateAdd(mm, 1, @ReturnDate)))
    ELSE
        SET @ReturnDate = @ReturnDate + (@PaymentSemiMonthlyDays - DATEPART(day, DateAdd(mm, 1, @ReturnDate)))
END

Problem

Original source