Click here to Skip to main content
15,867,901 members
Please Sign up or sign in to vote.
5.00/5 (3 votes)
See more:
Hello,

I'm working with a few tables with this format:

-dbo.January-
USER STATS
=================
user1 123
user2 456

-dbo.February-
USER STATS
=================
user1 149
user2 987

-dbo.YearToDate-
USER STATS
=================
user1 ???
user2 ???

I want to write a stored procedure that can sum up the stats for each user in the Month tables and update the total to the YearToDate table.

I feel should be easy to write, but I'm not sure how to start. Can anyone point me in the right direction or have some snippet/example?

Thank you, your help is much appreciated!
Posted

You can also use database views for the solution. It will be easy for you and there will be no need to update the view manually by stored procedure as it will update automatically according to the values stored in the tables.
If you find this reply useful then you can further ask me and get clarification.
 
Share this answer
 
v2
Look at these three concepts in the links below. You might end up with a procedure with one create table (temp or variable) using a union to reduce the statements. Then do an update to yearToDate with a subquery. I'll write the update statement below these links.

UNION:
http://msdn.microsoft.com/en-us/library/ms180026.aspx

Temporary Tables, Table Variables
http://www.mssqltips.com/tip.asp?tip=1556


//create the bigTable from a union statement if you like here....
...

//update the values in the yearToDate table next

update yearToDate
set stats = (select sum(b.stats) from bigTable b where b.user = yearToDate.user)
 
Share this answer
 
Comments
Dalek Dave 8-Sep-10 17:29pm    
Good answer.
kidalphabet 13-Sep-10 15:45pm    
Thank you this was pretty much what I was looking for.
Insert into YEARTODATE
SELECT iif(count(JAN.USER)>count(FEB.USER), JAN.USER,FEB.USER) AS USER, Sum(FEB.STAR) + SUM(JAN.STAR) AS STAR
FROM FEB INNER JOIN JAN ON FEB.USER = JAN.USER
GROUP BY FEB.USER, JAN.USER;
--try this
 
Share this answer
 
Comments
CHill60 4-May-15 14:07pm    
And what about Mar, Apr, May ...., Dec?
Quite apart from the fact the OP confirmed the question as answered over 4 years ago.
--Create Table for January with Data
select *into January
from
(
select 'user1' [USER],123 [STATS]
union
select 'user2', 456
)Jan
--Create Table for February with Data
select *into February
from
(
select 'user1' [USER],125 [STATS]
union
select 'user2', 450
)Feb
--Create Table for March with Data
select *into March
from
(
select 'user1' [USER],150 [STATS]
union
select 'user2', 350
)Mar

--Create table to Update the Value
select *into YearToDate
from
(
select 'user1' [USER],NULL [STATS]
union
select 'user2', NULL
)YearToDate


CREATE Proc SP_UpdateToDate
as
Begin

update YTU set ytu.[stats]=TMP.totalStats
from YearToDate YTU JOIN
(
select [user],SUM([stats]) totalStats from
(
select *From January
Union ALL
select *From February
Union ALL
select *From March
--add rest of months here with "union all" statement

) tmp group by [user]
) TMP ON YTU.[USER]=tmp.[USER]

select *From January
select *From February
select *From March
select *From YearToDate

end
 
Share this answer
 
v2
Comments
CHill60 18-Dec-15 3:46am    
Question was asked, answered and solution accepted more than 5 years ago. You have added nothing new to the discussion

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900