• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 831
  • Last Modified:

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

Open in new window

0
dk04
Asked:
dk04
  • 2
  • 2
1 Solution
 
cyberkiwiCommented:
SELECT T1.StoreNumber, T1.Date, SalesComp - 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
0
 
cyberkiwiCommented:
Apologies, please ignore previous comment.  I thought you meant that SalesComp is a calculated field in the table, created using AS <something>.

You cannot reference a computed column within the same query, but you can subquery it.

SELECT StoreNumber, Date, Sales, DateComp, SalesComp, Labor, CashOS, Comment,
      SalesComp-Sales AS Expression1
From
(
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
) S
ORDER BY Date DESC, StoreNumber ASC
0
 
dk04Author Commented:
SalesComp is giving the error

Msg 207, Level 16, State 1, Line 2
Invalid column name 'SalesComp'.
0
 
appariCommented:
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

Open in new window

0
 
dk04Author Commented:
Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now