SQL Multiple Condition with Group By

Transact-SQL
1

I am trying to create a ranking system with two conditions.

  1. Membership status, if membership status is not current then -50% penalty.
  2. 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

2

You misspelt membername in declaration of membershipstats table.
Nationality is named country in membershipstats table, but on your final query, you reference a field called flag - is this the same field?
Your table definition is incomplete - in which table is nationality? I'm guessing the tblmember, but in your insert statement, you reference it to be in tblentryresults.
In your insert statement to reference member to be from table tblentryresults, and then you insert it into membershipstats table in membername field - shouldn't it be the membername from table tblmember?
In your insert statement, the else statement in the case section can never be reached, so this is unnecessary code.

Assuming I'm right about membername should be taken from tblmember, and nationality is the same as flag (and should be taken from tblmember), this might work for you:

insert
  into @MemberShipStats(Country
                       ,MemberID
                       ,MemberName
                       ,EventYear
                       ,TotalPoints
                       )
select tm.Nationality
      ,ter.MemberID
      ,tm.MemberName
      ,year(tev.EventStartDate) as EventYear
      ,sum((ter.NumEntries-tev.EventPlace-1)
          *cast(tb.EventPoints as decimal(22,8))
          *case
              when tm.YearDuesPaid<year(getdate)
              then .5
              else 1
           end
          ) as TotalPoints
  from dbo.tblEntryResults as ter 
       inner join tbkEventType as tb 
               on tb.EventTypeID=ter.EventTypeID
       inner join tblMember as tm
               on tm.MemberID=ter.MemberID
       inner join tblEvents as tev
               on tev.EventID=ter.EventID
 group by tm.Nationality
         ,ter.MemberID
         ,tm.Member
         ,year(tev.EventStartDate)
;

Select part:

select Country as Flag
      ,MemberID
      ,MemberName
      ,sum(TotalPoints
          *case
              when EventYear>=year(getdate())
              then 1
              when EventYear=year(getdate())-1
              then .5
              when EventYear=year(getdate())-2
              then .25
              else 0
           end
          ) as TotalPoints
  from @MemberShipStats
 group by Country
         ,MemberID
         ,MemberName
;

You could also do the query in one go, without the temp table:

select tm.Nationality as Flag
      ,ter.MemberID
      ,tm.Member
      ,sum((ter.NumEntries-tev.EventPlace-1)
          *cast(tb.EventPoints as decimal(22,8))
          *case
              when tm.YearDuesPaid<year(getdate)
              then .5
              else 1
           end
          *case
              when year(tev.EventStartDate)>=year(getdate())
              then 1
              when year(tev.EventStartDate)=year(getdate())-1
              then .5
              when year(tev.EventStartDate)=year(getdate())-2
              then .25
              else 0
           end
          ) as TotalPoints
  from dbo.tblEntryResults as ter 
       inner join tbkEventType as tb 
               on tb.EventTypeID=ter.EventTypeID
       inner join tblMember as tm
               on tm.MemberID=ter.MemberID
       inner join tblEvents as tev
               on tev.EventID=ter.EventID
 group by tm.Nationality
         ,ter.MemberID
         ,tm.Member
;
3

WOW.. Thank you for your help!! I never thought about the 2nd method.