Perform math on multiple select statements

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

SQL Server 2008 Forums

Transact-SQL (2008)

Perform math on multiple select statements

Author

Topic

Spica66
Starting Member

23 Posts

Posted - 2012-08-15 : 13:36:47

I am preparing a single dimension for a cube. I have to access one table and three views to obtain the values that I need for the calculation.
I am able to obtain the values properly, but now I want to subtract items 2, 3, and 4 from item #1 in this statement:

SELECT st.HRVFPINVOICEAMOUNT AS INVAMT

,( SELECT DiscFreight
FROM dbo.sales_Discount_Query_Freight --this is a view
WHERE SalesID = st.SalesID) AS DISCFREIGHT

,( SELECT Disc2611
FROM dbo.sales_Discount_Query_26_11 --this is a view
WHERE SalesID = st.SalesID) AS DISC2611

,( SELECT DiscGST
FROM dbo.sales_Discount_Query_GST --this is a view
WHERE SalesID = st.SalesID) AS DISCGST

FROM dbo.SALESTABLE st

I tried the following to no avail:

, ( SELECT INVAMT-DISCFREIGHT-DISC2611-DISCGST) AS ReportAmt

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-08-15 : 13:51:44

Why not do the calculations in the report itself?

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Spica66
Starting Member

23 Posts

Posted - 2012-08-15 : 13:53:20

This is a single measure for a cube. I would not know where to do the calculation...

Spica66
Starting Member

23 Posts

Posted - 2012-08-16 : 08:02:04

Here is the answer:

SELECT ISNULL(( SELECT SUM(LINEAMOUNT)
FROM dbo.SALESLINE sl
WHERE sl.SALESID = s.SALESID), 0)-

ISNULL(( SELECT DiscFreight
FROM dbo.sales_Discount_Query_Freight
WHERE SalesID = s.SalesID),0)-

ISNULL(( SELECT Disc2611
FROM dbo.sales_Discount_Query_26_11
WHERE SalesID = s.SalesID),0)-

ISNULL(( SELECT DiscGST
FROM dbo.sales_Discount_Query_GST
WHERE SalesID = s.SalesID),0) AS INVAMT

FROM dbo.SalesTable s

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-08-16 : 08:35:36

Great! thanks for posting back with the solution.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources