Calculate users level and sublevel

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

SQL Server 2005 Forums

Transact-SQL (2005)

Calculate users level and sublevel

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2008-02-07 : 00:33:18

Hi,

I have the following tables:

Task
TaskID
Points

User
UserID
UserFNm
UserLNm
LevelSubLevelID

TaskUser
TaskID
UserID
StartDateOfTask
EndDateOfTask

Level
LevelID
LevelName

SubLevel
SubLevelId
SubLevelName

LevelSubLevel
LevelSubLevelID
LevelID
SubLevelID
RequiredPoints

The tables represent a system where a user earns points on doing a 'task' on a forum. RequiredPoints depicts the total points required to move to the next Sublevel in that Level.

Sample
LevelSubLevelID  LevelID   SubLevelID  RequiredPoints
1                  1         1           500
2                  1         2           1000
3                  1         3           1500
4                  2         1           2000
5                  2         2           2500
6                  2         3           3000

and so on...

In each Level there are 3 SubLevels. There are total 5 Level - Novice, Intermediate, Advanced, Admin, SuperUser

I want 2 queries which will display data of the users who are eligible to move to the next sublevel or level in the following manner:

1. The points collected by user and the required points to move to the next SUBLEVEL by taking a % of difference between (ReqPoints - Points) and ordering by it. Eg:

UserFNm  Level   SubLevel Points  RequiredPoints
Jack     Novice   S1        480       500
Harry    Novice   S2        900       1000

2. All those user who are eligible to move to the next LEVEL (Users who are in SubLevel 3 in the Level 1. Now they are eligible to move to Level 2 Sublevel 1 order by the required points to move to the next Level - Current Sublevel points)

How to do this?

lols
Posting Yak Master

174 Posts

Posted - 2008-02-09 : 02:10:55

anyone?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-09 : 03:01:20

[code]1.SELECT u.UserFNm,l.Level,sl.SubLevel,Total.Points,
ISNULL(rp.RequiredPoints,0) AS RequiredPoints
FROM User u
INNER JOIN LevelSubLevel lsl
ON lsl.LevelSubLevelID=u.LevelSubLevelID
INNER JOIN Level l
ON l.LevelID=lsl.LevelID
INNER JOIN SubLevel sl
ON sl.SubLevelId=lsl.SubLevelId
INNER JOIN (SELECT u1.UserID,SUM(t.Points) AS Points
FROM User u1
INNER JOIN TaskUser tu
ON tu.UserID=u1.UserID
INNER JOIN Task t
ON tu.TaskID=t.TaskID
GROUP BY u.UserID)Total
ON Total.UserID=u.UserID
OUTER APPLY (SELECT MIN(RequiredPoints) AS RequiredPoints
FROM LevelSubLevel
WHERE RequiredPoints> Total.Points)rp
ORDER BY CASE WHEN RequiredPoints=0 THEN RequiredPoints
ELSE (RequiredPoints-Points)
END[/code]

didnt understand the 2nd scenario. Are you looking for lower levels which users can migrate too? can you illustrate this with some sample data?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources