PRINT 'BEGIN INSTALL/UPDATE LV_CUSTOMER_A1A2';
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO

/***********************************************************************************************************************
Author:       Nick Reilingh, Fisher Center at Bard College
Description:  This view is kind of like VB_CUSTOMER, but it works for individuals too.

This lets you join a collection of individuals and households on customer_no,
and return back name1 fields populated for all constituents, and name2 fields
populated for the constituents that are a household with an A2 affiliate.

***********************************************************************************************************************/
CREATE OR ALTER VIEW [dbo].[LV_CUSTOMER_A1A2]
AS

SELECT
    c.customer_no,
    customer_no1 = name1.customer_no,
    prefix1 = p1.description,
    fname1 = name1.fname,
    mname1 = name1.mname,
    lname1 = name1.lname,
    suffix1 = s1.description,
    customer_no2 = name2.customer_no,
    prefix2 = p2.description,
    fname2 = name2.fname,
    mname2 = name2.mname,
    lname2 = name2.lname,
    suffix2 = s2.description
  FROM dbo.T_CUSTOMER c
    LEFT JOIN dbo.T_AFFILIATION a1
      ON c.customer_no = a1.group_customer_no
        AND a1.name_ind = -1
    LEFT JOIN dbo.T_AFFILIATION a2
      ON c.customer_no = a2.group_customer_no
        AND a2.name_ind = -2
    LEFT JOIN dbo.T_CUSTOMER name1
      ON name1.customer_no = COALESCE(a1.individual_customer_no, c.customer_no)
    LEFT JOIN dbo.T_CUSTOMER name2
      ON name2.customer_no = a2.individual_customer_no
        AND name2.inactive = 1
    LEFT JOIN dbo.TR_PREFIX p1
      ON name1.prefix = p1.id
    LEFT JOIN dbo.TR_SUFFIX s1
      ON name1.suffix = s1.id
    LEFT JOIN dbo.TR_PREFIX p2
      ON name2.prefix = p2.id
    LEFT JOIN dbo.TR_SUFFIX s2
      ON name2.suffix = s2.id;
GO

GRANT SELECT ON [dbo].[LV_CUSTOMER_A1A2] TO [ImpUsers], [tessitura_app];
GO
/* END INSTALL/UPDATE LV_CUSTOMER_A1A2 */