SQLTeam.com | Weblogs | Forums

How do I group individual events by year?

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

Can you please give us access to your sql server?

Accessing the sql server doesn't seem to be a very difficult task, as long as you get one of them, you can solve it all.

Hi,

Sorry for late response.

My DB is on my laptop at this time.

So, it is not accessible to the outside.

ok in that case please provide us sample data? using DDL & DML

create table simflex([Event Month] varchar(10),  --etc

insert into table simflex
select 1, 'May 2019' --etc