V_QUERY_FINANCIALSPONSORSHIPDATERANGE

/*
Generated by Blackbaud AppFx Platform
Date:  11/11/2014 4:12:06 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_FINANCIALSPONSORSHIPDATERANGE AS



    -- all sponsorship constituency ranges; there are two cases:

    --  - gift sponsorship ranges begin w/ a transaction indicating the gift financial sponsor, and end with

    --    a subsequent transaction changing the financial sponsor or ending the sponsorship

    --  - non-gift sponsorship ranges either begin w/ an add transaction w/ no gift financial sponsor,

    --    or w/ a transaction changing the gift financial sponsor to blank, and end with a subsequent

    --    transaction setting a gift financial sponsor or ending the sponsorship

    with GIFTHISTORY_CTE as (
        select SPONSORSHIP.ID,
             SPONSORSHIP.CONSTITUENTID CORRESPONDINGSPONSORID,
             isnull(FINSTART.GIFTFINANCIALSPONSORID,SPONSORSHIP.CONSTITUENTID) FINANCIALSPONSORID,
             case FINSTART.ACTIONCODE when 0 then SPONSORSHIP.STARTDATE else FINSTART.TRANSACTIONDATE end STARTDATE,
             case when FINEND.TRANSACTIONDATE is not null then FINEND.TRANSACTIONDATE when LASTTRANSACTION.ACTIONCODE in(2,3,5) then LASTSPONSORSHIP.ENDDATE end ENDDATE,
             case when FINSTART.GIFTFINANCIALSPONSORID is not null then 1 else 0 end ISGIFT,
             SPONSORSHIP.STATUSCODE
        from dbo.SPONSORSHIPTRANSACTION FINSTART
        inner join dbo.SPONSORSHIP on SPONSORSHIP.ID = FINSTART.TARGETSPONSORSHIPID
        left join dbo.SPONSORSHIPTRANSACTION FINEND on FINEND.SPONSORSHIPCOMMITMENTID = FINSTART.SPONSORSHIPCOMMITMENTID and FINEND.TRANSACTIONSEQUENCE = (select min(X.TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION X where X.SPONSORSHIPCOMMITMENTID = FINSTART.SPONSORSHIPCOMMITMENTID and X.TRANSACTIONSEQUENCE > FINSTART.TRANSACTIONSEQUENCE and X.ACTIONCODE = 9)
         inner join dbo.SPONSORSHIPTRANSACTION LASTTRANSACTION on LASTTRANSACTION.SPONSORSHIPCOMMITMENTID = FINSTART.SPONSORSHIPCOMMITMENTID and LASTTRANSACTION.TRANSACTIONSEQUENCE = (select max(TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION MAXSEQUENCE where MAXSEQUENCE.SPONSORSHIPCOMMITMENTID = FINSTART.SPONSORSHIPCOMMITMENTID and MAXSEQUENCE.ACTIONCODE <> 9)
         left outer join dbo.SPONSORSHIP LASTSPONSORSHIP on LASTSPONSORSHIP.SPONSORSHIPCOMMITMENTID = LASTTRANSACTION.SPONSORSHIPCOMMITMENTID and ((LASTTRANSACTION.ACTIONCODE = 5 and LASTSPONSORSHIP.ID in(LASTTRANSACTION.TARGETSPONSORSHIPID,LASTTRANSACTION.CONTEXTSPONSORSHIPID)) or (LASTTRANSACTION.ACTIONCODE <> 5 and LASTSPONSORSHIP.ID = isnull(LASTTRANSACTION.TARGETSPONSORSHIPID,LASTTRANSACTION.CONTEXTSPONSORSHIPID)))
              where FINSTART.ACTIONCODE in(0,5,9)
    )
    select ID, CORRESPONDINGSPONSORID CONSTITUENTID, case when ISGIFT = 1 then 1 else 0 end SPONSORTYPECODE, STARTDATE, ENDDATE, STATUSCODE
    from GIFTHISTORY_CTE
    union
    select ID, FINANCIALSPONSORID, 2, STARTDATE, ENDDATE, STATUSCODE
    from GIFTHISTORY_CTE
    where ISGIFT = 1;