其中GH_NGHZDK里面数据有400万条,通过遍历这400万条去其他数据表中取出相关数据进行计算,
ALTER PROCEDURE [dbo].[usp_GetFactWaitTime]
-- Add the parameters for the stored procedure here
AS
BEGIN
--定义各类变量以供存储过程使用
declare @ghxh numeric(18,0),@cardno varchar(20),@ksbm varchar(10),@ysdm varchar(10),@czyh varchar(10),@ghlb varchar(10),@sex varchar(10),@age varchar(10),@brq datetime,@hrq datetime,@jssjh varchar(20),@second int,@rowjsk int,@rowmzcfk int,@rowhjcfk int,@jskindex int,@mzcfkindex int,@hjcfkindex int,@jssfrq datetime,@mzcflrrq datetime,@mzcfqrrq datetime,@hjcflrrq datetime,@mzcfysdm varchar(10),@hjcfysdm varchar(10),@mzcfksdm varchar(10),@hjcfksdm varchar(10),@ghsfbz int,@jsczyh varchar(10),@fyck varchar(4),@rowqymzcfk int,@qymzcfkindex int,@hzxm varchar(12),@cyqysj datetime,@yfdm varchar(4),@mzcfxh numeric(12,0),@jcrq datetime,@jyrq datetime,@jcczy varchar(4),@jyczy varchar(4),@tmptest varchar(50),@selectrowcount int
--定义临时表存放结算库
if Object_Id( 'Tempdb.dbo.#jsktmp') Is Not Null
begin
Drop Table #jsktmp
end
create table #jsktmp
(
jskid int identity(1,1),
sjh varchar(18),
czyh varchar(6),
sfrq datetime,
sfksdm varchar(4),
ksdm varchar(4),
jlzt smallint,
qrrq datetime,
ghsfbz int
)
--定义临时表存放门诊处方库
if Object_Id( 'Tempdb.dbo.#mzcftmp') Is Not Null
begin
Drop Table #mzcftmp
end
create table #mzcftmp
(
mzcfid int identity(1,1),
mzcfxh numeric(12,0),
jssjh varchar(18),
hjxh numeric(12,0),
cfxh int,
lrrq datetime,
ysdm varchar(6),
ksdm varchar(4),
czyh varchar(6),
yfdm varchar(4),
qrrq datetime,
qrksdm varchar(4),
cflx smallint,
fyck varchar(2),
hzxm varchar(12)
)
--定义取药临时库表
if Object_Id( 'Tempdb.dbo.#qymzcftmp') Is Not Null
begin
Drop Table #qymzcftmp
end
create table #qymzcftmp
(
qymzcfid int identity(1,1),
qrrq datetime,
fyck varchar(4),--作为发药窗口与药房代码的共同字段
hzxm varchar(12)
)
--定义划价处方临时表
if Object_Id( 'Tempdb.dbo.#hjcftmp') Is Not Null
begin
Drop Table #hjcftmp
end
create table #hjcftmp
(
hjcfid int identity(1,1),
hjcfxh numeric(12,0),
czyh varchar(6),
lrrq datetime,
ysdm varchar(6),
ksdm varchar(4),
sfksdm varchar(4),
yfdm varchar(4),
cflx smallint
)
--定义游标,检索挂号账单库的数据
declare C1 CURSOR FAST_FORWARD For
SELECT DISTINCT
g.xh, g.cardno,g.jssjh,g.ksdm, CASE WHEN isnull(g.ysdm, '') <> '' THEN g.ysdm ELSE 'weizhi' END AS ysdm, CASE WHEN isnull(g.czyh, '')
<> '' THEN g.czyh ELSE 'weizhi' END AS czyh, g.ghlb, ISNULL(b.sex, '未知') AS sex, CASE WHEN LEN(ISNULL(birth, '')) = 6 AND CONVERT(int, substring(birth, 1, 2))
> 1 AND CONVERT(int, substring(birth, 3, 2)) >= 1 AND CONVERT(int, substring(birth, 3, 2)) <= 12 AND (((CONVERT(int, substring(birth, 3, 2)) = 1 OR
CONVERT(int, substring(birth, 3, 2)) = 3 OR
CONVERT(int, substring(birth, 3, 2)) = 5 OR
CONVERT(int, substring(birth, 3, 2)) = 7 OR
CONVERT(int, substring(birth, 3, 2)) = 8 OR
CONVERT(int, substring(birth, 3, 2)) = 10 OR
CONVERT(int, substring(birth, 3, 2)) = 12) AND CONVERT(int, substring(birth, 5, 2)) >= 1 AND CONVERT(int, substring(birth, 5, 2)) <= 31) OR
((CONVERT(int, substring(birth, 3, 2)) = 4 OR
CONVERT(int, substring(birth, 3, 2)) = 6 OR
CONVERT(int, substring(birth, 3, 2)) = 9 OR
CONVERT(int, substring(birth, 3, 2)) = 11) AND CONVERT(int, substring(birth, 5, 2)) >= 1 AND CONVERT(int, substring(birth, 5, 2)) <= 30) OR
(CONVERT(int, substring(birth, 3, 2)) = 2 AND CONVERT(int, substring(birth, 5, 2)) >= 1 AND CONVERT(int, substring(birth, 5, 2)) <= 28)) THEN datediff(yy, birth,
GETDATE()) WHEN LEN(ISNULL(birth, '')) = 8 AND CONVERT(int, substring(birth, 1, 4)) > 1 AND CONVERT(int, substring(birth, 5, 2)) >= 1 AND CONVERT(int,
substring(birth, 5, 2)) <= 12 AND (((CONVERT(int, substring(birth, 5, 2)) = 1 OR
CONVERT(int, substring(birth, 5, 2)) = 3 OR
CONVERT(int, substring(birth, 5, 2)) = 5 OR
CONVERT(int, substring(birth, 5, 2)) = 7 OR
CONVERT(int, substring(birth, 5, 2)) = 8 OR
CONVERT(int, substring(birth, 5, 2)) = 10 OR
CONVERT(int, substring(birth, 5, 2)) = 12) AND CONVERT(int, substring(birth, 7, 2)) >= 1 AND CONVERT(int, substring(birth, 7, 2)) <= 31) OR
((CONVERT(int, substring(birth, 5, 2)) = 4 OR
CONVERT(int, substring(birth, 5, 2)) = 6 OR
CONVERT(int, substring(birth, 5, 2)) = 9 OR
CONVERT(int, substring(birth, 5, 2)) = 11) AND CONVERT(int, substring(birth, 7, 2)) >= 1 AND CONVERT(int, substring(birth, 7, 2)) <= 30) OR
(CONVERT(int, substring(birth, 5, 2)) = 2 AND CONVERT(int, substring(birth, 7, 2)) >= 1 AND CONVERT(int, substring(birth, 7, 2)) <= 28)) THEN datediff(yy, birth,
GETDATE()) WHEN (LEN(ISNULL(birth, '')) <> 6 OR
LEN(ISNULL(birth, '')) <> 8) AND LEN(isnull(sfzh, '')) = 15 AND CONVERT(int, substring(sfzh, 7, 2)) > 1 AND CONVERT(int, substring(sfzh, 9, 2)) >= 1 AND CONVERT(int,
substring(sfzh, 9, 2)) <= 12 AND (((CONVERT(int, substring(sfzh, 9, 2)) = 1 OR
CONVERT(int, substring(sfzh, 9, 2)) = 3 OR
CONVERT(int, substring(sfzh, 9, 2)) = 5 OR
CONVERT(int, substring(sfzh, 9, 2)) = 7 OR
CONVERT(int, substring(sfzh, 9, 2)) = 8 OR
CONVERT(int, substring(sfzh, 9, 2)) = 10 OR
CONVERT(int, substring(sfzh, 9, 2)) = 12) AND CONVERT(int, substring(sfzh, 11, 2)) >= 1 AND CONVERT(int, substring(sfzh, 11, 2)) <= 31) OR
((CONVERT(int, substring(sfzh, 9, 2)) = 4 OR
CONVERT(int, substring(sfzh, 9, 2)) = 6 OR
CONVERT(int, substring(sfzh, 9, 2)) = 9 OR
CONVERT(int, substring(sfzh, 9, 2)) = 11) AND CONVERT(int, substring(sfzh, 11, 2)) >= 1 AND CONVERT(int, substring(sfzh, 11, 2)) <= 30) OR
(CONVERT(int, substring(sfzh, 9, 2)) = 2 AND CONVERT(int, substring(sfzh, 11, 2)) >= 1 AND CONVERT(int, substring(sfzh, 11, 2)) <= 28)) THEN datediff(yy,
substring(sfzh, 7, 6), GETDATE()) WHEN (LEN(ISNULL(birth, '')) <> 6 OR
LEN(ISNULL(birth, '')) <> 8) AND LEN(isnull(sfzh, '')) = 18 AND CONVERT(int, substring(sfzh, 7, 4)) > 1 AND CONVERT(int, substring(sfzh, 11, 2)) >= 1 AND CONVERT(int,
substring(sfzh, 11, 2)) <= 12 AND (((CONVERT(int, substring(sfzh, 11, 2)) = 1 OR
CONVERT(int, substring(sfzh, 11, 2)) = 3 OR
CONVERT(int, substring(sfzh, 11, 2)) = 5 OR
CONVERT(int, substring(sfzh, 11, 2)) = 7 OR
CONVERT(int, substring(sfzh, 11, 2)) = 8 OR
CONVERT(int, substring(sfzh, 11, 2)) = 10 OR
CONVERT(int, substring(sfzh, 11, 2)) = 12) AND CONVERT(int, substring(sfzh, 13, 2)) >= 1 AND CONVERT(int, substring(sfzh, 13, 2)) <= 31) OR
((CONVERT(int, substring(sfzh, 11, 2)) = 4 OR
CONVERT(int, substring(sfzh, 11, 2)) = 6 OR
CONVERT(int, substring(sfzh, 11, 2)) = 9 OR
CONVERT(int, substring(sfzh, 11, 2)) = 11) AND CONVERT(int, substring(sfzh, 13, 2)) >= 1 AND CONVERT(int, substring(sfzh, 13, 2)) <= 30) OR
(CONVERT(int, substring(sfzh, 11, 2)) = 2 AND CONVERT(int, substring(sfzh, 13, 2)) >= 1 AND CONVERT(int, substring(sfzh, 13, 2)) <= 28)) THEN datediff(yy,
substring(sfzh, 7, 8), GETDATE()) ELSE '-1' END AS NL, CONVERT(datetime,(SUBSTRING(g.ghrq,1,8) + ' ' + SUBSTRING(g.ghrq,9,8))) as ghrq, case when g.fzrq is null then null else CONVERT(datetime,(SUBSTRING(g.fzrq,1,8) + ' ' + SUBSTRING(g.fzrq,9,8))) end as fzrq
FROM HIS40.dbo.GH_NGHZDK AS g INNER JOIN
HIS40.dbo.SF_BRXXK AS b ON g.patid = b.patid
--打开游标
OPEN C1
FETCH NEXT FROM C1 INTO @ghxh,@cardno,@jssjh,@ksbm,@ysdm,@czyh,@ghlb,@sex,@age,@brq,@hrq;
ALTER PROCEDURE [dbo].[usp_GetFactWaitTime]
-- Add the parameters for the stored procedure here
AS
BEGIN
--定义各类变量以供存储过程使用
declare @ghxh numeric(18,0),@cardno varchar(20),@ksbm varchar(10),@ysdm varchar(10),@czyh varchar(10),@ghlb varchar(10),@sex varchar(10),@age varchar(10),@brq datetime,@hrq datetime,@jssjh varchar(20),@second int,@rowjsk int,@rowmzcfk int,@rowhjcfk int,@jskindex int,@mzcfkindex int,@hjcfkindex int,@jssfrq datetime,@mzcflrrq datetime,@mzcfqrrq datetime,@hjcflrrq datetime,@mzcfysdm varchar(10),@hjcfysdm varchar(10),@mzcfksdm varchar(10),@hjcfksdm varchar(10),@ghsfbz int,@jsczyh varchar(10),@fyck varchar(4),@rowqymzcfk int,@qymzcfkindex int,@hzxm varchar(12),@cyqysj datetime,@yfdm varchar(4),@mzcfxh numeric(12,0),@jcrq datetime,@jyrq datetime,@jcczy varchar(4),@jyczy varchar(4),@tmptest varchar(50),@selectrowcount int
--定义临时表存放结算库
if Object_Id( 'Tempdb.dbo.#jsktmp') Is Not Null
begin
Drop Table #jsktmp
end
create table #jsktmp
(
jskid int identity(1,1),
sjh varchar(18),
czyh varchar(6),
sfrq datetime,
sfksdm varchar(4),
ksdm varchar(4),
jlzt smallint,
qrrq datetime,
ghsfbz int
)
--定义临时表存放门诊处方库
if Object_Id( 'Tempdb.dbo.#mzcftmp') Is Not Null
begin
Drop Table #mzcftmp
end
create table #mzcftmp
(
mzcfid int identity(1,1),
mzcfxh numeric(12,0),
jssjh varchar(18),
hjxh numeric(12,0),
cfxh int,
lrrq datetime,
ysdm varchar(6),
ksdm varchar(4),
czyh varchar(6),
yfdm varchar(4),
qrrq datetime,
qrksdm varchar(4),
cflx smallint,
fyck varchar(2),
hzxm varchar(12)
)
--定义取药临时库表
if Object_Id( 'Tempdb.dbo.#qymzcftmp') Is Not Null
begin
Drop Table #qymzcftmp
end
create table #qymzcftmp
(
qymzcfid int identity(1,1),
qrrq datetime,
fyck varchar(4),--作为发药窗口与药房代码的共同字段
hzxm varchar(12)
)
--定义划价处方临时表
if Object_Id( 'Tempdb.dbo.#hjcftmp') Is Not Null
begin
Drop Table #hjcftmp
end
create table #hjcftmp
(
hjcfid int identity(1,1),
hjcfxh numeric(12,0),
czyh varchar(6),
lrrq datetime,
ysdm varchar(6),
ksdm varchar(4),
sfksdm varchar(4),
yfdm varchar(4),
cflx smallint
)
--定义游标,检索挂号账单库的数据
declare C1 CURSOR FAST_FORWARD For
SELECT DISTINCT
g.xh, g.cardno,g.jssjh,g.ksdm, CASE WHEN isnull(g.ysdm, '') <> '' THEN g.ysdm ELSE 'weizhi' END AS ysdm, CASE WHEN isnull(g.czyh, '')
<> '' THEN g.czyh ELSE 'weizhi' END AS czyh, g.ghlb, ISNULL(b.sex, '未知') AS sex, CASE WHEN LEN(ISNULL(birth, '')) = 6 AND CONVERT(int, substring(birth, 1, 2))
> 1 AND CONVERT(int, substring(birth, 3, 2)) >= 1 AND CONVERT(int, substring(birth, 3, 2)) <= 12 AND (((CONVERT(int, substring(birth, 3, 2)) = 1 OR
CONVERT(int, substring(birth, 3, 2)) = 3 OR
CONVERT(int, substring(birth, 3, 2)) = 5 OR
CONVERT(int, substring(birth, 3, 2)) = 7 OR
CONVERT(int, substring(birth, 3, 2)) = 8 OR
CONVERT(int, substring(birth, 3, 2)) = 10 OR
CONVERT(int, substring(birth, 3, 2)) = 12) AND CONVERT(int, substring(birth, 5, 2)) >= 1 AND CONVERT(int, substring(birth, 5, 2)) <= 31) OR
((CONVERT(int, substring(birth, 3, 2)) = 4 OR
CONVERT(int, substring(birth, 3, 2)) = 6 OR
CONVERT(int, substring(birth, 3, 2)) = 9 OR
CONVERT(int, substring(birth, 3, 2)) = 11) AND CONVERT(int, substring(birth, 5, 2)) >= 1 AND CONVERT(int, substring(birth, 5, 2)) <= 30) OR
(CONVERT(int, substring(birth, 3, 2)) = 2 AND CONVERT(int, substring(birth, 5, 2)) >= 1 AND CONVERT(int, substring(birth, 5, 2)) <= 28)) THEN datediff(yy, birth,
GETDATE()) WHEN LEN(ISNULL(birth, '')) = 8 AND CONVERT(int, substring(birth, 1, 4)) > 1 AND CONVERT(int, substring(birth, 5, 2)) >= 1 AND CONVERT(int,
substring(birth, 5, 2)) <= 12 AND (((CONVERT(int, substring(birth, 5, 2)) = 1 OR
CONVERT(int, substring(birth, 5, 2)) = 3 OR
CONVERT(int, substring(birth, 5, 2)) = 5 OR
CONVERT(int, substring(birth, 5, 2)) = 7 OR
CONVERT(int, substring(birth, 5, 2)) = 8 OR
CONVERT(int, substring(birth, 5, 2)) = 10 OR
CONVERT(int, substring(birth, 5, 2)) = 12) AND CONVERT(int, substring(birth, 7, 2)) >= 1 AND CONVERT(int, substring(birth, 7, 2)) <= 31) OR
((CONVERT(int, substring(birth, 5, 2)) = 4 OR
CONVERT(int, substring(birth, 5, 2)) = 6 OR
CONVERT(int, substring(birth, 5, 2)) = 9 OR
CONVERT(int, substring(birth, 5, 2)) = 11) AND CONVERT(int, substring(birth, 7, 2)) >= 1 AND CONVERT(int, substring(birth, 7, 2)) <= 30) OR
(CONVERT(int, substring(birth, 5, 2)) = 2 AND CONVERT(int, substring(birth, 7, 2)) >= 1 AND CONVERT(int, substring(birth, 7, 2)) <= 28)) THEN datediff(yy, birth,
GETDATE()) WHEN (LEN(ISNULL(birth, '')) <> 6 OR
LEN(ISNULL(birth, '')) <> 8) AND LEN(isnull(sfzh, '')) = 15 AND CONVERT(int, substring(sfzh, 7, 2)) > 1 AND CONVERT(int, substring(sfzh, 9, 2)) >= 1 AND CONVERT(int,
substring(sfzh, 9, 2)) <= 12 AND (((CONVERT(int, substring(sfzh, 9, 2)) = 1 OR
CONVERT(int, substring(sfzh, 9, 2)) = 3 OR
CONVERT(int, substring(sfzh, 9, 2)) = 5 OR
CONVERT(int, substring(sfzh, 9, 2)) = 7 OR
CONVERT(int, substring(sfzh, 9, 2)) = 8 OR
CONVERT(int, substring(sfzh, 9, 2)) = 10 OR
CONVERT(int, substring(sfzh, 9, 2)) = 12) AND CONVERT(int, substring(sfzh, 11, 2)) >= 1 AND CONVERT(int, substring(sfzh, 11, 2)) <= 31) OR
((CONVERT(int, substring(sfzh, 9, 2)) = 4 OR
CONVERT(int, substring(sfzh, 9, 2)) = 6 OR
CONVERT(int, substring(sfzh, 9, 2)) = 9 OR
CONVERT(int, substring(sfzh, 9, 2)) = 11) AND CONVERT(int, substring(sfzh, 11, 2)) >= 1 AND CONVERT(int, substring(sfzh, 11, 2)) <= 30) OR
(CONVERT(int, substring(sfzh, 9, 2)) = 2 AND CONVERT(int, substring(sfzh, 11, 2)) >= 1 AND CONVERT(int, substring(sfzh, 11, 2)) <= 28)) THEN datediff(yy,
substring(sfzh, 7, 6), GETDATE()) WHEN (LEN(ISNULL(birth, '')) <> 6 OR
LEN(ISNULL(birth, '')) <> 8) AND LEN(isnull(sfzh, '')) = 18 AND CONVERT(int, substring(sfzh, 7, 4)) > 1 AND CONVERT(int, substring(sfzh, 11, 2)) >= 1 AND CONVERT(int,
substring(sfzh, 11, 2)) <= 12 AND (((CONVERT(int, substring(sfzh, 11, 2)) = 1 OR
CONVERT(int, substring(sfzh, 11, 2)) = 3 OR
CONVERT(int, substring(sfzh, 11, 2)) = 5 OR
CONVERT(int, substring(sfzh, 11, 2)) = 7 OR
CONVERT(int, substring(sfzh, 11, 2)) = 8 OR
CONVERT(int, substring(sfzh, 11, 2)) = 10 OR
CONVERT(int, substring(sfzh, 11, 2)) = 12) AND CONVERT(int, substring(sfzh, 13, 2)) >= 1 AND CONVERT(int, substring(sfzh, 13, 2)) <= 31) OR
((CONVERT(int, substring(sfzh, 11, 2)) = 4 OR
CONVERT(int, substring(sfzh, 11, 2)) = 6 OR
CONVERT(int, substring(sfzh, 11, 2)) = 9 OR
CONVERT(int, substring(sfzh, 11, 2)) = 11) AND CONVERT(int, substring(sfzh, 13, 2)) >= 1 AND CONVERT(int, substring(sfzh, 13, 2)) <= 30) OR
(CONVERT(int, substring(sfzh, 11, 2)) = 2 AND CONVERT(int, substring(sfzh, 13, 2)) >= 1 AND CONVERT(int, substring(sfzh, 13, 2)) <= 28)) THEN datediff(yy,
substring(sfzh, 7, 8), GETDATE()) ELSE '-1' END AS NL, CONVERT(datetime,(SUBSTRING(g.ghrq,1,8) + ' ' + SUBSTRING(g.ghrq,9,8))) as ghrq, case when g.fzrq is null then null else CONVERT(datetime,(SUBSTRING(g.fzrq,1,8) + ' ' + SUBSTRING(g.fzrq,9,8))) end as fzrq
FROM HIS40.dbo.GH_NGHZDK AS g INNER JOIN
HIS40.dbo.SF_BRXXK AS b ON g.patid = b.patid
--打开游标
OPEN C1
FETCH NEXT FROM C1 INTO @ghxh,@cardno,@jssjh,@ksbm,@ysdm,@czyh,@ghlb,@sex,@age,@brq,@hrq;
解决方案 »
- 字符串的查找问题
- 请教数据库的设计准则
- 我想将一个表中的数据某一个字段的值,付给一个变量,有这样的语句吗?
- 急!无法删除Replication,求救阿(我在疑难版中也发了一次,该帖实际是120分,高手请帮忙啊)
- 菜鸟向高人请教:修改基本表的问题
- 求救ACCESS数据库字段分割统计
- 超急求——谁有ado中调用sql server 2000的源码例子 越简单越好 up给分
- ,急了,请问这个查询怎么写?
- 关于TEXT类型字段内容的显示,读取,修改问题!
- sql server r2 2008 安装完打不开,帮忙看一下
- 求一SQL语句,如何快速的展示简历详细信息,望大虾们多多关注~.~
- 关键字新闻查询?
--循环遍历ghzdk
begin
--判断是否有分诊。有的话就保存一条分诊等候时间==分诊日期-挂号日期
print getdate()
print @ghxh
if(@hrq is not NULL AND @hrq != '')
BEGIN
set @second = DATEDIFF(S,@brq,@hrq)--算出等候时间,将来要放判断是否为负,以及是否为第二天数据
--插入实体表
INSERT INTO DW.dbo.tmpWaitTime (ghxh,jssjh,czyh,czrq,ghlb,waittype,age,sex,sjd,ksbm,fyck,yplx,ysdm,waittime)
VALUES
(@ghxh,@jssjh,@czyh,@hrq,@ghlb,'分诊等候时间',@age,@sex,null,@ksbm,null,null,@ysdm,@second)
END
--初始化结算库以及划价处方库的临时表,以及表行号变量
truncate table #jsktmp
truncate table #hjcftmp
set @jskindex=1
set @hjcfkindex=1
set @mzcfkindex=1
--根据遍历的挂号序号,将SF_BRSJK中的收据信息存如临时表,并将记录数存入@rowjsk
insert into #jsktmp select sjh,czyh,CONVERT(datetime,(SUBSTRING(sfrq,1,8) + ' ' + SUBSTRING(sfrq,9,8))) as sfrq,sfksdm,ksdm,jlzt,CONVERT(datetime,(SUBSTRING(qrrq,1,8) + ' ' + SUBSTRING(qrrq,9,8))) as qrrq,ghsfbz from HIS40.dbo.SF_NBRJSK WHERE ghsjh=@jssjh order by sjh
select @rowjsk=@@ROWCOUNT--获得总记录数
--根据遍历的挂号序号,将SF_HJCFK中的处方信息存如临时表,并将记录数存入@rowhjcfk
insert into #hjcftmp select xh,czyh,CONVERT(datetime,(SUBSTRING(lrrq,1,8) + ' ' + SUBSTRING(lrrq,9,8))),ysdm,ksdm,sfksdm,yfdm,cflx from HIS40.dbo.SF_NHJCFK WHERE ghxh=@ghxh order by xh
select @rowhjcfk=@@ROWCOUNT--获得总记录数
--根据遍历的#tmpjsk,将SF_MZCFK中的处方信息存如临时表,并将记录数存入@rowmzcfk
truncate table #mzcftmp
insert into #mzcftmp select xh,jssjh,hjxh,cfxh,CONVERT(datetime,(SUBSTRING(lrrq,1,8) + ' ' + SUBSTRING(lrrq,9,8))),ysdm,ksdm,czyh,yfdm,CONVERT(datetime,(SUBSTRING(qrrq,1,8) + ' ' + SUBSTRING(qrrq,9,8))),qrksdm,cflx,fyckdm,hzxm from HIS40.dbo.SF_NMZCFK WHERE jssjh in (select sjh from #jsktmp) order by xh
select @rowmzcfk=@@ROWCOUNT
--循环遍历结算库数据
WHILE @jskindex<=@rowjsk
begin
--查询结算库,并赋值时间和收据号
select @jssjh=sjh,@ghsfbz=ghsfbz,@jssfrq=sfrq,@jsczyh=czyh from #jsktmp where jskid=@jskindex
--判断是否为挂号收据,如果是,就推算就诊时间,如果不是就不用推算就诊时间
if(@ghsfbz=0)
begin
--查询划价处方库和门诊处方库的第一条记录,判断哪个在先
select top 1 @mzcfksdm=ksdm,@mzcfysdm=ysdm,@mzcflrrq=lrrq from #mzcftmp
select top 1 @hjcfksdm=ksdm,@hjcfysdm=ysdm,@hjcflrrq = lrrq from #hjcftmp
if(DATEDIFF(S,@hjcflrrq,@mzcflrrq)>0)
begin
--判断是否存在分诊,如果有,就诊等候=划价录入日期-分诊日期,如果没有,就诊等候=划价录入日期-挂号日期
if(@hrq is not NULL)
begin
set @second = DATEDIFF(S,@hrq,@hjcflrrq)--算出等候时间,将来要放判断是否为负,以及是否为第二天数据
end
else
begin
set @second = DATEDIFF(S,@brq,@hjcflrrq)--算出等候时间,将来要放判断是否为负,以及是否为第二天数据
end
--插入就诊等候时间数据
INSERT INTO DW.dbo.tmpWaitTime (ghxh,jssjh,czyh,czrq,ghlb,waittype,age,sex,sjd,ksbm,fyck,yplx,ysdm,waittime)
VALUES
(@ghxh,@jssjh,null,@hjcflrrq,@ghlb,'就诊等候时间',@age,@sex,null,@hjcfksdm,null,null,@hjcfysdm,@second)
end
else
begin
--判断是否存在分诊,如果有,就诊等候=门诊录入日期-分诊日期,如果没有,就诊等候=门诊录入日期-挂号日期
if(@hrq is not NULL)
begin
set @second = DATEDIFF(S,@hrq,@mzcflrrq)--算出等候时间,将来要放判断是否为负,以及是否为第二天数据
end
else
begin
set @second = DATEDIFF(S,@brq,@mzcflrrq)--算出等候时间,将来要放判断是否为负,以及是否为第二天数据
end
--插入就诊等候时间数据
INSERT INTO DW.dbo.tmpWaitTime (ghxh,jssjh,czyh,czrq,ghlb,waittype,age,sex,sjd,ksbm,fyck,yplx,ysdm,waittime)
VALUES
(@ghxh,@jssjh,null,@mzcflrrq,@ghlb,'就诊等候时间',@age,@sex,null,@mzcfksdm,null,null,@mzcfysdm,@second)
end
end
--如果不是挂号收费,就开始计算排队收费等候时间
ELSE
begin
--根据遍历的#tmpjsk,将SF_MZCFK中的处方信息存如临时表,并将记录数存入@rowmzcfk
truncate table #mzcftmp
insert into #mzcftmp select xh,jssjh,hjxh,cfxh,CONVERT(datetime,(SUBSTRING(lrrq,1,8) + ' ' + SUBSTRING(lrrq,9,8))),ysdm,ksdm,czyh,yfdm,CONVERT(datetime,(SUBSTRING(qrrq,1,8) + ' ' + SUBSTRING(qrrq,9,8))),qrksdm,cflx,fyckdm,hzxm from HIS40.dbo.SF_NMZCFK WHERE jssjh =@jssjh order by xh
select @rowmzcfk=@@ROWCOUNT
--根据该结算号对应的最后一条的门诊处方寻找划价处方
select top 1 @tmptest=hjxh from #mzcftmp where LEN(hjxh)>5 order by mzcfid desc
select @hjcflrrq=lrrq from #hjcftmp where hjcfxh=(select top 1 hjxh from #mzcftmp where LEN(hjxh)>5 order by mzcfid desc)
if @@ROWCOUNT>0
begin
set @second=DATEDIFF(S,@hjcflrrq,@jssfrq)
--插入付费等候时间数据
INSERT INTO DW.dbo.tmpWaitTime (ghxh,jssjh,czyh,czrq,ghlb,waittype,age,sex,sjd,ksbm,fyck,yplx,ysdm,waittime)
VALUES
(@ghxh,@jssjh,@jsczyh,@jssfrq,@ghlb,'付费等候时间',@age,@sex,null,null,null,null,null,@second)
end
truncate table #qymzcftmp
set @qymzcfkindex=1
insert into #qymzcftmp select MAX(qrrq),fyck,hzxm from #mzcftmp where jssjh =@jssjh and cflx=1 group by cflx,fyck,hzxm
select @rowqymzcfk=@@ROWCOUNT
IF @rowqymzcfk>0
begin
WHILE @qymzcfkindex<=@rowqymzcfk
begin
select @mzcfqrrq=qrrq,@fyck=fyck from #qymzcftmp where qymzcfid=@qymzcfkindex
set @second=DATEDIFF(S,@jssfrq,@mzcfqrrq)
INSERT INTO DW.dbo.tmpWaitTime (ghxh,jssjh,czyh,czrq,ghlb,waittype,age,sex,sjd,ksbm,fyck,yplx,ysdm,waittime)
VALUES
(@ghxh,@jssjh,@jsczyh,@jssfrq,@ghlb,'取西药等候时间',@age,@sex,null,null,null,null,null,@second)
set @qymzcfkindex = @qymzcfkindex + 1
end
end
--取中成药的等候时间,根据发药窗口分组,得出多个取中成药数据中的最后一个
truncate table #qymzcftmp
set @qymzcfkindex=1
insert into #qymzcftmp select MAX(qrrq),fyck,hzxm from #mzcftmp where jssjh =@jssjh and cflx=2 group by cflx,fyck,hzxm
select @rowqymzcfk=@@ROWCOUNT
IF @rowqymzcfk>0
begin
WHILE @qymzcfkindex<=@rowqymzcfk
begin
select @mzcfqrrq=qrrq,@fyck=fyck from #qymzcftmp where qymzcfid=@qymzcfkindex
set @second=DATEDIFF(S,@jssfrq,@mzcfqrrq)
INSERT INTO DW.dbo.tmpWaitTime (ghxh,jssjh,czyh,czrq,ghlb,waittype,age,sex,sjd,ksbm,fyck,yplx,ysdm,waittime)
VALUES
(@ghxh,@jssjh,@jsczyh,@jssfrq,@ghlb,'取中成药等候时间',@age,@sex,null,null,null,null,null,@second)
set @qymzcfkindex = @qymzcfkindex + 1
end
end
--取草药的等候时间
truncate table #qymzcftmp
set @qymzcfkindex=1
insert into #qymzcftmp select MAX(qrrq),fyck,hzxm from #mzcftmp where jssjh =@jssjh and cflx=3 group by cflx,fyck,hzxm
select @rowqymzcfk=@@ROWCOUNT
IF @rowqymzcfk>0
begin
WHILE @qymzcfkindex<=@rowqymzcfk
begin
select @mzcfqrrq=qrrq,@fyck=fyck,@hzxm=hzxm from #qymzcftmp where qymzcfid=@qymzcfkindex
select @cyqysj=JZSJ FROM ODS.dbo.t_JZJL where BRXM=@hzxm and GHSJ=@mzcfqrrq and ZTBZ=1
if @@ROWCOUNT>0
begin
set @second=DATEDIFF(S,@jssfrq,@cyqysj)
INSERT INTO DW.dbo.tmpWaitTime (ghxh,jssjh,czyh,czrq,ghlb,waittype,age,sex,sjd,ksbm,fyck,yplx,ysdm,waittime)
VALUES
(@ghxh,@jssjh,@jsczyh,@jssfrq,@ghlb,'取中成药等候时间',@age,@sex,null,null,null,null,null,@second)
end
set @qymzcfkindex = @qymzcfkindex + 1
end
end
--获取检查等候时间
truncate table #qymzcftmp
set @qymzcfkindex=1
insert into #qymzcftmp select MAX(qrrq),yfdm,hzxm from #mzcftmp where jssjh =@jssjh and yfdm in ('4503','4101','4507','3272','4501','4502') and cflx in (4,5) group by cflx,yfdm,hzxm
select @rowqymzcfk=@@ROWCOUNT
IF @rowqymzcfk>0
begin
WHILE @qymzcfkindex<=@rowqymzcfk
begin
select top 1 @mzcfqrrq=qrrq,@hzxm=hzxm,@yfdm=fyck from #qymzcftmp where qymzcfid=@qymzcfkindex
select top 1 @mzcfxh=mzcfxh from #mzcftmp where qrrq = @mzcfqrrq and yfdm=@yfdm
if @@ROWCOUNT>0
begin
select @jcczy=convert(varchar,UserID),@jcrq=convert(datetime,ActionDate + ' ' + substring(ActionTime,1,2) + ':' + substring(ActionTime,3,2) + ':' + substring(ActionTime,5,2)) from Ris.dbo.RIS_ReportACTION where RequisitionID in (select RequisitionID from Ris.dbo.HISRequestFeeInfo where qqxh = convert(varchar,@mzcfxh)) and ActionNameID=3
if @@ROWCOUNT>0
begin
set @second=DATEDIFF(S,@jssfrq,@jcrq)
INSERT INTO DW.dbo.tmpWaitTime (ghxh,jssjh,czyh,czrq,ghlb,waittype,age,sex,sjd,ksbm,fyck,yplx,ysdm,waittime)
VALUES
(@ghxh,@jssjh,@jcczy,@jcrq,@ghlb,'检查等候时间',@age,@sex,null,@yfdm,null,null,null,@second)
end
ELSE
begin
select top 1 @jcczy=convert(varchar,UserID),@jcrq=convert(datetime,ActionDate + ' ' + substring(ActionTime,1,2) + ':' + substring (ActionTime,3,2) + ':' + substring(ActionTime,5,2)) from Ris.dbo.RIS_ReportACTION where RequisitionID in (select RequisitionID from Ris.dbo.HISRequestFeeInfo where qqxh = convert(varchar,@mzcfxh)) and ActionNameID in (1,2) order by ActionNameID
if @@ROWCOUNT>0
begin
set @second=DATEDIFF(S,@jssfrq,@jcrq)
INSERT INTO DW.dbo.tmpWaitTime (ghxh,jssjh,czyh,czrq,ghlb,waittype,age,sex,sjd,ksbm,fyck,yplx,ysdm,waittime)
VALUES
(@ghxh,@jssjh,@jcczy,@jcrq,@ghlb,'检查等候时间',@age,@sex,null,@yfdm,null,null,null,@second)
end
Else
begin
if @mzcfqrrq is not null
begin
set @second=DATEDIFF(S,@jssfrq,@mzcfqrrq)
INSERT INTO DW.dbo.tmpWaitTime (ghxh,jssjh,czyh,czrq,ghlb,waittype,age,sex,sjd,ksbm,fyck,yplx,ysdm, waittime) VALUES (@ghxh,@jssjh,null,@mzcfqrrq,@ghlb,'检查等候时间',@age,@sex,null,@yfdm,null,null,null,@second)
end
end
end
end
set @qymzcfkindex = @qymzcfkindex + 1
end
end
--取检验等候时间
truncate table #qymzcftmp
set @qymzcfkindex=1
insert into #qymzcftmp select MAX(qrrq),yfdm,hzxm from #mzcftmp where jssjh =@jssjh and yfdm in ('4301','4300','4304') and cflx in (4,5) group by cflx,yfdm,hzxm
select @rowqymzcfk=@@ROWCOUNT
IF @rowqymzcfk>0
begin
WHILE @qymzcfkindex<=@rowqymzcfk
begin
select top 1 @mzcfqrrq=qrrq,@hzxm=hzxm,@yfdm=fyck from #qymzcftmp where qymzcfid=@qymzcfkindex
select @jyrq=MAX(AcceptTime),@ksbm=ExeDept from DBLIS50.dbo.Lis_AcceptItems where HisXxh in (select convert(varchar,mzcfxh) from #mzcftmp where qrrq = @mzcfqrrq and yfdm=@yfdm) group by ExeDept
if @@ROWCOUNT>0
begin
set @second=DATEDIFF(S,@jssfrq,@jyrq)
INSERT INTO DW.dbo.tmpWaitTime (ghxh,jssjh,czyh,czrq,ghlb,waittype,age,sex,sjd,ksbm,fyck,yplx,ysdm,waittime)
VALUES
(@ghxh,@jssjh,@jcczy,@jyrq,@ghlb,'检验等候时间',@age,@sex,null,@ksbm,null,null,null,@second)
end
ELSE
begin
set @second=DATEDIFF(S,@jssfrq,@mzcfqrrq)
INSERT INTO DW.dbo.tmpWaitTime (ghxh,jssjh,czyh,czrq,ghlb,waittype,age,sex,sjd,ksbm,fyck,yplx,ysdm,waittime)
VALUES
(@ghxh,@jssjh,@jcczy,@mzcfqrrq,@ghlb,'检验等候时间',@age,@sex,null,@ksbm,null,null,null,@second)
end
set @qymzcfkindex = @qymzcfkindex + 1
end
end
end
SET @jskindex = @jskindex +1
end --jsk循环
FETCH NEXT FROM C1 INTO @ghxh,@cardno,@jssjh,@ksbm,@ysdm,@czyh,@ghlb,@sex,@age,@brq,@hrq;
end --挂号账单库 游标循环
CLOSE C1
DEALLOCATE C1
END