Notes

copy cdp_actln_dwh.TBL_VALCHLOR_Shipment_Avella
from 's3://itx-acm-cdp-actln-datafile/rohit-testing/VW_VALCHLOR_Shipment_Avella.csv'
--credentials 'aws_access_key_id=AKIAIJEQZXUMPSQONADQ;aws_secret_access_key=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'CSV;
iam_role 'arn:aws:iam::461126651296:role/RedshiftSpectrumRole' FORMAT AS CSV
DELIMITER AS ','
DATEFORMAT 'YYYY-MM-DD'
NULL 'NULL'
region 'us-east-1'
IGNOREHEADER 1;


create view cdp_actln_dwh.VW_VALCHLOR_Shipment_Avella as select * from cdp_actln_dwh.TBL_VALCHLOR_Shipment_Avella
with no schema binding;
CREATE TABLE cdp_actln_dwh.TBL_VALCHLOR_Shipment_Avella(
CheckSumKey INT
,REPORT_PERIOD_START_DATE DATE
,REPORT_PERIOD_END_DATE DATE
,PATIENT_ID VARCHAR(50)
,GENDER VARCHAR(50)
,YEAR_OF_BIRTH VARCHAR(50)
,PHYSICIAN_FIRST_NAME VARCHAR(50)
,PHYSICIAN_MIDDLE_INITIAL VARCHAR(50)
,PHYSICIAN_LAST_NAME VARCHAR(50)
,PHYSICIAN_ADDRESS_1 VARCHAR(100)
,PHYSICIAN_ADDRESS_2 VARCHAR(100)
,PHYSICIAN_CITY VARCHAR(50)
,PHYSICIAN_STATE VARCHAR(50)
,PHYSICIAN_ZIP VARCHAR(50)
,DEA_NUMBER VARCHAR(50)
,NPI_NUMBER VARCHAR(50)
,PRACTICE_NAME VARCHAR(50)
,PRIMARY_PAYER_NAME VARCHAR(50)
,INSURANCE_TYPE VARCHAR(50)
,PHARMACY_NAME VARCHAR(50)
,PHARMACY_DEA VARCHAR(50)
,SHIP_DATE DATE
,PY_Shipment_Flag VARCHAR(1) NOT NULL
,NDC_NUMBER VARCHAR(50)
,DRUG_NAME VARCHAR(50)
,QUANTITY_SHIPPED FLOAT
,DAYS_SUPPLY FLOAT
,NEW_OR_REFILL VARCHAR(50)
,PRESCRIPTION_NUMBER VARCHAR(50)
,DEDUCTIBLE FLOAT
,PRIMARY_COPAY_AMOUNT FLOAT
,COPAY_TYPE VARCHAR(2)
,CO_INSURANCE_PERCENTAGE VARCHAR(20)
,PRIMARY_ICD9_CODE VARCHAR(50)
,PA_REQUIRED VARCHAR(50)
,PRODUCT_NAME VARCHAR(50)
,BIN_NUMBER VARCHAR(50)
,PCN_NUMBER VARCHAR(50)
,SHIPMENT_ID VARCHAR(50)
,REFILLS_REMAINING FLOAT
,REFILLS_AUTHORIZED FLOAT
,PRESCRIBER_ID VARCHAR(50)
,TOKEN VARCHAR(50)
,LOAD_DATE DATE
,LOAD_ID BIGINT
,SP_NAME VARCHAR(50)
,ID INT NOT NULL
,ACTELION_ID INT
,Prism_City VARCHAR(50)
,Prism_State VARCHAR(50)
,Prism_Address VARCHAR(100)
,FLAG_PDRP VARCHAR(1)
,ASPIRE_SALES_RESTRICTED VARCHAR(1)
,Prism_FirstName VARCHAR(50)
,Prism_LastName VARCHAR(50)
,QTD_Shipment_Flag VARCHAR(1)
,Account NVARCHAR(255)
,HCO_Veeva_ID NVARCHAR(18)
,HCO_Tier NVARCHAR(255)
,HCP_Tier NVARCHAR(255)
,Payer_Type VARCHAR(10) NOT NULL
,WeekEnd DATE
,Territory_ID CHAR(25)
,Territory VARCHAR(50)
,Region_ID CHAR(25) NOT NULL
,Region VARCHAR(50) NOT NULL
,Shipment_WeekID BIGINT
,Exposure_Flag VARCHAR(1)
,Exposure_Date DATE
,Exposure_WeekEnd DATE
,Exposure_WeekID BIGINT
,LTD_Exposure_Flag VARCHAR(1)
,Exp_YTD_Flag VARCHAR(1)
,WeekID BIGINT
,CurrPrevWk VARCHAR(2)
,Rolling_4_Weeks VARCHAR(3)
,Rolling_12_Weeks VARCHAR(1)
,YTD_Flag VARCHAR(1)
,CurrPrev_Months VARCHAR(2) NOT NULL
,Month_Year VARCHAR(7)
,PDRP_Account VARCHAR(24)
,PDRP_Physician VARCHAR(26)
);