| Author |
 Topic  |
|
Michael71
Posting Yak Master
USA
126 Posts |
 Posted - 10/15/2006 : 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)
Singapore
16746 Posts |
Posted - 10/15/2006 : 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
|
 |
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/15/2006 : 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
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/15/2006 : 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
|
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/15/2006 : 23:44:25
|
I can try both of them right using T-SQL?
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/15/2006 : 23:45:18
|
Sure. Why not. Just make sure you try this on a development / test enviroment
KH
|
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/15/2006 : 23:54:21
|
Should I use this before it....
USE database name
GO
Cretae proc name
AS
.......then one of them
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/16/2006 : 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
|
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 01:00:18
|
Thanks KHtan,
I'll try it when I get to work.
Michael |
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 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'.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/16/2006 : 09:10:59
|
pk is the primary key column in your table.
perhaps an identity column, or a datetime column.
Peter Larsson
Helsingborg, Sweden |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/16/2006 : 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
|
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 09:31:24
|
ytdExpenses
0
The ytdExpenses is still not updating with the SUM, after I submitted new data to the database. Please help.
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/16/2006 : 10:07:00
|
Please post your stored procedure or trigger codes
KH
|
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 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
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/16/2006 : 10:32:25
|
did you code the Insert Trigger as well ?
KH
|
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 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 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/16/2006 : 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
|
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 10:43:13
|
I'll try this..I hope it works.
Michael71 |
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 11:03:25
|
| Still not adding up the ytdExpenses when I submit to the database |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/16/2006 : 11:08:32
|
Can you post your table structure, some sample data and the testing script that you use ?
KH
|
|
|
|
Topic |
|
Total monthly Expenses don't Sum up when I submit
