This is very interesting,, but might not work in all cases for me and let me explain why.

I'm sorry i did not include this in the beginning.

The tb_Points table will store points and rank for a number of stats.

There also exists a table:

tb_PointsType
Fields:
PointsTypeID
Description

Lets re-create the tb_Points so that it is more clear
Table 2: tb_Points
AccountID
PointsTypeID
Points
Rank



Again with some dummy data.

Table: tb_PointsType
1 ArticlePoints
2 ReplyPoints
3 RatingPoints

Table: tb_Points
AccountID PointsTypeID Points Rank
1 1 3000 (trying to populate this)
1 3 234 " " "
34 1 4500
34 2 1234

So i can't drop the whole table all the time because the way this is going to work is that
I will be taking 1 PointsTypeID at a time, calculating the points from it's related table, like tb_Articles For instance,
then i will be doing an update or insert int tb_Points with my new calculations.

So for instance,

I will take tb_PointsType table, and grab the appropriate table, calculate points for all who belong to that table(tb_Articles for instance) then
with that i want to store the points, rank, PointsTypeID, and AccountID in tb_Points.

SampleCode:

SQL = "SELECT * FROM tb_PointsType Order By PointsTypeID"
dr = DAL.ExecuteQUERY(SQL)

Do while dr.read()
    Select Case dr("PointsTypeID")
        case ePointsType.ArticlePoints
            SQL = "sp_BatchUpdateArticlePonts " & dr("AccountID")
        case ePointsType.ReplyPoints
            SQL = "sp_BatchUpdateReplyPonts " & dr("AccountID")
        case ePointsType.RatingPoints
        SQL = "sp_BatchUpdateRatingPonts " & dr("AccountID")
        .
        .

        cmd.ExecuteNonQuery(SQL)
LOOP


Create Procedure sp_BatchUpdateArticlePonts(@AccountID)

Select Sum(DailyPoints + TotalPoints) as Sum Points, AccountID, 1 As PointsTypeID FROM tb_Accounts
Group By AccountID
Order by Points DESC

i hope i'm being more clear. if not please let me know! thanks all!

Set RANK to bigint and set it as identity yes, seed 1. When you do your insert it will increase it by one for each new record. this will work for a one time shot, do you plan on clearing the tbPoints table and repopulating it? if so you would have to drop and recreate the table each time you run the stored procedure

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbPoints]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbPoints]
GO

CREATE TABLE [dbo].[tbPoints] (
    [RANK] [bigint] IDENTITY (1, 1) NOT NULL ,
    [POINTS] [bigint] NOT NULL ,
    [ACCOUNTID] [bigint] NOT NULL
) ON [PRIMARY]
GO

This is very interesting,, but might not work in all cases for me and let me explain why.

I'm sorry i did not include this in the beginning.

The tb_Points table will store points and rank for a number of stats.

There also exists a table:

tb_PointsType
Fields:
PointsTypeID
Description

Lets re-create the tb_Points so that it is more clear
Table 2: tb_Points
AccountID
PointsTypeID
Points
Rank



Again with some dummy data.

Table: tb_PointsType
1 ArticlePoints
2 ReplyPoints
3 RatingPoints

Table: tb_Points
AccountID PointsTypeID Points Rank
1 1 3000 (trying to populate this)
1 3 234 " " "
34 1 4500
34 2 1234

So i can't drop the whole table all the time because the way this is going to work is that
I will be taking 1 PointsTypeID at a time, calculating the points from it's related table, like tb_Articles For instance,
then i will be doing an update or insert into tb_Points with my new calculations.

So for instance,

I will take tb_POintsType, and grab each table the appropriate table, calculate points for all who belong to that table(tb_Articles for instance) then
with that i want to store the points, rank, PointsTypeID, and AccountID in tb_Points.

SampleCode:

SQL = "SELECT * FROM tb_PointsType Order By PointsTypeID"
dr = DAL.ExecuteQUERY(SQL)

Do while dr.read()
    Select Case dr("PointsTypeID")
        case ePointsType.ArticlePoints
            SQL = "sp_BatchUpdateArticlePonts " & dr("AccountID")
        case ePointsType.ReplyPoints
            SQL = "sp_BatchUpdateArticlePonts " & dr("AccountID")
        case ePointsType.RatingPoints
        SQL = "sp_BatchUpdateArticlePonts " & dr("AccountID")
        .
        .

        cmd.ExecuteNonQuery(SQL)
LOOP


i hope i'm being more clear. if not please let me know! thanks all!

Usually this is how I would do this. You'll need to check the syntax as I'm not sitting in front of a SQL console to verify:

Create Procedure sp_BatchUpdateArticlePonts(@AccountID)
Select Sum(DailyPoints + TotalPoints) as Sum Points, AccountID
INTO #Rank
FROM tb_Accounts
Group By AccountID
Order by Points DESC

MODIFY TABLE #Rank ADD Rank INT IDENTITY

UPDATE tb_Accounts SET Rank=tmp.Rank
FROM #Rank WHERE tb_Accounts.AccountID=tmp.AccountID

DROP TABLE #Rank

Try that and let us know if it works.

that worked great with some tweaks! thanks a bunch!!