让会员有一致的医生来访，在第一次探视后直到分娩之日。

Question

  SELECT DISTINCT
  DEL.BABY_ID, 
  DEL.MOTHER_ID, 
  MIN(DEL.CHECK_VISIT_DATE) OVER (PARTITION BY DEL.MOTHER_ID)  AS  Del_FIRST_DATE,
  (DEL.CHECK_VISIT_DATE)    VISIT_DATE,
  DEL.BABY_BIRTH_DATE,
  CASE ( I want to show the DEL.MOTHER_ID Who have a visit date every month, after the 
  Del_FIRST_DATE up up to the DEL.BABY_BIRTH_DATE and THEN LABEL AS 'SATISFACTORY'
 FROM FT_DEL_SUMMARY AS DEL

输出：

 Del_First_Date Visit_Date  Delivery Date 
 8/19/2021       8/19/2021   11/29/2021
 8/19/2021       9/16/2021   11/29/2021
 8/19/2021       10/19/2021  11/29/2021
 8/19/2021       10/28/2021   11/29/2021
 8/19/2021      11/4/2021     11/29/2021
 8/19/2021     11/12/2021     11/29/2021
 8/19/2021     11/17/2021     11/29/2021
 8/19/2021      11/23/2021    11/29/2021
8/19/2021       11/29/2021    11/29/2021

例如，上述日期将是一个令人满意的结果，因为第一次访问日期是2021年8月19日，然后是9/16，10/19。直到交货之日。因此，这是一个令人满意的结果。然而，当每个月在第一次访问日期之后，一直到交货日期都没有访问日期，那就不满意了。

从上面，我希望能够创建一个案例陈述或任何逻辑，将显示的成员有一个一致的医生访问日期后，他们第一次访问，直到交付日期。

Answer 1

这对于SQL来说非常复杂。每个病人从第一次探视到分娩的月数是不同的。我认为最简单的方法是计算每次就诊的天数，然后计算出每名病人的最高天数，如果> 30或31，则不能令人满意。

WITH    
    VISITS AS (
        SELECT
            DEL.BABY_ID,
            DEL.MOTHER_ID,
            DEL.CHECK_VISIT_DATE AS VISIT_DATE
        FROM    
            FT_DEL_SUMMARY AS DEL
        UNION
        SELECT  
            DEL.BABY_ID,
            DEL.MOTHER_ID,
            DEL.BABY_BIRTH_DATE
        FROM
            FT_DEL_SUMMARY AS DEL
    ),
    INTERVALS AS (
        SELECT
            V.BABY_ID,
            V.MOTHER_ID,
            MIN(V.VISIT_DATE) OVER (PARTITION BY V.BABY_ID, V.MOTHER_ID) AS FIRST_VISIT_DATE,
            MAX(V.VISIT_DATE) OVER (PARTITION BY V.BABY_ID, V.MOTHER_ID) AS BABY_BIRTH_DATE,
            V.VISIT_DATE - LAG(V.VISIT_DATE) OVER (PARTITION BY V.BABY_ID, V.MOTHER_ID ORDER BY V.VISIT_DATE) AS DAYS_SINCE_PREV_VISIT
        FROM
            VISITS V
    )
SELECT  
    BABY_ID,
    MOTHER_ID,
    FIRST_VISIT_DATE,
    BABY_BIRTH_DATE,
    CASE 
        WHEN MAX(DAYS_SINCE_PREV_VISIT) > 31 THEN 'UNSATISFACTORY'
        ELSE 'SATISFACTORY'
    END AS SATISFACTORY_CODE
FROM
    INTERVALS
GROUP BY
    BABY_ID,
    MOTHER_ID,
    FIRST_VISIT_DATE,
    BABY_BIRTH_DATE
/

Answer 2

我不知道这是否符合你的逻辑：

SELECT 
  DEL.BABY_ID, 
  DEL.MOTHER_ID, 
  MIN(DEL.CHECK_VISIT_DATE),
  DEL.BABY_BIRTH_DATE
FROM FT_DEL_SUMMARY AS DEL
group by 
  DEL.BABY_ID, 
  DEL.MOTHER_ID, 
  DEL.BABY_BIRTH_DATE
having -- number of distinct yyyymm equal to number of months between first and last vist
   count(distinct td_month_of_calendar(DEL.CHECK_VISIT_DATE))
   = max(td_month_of_calendar(DEL.CHECK_VISIT_DATE)) -
     min(td_month_of_calendar(DEL.CHECK_VISIT_DATE)) +1