sql.txt 16 KB
edit raw blame history

这个就是每个省份 每年的建档孕妇数

SELECT
  ODS_D_PROVINCE.PROVINCE_ID as province_id,
      ODS_D_PROVINCE.PROVINCE as province_name,
      ODS_D_DIM_DATE.YEAR_1 as year,
  COUNT(ODS_F_GRAVIDA_RECORD.RECORD_ID) as val
FROM
  ODS_D_PROVINCE,
  ODS_D_DIM_DATE,
  ODS_F_GRAVIDA_RECORD,
  ODS_D_HOSPITAL,
  ODS_D_CITY
WHERE
  ( ODS_F_GRAVIDA_RECORD.HOSPITAL_NO=ODS_D_HOSPITAL.HOSPITAL_NO  )
  AND  ( ODS_D_CITY.PROVINCE_ID=ODS_D_PROVINCE.PROVINCE_ID  )
  AND  ( ODS_F_GRAVIDA_RECORD.INSERT_DATE=ODS_D_DIM_DATE.DATE_1  )
  AND  ( ODS_D_HOSPITAL.CITY_ID=ODS_D_CITY.CITY_ID(+)  )
  AND ODS_D_DIM_DATE.YEAR_1=2016
GROUP BY
  ODS_D_PROVINCE.PROVINCE_ID,
  ODS_D_PROVINCE.PROVINCE,
  ODS_D_DIM_DATE.YEAR_1

  ---------------------------------------------------
  这个就是每个省份 每年的高危孕妇数

  SELECT
    ODS_D_PROVINCE.PROVINCE_ID as province_id,
    ODS_D_PROVINCE.PROVINCE as province_name,
    ODS_D_DIM_DATE.YEAR_1 as year,
    COUNT(ODS_F_GRAVIDA_RECORD.RECORD_ID) as val
  FROM
    ODS_D_PROVINCE,
    ODS_D_DIM_DATE,
    ODS_F_GRAVIDA_RECORD,
    ODS_F_EXAMINE_HISTORY,
    ODS_D_HOSPITAL,
    ODS_D_CITY
  WHERE
    ( ODS_F_GRAVIDA_RECORD.RECORD_ID=ODS_F_EXAMINE_HISTORY.RECORD_ID(+)  )
    AND  ( ODS_F_GRAVIDA_RECORD.HOSPITAL_NO=ODS_D_HOSPITAL.HOSPITAL_NO  )
    AND  ( ODS_D_CITY.PROVINCE_ID=ODS_D_PROVINCE.PROVINCE_ID  )
    AND  ( ODS_F_GRAVIDA_RECORD.INSERT_DATE=ODS_D_DIM_DATE.DATE_1  )
    AND  ( ODS_D_HOSPITAL.CITY_ID=ODS_D_CITY.CITY_ID(+)  )
    AND
    (
     ODS_F_EXAMINE_HISTORY.EXAMINE_HISTORY_NUM  =  1
     AND
     ODS_F_EXAMINE_HISTORY.HIGH_RISK_FACTOR  NOT IN  ( '健康','健康,无'  )
    )
    AND ODS_D_DIM_DATE.YEAR_1=2016
  GROUP BY
    ODS_D_PROVINCE.PROVINCE_ID,
    ODS_D_PROVINCE.PROVINCE,
    ODS_D_DIM_DATE.YEAR_1

------------------------------------------------------------------------
根据省ID，年份查询和地市（1-12月）的建档孕妇数


------------------------------------------------------------------------
根据省ID，年份查询和地市的高危孕妇数（高危颜色、高危值对应的数量）


------------------------------------------------------
1. 查询每个省当前的高危总人数
SELECT D.PROVINCE_ID as province_id,
       D.PROVINCE as province_name,
       COUNT(A.RECORD_ID) AS val
FROM ODS_F_GRAVIDA_RECORD A,
     ODS_F_EXAMINE_HISTORY B,
     ODS_D_HOSPITAL C,
     ODS_D_AREA_COUNTY D,
     ODS_D_DIM_DATE E
