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
 Ytd Expenses---Trigger or Stored Procedure
 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/16/2006 :  13:10:15  Show Profile  Reply with Quote
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 - 10/16/2006 :  13:12:37  Show Profile  Reply with Quote
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

USA
126 Posts

Posted - 10/16/2006 :  13:31:30  Show Profile  Reply with Quote
This won't work
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

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

USA
126 Posts

Posted - 10/16/2006 :  13:45:38  Show Profile  Reply with Quote
I' haven't got a answer yet..
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

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

USA
126 Posts

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

Sweden
30108 Posts

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

USA
36594 Posts

Posted - 10/16/2006 :  14:05:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
126 Posts

Posted - 10/16/2006 :  14:08:09  Show Profile  Reply with Quote
Ok lets see
Go to Top of Page

Michael71
Posting Yak Master

USA
126 Posts

Posted - 10/16/2006 :  14:14:59  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

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

Edited by - SwePeso on 10/16/2006 14:20:12
Go to Top of Page

Michael71
Posting Yak Master

USA
126 Posts

Posted - 10/16/2006 :  14:21:32  Show Profile  Reply with Quote
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

USA
126 Posts

Posted - 10/16/2006 :  14:25:28  Show Profile  Reply with Quote
Would this be a Stored Procedure?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 10/16/2006 :  14:26:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/16/2006 :  14:26:38  Show Profile  Reply with Quote
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

USA
126 Posts

Posted - 10/16/2006 :  14:29:40  Show Profile  Reply with Quote
Then what should I do Michael Valentine
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 10/16/2006 :  14:30:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
[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
Go to Top of Page

Michael71
Posting Yak Master

USA
126 Posts

Posted - 10/16/2006 :  14:32:32  Show Profile  Reply with Quote
How can I Sum the monthExpenses without the ytdExpenses
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/16/2006 :  14:34:48  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 10/16/2006 :  14:35:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
select sum(monthexpenses) from TOSS


Peter Larsson
Helsingborg, Sweden
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