Solved

SQL Subtract Calculated Field

Posted on 2010-09-13
5
814 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
  • 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

772 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