Hi Bhuvnesh
select
reg_id = dr.reg_id,
invitee_id = di.invitee_id,
contact_type = isnull(ct.cont_type_name, ''),
item_id = I.item_id,
item_type = i.item_type,
item_category = I.item_category,
item_name = I.item_name,
item_code = I.item_code,
fee_name = f.fee_name,
start_date = I.session_start_date,
end_date = I.session_end_date,
reg_date = ae.created_date,
item_quantity = ae.prod_qty,
item_price = bd.bskt_dtl_price_tier_amt,
eligible_credit = I.credit_value,
participant_flag = case
when pe.acct_id is null then 0
else 1 end,
cv_acct_id = ae.acct_id,
cv_entity_stub = ae.entity_stub,
cv_entity_type_id = ae.entity_type_id,
cv_prod_stub = ae.prod_stub
from [dbo].[ATTENDEE_ENTITY] ae with (nolock)
join [dbo].[DMP_EVENT_ITEM] I with (nolock)
on I.cv_acct_id = ae.acct_id and I.cv_evt_stub = ae.evt_stub
AND ae.prod_stub = I.cv_prod_stub
left join [dbo].[CONTACT_TYPE] ct with (nolock)
on ct.acct_id = ae.acct_id and ct.cont_type_stub = ae.cont_type_stub
left join [dbo].[BASKET_DETAIL] bd with (nolock)
on bd.acct_id = ae.acct_id and bd.bskt_dtl_stub = ae.bskt_dtl_stub
left join [dbo].[FEE] f with (nolock)
on f.acct_id = bd.acct_id and f.fee_stub = bd.bskt_dtl_fee_stub
join [dbo].[DMP_EVENT_INVITEE] di with (nolock)
on di.cv_acct_id = ae.acct_id
--- Changes
and Case When ISNULL(cv_cont_stub,0) != 0 THEN di.cv_cont_stub ELSE di.cv_free_stub END = ae.entity_stub
--and di.cv_free_stub = '00000000-0000-0000-0000-000000000000'
--- /Changes
and di.cv_evt_stub = i.cv_evt_stub
join [dbo].[DMP_EVENT_REGISTRATION] dr (nolock)
on dr.invitee_id = di.invitee_id and dr.event_id = di.event_id
left join [dbo].[PARTICIPANT_ENTITY] pe (nolock)
on pe.acct_id = ae.acct_id
and pe.evt_stub = ae.evt_stub and pe.prod_stub = i.cv_prod_stub and pe.entity_stub = ae.entity_stub
where
--- Change
ae.entity_type_id = Case When ISNULL(cv_cont_stub,0) != 0 THEN 3 ELSE 10 END
---/Change
AND ae.acct_id = 2000022
Please let me know if this helps....:)