Post reply

Creating a 'Calculated' Column and using IF THEN ELSE

  • June 3, 2019 at 2:45 pm

    #3649775

    I have a situation whereas I need to include a column that is nonexistent in the table(s).  The primary table has a CreatedDate and an UpdatedDate field.  I need to mark records that do not have an UpdatedDate and the date that this query is run is 7 or more days from the CreatedDate, as Late.  I have this line in my Select statement and I am sure it is totally incorrect

    IF  UpdatedDate is null and DATEDIFF(day,Now(),CreatedDate)>7 then Late=true else Late=false

    Thanks for the information

  • June 3, 2019 at 3:00 pm

    #3649781

    Brad Allison wrote:

    I have a situation whereas I need to include a column that is nonexistent in the table(s).  The primary table has a CreatedDate and an UpdatedDate field.  I need to mark records that do not have an UpdatedDate and the date that this query is run is 7 or more days from the CreatedDate, as Late.  I have this line in my Select statement and I am sure it is totally incorrect

    IF  UpdatedDate is null and DATEDIFF(day,Now(),CreatedDate)>7 then Late=true else Late=false

    Thanks for the information

    NOW() is not a tSQL function.

    That said, in SQL, you could add a calculated column

    ALTER TABLE xyz
    ADD IsLate AS CASE WHEN UpdatedDate IS NULL
                        AND DATEDIFF(day, CreatedDate, GETDATE()) > 7
                       THEN 1 ELSE 0
                  END;

    How to post data/code on a forum to get the best help.

  • June 3, 2019 at 3:05 pm

    #3649782

    Thanks for this useful information.  And yes, I just reworked your response and it works fine.   And yes, I am a programmer, not a DBA 🙂

    • This reply was modified 5 years, 5 months ago by  Brad Allison.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply