List/Output criteria request

Steve,

I set this view to be used in LIST MANAGER, but it could be viewed in the same way for OUPUT SET MANAGER. First create the view and then add an entry in T_KEYWORD so that it is added to the OUPUT SET MANAGER.

ALTER view [dbo].[lv_creditee]
as
/*
View created to give list builder the ability to pull on credited
contribution information. This view has an entry in t_keyword
*/

select
    ref_no,
    creditee_no as customer_no,
    credit_dt,
    credit_amt,
    xref_no,
    creditee_type
from t_creditee

Steve,

Quick edit to what i said above. You need to add a row in TR_QUERY_ELEMENT as opposed to T_KEYWORD.  You will put the view name where it says, data from.

Christian

Hi Steve, I've messed around with a few views to pull creditees and after a little modification this has turned out to the best for my use.

This view gives you donations and the credited donations for a constituent, it will not record the donation in the creditors account.

You might want to add additional columns to pull at some point.

USE [impresario]
GO
/****** Object:  View [dbo].[LVS_TOTAL_GIFTS_W_CREDITEE]    Script Date: 04/30/2009 12:24:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[LVS_TOTAL_GIFTS_W_CREDITEE]
AS

--modified 031009rmr Added cont_dt
--modified 032609rmr added logic to remove double counting of gift. now only creditees get credit for
--credited gifts.


SELECT customer_no,
    cont_dt,
    fyear,
    campaign_no,
    SUM(cont_amt) AS total_amt
FROM (SELECT c.customer_no,
        c.cont_dt,
        camp.fyear,
        c.campaign_no,
        SUM(c.cont_amt) AS cont_amt
          FROM dbo.T_CONTRIBUTION AS c WITH (Nolock)
        INNER JOIN dbo.VS_CAMPAIGN AS camp WITH (Nolock) ON c.campaign_no = camp.campaign_no
    WHERE ref_no not in (select distinct ref_no from t_creditee)
    GROUP BY c.customer_no,
        c.cont_dt,
        camp.fyear,
        c.campaign_no
UNION ALL
    SELECT cr.creditee_no,
        c2.cont_dt,
        camp2.fyear,
        c2.campaign_no,
        SUM(cr.credit_amt) AS cont_amt
    FROM dbo.T_CREDITEE AS cr WITH (Nolock)
        INNER JOIN dbo.T_CONTRIBUTION AS c2 WITH (Nolock) ON cr.ref_no = c2.ref_no
        INNER JOIN dbo.VS_CAMPAIGN AS camp2 WITH (nolock) ON c2.campaign_no = camp2.campaign_no
        GROUP BY cr.creditee_no,
            c2.cont_dt,
            camp2.fyear,
            c2.campaign_no) AS zz
GROUP BY customer_no,
        cont_dt,
        fyear,
        campaign_no