WHERE A.RECORD_ID=B.RECORD_ID
  AND A.HOSPITAL_NO=C.HOSPITAL_NO
  AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID
  AND B.INSERT_DATE=E.DATE_1
  AND A.NOW_WEEKS<42
  AND B.HIGH_RISK_FACTOR<>'健康'
  AND B.HIGH_RISK_FACTOR IS NOT NULL
  AND B.EXAMINE_HISTORY_NUM=1
  AND B.INSERT_DATE<=TRUNC(SYSDATE)
GROUP BY D.PROVINCE_ID,
         D.PROVINCE


------------------------------------------------------
1.1. 查询每个省内各地区当前的高危总人数
SELECT D.PROVINCE_ID as province_id,
       D.PROVINCE as province_name,
       D.CITY_ID as city_id,
       D.CITY as city_name,
       COUNT(A.RECORD_ID) AS val
FROM ODS_F_GRAVIDA_RECORD A,
     ODS_F_EXAMINE_HISTORY B,
     ODS_D_HOSPITAL C,
     ODS_D_AREA_COUNTY D,
     ODS_D_DIM_DATE E
WHERE A.RECORD_ID=B.RECORD_ID
  AND A.HOSPITAL_NO=C.HOSPITAL_NO
  AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID
  AND B.INSERT_DATE=E.DATE_1
  AND B.HIGH_RISK_FACTOR<>'健康'
  AND B.HIGH_RISK_FACTOR IS NOT NULL
  AND A.NOW_WEEKS<42
  AND B.EXAMINE_HISTORY_NUM=1
  AND B.INSERT_DATE<=TRUNC(SYSDATE)
  AND D.PROVINCE_ID=130000
GROUP BY D.PROVINCE_ID,
         D.PROVINCE,
         D.CITY_ID,
         D.CITY


------------------------------------------------------
1.2. 查询每个地区内各区县当前的高危总人数
SELECT D.PROVINCE_ID as province_id,
       D.PROVINCE as province_name,
       D.CITY_ID as city_id,
       D.CITY as city_name,
       D.AREA_COUNTY_ID as area_id,
       D.AREA_COUNTY as area_name,
       COUNT(A.RECORD_ID) AS val
FROM ODS_F_GRAVIDA_RECORD A,
     ODS_F_EXAMINE_HISTORY B,
     ODS_D_HOSPITAL C,
     ODS_D_AREA_COUNTY D,
     ODS_D_DIM_DATE E
WHERE A.RECORD_ID=B.RECORD_ID
  AND A.HOSPITAL_NO=C.HOSPITAL_NO
  AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID
  AND B.INSERT_DATE=E.DATE_1
  AND A.NOW_WEEKS<42
  AND B.HIGH_RISK_FACTOR<>'健康'
  AND B.HIGH_RISK_FACTOR IS NOT NULL
  AND B.EXAMINE_HISTORY_NUM=1
  AND B.INSERT_DATE<=TRUNC(SYSDATE)
  AND D.PROVINCE_ID=130000
  AND D.CITY_ID=130300
GROUP BY D.PROVINCE_ID,
         D.PROVINCE,
         D.CITY_ID,
         D.CITY,
         D.AREA_COUNTY_ID,
         D.AREA_COUNTY


------------------------------------------------------
2. 查询每个省近30天的建档总人数
SELECT D.PROVINCE_ID as province_id,
       D.PROVINCE as province_name,
       COUNT(A.RECORD_ID) AS val
FROM ODS_F_GRAVIDA_RECORD A,
     ODS_D_HOSPITAL C,
     ODS_D_AREA_COUNTY D,
     ODS_D_DIM_DATE E
WHERE A.HOSPITAL_NO=C.HOSPITAL_NO
  AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID
  AND A.CREATE_DATE=E.DATE_1
  AND A.CREATE_DATE<=TRUNC(SYSDATE)
  AND A.CREATE_DATE>=(TRUNC(SYSDATE)-30)
GROUP BY D.PROVINCE_ID,
         D.PROVINCE


------------------------------------------------------
2.1. 查询每个省内各地区近30天的建档总人数
SELECT D.PROVINCE_ID as province_id,
       D.PROVINCE as province_name,
       D.CITY_ID as city_id,
       D.CITY as city_name,
       COUNT(A.RECORD_ID) AS val
