I am trying to create a ranking system with two conditions.
- Membership status, if membership status is not current then -50% penalty.
- Points conditions, current year, 100% points, last year 50% points, and 2 years ago 25% 3years+ is 0 pts.
This is what I have so far but I have an expression containing an aggregate or subquery error at the WHEN. Not sure what to do on this...
I can use your expertise to get this work or other efficient way to get this to work let me konw.
Table tblEvent
EventID - event ID generated by SQL
EventName
EventStartDate
tblEventType
EventTypeID indent int
EventType varchar(250) --- type of event. for example National, Area, Local etc
EventPoints dec(18,2) -- Event point, for example National 5pts, Local 1pts...
tblMember
MemberID int
YearDuesPaid - Membership Status, year membership is paid.
MemeberName
tblEntryResults
MemberID int
Finishes
NumEntries - Number of participent is calculated when the results is entered
DECLARE @MemberShipStats Table(
Country varchar(50),
MemebrID INT,
MemberName varchar(500),
EventYear int,
TotalPoints Decimal(22,8)
)
INSERT INTO @MemberShipStats(Country, MemebrID, MemberName, EventYear, TotalPoints)
SELECT
ter.Nationality,
ter.MemberID,
ter.Member,
YEAR(tev.EventStartDate) As 'EventYear',
CASE
WHEN tm.YearDuesPaid < YEAR(GETDATE()) THEN SUM(((NumEntries-([EventPlace]-1))*CAST(tb.EventPoints AS DECIMAL(22,8)))/2)
WHEN tm.YearDuesPaid = YEAR(GETDATE()) THEN SUM((NumEntries-([EventPlace]-1))*CAST(tb.EventPoints AS DECIMAL(22,8)))
WHEN tm.YearDuesPaid > YEAR(GETDATE()) THEN SUM((NumEntries-([EventPlace]-1))*CAST(tb.EventPoints AS DECIMAL(22,8)))
ELSE SUM((NumEntries-([EventPlace]-1))*CAST(tb.EventPoints AS DECIMAL(22,8)))
END
FROM [dbo].[tblEntryResults] ter
Join tbkEventType tb
On tb.EventTypeID = ter.EventTypeID
Join tblMember tm
On ter.MemberID = tm.MemberID
Join tblEvents tev
On ter.EventID = tev.EventID
group by ter.MemberID, ter.Member, tm.YearDuesPaid, ter.Nationality, tev.EventStartDate
SELECT
Flag,
MemberID,
MemberName,
CASE
WHEN evYear = (YEAR(GETDATE())-2) THEN SUM(TotalPoints * 0.25)
WHEN evYear = (YEAR(GETDATE())-1) THEN SUM(TotalPoints * 0.5)
WHEN evYear = YEAR(GETDATE()) THEN SUM(TotalPoints )
ELSE SUM(CAST(0 AS DECIMAL(22,8)))
END AS 'TotalPoint'
FROM @MemberShipStats
group by Flag, MemberID, MemberName
Order by 'TotalPoint' DESC