USE DZ_TEMP
GO

DECLARE @StartTime DATETIME
DECLARE @EndTime DATETIME
DECLARE @OutQueryDate NVARCHAR(10) --此处传入字符串便于拼接数据
--******************************
--创建临时中间表 可以随时调整
DECLARE @table TABLE ( VisitDoctorCode NVARCHAR(50) ,VisitDoctorName NVARCHAR(50),
RegTypeCode NVARCHAR(50),RegTypeName NVARCHAR(50) , TotalCount INT ,StartPeriod DATETIME,EndPeriod DATETIME )

--******************************

DECLARE CURSOR_OUTER_QueryDate CURSOR FOR
--查询出日期,并且转化为 NVARCHAR(10)
SELECT CAST( CAST(StartTime AS DATE) AS NVARCHAR(10)) QueryDate
FROM [DZ_TEMP].[dbo].[Temp_Thirty_Time_Interval_Source] --这个表是创建好的如果说要查询更短的时间 如要重新再抽
WHERE CAST(StartTime AS DATE) IN ('2021-05-17','2021-05-18' ,'2021-05-19' ) -- 这里要传入天数
GROUP BY CAST( CAST(StartTime AS DATE) AS NVARCHAR(10))
ORDER BY 1 ASC

BEGIN
OPEN CURSOR_OUTER_QueryDate
FETCH NEXT FROM CURSOR_OUTER_QueryDate INTO @OutQueryDate
WHILE @@FETCH_STATUS=0
BEGIN
PRINT '查询的日期:'+ @OutQueryDate
--*************************内循环************************************

DECLARE CURSOR_ERKOPVISIT CURSOR FOR
SELECT [StartTime],[EndTime]
FROM [DZ_TEMP].[dbo].[Temp_Thirty_Time_Interval_Source](NOLOCK)
WHERE CAST (StartTime AS DATE)=@OutQueryDate
AND StartTime >= @OutQueryDate + ' 06:00:00' AND StartTime <= @OutQueryDate + ' 9:00:00' --次处控制每一天要查询出的时间段
ORDER BY StartTime ASC
BEGIN
OPEN CURSOR_ERKOPVISIT
FETCH NEXT FROM CURSOR_ERKOPVISIT INTO @StartTime,@EndTime
WHILE @@FETCH_STATUS=0
BEGIN

INSERT INTO @table
SELECT Main.VisitDoctorCode, ISNULL(M.PERSON_NAME,P.PERSON_NAME),b.挂号类型编码,b.挂号类型名称 , COUNT(1), @StartTime ,@EndTime
FROM EHR_CLINIC_ORDER.dbo.PatientClinicVisit (NOLOCK) Main
JOIN WeiTong_View..挂号类型_亚太 B WITH (NOLOCK) ON Main.RegTypeCode=b.挂号类型编码
LEFT JOIN
(
SELECT N.PERSON_NAME,N.SSO_CODE FROM (
SELECT e.PERSON_SEQ,e.PERSON_NAME,d.SSO_CODE ,ROW_NUMBER() OVER (PARTITION BY d.SSO_CODE ORDER BY e.PERSON_SEQ DESC ) rowid
FROM ERKHIS.dbo.ERKSTAFFMAIN d WITH(NOLOCK)
LEFT JOIN ERKHIS.dbo.ERKPERSON e WITH(NOLOCK) ON e.PERSON_SEQ=d.PERSON_SEQ
WHERE d.SSO_CODE IS NOT NULL AND d.SSO_CODE <>''
)N WHERE N.rowid=1
)M ON Main.VisitDoctorCode = M.SSO_CODE
LEFT JOIN
(
SELECT N.PERSON_NAME,N.HIS_CODE FROM (
SELECT e.PERSON_SEQ,e.PERSON_NAME,d.HIS_CODE ,ROW_NUMBER() OVER (PARTITION BY d.HIS_CODE ORDER BY e.PERSON_SEQ DESC ) rowid
FROM ERKHIS.dbo.ERKSTAFFMAIN d WITH(NOLOCK)
LEFT JOIN ERKHIS.dbo.ERKPERSON e WITH(NOLOCK) ON e.PERSON_SEQ=d.PERSON_SEQ
)N WHERE N.rowid=1

)P ON Main.VisitDoctorCode = p.HIS_CODE
--过滤条件
WHERE Main.IsTest='0'
AND FirstVisitTime > @StartTime AND FirstVisitTime <= @EndTime --每个半小时的时间区间筛选
AND FirstVisitTime IS NOT NULL
GROUP BY Main.VisitDoctorCode, ISNULL(M.PERSON_NAME,P.PERSON_NAME),b.挂号类型编码,b.挂号类型名称

FETCH NEXT FROM CURSOR_ERKOPVISIT INTO @StartTime,@EndTime
END
CLOSE CURSOR_ERKOPVISIT
DEALLOCATE CURSOR_ERKOPVISIT
END

--**************************************************************
FETCH NEXT FROM CURSOR_OUTER_QueryDate INTO @OutQueryDate
END
CLOSE CURSOR_OUTER_QueryDate
DEALLOCATE CURSOR_OUTER_QueryDate
END

SELECT
FinalMain.VisitDoctorCode,
FinalMain.VisitDoctorName,
FinalMain.RegTypeCode,
FinalMain.RegTypeName,
FinalMain.TotalCount,
FinalMain.StartPeriod ,
FinalMain.EndPeriod
FROM @table FinalMain --WHERE VisitDoctorCode='471'
ORDER BY StartPeriod ASC