FROM ODS_F_GRAVIDA_RECORD A,
     ODS_D_HOSPITAL C,
     ODS_D_AREA_COUNTY D,
     ODS_D_DIM_DATE E
WHERE A.HOSPITAL_NO=C.HOSPITAL_NO
  AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID
  AND A.CREATE_DATE=E.DATE_1
  AND D.PROVINCE_ID=130000
  AND A.CREATE_DATE<=TRUNC(SYSDATE)
  AND A.CREATE_DATE>=(TRUNC(SYSDATE)-30)
GROUP BY D.PROVINCE_ID,
         D.PROVINCE,
         D.CITY_ID,
         D.CITY


------------------------------------------------------
2.2. 查询每个地区内各区县近30天的建档总人数
SELECT D.PROVINCE_ID as province_id,
       D.PROVINCE as province_name,
       D.CITY_ID as city_id,
       D.CITY as city_name,
       D.AREA_COUNTY_ID as area_id,
       D.AREA_COUNTY as area_name,
       COUNT(A.RECORD_ID) AS val
FROM ODS_F_GRAVIDA_RECORD A,
     ODS_D_HOSPITAL C,
     ODS_D_AREA_COUNTY D,
     ODS_D_DIM_DATE E
WHERE A.HOSPITAL_NO=C.HOSPITAL_NO
  AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID
  AND A.CREATE_DATE=E.DATE_1
  AND D.PROVINCE_ID=130000
  AND D.CITY_ID=130300
  AND A.CREATE_DATE<=TRUNC(SYSDATE)
  AND A.CREATE_DATE>=(TRUNC(SYSDATE)-30)
GROUP BY D.PROVINCE_ID,
         D.PROVINCE,
         D.CITY_ID,
         D.CITY,
         D.AREA_COUNTY_ID,
         D.AREA_COUNTY


------------------------------------------------------
3. 按省或市或地区查询区域内当前高危总数（按颜色分组、按高危项分组）
SELECT D.PROVINCE_ID as province_id,
       D.PROVINCE as province_name,
       D.CITY_ID as city_id,
       D.CITY as city_name,
       D.AREA_COUNTY_ID as area_id,
       D.AREA_COUNTY as area_name,
       F.HIGH_RISK_GROUP as group_name,
       F.HIGH_RISK_CONTENT as risk_name,
       COUNT(A.RECORD_ID) AS val
FROM ODS_F_GRAVIDA_RECORD A,
     ODS_F_EXAMINE_HISTORY B,
     ODS_D_HOSPITAL C,
     ODS_D_AREA_COUNTY D,
     ODS_D_DIM_DATE E,
     ODS_F_HIGH_RISK_VALUE F
WHERE A.RECORD_ID=B.RECORD_ID
  AND A.HOSPITAL_NO=C.HOSPITAL_NO
  AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID
  AND B.INSERT_DATE=E.DATE_1
  AND B.EXAMINE_ID=F.EXAMINE_ID
  AND A.NOW_WEEKS<42
  AND F.HIGH_RISK_CONTENT<>'健康'
  AND B.EXAMINE_HISTORY_NUM=1
  AND E.YEAR_1=2016
  AND D.PROVINCE_ID=130000
  AND D.CITY_ID=130300
GROUP BY D.PROVINCE_ID,
         D.PROVINCE,
         D.CITY_ID,
         D.CITY,
         D.AREA_COUNTY_ID,
         D.AREA_COUNTY,
         F.HIGH_RISK_GROUP,
         F.HIGH_RISK_CONTENT


------------------------------------------------------
4. 按省或市或地区查询区域内近12个自然月每月建档人数
SELECT D.PROVINCE_ID as province_id,
       D.PROVINCE as province_name,
       D.CITY_ID as city_id,
       D.CITY as city_name,
       D.AREA_COUNTY_ID as area_id,
       D.AREA_COUNTY as area_name,
       E.YEAR_MONTH as year,
       COUNT(A.RECORD_ID) AS val
