Calculated Fields

Transact-SQL
1

In MS Access, I can write something like the following where I can create a field by calculated values. Then with that field, I can use it to be part of a calculation for the next field. Is there a way to do it in SQL Server?

SELECT 
	  My_ID
	, My_Unit + 1				AS My_1st_Total
	, My_1st_Total + 1			AS My_2nd_Total
FROM Table1
2

In SQL you cannot use results computed in a select statement within that same select statement.

There are alternatives, none of them as straighforward. One would be to redo the computations from scratch for each result you want to get. Another would be to use the values clause. A third option would be to use a CTE (common table expression) to compute some of the values. Example of the first and second approach are shown below:

DECLARE @tmp TABLE(My_Id INT);

INSERT INTO @tmp
    (
        My_Id
    )
VALUES
    (1),(20),(300);

-- 1.
SELECT 
	My_Id,
	c1= My_Id + 1,
	c2= My_Id + 1 + 2
FROM 
	@tmp;

SELECT
	My_Id,
	c1,
	c2
FROM
	@tmp t
	CROSS APPLY (VALUES (My_id+1) ) v1(c1)
	CROSS APPLY (VALUES (v1.c1 + 2) ) v2(c2)
3

I was going to do it like Method 1. Method 2 is new to me so thank you for showing me a sample of that. If you have a sample of Method 3, would love to see it so that I can learn. I know CTE but not all that great with it yet.

5

I am tasked to calculate Double Over Time, Over Time and Regular.

If an associate works more then:
12 hours = Double Over Time
8 hours = Over Time
Else = Regular Time

For example 1:
16 hours total daily
Type = Double Over Time
Double Over Time = 4
Over Time = 4
Regular = 8

For example 2:
7 hours total daily
Type = Regular
Double Over Time = 0
Over Time = 0
Regular = 7

I will write a Case statements to represent each of the 4 new columns (Type, DT Total, OT Total, Regular Total). As JamesK pointed out, I understand that I can't use a previous "computed" field to calculate the next field. However, I have been impressed with some of the short hand methods on here that I thought I post this question anyway just in case if someone has something impressive. Otherwise, I will just stick to the Case statements. Thanks