Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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		  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Michael71
Posting Yak Master

USA
126 Posts
Posted - 10/16/2006 :  11:16:50  Show Profile  Reply with Quote
ReportID expenseReportDate expenseStartDate expenseEndDate monthlyExpenses monthlyBudgeted monthlyBudgetVariance ytdExpenses ytdBudgeted ytdBudgetedVariance expenseExplain CapitalReportDate CapitalStartDate CapitalEndDate monthlyCapitalExpenses monthlyCapitalBudgeted monthlyCapitalVariance ytdCapitalExpenses ytdCapitalBudgeted ytdCapitalBudgetedVariance CapitalExplain employeeName employeeJobTitle employeeDepartment employeeEmail employeeManager ManagerEmail
18 10/3/2006 10/10/2006 10/19/2006 4000 6000 2000 4000 0 10/3/2006 10/11/2006 10/19/2006 6000 8000 2000 0 Patrick Michael Software Engineer Security JohnDoe@uhaul.com Mike Ferrel HarryDawn@cox.com
19 10/10/2006 10/3/2006 10/27/2006 5600 7000 1400 5600 0 10/2/2006 10/4/2006 10/6/2006 34000 45000 11000 0 sdfhh John Doe Cook Helper One I.T. JohnDoe@uhaul.com Mike Ferrel HarryDawn@cox.com




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
Go to Top of Page

Michael71
Posting Yak Master

USA
126 Posts
Posted - 10/16/2006 :  11:18:31  Show Profile  Reply with Quote
It won't all fit correct on the page. Sorry
Go to Top of Page

Michael71
Posting Yak Master

USA
126 Posts
Posted - 10/16/2006 :  11:22:48  Show Profile  Reply with Quote
monthlyExpenses
4000
5600
Go to Top of Page

Michael71
Posting Yak Master

USA
126 Posts
Posted - 10/16/2006 :  11:30:11  Show Profile  Reply with Quote
ReportID expenseReportDate expenseStartDate ExpenseEndDate monthlyExpenses monthlyBudgeted
monthlyBudgetVariance ytdExpenses ytdBudgeted ytdBudgetedVariance expenseExplain
Go to Top of Page

Michael71
Posting Yak Master

USA
126 Posts
Posted - 10/16/2006 :  11:34:35  Show Profile  Reply with Quote
Are you there?
Go to Top of Page

Michael71
Posting Yak Master

USA
126 Posts
Posted - 10/16/2006 :  12:02:20  Show Profile  Reply with Quote
khtan

I tried to post my table structure but it won't all fit on the screen. Can you help me?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts
Posted - 10/16/2006 :  12:31:16  Show Profile  Reply with Quote
quote:
Originally posted by Michael71

Are you there?

Bueller? Bueller?...




CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts
Posted - 10/16/2006 :  14:03:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Close this topic and continue here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73584


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts
Posted - 10/16/2006 :  21:03:24  Show Profile  Reply with Quote
quote:
Originally posted by Michael71

Are you there?

Hey. I am not a bot

KH
Go to Top of Page

Michael71
Posting Yak Master

USA
126 Posts
Posted - 10/16/2006 :  22:00:51  Show Profile  Reply with Quote
Khtan,

The code you gave me didn't update everytime I submitted to the database.. Any ideas
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts
Posted - 10/16/2006 :  22:15:22  Show Profile  Reply with Quote
see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73602

KH
Go to Top of Page

Michael71
Posting Yak Master

USA
126 Posts
Posted - 10/17/2006 :  15:53:43  Show Profile  Reply with Quote
khtan,

Could you help me out with a trigger..I just can't get this database to update when I know it should. I just think I want the ytdExpenses Column to read the same..can you help me out with this..Please....just a trigger that will do this in a simple way..everytime I submit to the database. Here is the trigger I have create trigger tu_ytdExpenses 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






monthlyExpenses     ytdExpenses
$1,000              $13,000
$2,000              $13,000
$3,000              $13,000
$2,000              $13,000
$5,000              $13,000
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts
Posted - 10/17/2006 :  19:18:42  Show Profile  Reply with Quote
use the trigger i posted in this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73602 for table3

KH
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 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.14 seconds. Powered By: Snitz Forums 2000