查询用药时间间断不超过7天,合并后时间段大于3月的人

;WITH cte
AS (
   SELECT empi,
          [drugname],
          [startdttm],
          [enddttm],
          ROW_NUMBER() OVER (PARTITION BY empi, [drugname] ORDER BY [startdttm]) rowid
   FROM v_ccr_medications
   --JOIN empiList ON a.empi=b.empi 过滤病人数据集
   WHERE [drugname] IN ( '氯化钾注射液', '阿卡波糖片', '泼尼松片', '氯化钠（0.9%）注射液' )
         AND empi in ('0008BAAF-720A-4590-82D1-A7AC61144F7A','09B18155-FAF2-4A18-81A3-284D8E57B4F4')
  --替换WHERE [drugname] IN ( '氯化钾注射液', '阿卡波糖片', '泼尼松片','氯化钠（0.9%）注射液' ) 为具体的药物
   ),
      cte1
AS (SELECT *,
           1 [level]
    FROM cte
    WHERE rowid = 1
    UNION ALL
    SELECT a.empi,
           a.drugname,
           CASE
               WHEN DATEADD(DAY, 7, b.enddttm) > a.startdttm THEN    --这是合并用药结束到下个用药开始时间段7天内
                   b.startdttm
               ELSE
                   a.startdttm
           END starttime,
           CASE
               WHEN b.enddttm > a.enddttm THEN
                   b.enddttm
               ELSE
                   a.enddttm
           END enddttm,
           a.rowid,
           CASE
               WHEN DATEADD(DAY, 7, b.enddttm) > a.startdttm THEN  --这是合并用药结束到下个用药开始时间段7天内
                   b.[level]
               ELSE
                   b.[level] + 1
           END [level]
    FROM cte a
        INNER JOIN cte1 b
            ON a.empi = b.empi
               AND a.drugname = b.drugname
               AND a.rowid = b.rowid + 1
   )
SELECT empi,
       [drugname],
       MIN(cte1.startdttm) AS startdttm,
       MAX(cte1.enddttm) AS enddttm
       into ##temp
FROM cte1
GROUP BY empi,
         [drugname],
         [level]

--查询用药时间间断不超过7天,合并后时间段大于3月的人
SELECT * FROM ##temp
         where DATEDIFF(MONTH,startdttm,enddttm)>3