Subtracting to queries - 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

Subtracting to queries

Author

Topic

f9Pete
Starting Member

20 Posts

Posted - 2010-09-13 : 04:31:22

Hey all,

This is my first post (and one of many I'm sure!)

I am creating a basic sales database using a MS Server db and asp.net web controls in Expression Web. The databse is comprised of three tables, called Sales, SalesReps and SalesBudget. All three tables are related via a 'Region' field in the following configuration:

SalesReps -> Sales (One to Many)
SalesReps -> SalesBudgets (One to One)

So far I have been able to output the year to date total of the (SterlingAmount) field from the 'Sales' table into a GridView using a 'region' dropdownlist control combined with the following query:

SELECT SUM(SterlingAmount)FROM Sales WHERE (Region = @Region) AND (Date >= '2010-01-01 AND Date > '2010-12'31')

I have also been able output the year budget total from the SalesReps table into a second GridView control using:

SELECT (BudgetTotal), (Region) FROM (SalesBudgets) WHERE (Region =@Region)

What I would like to be able to do now is subtract the result of query 1 from query 2 and display the output in a third gridview control. Would anyone know how this is done please?

Many thanks,

Pete

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-13 : 05:01:41

Hi Pete,

If you want to substract the values:

DECLARE @sterling_amount_total INT
DECLARE @budget_total INT

SELECT @sterling_amount_total= SUM(SterlingAmount)FROM Sales WHERE (Region = @Region) AND (Date >= '2010-01-01 AND Date > '2010-12'31')
SELECT @budget_total = (BudgetTotal), (Region) FROM (SalesBudgets) WHERE (Region =@Region)

you can the result by doing
SELECT (@budget_total - @sterling_amount_total) AS [Result] , @Region AS [Region]

I hope this is your requirement. Feel free to revert back.

Thanks
Rohit

f9Pete
Starting Member

20 Posts

Posted - 2010-09-13 : 05:19:37

Rohit,

Many thanks for your reply it's much appreciated. I'm very new to SQL so please don't shake your head ;) - Where do the declarations sit, in the orginal queries or just in the third query that is performing the final calculation?

Example

Query1 =

DECLARE @sterling_amount_total INT
SELECT @sterling_amount_total= SUM(SterlingAmount)FROM Sales WHERE (Region = @Region) AND (Date >= '2010-01-01 AND Date > '2010-12'31')

Query 2 =

DECLARE @budget_total INT
SELECT @budget_total = (BudgetTotal), (Region) FROM (SalesBudgets) WHERE (Region =@Region)

Query 3 =

SELECT (@budget_total - @sterling_amount_total) AS [Result] , @Region AS [Region]

...or are they just included in my query for the third gridview conrol.

Many thanks,

Pete

Sachin.Nand

2937 Posts

Posted - 2010-09-13 : 05:20:41

Is there any relationship between the 2 tables?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

f9Pete
Starting Member

20 Posts

Posted - 2010-09-13 : 05:23:51

Idera, thank for your reply - Yes, through a Region field

All three tables are related via a 'Region' field in the following configuration:

SalesReps -> Sales (One to Many)
SalesReps -> SalesBudgets (One to One)

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-13 : 05:29:56

hi Pete,

The only requirement of the SQL is to declare the variables before their first use so, practically you can declare them anywhere you want before first use . But the convention used by the programmer is that you declare all of them at the top at one place. The Queries you have posted will do as the variables are declared before their use. Please , go through the comments:-

Query1 =

DECLARE @sterling_amount_total INT // @sterling_amount_total declared before being used in the following query
SELECT @sterling_amount_total= SUM(SterlingAmount)FROM Sales WHERE (Region = @Region) AND (Date >= '2010-01-01 AND Date > '2010-12'31')

Query 2 =

DECLARE @budget_total INT // @budget_total declared before being used in the following query
SELECT @budget_total = (BudgetTotal), (Region) FROM (SalesBudgets) WHERE (Region =@Region)

Query 3 =
// @sterling_amount_total ,@budget_total already declared above before being used in the following query ,no need of declaring them again.
SELECT (@budget_total - @sterling_amount_total) AS [Result] , @Region AS [Region]

Sachin.Nand

2937 Posts

Posted - 2010-09-13 : 05:37:58

[code]
SELECT a.region,
a.sumsterlingamount - b.sumbudgettotal AS diff
FROM (SELECT Sum(sterlingamount) AS sumsterlingamount,
region
FROM sales
WHERE (region = @Region)
AND (DATE >= '2010-01-01'
AND DATE > '2010-12-31')
GROUP BY region) a
INNER JOIN (SELECT Sum(budgettotal) AS sumbudgettotal,
(region)
FROM ( salesbudgets )
WHERE (region = @Region)
GROUP BY region) b
ON a.region = b.region
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-09-13 : 05:39:30

quote:
Originally posted by rohitvishwakarma

Hi Pete,

If you want to substract the values:

DECLARE @sterling_amount_total INT
DECLARE @budget_total INT

SELECT @sterling_amount_total= SUM(SterlingAmount)FROM Sales WHERE (Region = @Region) AND (Date >= '2010-01-01 AND Date > '2010-12'31')
SELECT @budget_total = (BudgetTotal), (Region) FROM (SalesBudgets) WHERE (Region =@Region)

you can the result by doing
SELECT (@budget_total - @sterling_amount_total) AS [Result] , @Region AS [Region]

I hope this is your requirement. Feel free to revert back.

Thanks
Rohit

What happens if the query returns a multiple resultset?
How will you store it in a variable?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-13 : 05:50:21

The following query will give you all the outputs in a single query:

SELECT Sales.Region AS [Region] , SalesBudgets.BudgetTotal, SUM(SterlingAmount) , (SalesBudgets.BudgetTotal-SUM(SterlingAmount)) AS [FinalValue]
FROM Sales
INNER JOIN SalesBudgets
ON Sales.Region = SalesBudgets.Region
WHERE Sales.Region =@Region
GROUP BY Sales.Region , SalesBudgets.BudgetTotal

f9Pete
Starting Member

20 Posts

Posted - 2010-09-13 : 05:53:48

Rohit,

Many thanks for your reply. I have replaced the query within my first gridview sql datasource with:

DECLARE @sterling_amount_total INT
SELECT @sterling_amount_total= SUM(SterlingAmount)FROM Sales WHERE (Region = @Region) AND (Date >= '2010-01-01 AND Date > '2010-12'31')

Unfortunately I am getting a persistant error stating that @sterling_amount_total has already been declared when I test the query. As a test I have tried renaming @sterling_amount_total to something else but the issue persisted.

Any help is grately appreciated.

Many thanks,

Pete

Sachin.Nand

2937 Posts

Posted - 2010-09-13 : 05:54:04

quote:
Originally posted by rohitvishwakarma

The following query will give you all the outputs in a single query:

SELECT Sales.Region AS [Region] , SalesBudgets.BudgetTotal, SUM(SterlingAmount) , (SalesBudgets.BudgetTotal-SUM(SterlingAmount)) AS [FinalValue]
FROM Sales
INNER JOIN SalesBudgets
ON Sales.Region = SalesBudgets.Region
WHERE Sales.Region =@Region
GROUP BY Sales.Region , SalesBudgets.BudgetTotal

And what about the date filter on the sales table?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-13 : 05:55:54

quote:
Originally posted by Idera

quote:
Originally posted by rohitvishwakarma

Hi Pete,

If you want to substract the values:

DECLARE @sterling_amount_total INT
DECLARE @budget_total INT

SELECT @sterling_amount_total= SUM(SterlingAmount)FROM Sales WHERE (Region = @Region) AND (Date >= '2010-01-01 AND Date > '2010-12'31')
SELECT @budget_total = (BudgetTotal), (Region) FROM (SalesBudgets) WHERE (Region =@Region)

you can the result by doing
SELECT (@budget_total - @sterling_amount_total) AS [Result] , @Region AS [Region]

I hope this is your requirement. Feel free to revert back.

Thanks
Rohit

What happens if the query returns a multiple resultset?
How will you store it in a variable?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Since, Pete is using a region to filter the results(it will find the sum of a single region and the sum will always result in a single row) it can never give multiple resultset. However, in future it may trouble him if he removes the region filter i.e WHERE Region=@Region. I totally agree with you on this.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-13 : 05:58:25

quote:
Originally posted by Idera

quote:
Originally posted by rohitvishwakarma

The following query will give you all the outputs in a single query:

SELECT Sales.Region AS [Region] , SalesBudgets.BudgetTotal, SUM(SterlingAmount) , (SalesBudgets.BudgetTotal-SUM(SterlingAmount)) AS [FinalValue]
FROM Sales
INNER JOIN SalesBudgets
ON Sales.Region = SalesBudgets.Region
WHERE Sales.Region =@Region
GROUP BY Sales.Region , SalesBudgets.BudgetTotal

And what about the date filter on the sales table?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

that can be included in the WHERE Sales.Region =@Region part of the query ie: Final query will be like this:

SELECT Sales.Region AS [Region] , SalesBudgets.BudgetTotal, SUM(SterlingAmount) , (SalesBudgets.BudgetTotal-SUM(SterlingAmount)) AS [FinalValue]
FROM Sales
INNER JOIN SalesBudgets
ON Sales.Region = SalesBudgets.Region
WHERE Sales.Region =@Region AND (Date >= '2010-01-01 AND Date > '2010-12'31')

GROUP BY Sales.Region , SalesBudgets.BudgetTotal

f9Pete
Starting Member

20 Posts

Posted - 2010-09-13 : 06:26:58

Rohit,

Nice work! The query works a just fine although, I'm not seeing any data beneath the columns when the query is run (and the gridview control doesn't appear when a region is selected from the @region control on my .aspx page)

