USP_DATALIST_SPONSORSHIPMERGEDATA

CREATE procedure dbo.USP_DATALIST_SPONSORSHIPMERGEDATA
(
  @EVENTID uniqueidentifier
  ,@CONSTITUENTID uniqueidentifier
)
as
set nocount on;

select '<table>' 
       + '<tr><td>Name </td><td>' + EST.NAME + '</td></tr>'
         + '<tr><td>Description</td><td>' + EST.DESCRIPTION + '</td></tr>'
         + '<tr><td>Benefits</td><td>' + dbo.UDA_BUILDLIST(B.NAME) + '</td></tr>'
         + '<tr><td>Amount</td><td>$'+ convert(varchar(20),EST.FEESAMOUNT) + '</td></tr>'
       + '<tr><td>Number available</td><td>' + (CASE WHEN EST.NUMBERAVAILABLE > 0 THEN CAST((EST.NUMBERAVAILABLE - (select count(*) from dbo.EVENTSPONSOR(nolock) where EVENTSPONSORSHIPTYPEID = EST.ID)) AS nvarchar(20)) ELSE 'Unlimited' END) + '</td></tr>'
       + '<tr><td>Active</td><td>' + (case EST.ISACTIVE when 1 then 'Yes' else 'No' end) + '</td></tr>'
       + '<tr><td>Early discount date</td><td>' + CASE WHEN EST.EARLYREGISTRATIONDATE is null then '' else CONVERT(varchar(20),EST.EARLYREGISTRATIONDATE,101) end + '</td></tr>'
       + '<tr><td>Early discount</td><td>$'+ convert(varchar(20),EST.EARLYREGISTRATIONDISCOUNT) + '</td></tr>'
       + '<tr><td>Allow to waive benefits</td><td>' + (case EST.ALLOWSPONSORWAIVEBENEFITS when 1 then 'Yes' else 'No' End) + '</td></tr>'
       + '<tr><td>Late fee date</td><td>' + CASE WHEN EST.LATEFEEDATE is null then '' else CONVERT(varchar(20),EST.LATEFEEDATE,101) end + '</td></tr>'
       + '<tr><td>Late fee</td><td>$'+ convert(varchar(20),EST.LATEFEE)+ '</td></tr>'
         + '<tr><td>Display number available</td><td>' + ( case EST.DISPLAYSPOTSAVAILABLE when 1 then 'Yes' else 'No' End)+ '</td></tr>'
       + '<tr><td>Tax deductible amount</td><td>$'+ convert(varchar(20),EST.TAXDEDUCTIBLEAMOUNT)  + '</td></tr>'
       + '</table>' as SPONSORSHIPOPTIONDETAIL
  , EST.NAME as SPONSORSHIPOPTIONTYPE
  , EST.[DESCRIPTION] as SPONSORSHIPOPTIONDESCRIPTION
  , ES.FEESAMOUNT as SPONSORSHIPOPTIONFEE
  , EVENTSPONSORSHIPURL
  , SPONSORSHIPMESSAGE
  , ES.LOOKUPID as SPONSORSHIPID
  , (CASE WHEN EST.NUMBERAVAILABLE>0 THEN convert(varchar(10),EST.NUMBERAVAILABLE) ELSE 'Unlimited' END) as SPONSORSHIPMAXQUANTITY
from dbo.EVENTSPONSOR ES (nolock)
join dbo.EVENTSPONSORSHIPTYPE EST (nolock) on EST.ID=ES.EVENTSPONSORSHIPTYPEID and ES.EVENTID=EST.EVENTID 
left join dbo.EVENTSPONSORSHIPTYPEBENEFIT ESTB (nolock) on EST.ID = ESTB.EVENTSPONSORSHIPTYPEID 
left join dbo.BENEFIT B (nolock) on B.ID = ESTB.BENEFITID 
where ES.EVENTID = @EVENTID 
        and ES.CONSTITUENTID = dbo.UFN_EVENTSPONSOR_GETSPONSORCONSTITUENTID_BYCONSTITUENTID(@CONSTITUENTID, @EVENTID)
group by EST.ID, EST.NAME, EST.DESCRIPTION, EST.FEESAMOUNT, EST.NUMBERAVAILABLE, EST.ISACTIVE, EST.EARLYREGISTRATIONDISCOUNT,EST.LATEFEE
      ,EST.LATEFEEDATE,EST.ALLOWSPONSORWAIVEBENEFITS, EST.WAITLISTMESSAGE,EST.DISPLAYSPOTSAVAILABLE,EST.EARLYREGISTRATIONDATE,
      EST.SPONSORMUSTPAYBALANCE,EST.SPONSORPAYSBALANCEMESSAGE,EST.TAXDEDUCTIBLEAMOUNT,ES.FEESAMOUNT,ES.EVENTSPONSORSHIPURL,ES.SPONSORSHIPMESSAGE, ES.LOOKUPID