SQLTeam.com | Weblogs | Forums

How to use calculated column to create the next column

Please advise me how can I do calculations based on the previous calculated column. This is an example, I`d like to create new_column_2 by using already calculated column

SELECT user_id+10 AS new_column,
new_column+ 100 AS new_column_2
FROM user_details

WITH TableUserID 
AS
(
SELECT 	
	user_id+10 AS new_column
FROM user_details
)
SELECT 
	new_column,
	new_column + 100
FROM
	TableUserID;
1 Like

I have found that using CROSS APPLY is much easier:

SELECT e1.new_column
     , new_column_2 = e1.new_column + 100
  FROM user_details                         ud
 CROSS APPLY (VALUES (ud.user_id + 10))     e1(new_column)

Or - you can stack them:

SELECT e1.new_column
     , e2.new_column2
  FROM user_details                         ud
 CROSS APPLY (VALUES (ud.user_id + 10))     e1(new_column)
 CROSS APPLY (VALUES (e1.new_column + 100)) e2(new_column_2)
1 Like