Author |
Topic |
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-10-16 : 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
126 Posts |
Posted - 2006-10-16 : 13:31:30
|
This won't work |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-10-16 : 13:45:38
|
I' haven't got a answer yet.. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-16 : 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
126 Posts |
Posted - 2006-10-16 : 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
30421 Posts |
Posted - 2006-10-16 : 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 LarssonHelsingborg, Sweden |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-16 : 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
126 Posts |
Posted - 2006-10-16 : 14:08:09
|
Ok lets see |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-10-16 : 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,000I also need a ytdBudgeted and ytdVariancefor expenses this is what I got so far...USE InfoPathBudgetGOcreate trigger tu_ytdExpense 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.ReportIDendIt don't seem to work. Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-16 : 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 tSET t.ytdExpenses = (SELECT SUM(q.monthlyExpenses) FROM TOSS q WHERE q.YearColumn = t.YearColumn AND q.MonthColumn <= t.MonthColumn)FROM TOSS t Peter LarssonHelsingborg, Sweden |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-10-16 : 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
126 Posts |
Posted - 2006-10-16 : 14:25:28
|
Would this be a Stored Procedure? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-16 : 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,000As you can see, this is summation in another order. You HAVE to tell us how to differentiate between the records!Peter LarssonHelsingborg, Sweden |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-16 : 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
126 Posts |
Posted - 2006-10-16 : 14:29:40
|
Then what should I do Michael Valentine |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-16 : 14:30:30
|
[Sigh]Even if you call the column ytmExpenses, do you understand what the part ytd or ytm means?Peter LarssonHelsingborg, Sweden |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-10-16 : 14:32:32
|
How can I Sum the monthExpenses without the ytdExpenses |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-16 : 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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-16 : 14:35:01
|
select sum(monthexpenses) from TOSSPeter LarssonHelsingborg, Sweden |
|
|
Next Page
|