Hey All,
I've read a couple of really old forum posts about a custom view that includes contributions and credited contributions to use for custom list criteria and output set elements. Does anyone have updated SQL for that view they could share with me?
I'd like to create new total giving elements that include soft credits and exclude a couple of our campaigns and I'd love to have something to build off of rather than starting from scratch!
Thanks,
Dorothy
Here is the SQL for a Flex Header Element I created for 13 month giving + soft credits. You might be able to re-purpose it for an Output Set Element.
SELECT (SELECT CASE WHEN SUM(cr.credit_amt) IS NULL THEN '0' ELSE SUM(cr.credit_amt) ENDFROM T_CREDITEE AS crJOIN VS_CONTRIBUTION_WITH_INITIATOR AS c1 ON cr.ref_no = c1.ref_no AND c1.cont_amt > 0WHERE cr.creditee_no = @customer_no AND (cr.credit_dt between dateadd(mm, -13 , getdate()) and getdate()))+(SELECT CASE WHEN SUM(c.cont_amt) IS NULL THEN '0' ELSE SUM(c.cont_amt) END FROM VS_CONTRIBUTION_WITH_INITIATOR AS c WITH (NOLOCK) WHERE c.customer_no = @customer_no and (c.cont_dt between dateadd(mm, -13 , getdate()) and getdate()))
Thanks Sara, this is a good jumping off point!