Convert IF THEN ELSE from Excel to SQL

Question

I am trying to convert this excel formula to SQL but I really don't know how to do it.

=IF(WEEKDAY(INT(DateDB))=2;INT([DateR])=INT([DateDB]])-3;INT([DateR])=INT([DataDB])-1)

I started with CASE but I am not getting anywhere

CASE WHEN DATEPART(dw, CONVERT(DATE, [DataDB], 105)) = 2  THEN

Answer 1

Here is my function about change the Excel formula to SQL. All you have to do is run this function.

ALTER FUNCTION [dbo].[ConvertEXCELToSQL] (@ExcelString nvarchar(MAX))         

    RETURNS  nvarchar(MAX)
    WITH RETURNS NULL ON NULL INPUT
    AS
    BEGIN 
    DECLARE @res nvarchar(MAX) = ''
    DECLARE @countIF int = 0
    DECLARE @countNotIF int = 0
    DECLARE @i int = 1
    DECLARE @table TABLE (row int IDENTITY(1,1), value nvarchar(200))
    INSERT INTO @table
    SELECT VALUE FROM string_split(@ExcelString, ',')
    DECLARE @VALUE nvarchar(100)

    WHILE @i <= (SELECT COUNT(*) FROM @table)
        BEGIN
            SET @VALUE = (SELECT [dbo].[RemoveBreakFromString]((SELECT value FROM @table WHERE row = @i)))
            DECLARE @delete int = (SELECT LEN((SELECT value FROM @table WHERE row = @i)) - LEN(REPLACE((SELECT value FROM @table WHERE row = @i),')',''))) 
                                - (LEN((REPLACE((SELECT value FROM @table WHERE row = @i), 'IF(', ''))) - LEN(REPLACE((REPLACE((SELECT value FROM @table WHERE row = @i), 'IF(', '')), '(', '')))
            IF CHARINDEX('IF', @VALUE) > 0
            BEGIN
                SET @countNotIF = 0
                SET @countIF += 1
                SET @res = @res + '(SELECT CASE WHEN ' + (SELECT RIGHT(@VALUE, LEN(@VALUE) - 3)) + ' THEN '
            END
            ELSE 
            BEGIN
                SET @res = @res + @VALUE
                SET @countNotIF += 1
            END
            IF @countNotIF = 1
            BEGIN
                SET @res = @res + ' ELSE '
            END
            IF @countNotIF = 2
            BEGIN
                 SET @res = @res + ' END)'
            END
            IF @i != (SELECT COUNT(*) FROM @table)
            BEGIN
                IF @delete > 1
                BEGIN
                    SET @res = @res + ' END) ELSE '
                END
                ELSE IF @delete = 1
                BEGIN
                     SET @res = @res + ' ELSE '
                END
            END
            ELSE 
            BEGIN
                DECLARE @countRes int = 2
                WHILE @countRes <= @delete
                BEGIN
                    SET @res = @res + ' END)'
                    SET @countRes += 1
                END
            END


            SET @i += 1
        END
    SET @res = (SELECT (LEFT(@res, LEN(@res)-1)))
    SET @res = (SELECT (RIGHT(@res, LEN(@res)-1)))
    RETURN @res; 
  END

This convert for you from Excel formula to SQL code. All you have to do is run this function and execute it in SQL.

Answer 2

You essentially want to return True under the following complex condition:

• The weekday of DateDB is 2
  AND
  The difference between DateR and DateDB is 3 days

  OR

• The weekday of DateDB is not 2
  AND
  The difference between DateR and DateDB is 1 day.

Otherwise you want to return False.

You can implement the above as a check on a single complex condition and thus end up with a conditional having just two branches, one THEN and one ELSE:

CASE
  WHEN (DATEPART(WEEKDAY, DateDB) =  2 AND DATEDIFF(DAY, DateR, DateDB) = 3)
    OR (DATEPART(WEEKDAY, DateDB) <> 2 AND DATEDIFF(DAY, DateR, DateDB) = 1)
  THEN 'TRUE'
  ELSE 'FALSE'
END

Note that you do not have to convert a datetime value to the date type in order to determine its weekday. Avoiding the conversion makes your DATEPART expressions more compact.

And rather than comparing a date to another date minus a certain number of days, you can just calculate the difference in days between the two dates and compare it to the required value. The benefit of turning it around like that is, again, that you do not need to convert the two datetime values to dates, ending up with a more compact (and thus perhaps more clear) expression for the comparison.

Now the above solution is certainly more concise than the one you came up with, but there is a way of making it even more concise if you put the logic e.g. like this:

• If the difference between DateR and DateDB is 3 (if the weekday of DateDB is 2) or 1 (otherwise),
  then True,
  otherwise False.

Although more obscure, the above logic can be rendered in a way that avoids repetition of identical expressions, making the result more compact, albeit with nesting of conditionals. See for yourself if it looks clear enough for you:

CASE DATEDIFF(DAY, DateR, DateDB)
  WHEN (CASE DATEPART(WEEKDAY, DateDB) WHEN 2 THEN 3 ELSE 1 END)
  THEN 'TRUE'
  ELSE 'FALSE'
END

By the way, SQL Server supports a function called IIF() which is basically the same as the Excel IF() function, except you cannot use boolean expressions in the Then or the Else part, which excludes the possibility of a straightforward conversion of your IF to a Transact-SQL IIF. You can, however, rewrite both solutions in this answer using IIF:

1. IIF(
     (DATEPART(WEEKDAY, DateDB) =  2 AND DATEDIFF(DAY, DateR, DateDB) = 3)
     OR (DATEPART(WEEKDAY, DateDB) <> 2 AND DATEDIFF(DAY, DateR, DateDB) = 1),
     'TRUE',
     'FALSE'
   )
   

2. IIF(DATEDIFF(DAY, DateR, DateDB) = IIF(DATEPART(WEEKDAY, DateDB) = 2, 3, 1), 'TRUE', 'FALSE')
   

Answer 3

Based on your Excel formula, It looks like you're trying to do something like...

If "Day 1" is Tuesday 
    Check That "Day 2" is 3 days prior
Otherwise
    Check That "Day 2" is 1 day prior

If this is the case, a slightly clearer version of the case statement above might look something like...

declare 
    @dt1 date = '2018-01-25',
    @dt2 date = '2018-01-22';

with cte as (
    select 
        diff = datediff(day,@dt1,@dt2),
        dow1 = datepart(weekday,@dt1)
) 
select 
    case dow1
        when 2 then 
            (case when diff = -3 then 'yes' else 'no' end)
        else 
            (case when diff = -1 then 'yes' else 'no' end)
from cte

I encourage you to check out the datefirst page to learn about the day-of-week settings on your instance.

Answer 4

I just found an answer. Maybe there is a simple way.

CASE WHEN DATEPART(dw, CONVERT(DATE, [DateBD], 105)) = 2  AND 
    CONVERT(DATE, [DateR], 105) = DATEADD(DAY, -3, CONVERT(DATE, [DateBD], 105)) THEN 'TRUE'
     WHEN DATEPART(dw, CONVERT(DATE, [DateBD], 105)) = 2  AND 
    CONVERT(DATE, [DateR], 105) <> DATEADD(DAY, -3, CONVERT(DATE, [DateBD], 105)) THEN 'FALSE'
     WHEN DATEPART(dw, CONVERT(DATE, [DateBD], 105)) <> 2 AND 
    CONVERT(DATE, [DateR], 105) = DATEADD(DAY, -1, CONVERT(DATE, [DateBD], 105)) THEN 'TRUE' ELSE 'FALSE'
END as [myValue]