SQL

SELECT DISTINCT 
PATIENT_IEN AS ACTIVE_PT_PATIENT_IEN, 
PATIENT_DOB AS ACTIVE_PT_PATIENT_DOB, 
PATIENT_SEX AS ACTIVE_PT_PATIENT_SEX, 
MAILING_ADDRESSZIP AS ZIP_CODE,
P.DIAGNOSIS_CODE_NUMBER AS DIAGNOSIS_CODE_NUMBER, 
P.STATUS,
PATIENT_SSN AS ACTIVE_PT_PATIENT_SSN, 
COMMUNITY,
TRIBE_OF_MEMBERSHIP_NAME
FROM  
(  SELECT 
  V.VISITADMIT_DATETIME,  
  V.IEN,  
  V.PATIENT_NAME AS PATIENT_IEN,  
  V.PATIENT_NAME -> CURRENT_COMMUNITY AS COMMUNITY,  
  V.PATIENT_NAME -> CURRENT_RESIDENCE_PTR -> SERVICE_UNIT_NAME AS COMMUNITY_SU,  
  V.PATIENT_NAME -> TRIBE_OF_MEMBERSHIP_NAME,
  V.PATIENT_NAME -> TRIBE_OF_MEMBERSHIP,  
  V.PATIENT_NAME -> DOB AS PATIENT_DOB,
  V.PATIENT_NAME -> SEX AS PATIENT_SEX, 
  V.PATIENT_NAME -> SSN AS PATIENT_SSN,
  V.PATIENT_NAME -> MAILING_ADDRESSZIP
 
  FROM BMW.VISIT V
  INNER JOIN BMW.VA_PATIENT VAP ON V.PATIENT_NAME = VAP.IEN
  WHERE
   AND V.VISITADMIT_DATETIME >=3221001
  AND V.DEPENDENT_ENTRY_COUNT IS NOT NULL
  AND V.DELETE_FLAG IS NULL
  AND V.CLINIC_NAME IS NOT NULL
  AND V.CLINIC -> CODE NOT IN (11, 68, 51)
  AND V.SERVICE_CATEGORY NOT IN ('DAILY HOSP DATA', 'ANCILLARY PACKAGE DAILY', 'EVENT (HISTORICAL)', 'CHART REVIEW')
  AND V.PATIENT_NAME -> DATE_OF_DEATH IS NULL
  AND (
    (TO_NUMBER(V.PATIENT_NAME -> TRIBE_OF_MEMBERSHIP) > 1
     AND TO_NUMBER(V.PATIENT_NAME -> TRIBE_OF_MEMBERSHIP) < 968)
    OR (TO_NUMBER(V.PATIENT_NAME -> TRIBE_OF_MEMBERSHIP) IN (997, 999))
  )
  AND VAP.PATIENT_MERGED_TO IS NULL
) AS DISTINCTPatients
LEFT JOIN BMW.Problem AS P ON DISTINCTPatients.PATIENT_IEN = P.PATIENT_NAME
WHERE  P.STATUS NOT IN ('DELETED')









==============

SELECT DISTINCT 
    DISTINCTPatients.PATIENT_IEN AS ACTIVE_PT_PATIENT_IEN, 
    DISTINCTPatients.PATIENT_DOB AS ACTIVE_PT_PATIENT_DOB, 
    DISTINCTPatients.PATIENT_SEX AS ACTIVE_PT_PATIENT_SEX, 
    DISTINCTPatients.MAILING_ADDRESSZIP AS ZIP_CODE,
    P.DIAGNOSIS_CODE_NUMBER AS DIAGNOSIS_CODE_NUMBER, 
    P.STATUS,
    DISTINCTPatients.PATIENT_SSN AS ACTIVE_PT_PATIENT_SSN, 
    DISTINCTPatients.COMMUNITY,
    DISTINCTPatients.TRIBE_OF_MEMBERSHIP_NAME
FROM  
(
    SELECT 
        V.VISITADMIT_DATETIME,  
        V.IEN,  
        V.PATIENT_NAME AS PATIENT_IEN,  
        V.CURRENT_COMMUNITY AS COMMUNITY,  
        V.CURRENT_RESIDENCE_PTR -> SERVICE_UNIT_NAME AS COMMUNITY_SU,  
        V.TRIBE_OF_MEMBERSHIP_NAME,
        V.TRIBE_OF_MEMBERSHIP,  
        V.DOB AS PATIENT_DOB,
        V.SEX AS PATIENT_SEX, 
        V.SSN AS PATIENT_SSN,
        V.MAILING_ADDRESSZIP
    FROM BMW.VISIT V
    INNER JOIN BMW.VA_PATIENT VAP ON V.PATIENT_NAME = VAP.IEN
    WHERE
        V.VISITADMIT_DATETIME >= 3221001
        AND V.DEPENDENT_ENTRY_COUNT IS NOT NULL
        AND V.DELETE_FLAG IS NULL
        AND V.CLINIC_NAME IS NOT NULL
        AND V.CLINIC -> CODE NOT IN (11, 68, 51)
        AND V.SERVICE_CATEGORY NOT IN ('DAILY HOSP DATA', 'ANCILLARY PACKAGE DAILY', 'EVENT (HISTORICAL)', 'CHART REVIEW')
        AND V.PATIENT_NAME -> DATE_OF_DEATH IS NULL
        AND (
            (TO_NUMBER(V.TRIBE_OF_MEMBERSHIP) > 1 AND TO_NUMBER(V.TRIBE_OF_MEMBERSHIP) < 968)
            OR (TO_NUMBER(V.TRIBE_OF_MEMBERSHIP) IN (997, 999))
        )
        AND VAP.PATIENT_MERGED_TO IS NULL
) AS DISTINCTPatients
LEFT JOIN BMW.Problem AS P ON DISTINCTPatients.PATIENT_IEN = P.PATIENT_NAME
WHERE P.STATUS NOT IN ('DELETED')