FROM ODS_F_GRAVIDA_RECORD A,
     ODS_D_HOSPITAL C,
     ODS_D_AREA_COUNTY D,
     ODS_D_DIM_DATE E
WHERE A.HOSPITAL_NO=C.HOSPITAL_NO
  AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID
  AND A.CREATE_DATE=E.DATE_1
  AND D.PROVINCE_ID=130000
  AND D.CITY_ID=130300
  AND D.AREA_COUNTY_ID=130302
  AND A.CREATE_DATE<=TRUNC(SYSDATE)
  AND A.CREATE_DATE>=TRUNC(ADD_MONTHS(SYSDATE,-11))
GROUP BY D.PROVINCE_ID,
         D.PROVINCE,
         D.CITY_ID,
         D.CITY,
         D.AREA_COUNTY_ID,
         D.AREA_COUNTY,
         E.YEAR_MONTH
ORDER BY E.YEAR_MONTH


------------------------------------------------------
4.1. 按省或市或地区查询区域内近12个自然周每周建档人数（如不能按周就同4.2查询近90天按天的）
SELECT D.PROVINCE_ID as province_id,
       D.PROVINCE as province_name,
       D.CITY_ID as city_id,
       D.CITY as city_name,
       D.AREA_COUNTY_ID as area_id,
       D.AREA_COUNTY as area_name,
       E.DATE_1 as ymd,
       COUNT(A.RECORD_ID) AS val
FROM ODS_F_GRAVIDA_RECORD A,
     ODS_D_HOSPITAL C,
     ODS_D_AREA_COUNTY D,
     ODS_D_DIM_DATE E
WHERE A.HOSPITAL_NO=C.HOSPITAL_NO
  AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID
  AND A.CREATE_DATE=E.DATE_1
  AND D.PROVINCE_ID=130000
  AND D.CITY_ID=130300
  AND D.AREA_COUNTY_ID=130302
  AND A.CREATE_DATE<=TRUNC(SYSDATE)
  AND A.CREATE_DATE>=(TRUNC(SYSDATE)-91)
GROUP BY D.PROVINCE_ID,
         D.PROVINCE,
         D.CITY_ID,
         D.CITY,
         D.AREA_COUNTY_ID,
         D.AREA_COUNTY,
         E.DATE_1
ORDER BY E.DATE_1


------------------------------------------------------
4.2. 按省或市或地区查询区域内近30天每天建档人数
SELECT D.PROVINCE_ID as province_id,
       D.PROVINCE as province_name,
       D.CITY_ID as city_id,
       D.CITY as city_name,
       D.AREA_COUNTY_ID as area_id,
       D.AREA_COUNTY as area_name,
       E.DATE_1 as ymd,
       COUNT(A.RECORD_ID) AS val
FROM ODS_F_GRAVIDA_RECORD A,
     ODS_D_HOSPITAL C,
     ODS_D_AREA_COUNTY D,
     ODS_D_DIM_DATE E
WHERE A.HOSPITAL_NO=C.HOSPITAL_NO
  AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID
  AND A.CREATE_DATE=E.DATE_1
  AND D.PROVINCE_ID=130000
  AND D.CITY_ID=130300
  AND D.AREA_COUNTY_ID=130302
  AND A.CREATE_DATE<=TRUNC(SYSDATE)
  AND A.CREATE_DATE>=(TRUNC(SYSDATE)-30)
GROUP BY D.PROVINCE_ID,
         D.PROVINCE,
         D.CITY_ID,
         D.CITY,
         D.AREA_COUNTY_ID,
         D.AREA_COUNTY,
         E.DATE_1
ORDER BY E.DATE_1


------------------------------------------------------
5. 按省或市或地区查询区域内近12个自然月每月初诊人数、复诊人数
SELECT D.PROVINCE_ID as province_id,
       D.PROVINCE as province_name,
       D.CITY_ID as city_id,
       D.CITY as city_name,
       D.AREA_COUNTY_ID as area_id,
       D.AREA_COUNTY as area_name,
       E.YEAR_MONTH as year,
       SUM(DECODE(B.EXAMINE_CATEGORY_ID,1,1,0)) AS val,
       SUM(DECODE(B.EXAMINE_CATEGORY_ID,2,1,0)) AS val2
