Subtract Two Column Values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subtract Two Column Values

Author

Topic

szafranman
Starting Member

2 Posts

Posted - 2011-01-09 : 16:18:12

Hello I am new to writing SQL Querys and have a specific Query I want to run but cannot figure out how to write it. The following is what I need to have done:
From Database QBs I want to access the InvoiceLine table within that table I need to Compute a new value "Total" Which will have to look into the Column InvoiceLineRefFullName and pull 'Subtotal' and 'Senior Discout' and subtract the two values they have under the InvoiceLineAmount column.

SELECT InvoiceLineAmount
FROM InvoiceLine
WHERE RefNumber = <cfoutput>'#URL.thenumber#1</cfoutput>
AND InvoiceLineRefFullName ='Subtotal'

That gives me the Subtotal amount specific to the logged in user the next query I run is

SELECT InvoiceLineAmount
FROM InvoiceLine
WHERE RefNumber = <cfoutput>'#URL.thenumber#'</cfoutput>
AND InvoiceLineRefFullName ='Senior Discount'

This gives me the dollar amount applied to the invoice for Senior Discount specific to the logged in user.

So I can generate both values but I want to have Subtotal-Senior Discount=Total

If that makes sense. Thanks in advance!!!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-09 : 16:38:40

What version of SQL is it?

you can create two derived tables and select all 3 values from those (joined on RefNumber)


Select   Subtotal
		,SeniorDiscount
		,Total = Subtotal-SeniorDiscount
FROM (SELECT REfNumber,InvoiceLineAmount as SubTotal
	  FROM InvoiceLine
      WHERE RefNumber = <cfoutput>'#URL.thenumber#1'</cfoutput>'
	  AND InvoiceLineRefFullName ='Subtotal' ) as S
INNER JOIN 
 	(   SELECT RefNumber,InvoiceLineAmount AS SeniorDiscount
    	FROM InvoiceLine
		WHERE RefNumber = <cfoutput>'#URL.thenumber#'</cfoutput>
		AND InvoiceLineRefFullName ='Senior Discount') as SD
on S.RefNumber = SD.RefNumber

OR with SQL 2005 or later you can use CTE's


;WITH S AS (SELECT REfNumber,InvoiceLineAmount as SubTotal
	  FROM InvoiceLine
      WHERE RefNumber = <cfoutput>'#URL.thenumber#1'</cfoutput>'
	  AND InvoiceLineRefFullName ='Subtotal' ) 
 , SD AS (   SELECT RefNumber,InvoiceLineAmount as SeniorDiscount
    	FROM InvoiceLine
		WHERE RefNumber = <cfoutput>'#URL.thenumber#'</cfoutput>
		AND InvoiceLineRefFullName ='Senior Discount') as SD
SELECT Subtotal
		,SeniorDiscount
		,Total = Subtotal-SeniorDiscount
FROM S 
INNER JOIN SD 
 	on S.RefNumber = SD.RefNumber

IF there is a chance there will be no records returned from the Senior Discount piece, you may have change to LEFT JOIN and use ISNULL(SeniorDiscount,0) in the final select...

Poor planning on your part does not constitute an emergency on my part.

szafranman
Starting Member

2 Posts

Posted - 2011-01-09 : 17:09:01

Thanks for your quick reply!
I am using Dreamweaver CS5 to input the SQL Query with Coldfusion 9

When I tried the first query it threw an error: Expected lexical element not found: CASE

The second one also threw an error Expected lexical element not found:

The error occurred in C:\inetpub\wwwroot\CFIDE\testsite\invoice_number_page.cfm: line 34

32 : , SD AS ( SELECT RefNumber,InvoiceLineAmount as SeniorDiscount
33 : FROM InvoiceLine
34 : WHERE RefNumber = <cfoutput>'#URL.thenumber#'</cfoutput>
35 : AND InvoiceLineRefFullName ='Senior Discount') as SD
36 : SELECT Subtotal

I feel like the first one is the one I should be using and all the variables that its looking against are correct so is there some way to tweak it to work within Dreamweaver? And smart thinking as far as there may not be a senior discount because for a good majority of my clients there won't be. Thanks for your help!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-09 : 18:04:19

Dreamweaver likely won't accept the CTE references

Are you sure it isn't MYSQL? Dreamweaver and PHP usually use MySQL instead of MS SQL, but maybe this one will work better.


SELECT	  a.InvoiceLineAmount as SubTotal,
		, b.InvoiceLineAmount as SeniorDiscount
		, a.InvoiceLineAmount-ISNULL(b.InvoiceLineAmount,0) as Total
FROM InvoiceLine a left join InvoiceLine b
 ON a.RefNumber = b.RefNumber
WHERE a.RefNumber = <cfoutput>'#URL.thenumber#'</cfoutput>
and (b.RefNumber is null OR b.InvoiceLineRefFullName = 'Senior Discount')

Not sure how dreamweaver works with SQL and there may be proposed syntax that works on SQL Server but not on php/MS SQL or php/My SQL queries.

Are the RefNumber values the SAME for both queries?

Edit: Alternatively, if you are capturing both values..can't you simply use them in a calculated field?

I am not well versed in Php or Dreamweaver....perhaps a Dreamweaver forum might be of more help..

[/code]

Poor planning on your part does not constitute an emergency on my part.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-09 : 19:05:20

SELECT
[Balance] =
CASE WHEN InvoiceLineRefFullName = 'Subtotal' THEN InvoiceLineAmount ELSE 0 END
- CASE WHEN InvoiceLineRefFullName = 'Senior Discount' THEN InvoiceLineAmount ELSE 0 END

FROM InvoiceLine
WHERE RefNumber = '#URL.thenumber#'

You can also wrap each case statement with a SUM, if needed

Jim

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 11:09:40

[code]
SELECT SUM(CASE WHEN InvoiceLineRefFullName ='Subtotal' THEN InvoiceLineAmount WHEN InvoiceLineRefFullName ='Senior Discount' THEN -1 * InvoiceLineAmount ELSE 0 END) AS Total
FROM InvoiceLine
WHERE RefNumber = <cfoutput>'#URL.thenumber#1</cfoutput>
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources