Sqlserver游标的嵌套查询每半个小时接诊多少人

互联网 2021/7/15 2:05:26

USE DZ_TEMPGO DECLARE @StartTime DATETIMEDECLARE @EndTime DATETIMEDECLARE @OutQueryDate NVARCHAR(10) --此处传入字符串便于拼接数据--******************************--创建临时中间表 可以随时调整DECLARE @table TABLE ( VisitDoctorCode NVARCHAR(50) ,…

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

 

随时随地学软件编程-关注百度小程序和微信小程序
关于找一找教程网

本站文章仅代表作者观点,不代表本站立场,所有文章非营利性免费分享。
本站提供了软件编程、网站开发技术、服务器运维、人工智能等等IT技术文章,希望广大程序员努力学习,让我们用科技改变世界。
[Sqlserver游标的嵌套查询每半个小时接诊多少人]http://www.zyiz.net/tech/detail-202941.html

赞(0)
关注微信小程序
程序员编程王-随时随地学编程

扫描二维码或查找【程序员编程王】

可以随时随地学编程啦!

技术文章导航 更多>