Solved

SQL Subtract Calculated Field

Posted on 2010-09-13
5
819 Views
Last Modified: 2012-05-10
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
Comment
Question by:dk04
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33668534
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
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33668541
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
 

Author Comment

by:dk04
ID: 33668543
SalesComp is giving the error

Msg 207, Level 16, State 1, Line 2
Invalid column name 'SalesComp'.
0
 
LVL 39

Expert Comment

by:appari
ID: 33668553
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
 

Author Closing Comment

by:dk04
ID: 33668555
Thanks
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question