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 + @expensesfrom yourtable t 2b. If the updating is via application directly to the table, then use an update triggerupdate t set ytdExpenses = ytdExpenses + i.monthlyExpenses - d.monthlyExpensesfrom 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 statementselect monthlyExpenses, ytdExpenses = (select sum(monthlyExpenses) from yourtable x where x.datecol <= t.datecol)from yourtable tHave i cover all possible solutions ? KH |
|
|
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.monthlyExpensesfrom inserted i inner join deleted d on i.pk = d.pk inner join yourtable t on i.pk = t.pkor update t set monthlyExpenses = monthlyExpenses + @expenses, ytdExpenses = ytdExpenses + @expensesfrom yourtable t |
|
|
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 |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-10-15 : 23:44:25
|
I can try both of them right using T-SQL? |
|
|
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 |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-10-15 : 23:54:21
|
Should I use this before it....USE database nameGOCretae proc nameAS .......then one of them |
|
|
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 nameGOCretae proc nameAS .......then one of them
If you are using Stored Procedure then you should only use create your_proc@expenses decimal(10,2)asbeginupdate t set monthlyExpenses = monthlyExpenses + @expenses, ytdExpenses = ytdExpenses + @expensesfrom yourtable tend The other one is for update trigger.create trigger tu_trigger_name on yourtable for updateasbeginupdate t set ytdExpenses = ytdExpenses + i.monthlyExpenses - d.monthlyExpensesfrom inserted i inner join deleted d on i.pk = d.pk inner join yourtable t on i.pk = t.pkend KH |
|
|
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 |
|
|
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 4Invalid column name 'pk'.Server: Msg 207, Level 16, State 1, Procedure tu_TOSS_ytdexpenses, Line 4Invalid column name 'pk'.Server: Msg 207, Level 16, State 1, Procedure tu_TOSS_ytdexpenses, Line 4Invalid column name 'pk'.Server: Msg 207, Level 16, State 1, Procedure tu_TOSS_ytdexpenses, Line 4Invalid column name 'pk'.Server: Msg 209, Level 16, State 1, Procedure tu_TOSS_ytdexpenses, Line 4Ambiguous column name 'ytdExpenses'. |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 updateasbeginupdate t set ytdExpenses = t.ytdExpenses + i.monthlyExpenses - d.monthlyExpensesfrom inserted i inner join deleted d on i.pk = d.pk inner join yourtable t on i.pk = t.pkend KH |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-10-16 : 09:31:24
|
ytdExpenses0The ytdExpenses is still not updating with the SUM, after I submitted new data to the database. Please help. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-16 : 10:07:00
|
Please post your stored procedure or trigger codes KH |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-10-16 : 10:13:59
|
USE InfoPathBudgetGOcreate trigger tu_trigger_name on TOSS for updateasbeginupdate t set ytdExpenses = t.ytdExpenses + i.monthlyExpenses - d.monthlyExpensesfrom inserted i inner join deleted d on i.ReportID = d.ReportID inner join TOSS t on i.ReportID = t.ReportIDend |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-16 : 10:32:25
|
did you code the Insert Trigger as well ? KH |
|
|
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 |
|
|
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 insertasbeginupdate tset ytdExpenses = isnull(t.ytdExpenses, 0) + i.monthlyExpensesfrom inserted i inner join TOSS ton i.ReportID = t.ReportIDend KH |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-10-16 : 10:43:13
|
I'll try this..I hope it works. Michael71 |
|
|
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 |
|
|
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 |
|
|
Next Page
|