Creating a 'Calculated' Column and using IF THEN ELSE

• Brad Allison

  Hall of Fame

  Points: 3550

  More actions

  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

• DesNorton

  SSC-Insane

  Points: 24465

  More actions

  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.

• Brad Allison

  Hall of Fame

  Points: 3550

  More actions

  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, 9 months ago by  Brad Allison.