Event Discount Codes

On the same theme of rapidly reporting data from an Event, here's a sproc to report the Discount Codes that have been used in the current event. You can deploy it with a Report Grid From Query on the Event Details page (see Event Costs And Fees for how to set up a RGFQ on the Events page, just make sure you set Public Site = True since you dont need all the extras for this grid) with a single parameter of "profile=;".

CREATE PROCEDURE dbo.cust_sp_rgfq_evnt_applied_discount_codes
@profile VARCHAR(20)
AS
SELECT DISTINCT
cp.person_id
,cp.last_name+', '+cp.first_name AS Person_Name
,edc.code_value AS [Discount Code Applied]
,(CASE WHEN ereg.date_created<ereg.date_modified THEN ereg.date_modified ELSE ereg.date_created END) AS [Date Last Modified]
FROM
evnt_registrant AS er
JOIN evnt_discount_code AS edc ON edc.discount_code_id=er.discount_code_applied
JOIN core_person AS cp ON cp.person_id=er.person_id
JOIN evnt_registration AS ereg ON ereg.registration_id=er.registration_id
WHERE er.profile_id=@profile
ORDER BY [Discount Code Applied],Person_Name