Solved

SQL Subtract Calculated Field

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now