Update ytdExpenses with a stored Procedure

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

Update ytdExpenses with a stored Procedure

Author

Topic

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 21:56:15

I'm trying to update the ytdExpenses from the monthly Expenses. I'm submitting to the SQL database a currency amount everymonth and need for the ytdExpenses to be calculated from the sum of the monthly expenses so that I can display it on a form. I've tried everything and still can't figure this out. Will I need a stored procedure or a trigger? I want to place the update ytdExpense total at the top of my database so when I open a new for the form picks up the top most value and displays it in the form. Also, as soon as someone submit to the database the ytdExpense should calculate and show up on the form right then. My database looks like this

ReportID    ReportDate    monthlyExpense    ytdExpenses

4             10/12/06             $1000               $10,000
3              11/12/06             $2000             $9,000
2              12/12/06             $3000              $7,000
1              1/12/06              $4000              $4,000

I just want to add the monthly expenses up and display them in the ytdExpenses Column at the top everytime someone submit tho the database. That way the form picks up the ytdExpenses at the top of the database everytime. Here is what I got so far...Thanks ahead of time....

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 22:11:03

[code]
-- calculate the ytdExpenses upon SELECT
create table #table1
(
ReportID int identity(1,1),
ReportDate datetime,
monthlyExpenses int
)

insert into #table1 (ReportDate, monthlyExpenses)
select '20061012', 1000 union all
select '20061015', 1500