FROM ODS_F_GRAVIDA_RECORD A,
     ODS_F_EXAMINE_HISTORY B,
     ODS_D_HOSPITAL C,
     ODS_D_AREA_COUNTY D,
     ODS_D_DIM_DATE E
WHERE A.RECORD_ID=B.RECORD_ID
  AND A.HOSPITAL_NO=C.HOSPITAL_NO
  AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID
  AND B.INSERT_DATE=E.DATE_1
  AND B.INSERT_DATE<=TRUNC(SYSDATE)
  AND B.INSERT_DATE>=TRUNC(ADD_MONTHS(SYSDATE,-11))
  AND D.PROVINCE_ID=130000
  AND D.CITY_ID=130300
  AND D.AREA_COUNTY_ID=130302
GROUP BY D.PROVINCE_ID,
         D.PROVINCE,
         D.CITY_ID,
         D.CITY,
         D.AREA_COUNTY_ID,
         D.AREA_COUNTY,
         E.YEAR_MONTH
ORDER BY E.YEAR_MONTH


------------------------------------------------------
5.1. 按省或市或地区查询区域内近12个自然周每周初诊人数、复诊人数（如不能按周就同5.2查询近90天按天的）
SELECT D.PROVINCE_ID as province_id,
       D.PROVINCE as province_name,
       D.CITY_ID as city_id,
       D.CITY as city_name,
       D.AREA_COUNTY_ID as area_id,
       D.AREA_COUNTY as area_name,
       E.DATE_1 as ymd,
       SUM(DECODE(B.EXAMINE_CATEGORY_ID,1,1,0)) AS val,
       SUM(DECODE(B.EXAMINE_CATEGORY_ID,2,1,0)) AS val2
FROM ODS_F_GRAVIDA_RECORD A,
     ODS_F_EXAMINE_HISTORY B,
     ODS_D_HOSPITAL C,
     ODS_D_AREA_COUNTY D,
     ODS_D_DIM_DATE E
WHERE A.RECORD_ID=B.RECORD_ID
  AND A.HOSPITAL_NO=C.HOSPITAL_NO
  AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID
  AND B.INSERT_DATE=E.DATE_1
  AND B.INSERT_DATE<=TRUNC(SYSDATE)
  AND B.INSERT_DATE>=(TRUNC(SYSDATE)-90)
  AND D.PROVINCE_ID=130000
  AND D.CITY_ID=130300
  AND D.AREA_COUNTY_ID=130302
GROUP BY D.PROVINCE_ID,
         D.PROVINCE,
         D.CITY_ID,
         D.CITY,
         D.AREA_COUNTY_ID,
         D.AREA_COUNTY,
         E.DATE_1
ORDER BY E.DATE_1


------------------------------------------------------
5.2. 按省或市或地区查询区域内近30天每天初诊人数、复诊人数
SELECT D.PROVINCE_ID as province_id,
       D.PROVINCE as province_name,
       D.CITY_ID as city_id,
       D.CITY as city_name,
       D.AREA_COUNTY_ID as area_id,
       D.AREA_COUNTY as area_name,
       E.DATE_1 as ymd,
       SUM(DECODE(B.EXAMINE_CATEGORY_ID,1,1,0)) AS val,
       SUM(DECODE(B.EXAMINE_CATEGORY_ID,2,1,0)) AS val2
FROM ODS_F_GRAVIDA_RECORD A,
     ODS_F_EXAMINE_HISTORY B,
     ODS_D_HOSPITAL C,
     ODS_D_AREA_COUNTY D,
     ODS_D_DIM_DATE E
WHERE A.RECORD_ID=B.RECORD_ID
  AND A.HOSPITAL_NO=C.HOSPITAL_NO
  AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID
  AND B.INSERT_DATE=E.DATE_1
  AND B.INSERT_DATE<=TRUNC(SYSDATE)
  AND B.INSERT_DATE>=(TRUNC(SYSDATE)-30)
  AND D.PROVINCE_ID=130000
  AND D.CITY_ID=130300
  AND D.AREA_COUNTY_ID=130302
