Program Classification Counts by Active Memberships

Use this Query in Query Builder to report on the count of Program Classifications/Program Information for Active Memberships.

This will show Membership Name, Program Classification, and Count of Program Classifications for that Membership.

Note the usage instructions in the opening comment of the SQL

/*This query will return the Count of each Program Classificaion/Program Information grouped by Membership type. Will only return data for Current Memberships.*/DECLARE @Membership_Types varchar(Max) = NULL --Leave NULL for all; to be more specific,provide comma separated Membership Type names like: 'Student (Import Only),Facutly/Staff,Alumni'/*Modify Above this line only*/DECLARE @MEMBERSHIP_TYPE_NAMES TABLE (Membership_Name varchar(max))IF @Membership_Types is NULL    INSERT INTO @MEMBERSHIP_TYPE_NAMES (Membership_Name)        Select p.NAME from t_product p            where p.PRODUCT_TYPE_CV = '00000000-0000-0000-0000-000000003066' IF @Membership_Types is NOT NULL    INSERT INTO @MEMBERSHIP_TYPE_NAMES (Membership_Name)        Select CAST (Item AS varchar(max)) FROM dbo.fn_split(@Membership_Types,',')select     cm.MembershipType as 'Membership Type'    ,isnull(m.PROGRAM_INFORMATION,'') AS 'Program Information'    ,count(isnull(m.PROGRAM_INFORMATION,'')) AS 'Count'from VQ_CURRENT_MEMBERSHIP cm join t_party_role pr    join t_member m        on m.id = pr.id    on pr.party_id = cm.partyidWHERE    cm.MembershipType in (select Membership_Name from @MEMBERSHIP_TYPE_NAMES)group by m.PROGRAM_INFORMATION,cm.MembershipTypeorder by cm.MembershipType,m.PROGRAM_INFORMATION

Example Results: