UFN_QUERY_MARKETINGEFFORTDETAILS

            create function BBDW.UFN_QUERY_MARKETINGEFFORTDETAILS(@EFFORTID uniqueidentifier)
            returns table
            as
            return
                select
                MS.MARKETINGSEGMENTDIMID,
                MS.SEGMENTNAME,
                MS.SEGMENTDESCRIPTION,
                MSC.SOURCECODE SEGMENTCODE,
                MS.PACKAGECODE,
                MS.PACKAGENAME,
                MS.PACKAGEDESCRIPTION,
                MS.LETTERCODE,
                MS.LETTERCODEDESCRIPTION,
                MS.SEGMENTATIONSEGMENTCOSTPERDOLLARRAISED,
                MS.SEGMENTATIONSEGMENTCOSTTOACQUIRE ,
                MS.PACKAGECHANNEL,
                MS.ASKLADDERNAME,
                MS.SAMPLESIZE,
                MS.SEGMENTATIONSEGMENTQUANTITY,
                (select COUNT(distinct CONSTITUENTDIMID)
                from BBDW.FACT_FINANCIALTRANSACTION FF
                where FF.MARKETINGSEGMENTDIMID=MS.MARKETINGSEGMENTDIMID) RESPONDERS,
                MS.SEGMENTATIONSEGMENTRESPONSES,
                MS.SEGMENTATIONSEGMENTRESPONSERATE,
                MS.SEGMENTATIONSEGMENTAVERAGEGIFTAMOUNT,
                MS.SEGMENTATIONPACKAGEUNITCOST,
                MS.RESPONSECOSTPERPIECE,
                MS.VARCOSTPERPIECE,
                MS.FIXEDCOSTPERPIECE,
                MS.SEGMENTATIONSEGMENTTOTALCOST,
                MS.SEGMENTATIONSEGMENTCOSTPERTHOUSAND,
                MS.SEGMENTATIONSEGMENTTOTALGIFTAMOUNT,
                MS.SEGMENTATIONSEGMENTGROSSPERTHOUSAND,
                MS.SEGMENTATIONSEGMENTNETPERTHOUSAND ,
                MS.SEGMENTATIONSEGMENTROIAMOUNT,
                MS.SEGMENTATIONSEGMENTROIPERCENT,
                C.CURRENCYSYSTEMID AS BASECURRENCYID
                from BBDW.DIM_MARKETINGSEGMENT    MS
                left outer join BBDW.DIM_CURRENCY C on C.CURRENCYDIMID = MS.BASECURRENCYDIMID
                left outer join BBDW.DIM_MARKETINGSOURCECODE MSC on MSC.MARKETINGSEGMENTDIMID = MS.MARKETINGSEGMENTDIMID
                where MS.SEGMENTATIONSEGMENTSYSTEMID <> cast(cast(0 as binary) as uniqueidentifier)
                and MS.SEGMENTATIONSYSTEMID = @EFFORTID