GROUP BY D.PROVINCE_ID,
         D.PROVINCE,
         D.CITY_ID,
         D.CITY,
         D.AREA_COUNTY_ID,
         D.AREA_COUNTY,
         E.DATE_1
ORDER BY E.DATE_1


------------------------------------------------------
6. 按省或市或地区查询区域内近6个自然月每月产检中历史高危人数（按颜色分组）
SELECT D.PROVINCE_ID as province_id,
       D.PROVINCE as province_name,
       D.CITY_ID as city_id,
       D.CITY as city_name,
       D.AREA_COUNTY_ID as area_id,
       D.AREA_COUNTY as area_name,
       F.HIGH_RISK_GROUP as group_name,
       E.YEAR_MONTH as year,
       COUNT(A.RECORD_ID) AS val
FROM ODS_F_GRAVIDA_RECORD A,
     ODS_F_EXAMINE_HISTORY B,
     ODS_D_HOSPITAL C,
     ODS_D_AREA_COUNTY D,
     ODS_D_DIM_DATE E,
     ODS_F_HIGH_RISK_VALUE F
WHERE A.RECORD_ID=B.RECORD_ID
  AND A.HOSPITAL_NO=C.HOSPITAL_NO
  AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID
  AND B.INSERT_DATE=E.DATE_1
  AND B.EXAMINE_ID=F.EXAMINE_ID
  AND F.HIGH_RISK_CONTENT<>'健康'
  AND F.HIGH_RISK_GROUP<>'其他'
  AND B.EXAMINE_HISTORY_NUM=1
  AND B.INSERT_DATE<=TRUNC(SYSDATE)
  AND B.INSERT_DATE>=TRUNC(ADD_MONTHS(SYSDATE,-6))
  AND D.PROVINCE_ID=130000
  AND D.CITY_ID=130300
  AND D.AREA_COUNTY_ID=130302
GROUP BY D.PROVINCE_ID,
         D.PROVINCE,
         D.CITY_ID,
         D.CITY,
         D.AREA_COUNTY_ID,
         D.AREA_COUNTY,
         F.HIGH_RISK_GROUP,
         E.YEAR_MONTH
ORDER BY F.HIGH_RISK_GROUP,E.YEAR_MONTH

-------------------------------------------------------
6.1. 按省或市或地区查询区域内近30天每天产检中历史高危人数（按颜色分组）
SELECT D.PROVINCE_ID as province_id,
       D.PROVINCE as province_name,
       D.CITY_ID as city_id,
       D.CITY as city_name,
       D.AREA_COUNTY_ID as area_id,
       D.AREA_COUNTY as area_name,
       F.HIGH_RISK_GROUP as group_name,
       E.DATE_2 as year,
       COUNT(A.RECORD_ID) AS val
FROM ODS_F_GRAVIDA_RECORD A,
     ODS_F_EXAMINE_HISTORY B,
     ODS_D_HOSPITAL C,
     ODS_D_AREA_COUNTY D,
     ODS_D_DIM_DATE E,
     ODS_F_HIGH_RISK_VALUE F
WHERE A.RECORD_ID=B.RECORD_ID
  AND A.HOSPITAL_NO=C.HOSPITAL_NO
  AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID
  AND B.INSERT_DATE=E.DATE_1
  AND B.EXAMINE_ID=F.EXAMINE_ID
  AND F.HIGH_RISK_CONTENT<>'健康'
  AND F.HIGH_RISK_GROUP<>'其他'
  AND B.EXAMINE_HISTORY_NUM=1
  AND B.INSERT_DATE<=TRUNC(SYSDATE)
  AND B.INSERT_DATE>=(TRUNC(SYSDATE)-30)
  AND D.PROVINCE_ID=130000
  AND D.CITY_ID=130300
  AND D.AREA_COUNTY_ID=130302
GROUP BY D.PROVINCE_ID,
         D.PROVINCE,
         D.CITY_ID,
         D.CITY,
         D.AREA_COUNTY_ID,
         D.AREA_COUNTY,
         F.HIGH_RISK_GROUP,
         E.DATE_2
ORDER BY F.HIGH_RISK_GROUP,E.DATE_2;