Declaring Parameters - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Declaring Parameters

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-10-14 : 05:01:56

Hi SQL dons.

I have a simple table called Weekly_sales
Weekno, WeeklySales, Weeklytarget
You can see the picture below .

I have created the following query (which works) to show me running totals and percentages from the three columns.
But I am convinced that I am not being efficient because I am repeating some of the formulas.
So I would like to create parameters to make this easier. So that I can just call a parameter called @Running_Sales and @Running_Target

My Query so far is this

SELECT * , (Weeksales * 100)/Weektarget AS 'Percent vs target',
(SELECT SUM(Weektarget)FROM Weekly_sales AS Weekly_sales1 WHERE Weekly_sales1.Weekno <= Weekly_sales.Weekno)AS 'Running Target' ,
(SELECT SUM(WeekSales)FROM Weekly_sales AS Weekly_sales1 WHERE Weekly_sales1.Weekno <= Weekly_sales.Weekno)AS 'Running Sales' ,
(SELECT SUM(WeekSales)FROM Weekly_sales AS Weekly_sales1 WHERE Weekly_sales1.Weekno <= Weekly_sales.Weekno) * 100 /
(SELECT SUM(WeekTarget)FROM Weekly_sales AS Weekly_sales1 WHERE Weekly_sales1.Weekno <= Weekly_sales.Weekno) AS 'Running Sales vs Target as Percent'
FROM weekly_sales

here is my attempt to create parameters which is failing
with the following message
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Weekly_sales.Weekno" could not be bound.

DECLARE @Running_Target INT
DECLARE @Running_Sales INT
SET @Running_Target = (SELECT SUM(Weektarget)FROM Weekly_sales AS Weekly_sales1 WHERE Weekly_sales1.Weekno <= Weekly_sales.Weekno)
SET @Running_Sales = (SELECT SUM(WeekSales)FROM Weekly_sales AS Weekly_sales1 WHERE Weekly_sales1.Weekno <= Weekly_sales.Weekno)

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-14 : 05:14:32

for populating your variable you cut and paste the inner query that had a reference to the Outer main query. By copying the inner query only, the reference became invalid

SET @Running_Sales = (SELECT SUM(WeekSales)FROM Weekly_sales AS Weekly_sales1 WHERE Weekly_sales1.Weekno <= Weekly_sales.Weekno)

Weekly_sales is not in your From clause as you gave it an alias Weekly_sales1.

And what are you trying to achieve? Running_Target returns multiple records and which value you want to store in to the variable?

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-10-15 : 00:21:03

Thanks for your reply

All i'm trying to do here is to make the SQL query more efficient.
maybe I've got the wrong end of the stick with the parameters.
Is there a way I can make this query better?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-15 : 00:35:33

[code]
SELECT w.*,
s.[Running Target],
s.[Running Sales],
s.[Running Target] * 100 / s.[Running Sales] AS [Running Sales vs Target AS Percent]
FROM weekly_sales w
CROSS APPLY
(
SELECT SUM(Weektarget) AS [Running Target],
SUM(WeekSales) AS [Running Sales]
FROM Weekly_sales AS x
WHERE x.Weekno <= w.Weekno
) s
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources