SELECT out.VISIT_CARD_NO FROM (
SELECT DISTINCT N.VISIT_CARD_NO FROM (
SELECT M.VISIT_CARD_NO,M.VISIT_NAME,M.VISIT_DEPT, M.第二次科室,M.时间间隔,M.REG_TIME,m.NEXT_RGETIME FROM (
SELECT VISIT_CARD_NO,VISIT_NAME, DATEDIFF( MINUTE,REG_TIME, LEAD(REG_TIME,1,null) OVER(PARTITION BY VISIT_CARD_NO ORDER BY REG_TIME asc)) 时间间隔,LEAD(VISIT_DEPT,1,null) OVER(PARTITION BY VISIT_CARD_NO ORDER BY REG_TIME asc) 第二次科室,VISIT_DEPT ,REG_TIME, LEAD(REG_TIME,1,null) OVER(PARTITION BY VISIT_CARD_NO ORDER BY REG_TIME asc) NEXT_RGETIME
FROM dbo.ERKOPVISIT WHERE
--REG_TYPE='6' AND
HOSPITAL_CODE='9900000001'
AND CONVERT(VARCHAR(30),REG_TIME,112)='20210527'
)M
WHERE M.时间间隔 <=60 --AND M.第二次科室 <> M.VISIT_DEPT
)N
)out
WHERE NOT EXISTS( SELECT 1 FROM (
SELECT M.VISIT_CARD_NO FROM (
SELECT VISIT_CARD_NO ,CONCAT( CONVERT(VARCHAR(30),REG_TIME,112),LEFT(CONVERT(VARCHAR(30),REG_TIME,108),2 )) REG_TIME ,COUNT(*) 挂号次数
FROM dbo.ERKOPVISIT (NOLOCK)
WHERE
HOSPITAL_CODE='9900000001'
AND CONVERT(VARCHAR(30),REG_TIME,112)='20210527'
GROUP BY VISIT_CARD_NO, CONCAT( CONVERT(VARCHAR(30),REG_TIME,112),LEFT(CONVERT(VARCHAR(30),REG_TIME,108),2 ))
HAVING COUNT(*) >=2
--483
)M
)A WHERE out.VISIT_CARD_NO=A.VISIT_CARD_NO

)

 

--按天来可取，按小时不可取

WITH T1 AS (
SELECT M.VISIT_DEPT,M.ID_NO,M.VISIT_TIME, SUBSTRING(CONVERT(VARCHAR(30),VISIT_TIME,108),1,2) Rhour
FROM dbo.ERKOPVISIT M WITH (NOLOCK)
WHERE M.VISIT_DATE='2021-05-27'
AND M.VISIT_STATUS <> '6900000005' -- AND M.ID_NO='110105199608144111'
),T2 AS (
SELECT T1.VISIT_DEPT,T1.ID_NO,T1.VISIT_TIME,T1.Rhour,
RANK () OVER (PARTITION BY T1.ID_NO ORDER BY T1.Rhour ) Rid
FROM T1
)--SELECT * FROM T2
, T3 AS (
SELECT * FROM T2
--WHERE T2.ID_NO='130403199406162714'
),T4 AS(
SELECT T3.VISIT_DEPT,T3.ID_NO,T3.VISIT_TIME,T3.Rhour,T3.Rid,
T3.Rhour- T3.Rid 时间差 FROM T3
),T5 AS (
SELECT T4.ID_NO ,COUNT(*) 出现的次数 FROM T4
GROUP BY T4.ID_NO HAVING COUNT(*) >=2
),T6 AS (
SELECT * FROM T4
WHERE T4.ID_NO IN (SELECT T5.ID_NO FROM T5)
),T7 AS (
SELECT T6.ID_NO,T6.VISIT_TIME,T6.时间差,RANK() OVER (PARTITION BY T6.ID_NO ORDER BY T6.时间差 ASC ) seq
FROM T6
)
,T8 AS (
SELECT T7.ID_NO,T7.时间差,T7.seq FROM T7
GROUP BY T7.ID_NO,T7.时间差,T7.seq
),T9 AS (
SELECT T8.ID_NO,COUNT(*) 计数 FROM T8
GROUP BY T8.ID_NO HAVING COUNT(*) >=2
),T10 AS (
SELECT * FROM T7 WHERE T7.ID_NO NOT IN (SELECT T9.ID_NO FROM T9)
)SELECT * FROM T10 -- WHERE T10.ID_NO='110105199608144111'

--按小时统计 1小时内 急诊科 多次挂号的人数
USE ERKHIS
GO
DECLARE @Input_date NVARCHAR(8)
SET @Input_date='20210531';
WITH Staff AS (
SELECT SSO_CODE --,COUNT(1) 次数
FROM dbo.ERKSTAFFMAIN
WHERE ISNULL(ISTEST,'0')<>'1' --排除测试
AND ISNULL(SSO_CODE,0)<>'0' --排除NULL值
AND SSO_CODE<>'' --排除空字符
GROUP BY SSO_CODE
), T AS (
SELECT M.VISIT_NO,M.VISIT_CARD_NO,ID_NO,M.VISIT_NAME,M.VISIT_DEPT, M.第二次科室,M.时间间隔,M.VISIT_TIME,m.NEXT_VISIT_TIME FROM
(
SELECT REG_TYPE,VISIT_NO,ID_NO, VISIT_CARD_NO,VISIT_NAME, VISIT_DEPT ,
LEAD(VISIT_DEPT,1,null) OVER(PARTITION BY ID_NO ORDER BY VISIT_TIME asc) 第二次科室,
DATEDIFF( MINUTE,VISIT_TIME, LEAD(VISIT_TIME,1,null) OVER(PARTITION BY ID_NO ORDER BY VISIT_TIME asc)) 时间间隔,
VISIT_TIME,
LEAD(VISIT_TIME,1,null) OVER(PARTITION BY ID_NO ORDER BY VISIT_TIME asc) NEXT_VISIT_TIME
FROM dbo.ERKOPVISIT WITH (NOLOCK)
JOIN Staff ON dbo.ERKOPVISIT.UPDATE_BY=Staff.SSO_CODE -- 排除测试号
WHERE
CONVERT(VARCHAR(8),VISIT_TIME,112)=@Input_date AND ID_NO <>'0'
AND ID_NO <> 'XXXXXXXXXXXXXXXXXX'
AND VISIT_STATUS<>'6900000005'
AND REG_TYPE='6' --表示急诊
-- AND ID_NO='110102198606093345'
)M
), Main AS (
SELECT T.VISIT_NO,t.VISIT_CARD_NO,t.ID_NO,t.VISIT_NAME,t.VISIT_DEPT,
t.第二次科室,t.时间间隔,t.VISIT_TIME,t.NEXT_VISIT_TIME,ROW_NUMBER()OVER(PARTITION BY T.ID_NO ORDER BY T.VISIT_TIME ASC ) r FROM T
),Main_多次 AS (
SELECT Main.ID_NO FROM Main WHERE Main.r > 1
GROUP BY Main.ID_NO
),FinalMian AS
(
SELECT T.* FROM T JOIN
Main_多次 ON T.ID_NO=Main_多次.ID_NO
)
SELECT * FROM FinalMian --WHERE FinalMian.时间间隔<60
ORDER BY FinalMian.ID_NO ,
FinalMian.VISIT_TIME