Any suggestions are greatly appreciated.

Many thanks,

Pete

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-13 : 07:04:35

Hi Pete,

SELECT Sales.Region AS [Region] , SalesBudgets.BudgetTotal, SUM(SterlingAmount) AS [TotalSterlingAmount] , (SalesBudgets.BudgetTotal-SUM(SterlingAmount)) AS [FinalValue]
FROM Sales
INNER JOIN SalesBudgets
ON Sales.Region = SalesBudgets.Region
WHERE Sales.Region =@Region AND (Date >= '2010-01-01 AND Date > '2010-12'31')
GROUP BY Sales.Region , SalesBudgets.BudgetTotal

---
In the above query you are getting 4 columns as output Region, BudgetTotal, TotalSterlingAmount and FinalValue. Bind the Gridview's SqlDataSource as
follows:

1st GridView
Bind SqlDataSource with TotalSterlingAmount column of the query

2nd Gridview
Bind SqlDataSource with BudgetTotal & Region column of the query

and in the 3rd Gridview
Bind SqlDataSource with FinalValue & Region column of the query

i hope this will solve your problem. If still there is any issue message me.

Thanks,
Rohit

f9Pete
Starting Member

20 Posts

Posted - 2010-09-13 : 07:09:03

I just checked all of the column names etc. The one I've noticed that if I force the region name to be an existing record with data (rather than the @Region control, the data is still not being passed through to the rest of the query and consequently the gridview control is not populating,

E.g.

Changing WHERE Sales.Region =@Region

to

WHERE Sales.Region = 'Europe' for example

From my basic understanding it looks as though the region selection is not working.

Any help is greatly appreciated.

Many thanks,

Pete

f9Pete
Starting Member

20 Posts

Posted - 2010-09-13 : 07:10:23

Rohit,

I just saw your reply, I wondered whether it was a databind issue.

I'll try that and come back to you.

Many thanks,

Pete

f9Pete
Starting Member

20 Posts

Posted - 2010-09-13 : 07:23:28

Rohit,

Makes sense, but I might need help with that. Currently my Gridview control just contains the query, whereabouts do I add these connections?

Many thanks,

Pete

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-13 : 07:46:37

hi Pete,

Do one thing remove the previous GridView Control you were having the aspx page.
Take a fresh GridView and select SqlDataSource as the DataSource.
After selection of the SqlDataSource it will ask you for the connection. Configure the connection as you were doing earlier(choose the database where your table Sales etc. resides).
Now, in the next step choose 'Specify a Custom Sql Statement or stored procedure'.
Place the above query in the space provide for the Sql Statement.

Next it will prompt you for the parameter @Region.
Click on 'Parameter Source' and select 'Control'. it will give you a list of controls on your page.
Select the DropDownlist using which you are displaying the Regions.
In the left hand side of the box you can see that it displays Region = DropDownlist.SelectedValue
You can also privde a default value if you want to. eg:- Europe
Select finsih and you are done with the SqlDataSource thing.

** Be sure that while binding the Region DropDown List you are putting the Region Names in the Value.

Now, go to design view (aspx) and Right click on the column of the GridView which you don't want to display for the 1st gridview ie. BudgetTotal , FinalValue & Region
On the right click Select Properties.
Go through the properties list and Find the 'Visible' property. Set it to false.

Do the same for other two gridviews( be sure to make the columns not reqd. invisible)

'Currently my Gridview control just contains the query, whereabouts do I add these connections? '

I am not sure what connections you are talking about. If you can please clarify.

Thanks
Rohit

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-13 : 08:03:08

Hi Pete,

Do one more thing, add the following to your dropdownlist control on the aspx

<asp:DropDownlist ID ="xyz" runat="server" AutoPostBack="True">

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-13 : 08:04:59

quote:
Originally posted by f9Pete

From my basic understanding it looks as though the region selection is not working.

Any help is greatly appreciated.

Many thanks,

Pete

Add the following to your dropdownlist control on the aspx

<asp:DropDownlist ID ="xyz" runat="server" AutoPostBack="True">

Thanks
Rohit

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources