calculate on newly created columns

• mcinvalek

  SSC-Addicted

  Points: 429

  More actions

  August 2, 2014 at 12:36 pm

  #288725

  I have a table that I have created a table and desire to do some basic math by adding a few new columns. The problem is that i cant get this to work without create many new select statements. The new columns that I wish to add refer to other newly created columns. Is there a way I can do this with CTW or subqueries? Unless it is a best practice to chain out the logic for the newly created columns

  I have an example from AdventureWorksDW since the data is very accessible. I can safely create EMP_TENURE and PTO_REMAINING is this select statement. I would then need to create a new select statement to define 'BONUS' and then another select statement to define 'NEW_COL1' and so on.

  Im still pretty new at SQL and am trying to learn how to complete such a task using subqueries or CTE.

  SELECT

  NAME = [LastName] + ',' + [FirstName]

  ,HireDate

  ,Title

  ,DepartmentName

  ,BaseRate

  ,VacationHours

  ,SickLeaveHours

  ---NEW COLUMNS THAT i WOULD LIKE TO ADD

  ,EMP_TENURE = ROUND(DATEDIFF(DAY,HireDate,'2014-08-01')/365.25,2)

  ,PTO_REMAINING --SOME LOGIC OFF OF VACATION DAYS

  ,BONUS = --SOME LOGIC INVOLVING 'EMP_TENURE' AND 'PTO_REMAINING'

  ,NEW_COL1 = -- SOME LOGIC INVOLVING 'EMP_TENURE' AND 'BONUS'

  ,NEW_COL2 = -- SOME LOGIC INVOLVING 'NEW_COL1', 'TITLE', AND 'BONUS'

  FROM [AdventureWorksDW].dbo.DimEmployee

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 2, 2014 at 12:50 pm

  #1734627

  WITH Employees AS (

  SELECT

  [LastName] + ',' + [FirstName] AS FullName

  ,HireDate

  ,Title

  ,DepartmentName

  ,BaseRate

  ,VacationHours

  ,SickLeaveHours

  ,ROUND(DATEDIFF(DAY,HireDate,'2014-08-01')/365.25,2) AS EMP_TENURE

  ,(SOME LOGIC OFF OF VACATION DAYS) AS PTO_REMAINING

  FROM [AdventureWorksDW].dbo.DimEmployee

  )

  SELECT

  FullName

  ,HireDate

  ,Title

  ,DepartmentName

  ,BaseRate

  ,VacationHours

  ,SickLeaveHours

  ,EMP_TENURE

  ,PTO_REMAINING

  , (SOME LOGIC INVOLVING 'EMP_TENURE' AND 'PTO_REMAINING') AS BONUS

  , (SOME LOGIC INVOLVING 'EMP_TENURE' AND 'PTO_REMAINING') AS NewCol1

  FROM Employees;

  You can nest CTEs, but it can become unreadable. Sometimes better just to repeat the logic for the other columns

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass