dk04
asked on
SQL Subtract Calculated Field
This is probably an easy question but how can I subtract T1.Sales from SalesComp a calculated field that is using "AS." It's not a field in the table so I'm having problems. I know I could write the formula again to get SalesComp but would like to just use the field name if possible.
SELECT T1.StoreNumber, T1.Date, T1.Sales, DateAdd(DAY,-371,T1.Date) AS DateComp,
(Select Sum (Sales)
FROM Sales
WHERE T1.StoreNumber = Sales.StoreNumber and Sales.Date = DateAdd(DAY,-371,T1.Date))
AS SalesComp, T1.Labor, T1.CashOS, T1.Comment
FROM Sales AS T1
ORDER BY T1.Date DESC, T1.StoreNumber ASC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SalesComp is giving the error
Msg 207, Level 16, State 1, Line 2
Invalid column name 'SalesComp'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'SalesComp'.
try this
;with a as (SELECT T1.StoreNumber, T1.Date, T1.Sales, DateAdd(DAY,-371,T1.Date) AS DateComp,
(Select Sum (Sales)
FROM Sales
WHERE T1.StoreNumber = Sales.StoreNumber and Sales.Date = DateAdd(DAY,-371,T1.Date))
AS SalesComp, T1.Labor, T1.CashOS, T1.Comment
FROM Sales AS T1)
Select *, SalesComp-Sales as sales1 from a
ORDER BY Date DESC, StoreNumber ASC
ASKER
Thanks
(Select Sum (Sales)
FROM Sales
WHERE T1.StoreNumber = Sales.StoreNumber and Sales.Date = DateAdd(DAY,-371,T1.Date))
AS SalesComp, T1.Labor, T1.CashOS, T1.Comment
FROM Sales AS T1
ORDER BY T1.Date DESC, T1.StoreNumber ASC