'Show members that have a consistent doctors visit, after the first visit till the day of delivery

  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

Output:

 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

For example, the above date will represent a satisfactory outcome, because the first visit date was 8/19/2021, and then there is a visit date on 9/16, 10/19 .... up till the date of delivery. So this is a satisfactory outcome. However, when there is no visit date each month after the first visit date up till the delivery date, then it's unsatisfactory.

From the above, I want to be able to create a case statement or any logic that will show the members that have a consistent doctor visit date after their first visit up till the date of delivery.

sqlteradata


Solution 1:[1]

This is very complicated for SQL. The number of months between first visit and delivery will be different for each patient. I think easiest way would be to calculate for each visit the number of days since the previous visit, then take the MAX of that calculation per patient and if > 30 or 31 then it was unsatisfactory.

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
/

Solution 2:[2]

I don't know if this matches your logic:

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 dnoeth

Related Questions