SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Total monthly Expenses don't Sum up when I submit
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Michael71
Posting Yak Master

USA
126 Posts

Posted - 10/15/2006 :  21:37:48  Show Profile  Reply with Quote
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
17681 Posts

Posted - 10/15/2006 :  21:50:56  Show Profile  Reply with Quote
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

USA
126 Posts

Posted - 10/15/2006 :  23:32:37  Show Profile  Reply with Quote
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)

Singapore
17681 Posts

Posted - 10/15/2006 :  23:37:53  Show Profile  Reply with Quote
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

USA
126 Posts

Posted - 10/15/2006 :  23:44:25  Show Profile  Reply with Quote
I can try both of them right using T-SQL?


Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

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


KH

Go to Top of Page

Michael71
Posting Yak Master

USA
126 Posts

Posted - 10/15/2006 :  23:54:21  Show Profile  Reply with Quote
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)

Singapore
17681 Posts

Posted - 10/16/2006 :  00:00:04  Show Profile  Reply with Quote
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

USA
126 Posts

Posted - 10/16/2006 :  01:00:18  Show Profile  Reply with Quote
Thanks KHtan,

I'll try it when I get to work.

Michael
Go to Top of Page

Michael71
Posting Yak Master

USA
126 Posts

Posted - 10/16/2006 :  08:54:12  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 10/16/2006 :  09:10:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)

Singapore
17681 Posts

Posted - 10/16/2006 :  09:12:15  Show Profile  Reply with Quote
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

USA
126 Posts

Posted - 10/16/2006 :  09:31:24  Show Profile  Reply with Quote
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)

Singapore
17681 Posts

Posted - 10/16/2006 :  10:07:00  Show Profile  Reply with Quote
Please post your stored procedure or trigger codes


KH

Go to Top of Page

Michael71
Posting Yak Master

USA
126 Posts

Posted - 10/16/2006 :  10:13:59  Show Profile  Reply with Quote
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)

Singapore
17681 Posts

Posted - 10/16/2006 :  10:32:25  Show Profile  Reply with Quote
did you code the Insert Trigger as well ?


KH

Go to Top of Page

Michael71
Posting Yak Master

USA
126 Posts

Posted - 10/16/2006 :  10:35:42  Show Profile  Reply with Quote
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)

Singapore
17681 Posts

Posted - 10/16/2006 :  10:41:03  Show Profile  Reply with Quote
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

USA
126 Posts

Posted - 10/16/2006 :  10:43:13  Show Profile  Reply with Quote
I'll try this..I hope it works.

Michael71
Go to Top of Page

Michael71
Posting Yak Master

USA
126 Posts

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

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

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


KH

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000