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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Ytd Expenses---Trigger or Stored Procedure

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

Posted - 2006-10-16 : 13:12:37
Got some DDL?

In any case, that's derived data, and I really don't think you want to do this.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 13:31:30
This won't work
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 13:34:02
Similar question here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73534
and here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73581


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 13:45:38
I' haven't got a answer yet..
Go to Top of Page

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

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

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 14:08:09
Ok lets see
Go to Top of Page

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

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

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

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 14:25:28
Would this be a Stored Procedure?
Go to Top of Page

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

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

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 14:29:40
Then what should I do Michael Valentine
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 14:32:32
How can I Sum the monthExpenses without the ytdExpenses
Go to Top of Page

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 14:35:01
select sum(monthexpenses) from TOSS


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
    Next Page

- Advertisement -