| Author |
 Topic  |
|
Michael71
Posting Yak Master
USA
126 Posts |
 Posted - 10/16/2006 : 13:10:15
|
I'm trying to come up with a Stored Procedure or a Trigger to Sum up monthly =Expenses to the YTDExpenses Column. Can Someone help please.
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
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 13:31:30
|
| This won't work |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 13:45:38
|
I' haven't got a answer yet..
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 10/16/2006 : 13:46:45
|
You keep posting this same question over and over again. You might get some help if you actually explained what you are trying to do, but you don't answer any of the questions people ask, and just post another topic.
CODO ERGO SUM |
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 13:53:38
|
Michael Jones,
I told you clear as day... All I'm trying to do is Add a column up called ytdMonthly expenses to ytdExpenses. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/16/2006 : 14:02:13
|
And all we tell you, is to listen to our advices and provide the data we ask for.
What we need is a name for another column for which each monthly expense is aggregated for.
Peter Larsson
Helsingborg, Sweden |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 10/16/2006 : 14:05:39
|
quote:
Originally posted by Michael71
Michael Jones,
I told you clear as day... All I'm trying to do is Add a column up called ytdMonthly expenses to ytdExpenses.
If it were as clear as day, we'd have an answer for you already.
Tara Kizer |
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 14:08:09
|
| Ok lets see |
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 14:14:59
|
Hey,
I'm trying to Sum monthlyExpenses, monthlyBudgeted, and monthlyVariance columns and display the total into the ytd column for every month. Example....
monthlyExpenses monthlyBudgeted monthlyVariance ytdExpenses .....
$10,000 $20,000 $10,000 $10,000
I also need a ytdBudgeted and ytdVariance
for expenses this is what I got so far...
USE InfoPathBudget
GO
create trigger tu_ytdExpense 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
It don't seem to work. Thanks
|
Edited by - Michael71 on 10/16/2006 14:15:48 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/16/2006 : 14:18:19
|
How are we expected to understand for which months the aggregated sample data you provided, belongs to?
Year Month monthlyExpenses ytdExpenses
? ? $1,000 $1,000
? ? $2,000 $3,000
? ? $3,000 $6,000
? ? $2,000 $8,000
? ? $5,000 $13,000
You most probably need something likeUPDATE t
SET t.ytdExpenses = (SELECT SUM(q.monthlyExpenses) FROM TOSS q WHERE q.YearColumn = t.YearColumn AND q.MonthColumn <= t.MonthColumn)
FROM TOSS t
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 10/16/2006 14:20:12 |
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 14:21:32
|
| Why put month....I just want to Sum up the ytdExpenses every time a person submit to the database |
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 14:25:28
|
| Would this be a Stored Procedure? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/16/2006 : 14:26:29
|
Yes Michael, we are VERY aware of that. But you still need to have a business rule on how to distinguish between the records in the database, so the query we provide you aggregates the numbers in the right order. Otherwise the summation will be random and eventually we would sum up
monthlyExpenses ytdExpenses
$2,000 $2,000
$5,000 $7,000
$3,000 $10,000
$1,000 $11,000
$2,000 $13,000
As you can see, this is summation in another order. You HAVE to tell us how to differentiate between the records!
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 10/16/2006 14:27:26 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 10/16/2006 : 14:26:38
|
Also, it doesn't make any sense to have a column for any YTD numbers in a table that holds MTD numbers. These are summaries at two different levels.
CODO ERGO SUM |
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 14:29:40
|
| Then what should I do Michael Valentine |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/16/2006 : 14:30:30
|
[Sigh]
Even if you call the column ytmExpenses, do you understand what the part ytd or ytm means?
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 10/16/2006 14:32:13 |
|
|
|
Michael71
Posting Yak Master
USA
126 Posts |
Posted - 10/16/2006 : 14:32:32
|
| How can I Sum the monthExpenses without the ytdExpenses |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 10/16/2006 : 14:34:48
|
quote:
Originally posted by Michael71
Then what should I do Michael Valentine
More than likely, redesign your database to only hold expense transactions. You can then sum them up at any level you want when you need the information: by day, by week , by month, by quarter, by year, etc.
CODO ERGO SUM |
Edited by - Michael Valentine Jones on 10/16/2006 14:36:00 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/16/2006 : 14:35:01
|
select sum(monthexpenses) from TOSS
Peter Larsson
Helsingborg, Sweden |
|
|
|
Topic |
|
Ytd Expenses---Trigger or Stored Procedure
