January 19, 2010 at 2:07 am
Hi all,
I have a problem for the SUM and REPLACE function, the SQL script will as below,
select DISTINCT
CapitalForecastID
,Description
,BudgetOwnerName
,CapitalForecastCostPerUnit
,' + @SelectColNames + '
SUM(' + @SelectColNames + ') AS TotalCost
for the @SelectColNames parameter will return the result as
[Q1 '10] ,[Q2 '10] ,[Q3 '10] ,[Q4 '10] ,
My Question as below,
(1) How can i SUM all the Quarters as above with the REPLACE symbol (+) rather than (,)
(2) Since from the statement above, i have symbol (') and (,). So i cant write the correct way on that.
Please advice.
Thanks 🙂
January 19, 2010 at 3:49 am
Hi Knockyo,
There is no good way to build dynamic queries the way you suggest. There is no way to dynamicaly change what columns to present in the result. You have to rebuild the query each time.
The query you are looking for is probably:
select CapitalForecastID, Description, BudgetOwnerName,
CapitalForecastCostPerUnit,
[Q1 '10] ,[Q2 '10] ,[Q3 '10] ,[Q4 '10],
[Q1 '10] + [Q2 '10] + [Q3 '10] + [Q4 '10] AS TotalCost
from ...
where ...
group by CapitalForecastID, Description, BudgetOwnerName,
CapitalForecastCostPerUnit, [Q1 '10] ,[Q2 '10] ,[Q3 '10] ,[Q4 '10]
or
select CapitalForecastID, Description, BudgetOwnerName,
CapitalForecastCostPerUnit,
SUM([Q1 '10]) AS [Q1 '10], SUM([Q2 '10]) AS [Q2 '10],
SUM([Q3 '10]) AS [Q3 '10], SUM([Q4 '10]) AS [Q4 '10],
SUM([Q1 '10]) AS [Q1 '10] + SUM([Q2 '10]) AS [Q2 '10] +
SUM([Q3 '10]) AS [Q3 '10] + SUM([Q4 '10]) AS [Q4 '10] AS TotalCost
from ...
where ...
group by CapitalForecastID, Description, BudgetOwnerName,
CapitalForecastCostPerUnit
But I have a feeling that you should rethink the design of the table, though. With this table layout you have to add columns to the table continuously. That's not a good layout.
It's better to split the table in two and that way you can have the table layout fixed regardless of how many years you store information about (I have guessed your layout and fieldtypes based on your query):
CREATE TABLE CapitalForecast
(
CapitalForecastID int IDENTITY(1,1) PRIMARY KEY,
Description varchar(30),
BudgetOwnerName varchar(30),
CapitalForecastCostPerUnit money,
...
)
CREATE TABLE CapitalForecastQuarters
(
id int IDENTITY(1,1) PRIMARY KEY,
CapitalForecastID int CONSTRAINT
FK_CapitalForecastID REFERENCES CapitalForecast (CapitalForecastID),
Quarter decimal(5,1), -- * See note below
Value money,
)
* It's allways hard to find a good representation on quarters. One could have choosen two different columns, year and quarter, but then all comparisons will be harder, especially when finding an intervall. So I here choose decimal representation. Perhaps someone else has a better suggestion...
The query above would with this new layout become something like this:
select F.CapitalForecastID, F.Description, F.BudgetOwnerName,
F.CapitalForecastCostPerUnit,
SUM(CASE WHEN Q.Quarter = 2010.1 THEN Q.Value ELSE 0 END,
SUM(CASE WHEN Q.Quarter = 2010.2 THEN Q.Value ELSE 0 END,
SUM(CASE WHEN Q.Quarter = 2010.3 THEN Q.Value ELSE 0 END,
SUM(CASE WHEN Q.Quarter = 2010.4 THEN Q.Value ELSE 0 END,
SUM(Value) AS TotalCost
from CapitalForecast F
INNER JOIN CapitalForecastQuarters Q
ON F.CapitalForecastID = Q.CapitalForecastID
where Q.Quarter BETWEEN 2010.1 AND 2010.4
and ...
group by F.CapitalForecastID, F.Description, F.BudgetOwnerName,
F.CapitalForecastCostPerUnit
This still haven't solved the issue with dynamic queries, but is a more flexible layout.
Hope this gives some hints on how to solve your problem!
/Markus
January 19, 2010 at 5:20 am
Hi,
will this not work?
declare @selectColumns nvarchar(400)
declare @W nvarchar(200)
set @selectColumns ='[Q1 ''10] ,[Q2 ''10] ,[Q3 ''10] ,[Q4 ''10] ,'
select @selectColumns
set @w=left(REPLACE(@selectColumns,',','+'),LEN(@selectColumns)-1)
select @w
January 20, 2010 at 1:21 am
shanu.hoosen (1/19/2010)
Hi,will this not work?
declare @selectColumns nvarchar(400)
declare @W nvarchar(200)
set @selectColumns ='[Q1 ''10] ,[Q2 ''10] ,[Q3 ''10] ,[Q4 ''10] ,'
select @selectColumns
set @w=left(REPLACE(@selectColumns,',','+'),LEN(@selectColumns)-1)
select @w
Hi
That's work but unfortunately the @selectColumns parameter it only contains single quote and not double quote as below,
[Q1 '10] ,[Q2 '10] ,[Q3 '10] ,[Q4 '10] ,
Is that any ways to enhance it?
January 20, 2010 at 1:26 am
allow your label to read as [Q4 '10], but your value as [Q4 ''10].
you can achieve this by selecting your quarters as labels and use a replace quarters for values.
hope this helps
January 20, 2010 at 2:02 am
knockyo (1/20/2010)
shanu.hoosen (1/19/2010)
Hi,will this not work?
declare @selectColumns nvarchar(400)
declare @W nvarchar(200)
set @selectColumns ='[Q1 ''10] ,[Q2 ''10] ,[Q3 ''10] ,[Q4 ''10] ,'
select @selectColumns
set @w=left(REPLACE(@selectColumns,',','+'),LEN(@selectColumns)-1)
select @w
Hi
That's work but unfortunately the @selectColumns parameter it only contains single quote and not double quote as below,
[Q1 '10] ,[Q2 '10] ,[Q3 '10] ,[Q4 '10] ,
Is that any ways to enhance it?
Hi,
You means i need hard code the parameters values? For my case, the @selectColumns will get the values automatically, I can't hard code it on my script.
January 28, 2010 at 12:21 am
shanu.hoosen (1/20/2010)
run this sqldeclare @S nvarchar(200)
set @S= '[Q1 ''10] ,[Q2 ''10] ,[Q3 ''10] ,[Q4 ''10] '
select @S
--this is the result
[Q1 '10] ,[Q2 '10] ,[Q3 '10] ,[Q4 '10]
select REPLACE(@s,'''','''''')
--this is the result
[Q1 ''10] ,[Q2 ''10] ,[Q3 ''10] ,[Q4 ''10]
Hi ,
thanks your help.... i solve this problem based on ur idea ... thanks anyway!
January 29, 2010 at 12:09 am
Knockyo:
Try this. For @SelectColNames, you simply want to replace all occurrences of '] ,' with ']+'.
Therefore,
select DISTINCT
CapitalForecastID
,Description
,BudgetOwnerName
,CapitalForecastCostPerUnit
,' +
'SUM(' +
LEFT(REPLACE(@SelectColNames, '] ,', ']+'), LEN(REPLACE(@SelectColNames, '] ,', ']+')) - 1) +
') AS TotalCost
Hope this helps......
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply