PRINT 'BEGIN INSTALL/UPDATE LV_CUSTOMER_SUBSTITUTE_HOUSEHOLD';
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO

/***********************************************************************************************************************
Author:       Nick Reilingh, Fisher Center at Bard College
Description:  Join on customer_no to replace with household ID if one exists.

Renaming this to match the convention of V_CUSTOMER_ views.
***********************************************************************************************************************/
CREATE OR ALTER VIEW [dbo].[LV_CUSTOMER_SUBSTITUTE_HOUSEHOLD]
AS

SELECT
    c.customer_no,
    expanded_customer_no = COALESCE(a.group_customer_no, c.customer_no)
  FROM dbo.T_CUSTOMER c
    LEFT JOIN dbo.T_AFFILIATION a
      ON c.customer_no = a.individual_customer_no
        AND a.primary_ind = 'Y'
        AND a.inactive = 'N';

GO

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