Creating a 'Calculated' Column and using IF THEN ELSE

  • Brad Allison

    Hall of Fame

    Points: 3473

    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

  • DesNorton

    SSC-Insane

    Points: 22757

    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;
  • Brad Allison

    Hall of Fame

    Points: 3473

    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 4 months, 2 weeks ago by  Brad Allison.

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

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