Re: SQL Help - Is it possible to do the following?

On Sun, 13 Apr 2008 18:34:00 -0700, James Terrington wrote:

Hi all,

I have created a SQL query that gives me the following, but I want to build
onto it:

Project Manager/Job Name/Contract Revenue/Labour $/Material $

What I am trying to now do is:

1) Create a field in the query Margin = Contract Revenue - Material $

Hi James,

Since you don't show the query you already have, I can only give you a
rough outline:

SELECT (Expression1) AS ProjectManager,
(Expression2) AS JobName,
(Expression3) AS ContractRevenue,
(Expression4) AS LabourCost,
(Expression5) AS MaterialCost,
(Expression3 - Expression5) AS Margin
FROM bla bla bla;

That is, you just repeat the expressions for ContractRevenue and
MaterialCost.

If these expressions are cumbersome, the duplication might make the
query hard to maintain. If they involve subqueries, the duplication may
also cause performance overhead. In these cases, you can also use the
derived table technique:

SELECT ProjectManager, JobName, ContractRevenue,
LabourCost, MaterialCost,
(ContractRevenue - MaterialCost) AS Margin
FROM (Your original query goes here) AS SomeAlias;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
.