select ReportID, ReportDate, monthlyExpenses,
ytdExpenses = (select sum(monthlyExpenses) from #table1)
from #table1

/*
ReportID ReportDate monthlyExpenses ytdExpenses
----------- ----------- --------------- -----------
1 2006-10-12 1000 2500
2 2006-10-15 1500 2500
*/
drop table #table1
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 22:12:23

[code]
-- store procedure method. Use SP to insert / update to the table
-- and calculate the ytdExpenses

create table #table2
(
ReportID int identity(1,1),
ReportDate datetime,
monthlyExpenses int,
ytdExpenses int
)

insert into #table2 (ReportDate, monthlyExpenses)
select '20061012', 1000

-- update it everytime a record is inserted or updated to the table
update t
set ytdExpenses = (select sum(monthlyExpenses) from #table2)
from #table2 t

insert into #table2 (ReportDate, monthlyExpenses)
select '20061012', 1500

update t
set ytdExpenses = (select sum(monthlyExpenses) from #table2)
from #table2 t

select * from #table2
/*
ReportID ReportDate monthlyExpenses ytdExpenses
----------- ------------ --------------- -----------
1 2006-10-12 1000 2500
2 2006-10-12 1500 2500

(2 row(s) affected)
*/

drop table #table2
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 22:14:18

[code]
create table table3
(
ReportID int identity(1,1),
ReportDate datetime,
monthlyExpenses int,
ytdExpenses int
)
go
create trigger ti_table3 on table3 for insert, update
as
begin
update t
set ytdExpenses = (select sum(monthlyExpenses) from table3)
from table3 t
end
go

insert into table3 (ReportDate, monthlyExpenses) select '20061012', 1000
insert into table3 (ReportDate, monthlyExpenses) select '20061015', 1500
update table3 set monthlyExpenses = 1200 where ReportID = 1

select * from table3
/*
ReportID ReportDate monthlyExpenses ytdExpenses
----------- ------------ --------------- -----------
1 2006-10-12 1200 2700
2 2006-10-15 1500 2700
*/
drop table table3
go
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 22:14:45

Is this what you want ? ?

all rows of the ytdExpenses having same values ? ?

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 22:16:09

khtan,

I'm submitting to this database. Don't you think I need a Update statement or a trigger or something. I mean a Select statement won't allow me to update the database as soon as a person submit to it, right? I thank you very much for all your help...I need to get this done...my job is depending on it.

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 22:19:37

I think that will do khtan..I just need for that Sum to be there so I can pull it out and display it...in a form...but it would be nice if it accumulated monthly like

from the top

$12,000
$10,000
$8,000

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 22:21:49

khtan why did you put table3...would this just be my table...and will this update everytime someone submit to the database?

create trigger ti_table3 on table3 for insert, update
as
begin
update t
set ytdExpenses = (select sum(monthlyExpenses) from table3)
from table3 t
end
go

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 22:25:25

I think you gave me the answer..khtan...one more question though..When a person submit to the database The ReportID should be..how do I do this and is is it best to do it this way?
ReportID
2
1

ReportID ReportDate monthlyExpenses ytdExpenses
----------- ------------ --------------- -----------
1 2006-10-12 1200 2700
2 2006-10-15 1500 2700
*/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 22:29:00

quote:
Originally posted by Michael71

I think that will do khtan..I just need for that Sum to be there so I can pull it out and display it...in a form...but it would be nice if it accumulated monthly like

from the top

$12,000
$10,000
$8,000

Ah so what you want is a running total / accumulation ?

Does your front end tool has a running total feature ? Do it there.
Or use the following code


-- calculate the ytdExpenses upon SELECT
create table #table1
(
	ReportID		int identity(1,1),
	ReportDate		datetime,
	monthlyExpenses		int
)

insert into #table1 (ReportDate, monthlyExpenses)
select '20061012', 1000	union all
select '20061015', 1500 union all
select '20061020', 2500


select	ReportID, ReportDate, monthlyExpenses,
	ytdExpenses = (select sum(monthlyExpenses) from #table1 x where x.ReportDate <= t.ReportDate)
from	#table1 t
order by ReportDate

/*
ReportID    ReportDate  monthlyExpenses ytdExpenses 
----------- ----------- --------------- ----------- 
1           2006-10-12  1000            1000
2           2006-10-15  1500            2500
3           2006-10-20  2500            5000
*/
drop table #table1

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 22:34:00

khtan that is almost perfect, but 5000 is not at the top of the table..there fore my form won't pic it up it will pic up the 1000 I just need to flip flop the chart if possible.....

ReportID ReportDate monthlyExpenses ytdExpenses
----------- ----------- --------------- -----------
3 2006-10-20 2500 5000
2 2006-10-15 1500 2500
1 2006-10-12 1000 1000
*/

ReportID ReportDate monthlyExpenses ytdExpenses
----------- ----------- --------------- -----------
1 2006-10-12 1000 1000
2 2006-10-15 1500 2500
3 2006-10-20 2500 5000
*/
drop table #table1

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 22:36:12

Will this update evry time someone submit to the database

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 22:36:40

Add

order by ReportDate desc

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 22:39:23

You mean just place a Select statement ...sorry I'm new at SQL server

order by ReportDate desc

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 22:47:41

I think I get it now khtan....One more Question? If I submitted to the database so much that the ReportID is at 45, how do I start it over to 1....I know that's a crazy question.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 22:47:51

Yup. In the select statement


select	ReportID, ReportDate, monthlyExpenses,
	ytdExpenses = (select sum(monthlyExpenses) from #table1 x where x.ReportDate <= t.ReportDate)
from	#table1 t
order by ReportDate DESC

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 22:52:46

So once I execute this Select Statement, I don't have to execute it anymore. Everytime I open up the database it will be position like this?

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 22:53:40

#table1 is just my table right...8-)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 23:11:05

yes. #table1 is your table.

"So once I execute this Select Statement, I don't have to execute it anymore. Everytime I open up the database it will be position like this?"
You will have to peform the SELECT statement everytime you need to retrieve records from the table.

Note records are not stored in table in a database in any particular order. Physical ordering of the record has no meaning. You specify the ordering / sequence the records you want using the ORDER BY clause.

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-16 : 23:41:34

khtan,
when I submit to the database by a form, how can I Order By desc. Because when the form opens its going to choose the most top ytdExpense.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 23:47:31

I am not familiar with the front end tools, I do things mostly at the back