USP_BBNC_GETEVENT

            CREATE procedure dbo.USP_BBNC_GETEVENT
              (
                  @BBNCID int
              )
              as
                  set nocount on;

                  select
                      [MAP].ID as [BBNCID],
                      [MAP].EVENTID,
                      EVENT.NAME,
                      EVENT.DESCRIPTION,
                      EVENT.ISACTIVE,
                      EVENT.STARTDATE,
                      EVENT.STARTTIME,
                      EVENT.ENDDATE,
                      EVENT.ENDTIME,
                      EVENT.CAPACITY,
                      ECC.DESCRIPTION AS CATEGORY,
                      (select count(*) from dbo.[REGISTRANT] where [REGISTRANT].[EVENTID] = [EVENT].[ID]) [REGISTRANTCOUNT],
                      (select count(REGISTRANT.ID) from dbo.REGISTRANT 
                          where REGISTRANT.EVENTID = EVENT.ID 
                          and (REGISTRANT.WILLNOTATTEND = 1 
                          or dbo.[UFN_REGISTRANT_ISCANCELLED]([REGISTRANT].[ID]) = 1)) as [WILLNOTATTEND],
                      EVENT.BASECURRENCYID,
            EVENTLOCATION.NAME as LOCATIONNAME,
            EVENTLOCATION.ADDRESSBLOCK as LOCATIONADDRESS,
            EVENTLOCATION.CITY as LOCATIONCITY,
            STATE.DESCRIPTION as LOCATIONSTATE,
            EVENTLOCATION.POSTCODE as LOCATIONPOSTCODE,
            EVENTLOCATION.PHONENUMBER as LOCATIONPHONE,
            CONSTITUENT.NAME as LOCATIONCONTACT
                  from
                      dbo.BBNCEVENTIDMAP as [MAP]
                      inner join dbo.EVENT on EVENT.ID = [MAP].EVENTID
            left outer join dbo.EVENTLOCATION on EVENTLOCATION.ID = EVENT.EVENTLOCATIONID
            left outer join dbo.CONSTITUENT on CONSTITUENT.ID = EVENT.EVENTLOCATIONCONTACTID
                      left outer join dbo.EVENTCATEGORYCODE ECC on ECC.ID = EVENT.EVENTCATEGORYCODEID
            left outer join dbo.STATE on STATE.ID = EVENTLOCATION.STATEID
                  where
                      [MAP].ID = @BBNCID;