Greetings again experts,
Now that I am back on track, I have a set of challenging issues.
My title is a bit tricky.
Our members make various contributions each year.
Those contributions are broken down by Event Type.
There are 6 such event types for now.
Year is represented by eventYear and are broken down by Month.
What I am struggling to accomplish is grouping those events by Year, by memberName and by EventType.
For instance, if a member by the name of Joe Bloe makes contributions for Two event Types for the year 2019 and both events are for the month of May, we would like the layout to look similar to this:
Event Month Event Type Member Name Amount Total
May 2019 Annual Dues Joe Bloe $120.00 Sum(Amount)
Fight Cancer Month Joe Bloe $250.00
--------------------------------------------------------------------------------------------------------------------------
Event Month Event Type Member Name Amount Total
May 2019 Picnic Event Jane Doe $100.00 Sum(Amount)
Test of DOIT Jane Doe $450.00
--------------------------------------------------------------------------------------------------------------------------
So, assuming that Joe made all his contributions for the month of May, everything is grouped together by eventDate and all Joe's contributions for that month are grouped together and total for the month added up.
Same with Jane Doe.
At the end of the year, each member's total is added and summed up by column and by row.
Can anyone please assist?
Thank you in advance.
ALTER PROCEDURE [dbo].[SummaryRPT]
(
@fromDate DateTime,
@toDate DateTime
)
AS
SELECT
m.memberName,
m.date_registered,
t.envelopeNumber,
t.registrationFee,
t.Amount,
t.eventyear,
t.AmountPaid,
t.Balance,
p.EventType,
(SELECT SUM(t.Amount)
FROM Transactions t2
WHERE t2.transactionID = t.transactionID) AS MemberTotal
FROM
dbo.Transactions AS t
INNER JOIN dbo.Members AS m
ON t .MemberID = m.MemberID
INNER JOIN dbo.PaymentTypes AS p
ON t .TypeID = p.PaymentTypeID
WHERE t.eventyear between @fromDate and @toDate
GROUP BY m.memberName,
m.date_registered,
t.transactionID,t.envelopeNumber,
t.registrationFee,
t.Amount,
t.eventyear,
t.AmountPaid,
t.Balance,
p.Description
order by t.eventYear ASC