if not exists(select * from Report_Categories where code = 'HO.DocMedApply')
begin
declare @maxid int,@parentid int,@productid int
select @maxid=MAX(id) from Report_Categories
select @parentid=ID,@productid=productid from Report_Categories where Code='HIS'
insert into Report_Categories
select @maxid+1,@parentid,0,'HO.DocMedApply','医嘱审批',null,@productid
end
GO
if not exists(select * from Report_Categories where code = 'HO.DocMedApply.1')
begin
declare @maxid int,@parentid int,@productid int
select @maxid=MAX(id) from Report_Categories
select @parentid=ID,@productid=productid from Report_Categories where Code='HO.DocMedApply'
insert into Report_Categories
select @maxid+1,@parentid,0,'HO.DocMedApply.1','药师处方审核管理',null,@productid
end
GO
delete from Report_Objects where Code like 'HO.DocMedApply%' and CategoryID is null
delete from Report_Objects where Code like 'HO.DoctorMedicationApply%' and CategoryID is null
GO
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.DocMedApply001')
begin --插入
Declare @Report_Id_1664 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1664 out
Declare @Report_Id_1664_CateID int
Set @Report_Id_1664_CateID = (select id from Report_Categories where code='HO.DocMedApply')
insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval) Values(@Report_Id_1664, 'HO.DocMedApply001', '药师医嘱审核报表', '药师医嘱审核报表', Null, @Report_Id_1664_CateID, 1, 3, 100, 0, Null, '2018-08-18 17:19:47', '2018-08-18 17:19:47', Null, '
A017858744B5E6D96276668DBEFB74D3
select top 1 * from vaf1
select top 1 * from vaf1
', Null, 0, 1, Null, 0, Null)
end
else --更新
begin
print 'HO.DocMedApply001 已经存在.'
end
GO
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.DocMedApply.Q.001')
begin --插入
Declare @Report_Id_1665 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1665 out
Declare @Report_Id_1665_CateID int
Set @Report_Id_1665_CateID = (select id from Report_Categories where code='HO.DocMedApply')
insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval) Values(@Report_Id_1665, 'HO.DocMedApply.Q.001', '医嘱不通过列表', '医嘱不通过列表', Null, @Report_Id_1665_CateID, 1, 4, 100, 0, Null, '2018-08-20 09:15:14', '2018-08-20 09:15:14', Null, Null, Null, 0, 1, Null, 0, Null)
end
else --更新
begin
print 'HO.DocMedApply.Q.001 已经存在.'
end
GO
--删除旧记录
--delete from Report_Objects where Code='HO.DocMedApply.Q.002' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.DocMedApply.Q.002')
begin --插入
Declare @Report_Id_1839 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1839 out
Declare @Report_Id_1839_CateID int
Set @Report_Id_1839_CateID = (select id from Report_Categories where code='HO.DocMedApply')
insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval) Values(@Report_Id_1839, 'HO.DocMedApply.Q.002', '药师医嘱查询', '药师医嘱查询', Null, @Report_Id_1839_CateID, 1, 4, 100, 0, Null, '2019-06-25 09:23:03', '2019-06-25 09:24:44', Null, '
Declare @bdate datetime ,
@edate datetime ,
@vaa05 varchar(20)
set @bdate = :bdate
set @edate = :edate
set @vaa05=:vaa05
select distinct 0 FCheck,VFK01,a.ACF01,a.VAA01,VAA05,a.VAA07,a.CBM01,a.BCE01A,a.BCE03A,VFK08,a.BCE01B,a.BCE03B,VFK11,VFK12,VFK13,VFK14,c.CBM06,c.CBM07
from VFK1 a WITH(NOLOCK)
join VAA1 b WITH(NOLOCK) on a.VAA01 = b.VAA01
join CBM1 c WITH(NOLOCK) on a.CBM01 = c.CBM01
where VFK12 = 1 and (b.vaa05=@vaa05 or @vaa05='''')
and vfk11 between @bdate and @edate<?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid UseW="1"><Sql>select distinct vaa05,abbrp,abbrw from vaa1 where vaa03 is not null</Sql><HD><Ie FD="vaa05" CA="姓名" WD="100"/></HD><QF><Ie FD="vaa05" WE="7"/><Ie FD="abbrp" WE="7"/><Ie FD="abbrw" WE="7"/></QF></Grid></Configs>
', Null, 0, 1, Null, 0, Null)
end
else --更新
begin
print 'HO.DocMedApply.Q.002 已经存在.'
end
GO
--删除旧记录
--delete from Report_Objects where Code='HO.DocMedApply.MQTT.001' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.DocMedApply.MQTT.001')
begin --插入
Declare @Report_Id_1935 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1935 out
Declare @Report_Id_1935_CateID int
Set @Report_Id_1935_CateID = (select id from Report_Categories where code='HO.DocMedApply')
insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval) Values(@Report_Id_1935, 'HO.DocMedApply.MQTT.001', '医嘱审批通知', '调用消息系统的参数信息', Null, @Report_Id_1935_CateID, 1, 4, 100, 0, Null, '2019-12-26 13:40:57', '2019-12-26 13:40:57', Null, '
19A74CE68D859153737762B44EC28A2Edeclare @VAA01 int,@VAA07 int,@ACF01 int, @CBM01 int,@BCE01 int,@BCE03 varchar(64),@lMode int
set @VAA01 = %d
set @VAA07 = %d
set @ACF01 = %d
set @CBM01 = %d
set @BCE01 = %d
set @BCE03 = %s
set @lMode = %d
declare @BCE01S int,@BCK01S int
select @BCK01S = BCK01C,@BCE01S=BCE01 from CBM1 with(nolock) where CBM01 = @CBM01
select ''ProductID=100'' + char(13)+char(10) + ''ProgramID=130003'' + char(13)+char(10) +
''BCK01S='' + cast(@BCK01S as varchar(20))+ char(13)+char(10) + ''BCE01S='' +Cast(@BCE01S as varchar(64))+ char(13)+char(10) + ''Cmd='' + char(13)+char(10) +
''CmdStr=''
+ char(13)+char(10) +
''Title=医嘱审批管理'' + char(13)+char(10) + ''Content=处方审核不通过'' + char(13)+char(10) + ''ConfigID=2011'' + char(13)+char(10) +
''Remark='' + char(13)+char(10) + ''Tels='' Value
', Null, 0, 1, Null, 0, Null)
end
else --更新
begin
print 'HO.DocMedApply.MQTT.001 已经存在.'
end
GO
--删除旧记录
--delete from Report_Objects where Code='HO.DoctorMedicationApply.Q.001' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.DoctorMedicationApply.Q.001')
begin --插入
Declare @Report_Id_1661 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1661 out
Declare @Report_Id_1661_CateID int
Set @Report_Id_1661_CateID = (select id from Report_Categories where code='HO.DocMedApply.1')
insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval) Values(@Report_Id_1661, 'HO.DoctorMedicationApply.Q.001', '待审核列表', '医嘱待审核列表', Null, @Report_Id_1661_CateID, 1, 3, 100, 0, Null, '2018-08-18 09:25:03', '2018-08-18 09:44:51', Null, '
E28898232F3CA292B2879B4014227A76
IF Object_id(''tempdb..#tempVFK1'') IS NOT NULL DROP TABLE #tempVFK1
IF Object_id(''tempdb..#tempVAF1_LF'') IS NOT NULL DROP TABLE #tempVAF1_LF
select distinct 0 FCheck, VFK01,a.ACF01,a.VAA01,VAA05,a.VAA07,a.CBM01,a.BCE01A,a.BCE03A,VFK08,a.BCE01B,a.BCE03B
,VFK11,VFK12,cast('''' as varchar(256)) VFK13,VFK14,c.CBM06,c.CBM07,d.VAC10,d.BCK01A BCK01, f.BCK03 BCK03
into #tempVFK1
from VFK1 a WITH(NOLOCK)
join VAA1 b WITH(NOLOCK) on a.VAA01 = b.VAA01
join CBM1 c WITH(NOLOCK) on a.CBM01 = c.CBM01
join VAC1 d with(nolock) on a.VAA07 = d.VAC01
join BCK1 f with(nolock) on d.BCK01A = f.BCK01
where VFK12 = 0 and isnull(c.DFlag,0) = 0
order by d.VAC10 desc ,a.VAA07,VFK08
select a.CBM01,a.VAF01,a.VAF22,a.VAF26,a.BDA01,a.VAF59,a.rownr
into #tempVAF1_LF
from VAF1 a with(nolock)
join #tempVFK1 b with(nolock) on a.CBM01 = b.CBM01
select s.VAF22 VAF22,s.isGroup, *
from #tempVFK1 v
join (
select a.cbm01, VAF22=
CASE
WHEN COUNT(*)> 2 then ''[多]''+ CAST((select top 1 b.VAF22 from #tempVAF1_LF b where a.CBM01 = b.CBM01 and b.ROWNR = 1 and b.VAF59 = 1) as varchar)+ ''...''+CAST((select top 1 c.VAF22+''/''+c.VAF26 from #tempVAF1_LF c where a.CBM01 = c.CBM01 and c.ROWNR = 0 and c.VAF59 = 1) as varchar)
WHEN COUNT(*)= 2 then CAST((select top 1 b.VAF22 from #tempVAF1_LF b where a.CBM01 = b.CBM01 and b.ROWNR = 1) as varchar)+'' ''+CAST((select top 1 d.VAF22 +''/''+d.VAF26 from #tempVAF1_LF d where a.CBM01 = d.CBM01 and d.ROWNR = 0) as varchar)
else
null
END
,case when COUNT(*)> 2 then 1 else 0 end isGroup
from #tempVAF1_LF a
where a.BDA01 in (''1'',''2'',''3'',''T'')
group by cbm01
union all
select a.cbm01, VAF22= CAST((select top 1 b.VAF22 from #tempVAF1_LF b where a.CBM01 = b.CBM01 and b.ROWNR = 0 and b.VAF59 = 1) as varchar)+
CASE
WHEN COUNT(*)> 2 then '' ''+CAST((select top 1 c.VAF22+''/''+c.VAF26 from #tempVAF1_LF c where a.CBM01 = c.CBM01 and c.ROWNR = 0 and c.VAF59 = 2) as varchar)+''...''
ELSE '' ''+CAST((select top 1 d.VAF22 +''/''+d.VAF26 from #tempVAF1_LF d where a.CBM01 = d.CBM01 and d.ROWNR = 0 and d.VAF59 = 2) as varchar)
END
,0 isGroup
from #tempVAF1_LF a
where a.BDA01 not in (''1'',''2'',''3'',''T'')
group by cbm01
) s on v.CBM01 = s.CBM01
where s.VAF22 is not null
IF Object_id(''tempdb..#tempVFK1'') IS NOT NULL DROP TABLE #tempVFK1
IF Object_id(''tempdb..#tempVAF1_LF'') IS NOT NULL DROP TABLE #tempVAF1_LF
', Null, 0, 1, Null, 0, Null)
end
else --更新
begin
print 'HO.DoctorMedicationApply.Q.001 已经存在.'
end
GO
--删除旧记录
--delete from Report_Objects where Code='HO.DoctorMedicationApply.Q.002' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.DoctorMedicationApply.Q.002')
begin --插入
Declare @Report_Id_1662 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1662 out
Declare @Report_Id_1662_CateID int
Set @Report_Id_1662_CateID = (select id from Report_Categories where code='HO.DocMedApply.1')
insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval) Values(@Report_Id_1662, 'HO.DoctorMedicationApply.Q.002', '审核通过列表', '审核通过列表', Null, @Report_Id_1662_CateID, 1, 3, 100, 0, Null, '2018-08-18 09:26:06', '2018-08-18 09:45:10', Null, '
664DEB9BBB8456801265A8E7751BB514
IF Object_id(''tempdb..#tempVFK1'') IS NOT NULL DROP TABLE #tempVFK1
IF Object_id(''tempdb..#tempVAF1_LF'') IS NOT NULL DROP TABLE #tempVAF1_LF
select distinct 0 FCheck, VFK01,a.ACF01,a.VAA01,VAA05,a.VAA07,a.CBM01,a.BCE01A,a.BCE03A,VFK08,a.BCE01B,a.BCE03B
,VFK11,VFK12,cast('''' as varchar(256)) VFK13,VFK14,c.CBM06,c.CBM07,d.VAC10,d.BCK01A BCK01, f.BCK03 BCK03
into #tempVFK1
from VFK1 a WITH(NOLOCK)
join VAA1 b WITH(NOLOCK) on a.VAA01 = b.VAA01
join CBM1 c WITH(NOLOCK) on a.CBM01 = c.CBM01
join VAC1 d with(nolock) on a.VAA07 = d.VAC01
join BCK1 f with(nolock) on d.BCK01A = f.BCK01
where VFK12 = 1 and isnull(c.DFlag,0) = 0
order by d.VAC10 desc ,a.VAA07,VFK08
select a.CBM01,a.VAF01,a.VAF22,a.VAF26,a.BDA01,a.VAF59,a.rownr
into #tempVAF1_LF
from VAF1 a with(nolock)
join #tempVFK1 b with(nolock) on a.CBM01 = b.CBM01
select s.VAF22 VAF22, *
from #tempVFK1 v
join (
select a.cbm01, VAF22=
CASE
WHEN COUNT(*)> 2 then ''[多]''+ CAST((select top 1 b.VAF22 from #tempVAF1_LF b where a.CBM01 = b.CBM01 and b.ROWNR = 1 and b.VAF59 = 1) as varchar)+ ''...''+CAST((select top 1 c.VAF22+''/''+c.VAF26 from #tempVAF1_LF c where a.CBM01 = c.CBM01 and c.ROWNR = 0 and c.VAF59 = 1) as varchar)
WHEN COUNT(*)= 2 then CAST((select top 1 b.VAF22 from #tempVAF1_LF b where a.CBM01 = b.CBM01 and b.ROWNR = 1) as varchar)+'' ''+CAST((select top 1 d.VAF22 +''/''+d.VAF26 from #tempVAF1_LF d where a.CBM01 = d.CBM01 and d.ROWNR = 0) as varchar)
else
null
END
from #tempVAF1_LF a
where a.BDA01 in (''1'',''2'',''3'',''T'')
group by cbm01
union all
select a.cbm01, VAF22= CAST((select top 1 b.VAF22 from #tempVAF1_LF b where a.CBM01 = b.CBM01 and b.ROWNR = 0 and b.VAF59 = 1) as varchar)+
CASE
WHEN COUNT(*)> 2 then '' ''+CAST((select top 1 c.VAF22+''/''+c.VAF26 from #tempVAF1_LF c where a.CBM01 = c.CBM01 and c.ROWNR = 0 and c.VAF59 = 2) as varchar)+''...''
ELSE '' ''+CAST((select top 1 d.VAF22 +''/''+d.VAF26 from #tempVAF1_LF d where a.CBM01 = d.CBM01 and d.ROWNR = 0 and d.VAF59 = 2) as varchar)
END
from #tempVAF1_LF a
where a.BDA01 not in (''1'',''2'',''3'',''T'')
group by cbm01
) s on v.CBM01 = s.CBM01
where s.VAF22 is not null
IF Object_id(''tempdb..#tempVFK1'') IS NOT NULL DROP TABLE #tempVFK1
IF Object_id(''tempdb..#tempVAF1_LF'') IS NOT NULL DROP TABLE #tempVAF1_LF
', Null, 0, 1, Null, 0, Null)
end
else --更新
begin
print 'HO.DoctorMedicationApply.Q.002 已经存在.'
end
GO
--删除旧记录
--delete from Report_Objects where Code='HO.DoctorMedicationApply.Q.003' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.DoctorMedicationApply.Q.003')
begin --插入
Declare @Report_Id_1663 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1663 out
Declare @Report_Id_1663_CateID int
Set @Report_Id_1663_CateID = (select id from Report_Categories where code='HO.DocMedApply.1')
insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval) Values(@Report_Id_1663, 'HO.DoctorMedicationApply.Q.003', '审核不通过列表', '审核不通过列表', Null, @Report_Id_1663_CateID, 1, 3, 100, 0, Null, '2018-08-18 09:26:45', '2018-08-18 09:44:34', Null, '
DB664592FB67490CF3C192829041447F
IF Object_id(''tempdb..#tempVFK1'') IS NOT NULL DROP TABLE #tempVFK1
IF Object_id(''tempdb..#tempVAF1_LF'') IS NOT NULL DROP TABLE #tempVAF1_LF
select distinct 0 FCheck, VFK01,a.ACF01,a.VAA01,VAA05,a.VAA07,a.CBM01,a.BCE01A,a.BCE03A,VFK08,a.BCE01B,a.BCE03B
,VFK11,VFK12,VFK13,VFK14,c.CBM06,c.CBM07,d.VAC10,d.BCK01A BCK01, f.BCK03 BCK03
into #tempVFK1
from VFK1 a WITH(NOLOCK)
join VAA1 b WITH(NOLOCK) on a.VAA01 = b.VAA01
join CBM1 c WITH(NOLOCK) on a.CBM01 = c.CBM01
join VAC1 d with(nolock) on a.VAA07 = d.VAC01
join BCK1 f with(nolock) on d.BCK01A = f.BCK01
where VFK12 = 2 and isnull(c.DFlag,0) = 0
order by d.VAC10 desc ,a.VAA07,VFK08
select a.CBM01,a.VAF01,a.VAF22,a.VAF26,a.BDA01,a.VAF59,a.rownr
into #tempVAF1_LF
from VAF1 a with(nolock)
join #tempVFK1 b with(nolock) on a.CBM01 = b.CBM01
select s.VAF22 VAF22, *
from #tempVFK1 v
join (
select a.cbm01, VAF22=
CASE
WHEN COUNT(*)> 2 then ''[多]''+ CAST((select top 1 b.VAF22 from #tempVAF1_LF b where a.CBM01 = b.CBM01 and b.ROWNR = 1 and b.VAF59 = 1) as varchar)+ ''...''+CAST((select top 1 c.VAF22+''/''+c.VAF26 from #tempVAF1_LF c where a.CBM01 = c.CBM01 and c.ROWNR = 0 and c.VAF59 = 1) as varchar)
WHEN COUNT(*)= 2 then CAST((select top 1 b.VAF22 from #tempVAF1_LF b where a.CBM01 = b.CBM01 and b.ROWNR = 1) as varchar)+'' ''+CAST((select top 1 d.VAF22 +''/''+d.VAF26 from #tempVAF1_LF d where a.CBM01 = d.CBM01 and d.ROWNR = 0) as varchar)
else
null
END
from #tempVAF1_LF a
where a.BDA01 in (''1'',''2'',''3'',''T'')
group by cbm01
union all
select a.cbm01, VAF22= CAST((select top 1 b.VAF22 from #tempVAF1_LF b where a.CBM01 = b.CBM01 and b.ROWNR = 0 and b.VAF59 = 1) as varchar)+
CASE
WHEN COUNT(*)> 2 then '' ''+CAST((select top 1 c.VAF22+''/''+c.VAF26 from #tempVAF1_LF c where a.CBM01 = c.CBM01 and c.ROWNR = 0 and c.VAF59 = 2) as varchar)+''...''
ELSE '' ''+CAST((select top 1 d.VAF22 +''/''+d.VAF26 from #tempVAF1_LF d where a.CBM01 = d.CBM01 and d.ROWNR = 0 and d.VAF59 = 2) as varchar)
END
from #tempVAF1_LF a
where a.BDA01 not in (''1'',''2'',''3'',''T'')
group by cbm01
) s on v.CBM01 = s.CBM01
where s.VAF22 is not null
IF Object_id(''tempdb..#tempVFK1'') IS NOT NULL DROP TABLE #tempVFK1
IF Object_id(''tempdb..#tempVAF1_LF'') IS NOT NULL DROP TABLE #tempVAF1_LF
', Null, 0, 1, Null, 0, Null)
end
else --更新
begin
print 'HO.DoctorMedicationApply.Q.003 已经存在.'
end
GO
--删除旧记录
--delete from Report_Objects where Code='HO.DoctorMedicationApply.Q.004' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.DoctorMedicationApply.Q.004')
begin --插入
Declare @Report_Id_1703 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1703 out
Declare @Report_Id_1703_CateID int
Set @Report_Id_1703_CateID = (select id from Report_Categories where code='HO.DocMedApply.1')
insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval) Values(@Report_Id_1703, 'HO.DoctorMedicationApply.Q.004', '强制通过列表', '医嘱强制发送列表', Null, @Report_Id_1703_CateID, 1, 3, 100, 0, Null, '2018-10-28 17:05:56', '2018-10-28 17:06:57', Null, '
78B8538D38C28C86B2D4BB0BF07066E2
IF Object_id(''tempdb..#tempVFK1'') IS NOT NULL DROP TABLE #tempVFK1
IF Object_id(''tempdb..#tempVAF1_LF'') IS NOT NULL DROP TABLE #tempVAF1_LF
select distinct 0 FCheck, VFK01,a.ACF01,a.VAA01,VAA05,a.VAA07,a.CBM01,a.BCE01A,a.BCE03A,VFK08,a.BCE01B,a.BCE03B
,VFK11,VFK12,VFK13,VFK14,c.CBM06,c.CBM07,d.VAC10,d.BCK01A BCK01, f.BCK03 BCK03
into #tempVFK1
from VFK1 a WITH(NOLOCK)
join VAA1 b WITH(NOLOCK) on a.VAA01 = b.VAA01
join CBM1 c WITH(NOLOCK) on a.CBM01 = c.CBM01
join VAC1 d with(nolock) on a.VAA07 = d.VAC01
join BCK1 f with(nolock) on d.BCK01A = f.BCK01
where VFK12 = 3 and isnull(c.DFlag,0) = 0
order by d.VAC10 desc ,a.VAA07,VFK08
select a.CBM01,a.VAF01,a.VAF22,a.VAF26,a.BDA01,a.VAF59,a.rownr
into #tempVAF1_LF
from VAF1 a with(nolock)
join #tempVFK1 b with(nolock) on a.CBM01 = b.CBM01
select s.VAF22 VAF22, *
from #tempVFK1 v
join (
select a.cbm01, VAF22=
CASE
WHEN COUNT(*)> 2 then ''[多]''+ CAST((select b.VAF22 from #tempVAF1_LF b where a.CBM01 = b.CBM01 and b.ROWNR = 1 and b.VAF59 = 1) as varchar)+ ''...''+CAST((select top 1 c.VAF22+''/''+c.VAF26 from #tempVAF1_LF c where a.CBM01 = c.CBM01 and c.ROWNR = 0 and c.VAF59 = 1) as varchar)
WHEN COUNT(*)= 2 then CAST((select b.VAF22 from #tempVAF1_LF b where a.CBM01 = b.CBM01 and b.ROWNR = 1) as varchar)+'' ''+CAST((select top 1 d.VAF22 +''/''+d.VAF26 from #tempVAF1_LF d where a.CBM01 = d.CBM01 and d.ROWNR = 0) as varchar)
else
null
END
from #tempVAF1_LF a
where a.BDA01 in (''1'',''2'',''3'',''T'')
group by cbm01
union all
select a.cbm01, VAF22= CAST((select b.VAF22 from #tempVAF1_LF b where a.CBM01 = b.CBM01 and b.ROWNR = 0 and b.VAF59 = 1) as varchar)+
CASE
WHEN COUNT(*)> 2 then '' ''+CAST((select top 1 c.VAF22+''/''+c.VAF26 from #tempVAF1_LF c where a.CBM01 = c.CBM01 and c.ROWNR = 0 and c.VAF59 = 2) as varchar)+''...''
ELSE '' ''+CAST((select top 1 d.VAF22 +''/''+d.VAF26 from #tempVAF1_LF d where a.CBM01 = d.CBM01 and d.ROWNR = 0 and d.VAF59 = 2) as varchar)
END
from #tempVAF1_LF a
where a.BDA01 not in (''1'',''2'',''3'',''T'')
group by cbm01
) s on v.CBM01 = s.CBM01
where s.VAF22 is not null
IF Object_id(''tempdb..#tempVFK1'') IS NOT NULL DROP TABLE #tempVFK1
IF Object_id(''tempdb..#tempVAF1_LF'') IS NOT NULL DROP TABLE #tempVAF1_LF
', Null, 0, Null, Null, 0, Null)
end
else --更新
begin
print 'HO.DoctorMedicationApply.Q.004 已经存在.'
end
GO