View: dbo.Customer_View
View definition
CREATE VIEW dbo.Customer_View
AS (
SELECT
customer_KEY = C.customer_KEY
,client_KEY = C.client_KEY
,customer_id = C.customer_id
,customer_name = C.customer_name
,is_prospect = C.is_prospect
,contact_KEY = C.contact_KEY
,ein_ssn = C.ein_ssn
,tax_exempt_number = C.tax_exempt_number
,date_inactive = C.date_inactive
,inactive_reason = C.inactive_reason
,ar__gl_account_KEY = C.ar__gl_account_KEY
,payment_term_KEY = C.payment_term_KEY
,finance_charge_definition_KEY = C.finance_charge_definition_KEY
,purchase_order_number = C.purchase_order_number
,shipping__ar_item_KEY = C.shipping__ar_item_KEY
,sales_tax__ar_item_KEY = C.sales_tax__ar_item_KEY
,customer_message = C.customer_message
,credit_limit_amount = C.credit_limit_amount
,enable_credit_limit_prompt = C.enable_credit_limit_prompt
,enable_credit_hold = C.enable_credit_hold
,credit_hold_explanation = C.credit_hold_explanation
,customer_notes = C.customer_notes
,customer_id_sortable = C.customer_id_sortable
,description = C.customer_name
-- added a new column to fetch the active status based on 'date_inactive'
,active_status_KEY = CASE WHEN C.date_inactive is not null
and C.date_inactive <= getdate()
THEN 2
ELSE 1
END
FROM
dbo.Customer C
)