perform some calculations in specific rows

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

perform some calculations in specific rows

Author

Topic

MGA
Starting Member

28 Posts

Posted - 2011-04-04 : 17:32:01

i have these three tables :
table Category_Types
(
Cat_Type_Id int primary key,
Cat_Type varchar(50)
)
table Categories(
Category_Id int primary key,
Cat_Type_Id int foreign key references Category_Types(Cat_Type_Id),
Category_Name varchar(20) not null,
Mode int not null /* 1:monthly 0:daily 2:multi months */
)
table Budget (
Budget_Id int primary key,
Category_Id int foreign key references Categories(Category_Id),
Budget_Money money not null ,
Date_From date not null,
Date_To date not null
)

How to compute the total budget for each categories as :
category_Budget = Budget_Money * Days if mode = 0 if the budget is for each day (Daily ) if mode = 0
Category_Budget = Budget_Money if mode = 1 or 2
so how to perform these calculations in the select statement
i calculated the toal budget for a given days interval for all categories but how to do this for the list of categories and a list of category_Types
i want to do something like the following but for all the list of categories and category_types not for the total budget and without insert the result into temp table

create proc Total_Budget(@From date,@To date,@Total_Budget money output)
as
begin
DECLARE @Category_Names varchar(50)
DECLARE @Budget_Money money
DECLARE @Mode int
set @Total_Budget = 0
DECLARE BudgetCursor CURSOR FOR(
select Categories.Category_Name, SUM(Isnull(Budget.Budget_Money,0)) Budget_Money ,
Categories.Mode
from Budget
right join Categories on Budget.Category_Id = Categories.Category_Id
where Budget.Date_From = @From and Budget.Date_To = @To
group by Categories.Category_Name , Budget.Budget_Money ,Categories.Mode )
open BudgetCursor ;
fetch NEXT From BudgetCursor into @Category_Names,@Budget_Money,@Mode ;
WHILE @@FETCH_STATUS = 0
begin
if( @Mode = 0)
begin
set @Budget_Money = @Budget_Money * DateDiff(D,@From,@To) ;
end ;
set @Total_Budget = @Total_Budget + @Budget_Money ;
fetch NEXT From BudgetCursor into @Category_Names,@Budget_Money,@Mode ;
end ;
select @Total_Budget
close BudgetCursor
deallocate BudgetCursor
end
go

Any ideas

Thanks

matty
Posting Yak Master

161 Posts

Posted - 2011-04-05 : 05:58:30

I dont fully understand your requirement.However, to calculate category_Budget you can a use a CASE in SELECT like

SELECT category_Budget = CASE Mode WHEN 0 THEN Budget_Money * Days ELSE Budget_Money END

Post sample data and expected output to get more help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources