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
 Total monthly Expenses don't Sum up when I submit

Author  Topic 

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-15 : 21:37:48
I have a Monthlyexpense column. How do I Sum up this column and put the Total in my ytdexpenses column. Do I use a stored procedure, because I want the monthlyExpenses to SUm up every time I submit a monthly expense to the database and siplay in the ytdExpenses Column.
When I Write a Query all of the rows in the ytdExpenses shows the same amount and do not total up every time I submit to the database. Help please.

monthlyExpenses   ytdExpenses
$1,000    $1,000
$2,000    $3,000
$3,000    $6,000
$2,000    $8,000
$5,000    $13,000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-15 : 21:50:56
There are many ways.

1. Do the running total (ytdExpenses) in the front end application. (This will get the most votes)

2. Update to ytdExpenses everytime monthlyExpenses in updated.

2a. If you are performing the update to monthlyExpenses via Stored Procedure, just change the SP to update ytdExpenses as well.

update t
set monthlyExpenses = monthlyExpenses + @expenses,
ytdExpenses = ytdExpenses + @expenses
from yourtable t


2b. If the updating is via application directly to the table, then use an update trigger

update t
set ytdExpenses = ytdExpenses + i.monthlyExpenses - d.monthlyExpenses
from inserted i inner join deleted d
on i.pk = d.pk
inner join yourtable t
on i.pk = t.pk


3. calculate the ytdExpneses in your select statement


select monthlyExpenses,
ytdExpenses = (select sum(monthlyExpenses) from yourtable x where x.datecol <= t.datecol)
from yourtable t

Have i cover all possible solutions ?



KH

Go to Top of Page

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-15 : 23:32:37
Yes,

I think you have. I'm using infoPath to submit to the database and using it as well to retrieve the ytdExpenses back from the database. All I have to do is update the ytdExpenses every time someone submit to the database. Do you think this will be the best Solution......

update t
set ytdExpenses = ytdExpenses + i.monthlyExpenses - d.monthlyExpenses
from inserted i inner join deleted d
on i.pk = d.pk
inner join yourtable t
on i.pk = t.pk

or


update t
set monthlyExpenses = monthlyExpenses + @expenses,
ytdExpenses = ytdExpenses + @expenses
from yourtable t



Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-15 : 23:37:53
I am not sure what infoPath is.

If all the updates can be performed via Stored Procedure then use the non trigger method else do it in the update trigger.


KH

Go to Top of Page

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-15 : 23:44:25
I can try both of them right using T-SQL?


Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-15 : 23:45:18
Sure. Why not. Just make sure you try this on a development / test enviroment


KH

Go to Top of Page

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-15 : 23:54:21
Should I use this before it....

USE database name
GO
Cretae proc name
AS
.......then one of them

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 00:00:04
quote:
Originally posted by Michael71

Should I use this before it....

USE database name
GO
Cretae proc name
AS
.......then one of them





If you are using Stored Procedure then you should only use

create your_proc
@expenses decimal(10,2)
as
begin
update t
set monthlyExpenses = monthlyExpenses + @expenses,
ytdExpenses = ytdExpenses + @expenses
from yourtable t
end


The other one is for update trigger.

create trigger tu_trigger_name on yourtable for update
as
begin
update t
set ytdExpenses = ytdExpenses + i.monthlyExpenses - d.monthlyExpenses
from inserted i inner join deleted d
on i.pk = d.pk
inner join yourtable t
on i.pk = t.pk
end



KH

Go to Top of Page

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 01:00:18
Thanks KHtan,

I'll try it when I get to work.

Michael
Go to Top of Page

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 08:54:12
When I ran this SQL Trigger I got this for an error...

Server: Msg 207, Level 16, State 3, Procedure tu_TOSS_ytdexpenses, Line 4
Invalid column name 'pk'.
Server: Msg 207, Level 16, State 1, Procedure tu_TOSS_ytdexpenses, Line 4
Invalid column name 'pk'.
Server: Msg 207, Level 16, State 1, Procedure tu_TOSS_ytdexpenses, Line 4
Invalid column name 'pk'.
Server: Msg 207, Level 16, State 1, Procedure tu_TOSS_ytdexpenses, Line 4
Invalid column name 'pk'.
Server: Msg 209, Level 16, State 1, Procedure tu_TOSS_ytdexpenses, Line 4
Ambiguous column name 'ytdExpenses'.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 09:10:59
pk is the primary key column in your table.
perhaps an identity column, or a datetime column.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 09:12:15
replace PK with the primary key column of your table

"Ambiguous column name 'ytdExpenses'."
Sorry about the missing table alias on the column.


create trigger tu_trigger_name on yourtable for update
as
begin
update t
set ytdExpenses = t.ytdExpenses + i.monthlyExpenses - d.monthlyExpenses
from inserted i inner join deleted d
on i.pk = d.pk
inner join yourtable t
on i.pk = t.pk
end



KH

Go to Top of Page

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 09:31:24
ytdExpenses
0


The ytdExpenses is still not updating with the SUM, after I submitted new data to the database. Please help.


Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 10:07:00
Please post your stored procedure or trigger codes


KH

Go to Top of Page

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 10:13:59
USE InfoPathBudget
GO
create trigger tu_trigger_name on TOSS for update
as
begin
update t
set ytdExpenses = t.ytdExpenses + i.monthlyExpenses - d.monthlyExpenses
from inserted i inner join deleted d
on i.ReportID = d.ReportID
inner join TOSS t
on i.ReportID = t.ReportID
end
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 10:32:25
did you code the Insert Trigger as well ?


KH

Go to Top of Page

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 10:35:42
Insert Trigger??
You mean the other one you sent me. I tried but it didn't work. let me try again.

Michael
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 10:41:03
that's is the update trigger. Note the word "for update"
When you insert new records into the TOSS table, you will need to accumulate the value into ytdExpenses as well right ?

create trigger ti_trigger_name on TOSS for insert
as
begin
update t
set ytdExpenses = isnull(t.ytdExpenses, 0) + i.monthlyExpenses
from inserted i inner join TOSS t
on i.ReportID = t.ReportID
end



KH

Go to Top of Page

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 10:43:13
I'll try this..I hope it works.

Michael71
Go to Top of Page

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 11:03:25
Still not adding up the ytdExpenses when I submit to the database
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 11:08:32
Can you post your table structure, some sample data and the testing script that you use ?


KH

Go to Top of Page
    Next Page

- Advertisement -