Historical Calculation - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Historical Calculation

Author

Topic

rpcutts
Starting Member

10 Posts

Posted - 2006-03-24 : 11:22:56

Any guidance or point in the direction of relevant articles would be appreciated.

I am attempting to model a system in which a group of entrants a running an assault course once a week.
The entrants are split into 4 groups each group having a different level of handicap.

i.e.
Group 1: no handicap
Group 2: run in Wellington boots
Group 3: run with a weighted backpack
Group 4: run with a weighted backpack and 30 second penalty

An entrant is assigned a group for a certain time period and could stay ion the same group for the duration of the exercise or could change to another group after one week.

The points system is 40000 points for first place 39000 for 2nd 38000 for 3rd etc. In addition a % of points is deducted based on the group assigned to the kid.

Group 1: 20%
Group 2: 15%
Group 3: 10%
Group 4: 0%

I have the following tables:

Entrant(EntrantID, Firstname, Surname)

Group(GroupNumber, PenaltyPercentage)

Prize(Position, Points)

Result(RaceDate, EntrantID, Position)

GroupAssignment(EntrantID, GroupNumber, FromDate, ToDate)

I need to write a sproc to give me the total points for an entrant between 2 dates so I can populate a league table
for any time period (Who scored the most points between 1st May and the 30th June).


SELECT	Entrant.EntrantID,
	SUM(Prize.Points - (Prize.Points / Group.PenaltyPercentage) AS TotalPonts

FROM    Prize
INNER JOIN Result ON Prize.Position = Result.Position AND RaceDate BETWEEN @StartDate AND @EndDate
INNER JOIN Entrant ON Result.EntrantID = Entrant.EntrantID
INNER JOIN GroupAssignment ON Entrant.EntrantID = GroupAssignment.EntrantID
INNER JOIN Group ON GroupAssignment.GroupNumber = Group.GroupNumber

This would only work if the group assignments did not change over time. At the moment I can't despite my best efforts think of a way to calculate the points total taking into consideration the historical changes of the group assignment.

I need to (probably using a corrolated subquery) somehow find the group assignment corresponding to the entrant and the race date in question and use it in my calculated field.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-24 : 12:28:11

If you give us some sample data and the expected output of the query based on that data, that will make it a lot easier for us to help you...

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

rpcutts
Starting Member

10 Posts

Posted - 2006-03-24 : 12:50:17

Entrants:
1, Mike, Smith
2, Joe, Bloggs

Groups:
1, 20
2, 15
3, 10
4, 0

Prize:
1, 40000
2, 39000
3, 38000 ... etc.

Results:
2006-03-01, 1(mike), 1(1st)
2006-03-01, 2(joe), 2(2nd)
2006-03-08, 1(mike), 2(2nd)
2006-03-08, 2(joe), 1(1st)

GroupAssignments:
1, 1, 2006-03-01, NULL
2, 4, 2006-03-01, 2006-03-07
2, 1, 2006-03-08, NULL

EXPECTED RESULT:

Mike Smith, 63200 (40,000-20% + 39,000-20%)
Joe Bloggs, 71000 (39,000 + 40,000-20%)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-24 : 13:52:54

How's this?

--data
declare @Entrant table (EntrantID int, Firstname varchar(30), Surname varchar(30))
insert @Entrant
          select 1, 'Mike', 'Smith'
union all select 2, 'Joe', 'Bloggs'
union all select 3, 'Sam', 'Williams'

declare @Group table (GroupNumber int, PenaltyPercentage int)
insert @Group
          select 1, 20
union all select 2, 15
union all select 3, 10
union all select 4, 0

declare @Prize table (Position int, Points int)
insert @Prize
          select 1, 40000
union all select 2, 39000
union all select 3, 38000

declare @Result table (RaceDate datetime, EntrantID int, Position int)
insert @Result
          select '2006-03-01', 1, 1
union all select '2006-03-01', 2, 2
union all select '2006-03-08', 1, 2
union all select '2006-03-08', 2, 1

declare @GroupAssignment table (EntrantID int, GroupNumber int, FromDate datetime, ToDate datetime)
insert @GroupAssignment
          select 1, 1, '2006-03-01', NULL
union all select 2, 4, '2006-03-01', '2006-03-07'
union all select 2, 1, '2006-03-08', NULL

--the query
select e.Firstname, e.Surname, SUM(Points * (100.0 - PenaltyPercentage) / 100) as TotalPoints from
    @Entrant e
    left outer join @GroupAssignment a on e.EntrantID = a.EntrantID
    left outer join @Result r on e.EntrantID = r.EntrantID and r.RaceDate between a.FromDate and isnull(a.ToDate, '31 dec 9999')
    left outer join @Prize p on r.Position = p.Position
    left outer join @Group g on a.GroupNumber = g.GroupNumber
group by
    e.Firstname, e.Surname

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

rpcutts
Starting Member

10 Posts

Posted - 2006-03-24 : 19:09:18

sound, cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources