Полная Версия: Самый большой запрос/сложный
| Цитата (sergeiss @ 11.12.2011 - 23:33) |
| Выполняется каждый запрос порядка 30-40 секунд |
| Цитата (sergeiss @ 3.02.2012 - 16:20) |
Предыдущий большой запросец (из этой темы) "немного" разросся  , реструктурировался и стал таким: |
После проведения оптимизации БД запрос стал выполняться не 30-40 секунд, а всего 4-5. А именно, были сделаны корректные партиции (partition). Данные были раздроблены на большое количество частей, в обработку вызываются одна или две, не более. В которых содержится не так уж и много данных - всего-то 3-4-5 миллионов в каждой
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
| Цитата |
| всего-то 3-4-5 миллионов в каждой |
да.всего-то..
рука лицо, лицо в яйцо, яйцо в утку, утка в зайца - заяц в шоке!
ps/ подумал я тут и понял, что в sql я знаю только .... эм.. ну это... как его.. короче нифига не знаю
_____________
[FAQ]Регистрации пользователей, сохранение в БД
---------------------------------------------------------------------------
Выходя из ванной, вышел из нее два раза
да уж, когда тема началась - ничего общего с ней не имел, но сейчас это моя работа ...
в постоянку хп такого вида
_____________
HTML, CSS (Bootstrap), JS(JQuery, ExtJS), PHP, MySQL, MSSql, Posgres, (TSql, BI OLAP, MDX), Mongo, Git, SVN, CodeIgnater, Symfony, Yii 2, JiRA, Redmine, Bitbucket, Composer, Rabbit MQ, Amazon (SQS, S3, Transcribe), Docker
в постоянку хп такого вида
Свернутый текст
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [d6].[p_Reg_Any_pay]
(@Reg_id bigint,
@RegeRType_id bigint,
@eR_Code int = null output,
@eR_Tessage varchar(4000) = null output)
as
set nocount on
begin try
if (Select RegType_id From d6.Reg with (nolock) where Reg_id = @Reg_id) not in(1,2)
begin
return
end
if (Select RegType_id From d6.Reg with (nolock) where Reg_id = @Reg_id) = 1
begin
insert into d6.RegeREdnPS with (ROWLOCK) (Reg_id
,E_id
,RegeRType_id
,BOSSection_id
,pUs_insID
,pUs_updID
,RegeR_insDT
,RegeR_updDT
,comments
)
select distinct
@Reg_id as Reg_id,
RFG.E_id as E_id,
@RegeRType_id as RegeRType_id,
RFG.BOSSection_id as BOSSection_id,
RFG.pUs_insID as pUs_insID,
RFG.pUs_updID as pUs_updID,
GETDATE() as RegeR_insDT,
GETDATE() as RegeR_updDT
,'ДКО не финансируется по ОМС'as Tesstext
from d6.Reg r with (nolock)
inner join d6.v_RegData RFG with (nolock) on R.Reg_id = RFG.Reg_id
inner join v_EdnSection EdnSection with (nolock) on EdnSection.EdnSection_id = RFG.E_id
inner join v_ClientState Client with (NOLOCK) on Client.Client_id = RFG.Client_id
left join v_Polis Polis with (NOLOCK) on Polis.Polis_id = Client.Polis_id
left join TesOld with (nolock) on TesOld.Tes_id = EdnSection.Tes_id
left join v_BOSUnitSet BOSUnitSet with (nolock) on BOSUnitSet.BOSUnitSet_id = R.BOSUnitSet_id
inner join dbo.DiagFinance DF with (nolock) on RFG.Diag_id = DF.Diag_id
inner join v_ClientState PS with (NOLOCK) on PS.Client_id = RFG.Client_id
inner join v_EdnPS EdnPS with (NOLOCK) on EdnPS.EdnPS_id = RFG.Edn_rid
inner join Diag D with (nolock) on D.Diag_id = DF.Diag_id
left join v_ClientState ps2 with (NOLOCK) on ps2.Client_id = (case when (EdnPS.Client_age=0 and (dateadd(month, 3, PS.Client_BirthDay)>RFG.Edn_disDate)) then RFG.Client_id else 0 end)
left join ClientAgeGroup PA with (nolock) on PA.ClientAgeGroup_id = DF.ClientAgeGroup_id
where r.Reg_id = @Reg_id
and TesOld.Tes_Code is null
and
DF.DiagFinance_IsOms = 1
insert into d6.RegeREdnPS with (ROWLOCK) (Reg_id
,E_id
,RegeRType_id
,BOSSection_id
,pUs_insID
,pUs_updID
,RegeR_insDT
,RegeR_updDT
,comments
)
select distinct
@Reg_id as Reg_id,
RFG.E_id as E_id,
@RegeRType_id as RegeRType_id,
RFG.BOSSection_id as BOSSection_id,
RFG.pUs_insID as pUs_insID,
RFG.pUs_updID as pUs_updID,
GETDATE() as RegeR_insDT,
GETDATE() as RegeR_updDT
,'ДКО финансируется только для детей'as Tesstext
from d6.Reg r with (nolock)
inner join d6.v_RegData RFG with (nolock) on R.Reg_id = RFG.Reg_id
inner join v_EdnSection EdnSection with (nolock) on EdnSection.EdnSection_id = RFG.E_id
inner join v_ClientState Client with (NOLOCK) on Client.Client_id = RFG.Client_id
left join v_Polis Polis with (NOLOCK) on Polis.Polis_id = Client.Polis_id
left join TesOld with (nolock) on TesOld.Tes_id = EdnSection.Tes_id
left join v_BOSUnitSet BOSUnitSet with (nolock) on BOSUnitSet.BOSUnitSet_id = R.BOSUnitSet_id
inner join dbo.DiagFinance DF with (nolock) on RFG.Diag_id = DF.Diag_id
inner join v_ClientState PS with (NOLOCK) on PS.Client_id = RFG.Client_id
inner join v_EdnPS EdnPS with (NOLOCK) on EdnPS.EdnPS_id = RFG.Edn_rid
inner join Diag D with (nolock) on D.Diag_id = DF.Diag_id
left join v_ClientState ps2 with (NOLOCK) on ps2.Client_id = (case when (EdnPS.Client_age=0 and (dateadd(month, 3, PS.Client_BirthDay)>RFG.Edn_disDate)) then RFG.Client_id else 0 end)
left join ClientAgeGroup PA with (nolock) on PA.ClientAgeGroup_id = DF.ClientAgeGroup_id
where r.Reg_id = @Reg_id
and TesOld.Tes_Code is null
and
isnull(DF.ClientAgeGroup_id,1) = 2
and [dbo].[Age](
case when ps2.Client_id is null then RTrim(IsNull(convert(varchar(10), cast(PS.Client_BirthDay as datetime), 120), null))
else RTrim(IsNull(convert(varchar(10), cast(ps2.Client_BirthDay as datetime), 120), Null)) end,
RFG.Edn_setDate) > 18
insert into d6.RegeREdnPS with (ROWLOCK) (Reg_id
,E_id
,RegeRType_id
,BOSSection_id
,pUs_insID
,pUs_updID
,RegeR_insDT
,RegeR_updDT
,comments
)
select distinct
@Reg_id as Reg_id,
RFG.E_id as E_id,F
@RegeRType_id as RegeRType_id,
RFG.BOSSection_id as BOSSection_id,
RFG.pUs_insID as pUs_insID,
RFG.pUs_updID as pUs_updID,
GETDATE() as RegeR_insDT,
GETDATE() as RegeR_updDT
,'ДКО финансируется только для взрослых'as Tesstext
from d6.Reg r with (nolock)
inner join d6.v_RegData RFG with (nolock) on R.Reg_id = RFG.Reg_id
inner join v_EdnSection EdnSection with (nolock) on EdnSection.EdnSection_id = RFG.E_id
inner join v_ClientState Client with (NOLOCK) on Client.Client_id = RFG.Client_id
left join v_Polis Polis with (NOLOCK) on Polis.Polis_id = Client.Polis_id
left join TesOld with (nolock) on TesOld.Tes_id = EdnSection.Tes_id
left join v_BOSUnitSet BOSUnitSet with (nolock) on BOSUnitSet.BOSUnitSet_id = R.BOSUnitSet_id
inner join dbo.DiagFinance DF with (nolock) on RFG.Diag_id = DF.Diag_id
inner join v_ClientState PS with (NOLOCK) on PS.Client_id = RFG.Client_id
inner join v_EdnPS EdnPS with (NOLOCK) on EdnPS.EdnPS_id = RFG.Edn_rid
inner join Diag D with (nolock) on D.Diag_id = DF.Diag_id
left join v_ClientState ps2 with (NOLOCK) on ps2.Client_id = (case when (EdnPS.Client_age=0 and (dateadd(month, 3, PS.Client_BirthDay)>RFG.Edn_disDate)) then RFG.Client_id else 0 end)
left join ClientAgeGroup PA with (nolock) on PA.ClientAgeGroup_id = DF.ClientAgeGroup_id
where r.Reg_id = @Reg_id
and TesOld.Tes_Code is null
and
isnull(DF.ClientAgeGroup_id,1) = 1
and [dbo].[Age](
case when ps2.Client_id is null then RTrim(IsNull(convert(varchar(10), cast(PS.Client_BirthDay as datetime), 120), null))
else RTrim(IsNull(convert(varchar(10), cast(ps2.Client_BirthDay as datetime), 120), Null)) end,
RFG.Edn_setDate) < 18
insert into d6.RegeREdnPS with (ROWLOCK) (Reg_id
,E_id
,RegeRType_id
,BOSSection_id
,pUs_insID
,pUs_updID
,RegeR_insDT
,RegeR_updDT
,comments
)
select distinct
@Reg_id as Reg_id,
RFG.E_id as E_id,
@RegeRType_id as RegeRType_id,
RFG.BOSSection_id as BOSSection_id,
RFG.pUs_insID as pUs_insID,
RFG.pUs_updID as pUs_updID,
GETDATE() as RegeR_insDT,
GETDATE() as RegeR_updDT
,'ДКО финансируется только для жителей РБ'as Tesstext
from d6.Reg r with (nolock)
inner join d6.v_RegData RFG with (nolock) on R.Reg_id = RFG.Reg_id
inner join v_EdnSection EdnSection with (nolock) on EdnSection.EdnSection_id = RFG.E_id
inner join v_ClientState Client with (NOLOCK) on Client.Client_id = RFG.Client_id
left join v_Polis Polis with (NOLOCK) on Polis.Polis_id = Client.Polis_id
left join TesOld with (nolock) on TesOld.Tes_id = EdnSection.Tes_id
left join v_BOSUnitSet BOSUnitSet with (nolock) on BOSUnitSet.BOSUnitSet_id = R.BOSUnitSet_id
inner join dbo.DiagFinance DF with (nolock) on RFG.Diag_id = DF.Diag_id
inner join v_ClientState PS with (NOLOCK) on PS.Client_id = RFG.Client_id
inner join v_EdnPS EdnPS with (NOLOCK) on EdnPS.EdnPS_id = RFG.Edn_rid
inner join Diag D with (nolock) on D.Diag_id = DF.Diag_id
left join v_ClientState ps2 with (NOLOCK) on ps2.Client_id = (case when (EdnPS.Client_age=0 and (dateadd(month, 3, PS.Client_BirthDay)>RFG.Edn_disDate)) then RFG.Client_id else 0 end)
left join ClientAgeGroup PA with (nolock) on PA.ClientAgeGroup_id = DF.ClientAgeGroup_id
where r.Reg_id = @Reg_id
and TesOld.Tes_Code is null
and
DF.DiagFinance_IsAlien = 1 and RFG.OrgCMO_id= 8
and (substring(TesOld.Tes_Code,2,2) != '94' or RIGHT(TesOld.Tes_Code,3) not in ('910', '915') or D.Diag_Code != 'Z00.8')
insert into d6.RegeREdnPS with (ROWLOCK) (Reg_id
,E_id
,RegeRType_id
,BOSSection_id
,pUs_insID
,pUs_updID
,RegeR_insDT
,RegeR_updDT
,comments
)
select distinct
@Reg_id as Reg_id,
RFG.E_id as E_id,
@RegeRType_id as RegeRType_id,
RFG.BOSSection_id as BOSSection_id,
RFG.pUs_insID as pUs_insID,
RFG.pUs_updID as pUs_updID,
GETDATE() as RegeR_insDT,
GETDATE() as RegeR_updDT
,'ДКО финансируется только для мужчин'as Tesstext
from d6.Reg r with (nolock)
inner join d6.v_RegData RFG with (nolock) on R.Reg_id = RFG.Reg_id
inner join v_EdnSection EdnSection with (nolock) on EdnSection.EdnSection_id = RFG.E_id
inner join v_ClientState Client with (NOLOCK) on Client.Client_id = RFG.Client_id
left join v_Polis Polis with (NOLOCK) on Polis.Polis_id = Client.Polis_id
left join TesOld with (nolock) on TesOld.Tes_id = EdnSection.Tes_id
left join v_BOSUnitSet BOSUnitSet with (nolock) on BOSUnitSet.BOSUnitSet_id = R.BOSUnitSet_id
inner join dbo.DiagFinance DF with (nolock) on RFG.Diag_id = DF.Diag_id
inner join v_ClientState PS with (NOLOCK) on PS.Client_id = RFG.Client_id
inner join v_EdnPS EdnPS with (NOLOCK) on EdnPS.EdnPS_id = RFG.Edn_rid
inner join Diag D with (nolock) on D.Diag_id = DF.Diag_id
left join v_ClientState ps2 with (NOLOCK) on ps2.Client_id = (case when (EdnPS.Client_age=0 and (dateadd(month, 3, PS.Client_BirthDay)>RFG.Edn_disDate)) then RFG.Client_id else 0 end)
left join ClientAgeGroup PA with (nolock) on PA.ClientAgeGroup_id = DF.ClientAgeGroup_id
where r.Reg_id = @Reg_id
and TesOld.Tes_Code is null
and
PS.Sex_id = 2 and DF.Sex_id = 1
insert into d6.RegeREdnPS with (ROWLOCK) (Reg_id
,E_id
,RegeRType_id
,BOSSection_id
,pUs_insID
,pUs_updID
,RegeR_insDT
,RegeR_updDT
,comments
)
select distinct
@Reg_id as Reg_id,
RFG.E_id as E_id,
@RegeRType_id as RegeRType_id,
RFG.BOSSection_id as BOSSection_id,
RFG.pUs_insID as pUs_insID,
RFG.pUs_updID as pUs_updID,
GETDATE() as RegeR_insDT,
GETDATE() as RegeR_updDT
,'ДКО финансируется только для женщин'as Tesstext
from d6.Reg r with (nolock)
inner join d6.v_RegData RFG with (nolock) on R.Reg_id = RFG.Reg_id
inner join v_EdnSection EdnSection with (nolock) on EdnSection.EdnSection_id = RFG.E_id
inner join v_ClientState Client with (NOLOCK) on Client.Client_id = RFG.Client_id
left join v_Polis Polis with (NOLOCK) on Polis.Polis_id = Client.Polis_id
left join TesOld with (nolock) on TesOld.Tes_id = EdnSection.Tes_id
left join v_BOSUnitSet BOSUnitSet with (nolock) on BOSUnitSet.BOSUnitSet_id = R.BOSUnitSet_id
inner join dbo.DiagFinance DF with (nolock) on RFG.Diag_id = DF.Diag_id
inner join v_ClientState PS with (NOLOCK) on PS.Client_id = RFG.Client_id
inner join v_EdnPS EdnPS with (NOLOCK) on EdnPS.EdnPS_id = RFG.Edn_rid
inner join Diag D with (nolock) on D.Diag_id = DF.Diag_id
left join v_ClientState ps2 with (NOLOCK) on ps2.Client_id = (case when (EdnPS.Client_age=0 and (dateadd(month, 3, PS.Client_BirthDay)>RFG.Edn_disDate)) then RFG.Client_id else 0 end)
left join ClientAgeGroup PA with (nolock) on PA.ClientAgeGroup_id = DF.ClientAgeGroup_id
where r.Reg_id = @Reg_id
and TesOld.Tes_Code is null
and
PS.Sex_id = 1 and DF.Sex_id = 2
end
if (Select RegType_id From d6.Reg with (nolock) where Reg_id = @Reg_id) = 2
begin
insert into d6.RegeR with (ROWLOCK) (Reg_id
,E_id
,RegeRType_id
,BOSSection_id
,pUs_insID
,pUs_updID
,RegeR_insDT
,RegeR_updDT
,comments
)
select distinct
@Reg_id as Reg_id,
RFG.E_id as E_id,
@RegeRType_id as RegeRType_id,
RFG.BOSSection_id as BOSSection_id,
RFG.pUs_insID as pUs_insID,
RFG.pUs_updID as pUs_updID,
GETDATE() as RegeR_insDT,
GETDATE() as RegeR_updDT
,'ДКО не финансируется по ОМС' as Tesstext
from d6.v_Reg r with (nolock)
inner join d6.RegData RFG on R.Reg_id = RFG.Reg_id
inner join Edn with (NOLOCK) on Edn.E_id = RFG.E_id and Edn.BOS_id = R.BOS_id
inner join v_ClientState Client with (NOLOCK) on Client.Client_id = RFG.Client_id
left join v_Polis Polis with (NOLOCK) on Polis.Polis_id = Client.Polis_id
outer apply (
select top 1
ServiseComplex.ServiseComplex_id,
ServiseComplex.ServiseComplex_Code
from
v_EdnServise EdnServise with (nolock)
left join ServiseComplex on ServiseComplex.ServiseComplex_id = EdnServise.ServiseComplex_id
where
EdnServise.EdnServise_pid = RFG.E_id
) as EdnUsl
left join v_BOSUnitSet BOSUnitSet with (nolock) on BOSUnitSet.BOSUnitSet_id = R.BOSUnitSet_id
inner join dbo.DiagFinance DF with (nolock) on RFG.Diag_id = DF.Diag_id
inner join v_ClientState PS with (NOLOCK) on PS.Client_id = RFG.Client_id
inner join v_EdnPS EdnPS with (NOLOCK) on EdnPS.EdnPS_id = RFG.Edn_rid
inner join Diag D with (nolock) on D.Diag_id = DF.Diag_id
left join v_ClientState ps2 with (NOLOCK) on ps2.Client_id = (case when (EdnPS.Client_age=0 and (dateadd(month, 3, PS.Client_BirthDay)>RFG.Edn_disDate)) then RFG.Client_id else 0 end)
left join ClientAgeGroup PA with (nolock) on PA.ClientAgeGroup_id = DF.ClientAgeGroup_id
where r.Reg_id = @Reg_id
and case when Edn.EdnClass_id=61 then cast(RFG.BOSSectionProfile_Code AS char)+'835'
else EdnUsl.ServiseComplex_Code end not in ('684830', '684831', '658830', '658831', '584830', '584831', '558830', '558831')
and RIGHT(case when Edn.EdnClass_id=61 then cast(RFG.BOSSectionProfile_Code AS char)+'835' else EdnUsl.ServiseComplex_Code end, 3) != 862
and
DF.DiagFinance_IsOms = 1
insert into d6.RegeR with (ROWLOCK) (Reg_id
,E_id
,RegeRType_id
,BOSSection_id
,pUs_insID
,pUs_updID
,RegeR_insDT
,RegeR_updDT
,comments
)
select distinct
@Reg_id as Reg_id,
RFG.E_id as E_id,
@RegeRType_id as RegeRType_id,
RFG.BOSSection_id as BOSSection_id,
RFG.pUs_insID as pUs_insID,
RFG.pUs_updID as pUs_updID,
GETDATE() as RegeR_insDT,
GETDATE() as RegeR_updDT
,'ДКО финансируется только для детей' as Tesstext
from d6.v_Reg r with (nolock)
inner join d6.RegData RFG on R.Reg_id = RFG.Reg_id
inner join Edn with (NOLOCK) on Edn.E_id = RFG.E_id and Edn.BOS_id = R.BOS_id
inner join v_ClientState Client with (NOLOCK) on Client.Client_id = RFG.Client_id
left join v_Polis Polis with (NOLOCK) on Polis.Polis_id = Client.Polis_id
outer apply (
select top 1
ServiseComplex.ServiseComplex_id,
ServiseComplex.ServiseComplex_Code
from
v_EdnServise EdnServise with (nolock)
left join ServiseComplex on ServiseComplex.ServiseComplex_id = EdnServise.ServiseComplex_id
where
EdnServise.EdnServise_pid = RFG.E_id
) as EdnUsl
left join v_BOSUnitSet BOSUnitSet with (nolock) on BOSUnitSet.BOSUnitSet_id = R.BOSUnitSet_id
inner join dbo.DiagFinance DF with (nolock) on RFG.Diag_id = DF.Diag_id
inner join v_ClientState PS with (NOLOCK) on PS.Client_id = RFG.Client_id
inner join v_EdnPS EdnPS with (NOLOCK) on EdnPS.EdnPS_id = RFG.Edn_rid
inner join Diag D with (nolock) on D.Diag_id = DF.Diag_id
left join v_ClientState ps2 with (NOLOCK) on ps2.Client_id = (case when (EdnPS.Client_age=0 and (dateadd(month, 3, PS.Client_BirthDay)>RFG.Edn_disDate)) then RFG.Client_id else 0 end)
left join ClientAgeGroup PA with (nolock) on PA.ClientAgeGroup_id = DF.ClientAgeGroup_id
where r.Reg_id = @Reg_id
and case when Edn.EdnClass_id=61 then cast(RFG.BOSSectionProfile_Code AS char)+'835'
else EdnUsl.ServiseComplex_Code end not in ('684830', '684831', '658830', '658831', '584830', '584831', '558830', '558831')
and RIGHT(case when Edn.EdnClass_id=61 then cast(RFG.BOSSectionProfile_Code AS char)+'835' else EdnUsl.ServiseComplex_Code end, 3) != 862
and
isnull(DF.ClientAgeGroup_id,1) = 2
and [dbo].[Age](
case when ps2.Client_id is null then RTrim(IsNull(convert(varchar(10), cast(PS.Client_BirthDay as datetime), 120), null))
else RTrim(IsNull(convert(varchar(10), cast(ps2.Client_BirthDay as datetime), 120), Null)) end,
RFG.Edn_setDate) > 18
insert into d6.RegeR with (ROWLOCK) (Reg_id
,E_id
,RegeRType_id
,BOSSection_id
,pUs_insID
,pUs_updID
,RegeR_insDT
,RegeR_updDT
,comments
)
select distinct
@Reg_id as Reg_id,
RFG.E_id as E_id,
@RegeRType_id as RegeRType_id,
RFG.BOSSection_id as BOSSection_id,
RFG.pUs_insID as pUs_insID,
RFG.pUs_updID as pUs_updID,
GETDATE() as RegeR_insDT,
GETDATE() as RegeR_updDT
,'ДКО финансируется только для взрослых' as Tesstext
from d6.v_Reg r with (nolock)
inner join d6.RegData RFG on R.Reg_id = RFG.Reg_id
inner join Edn with (NOLOCK) on Edn.E_id = RFG.E_id and Edn.BOS_id = R.BOS_id
inner join v_ClientState Client with (NOLOCK) on Client.Client_id = RFG.Client_id
left join v_Polis Polis with (NOLOCK) on Polis.Polis_id = Client.Polis_id
outer apply (
select top 1
ServiseComplex.ServiseComplex_id,
ServiseComplex.ServiseComplex_Code
from
v_EdnServise EdnServise with (nolock)
left join ServiseComplex on ServiseComplex.ServiseComplex_id = EdnServise.ServiseComplex_id
where
EdnServise.EdnServise_pid = RFG.E_id
) as EdnUsl
left join v_BOSUnitSet BOSUnitSet with (nolock) on BOSUnitSet.BOSUnitSet_id = R.BOSUnitSet_id
inner join dbo.DiagFinance DF with (nolock) on RFG.Diag_id = DF.Diag_id
inner join v_ClientState PS with (NOLOCK) on PS.Client_id = RFG.Client_id
inner join v_EdnPS EdnPS with (NOLOCK) on EdnPS.EdnPS_id = RFG.Edn_rid
inner join Diag D with (nolock) on D.Diag_id = DF.Diag_id
left join v_ClientState ps2 with (NOLOCK) on ps2.Client_id = (case when (EdnPS.Client_age=0 and (dateadd(month, 3, PS.Client_BirthDay)>RFG.Edn_disDate)) then RFG.Client_id else 0 end)
left join ClientAgeGroup PA with (nolock) on PA.ClientAgeGroup_id = DF.ClientAgeGroup_id
where r.Reg_id = @Reg_id
and case when Edn.EdnClass_id=61 then cast(RFG.BOSSectionProfile_Code AS char)+'835'
else EdnUsl.ServiseComplex_Code end not in ('684830', '684831', '658830', '658831', '584830', '584831', '558830', '558831')
and RIGHT(case when Edn.EdnClass_id=61 then cast(RFG.BOSSectionProfile_Code AS char)+'835' else EdnUsl.ServiseComplex_Code end, 3) != 862
and
isnull(DF.ClientAgeGroup_id,1) = 1
and [dbo].[Age](
case when ps2.Client_id is null then RTrim(IsNull(convert(varchar(10), cast(PS.Client_BirthDay as datetime), 120), null))
else RTrim(IsNull(convert(varchar(10), cast(ps2.Client_BirthDay as datetime), 120), Null)) end,
RFG.Edn_setDate) < 18
insert into d6.RegeR with (ROWLOCK) (Reg_id
,E_id
,RegeRType_id
,BOSSection_id
,pUs_insID
,pUs_updID
,RegeR_insDT
,RegeR_updDT
,comments
)
select distinct
@Reg_id as Reg_id,
RFG.E_id as E_id,
@RegeRType_id as RegeRType_id,
RFG.BOSSection_id as BOSSection_id,
RFG.pUs_insID as pUs_insID,
RFG.pUs_updID as pUs_updID,
GETDATE() as RegeR_insDT,
GETDATE() as RegeR_updDT
,'ДКО финансируется только для жителей РБ' as Tesstext
from d6.v_Reg r with (nolock)
inner join d6.RegData RFG on R.Reg_id = RFG.Reg_id
inner join Edn with (NOLOCK) on Edn.E_id = RFG.E_id and Edn.BOS_id = R.BOS_id
inner join v_ClientState Client with (NOLOCK) on Client.Client_id = RFG.Client_id
left join v_Polis Polis with (NOLOCK) on Polis.Polis_id = Client.Polis_id
outer apply (
select top 1
ServiseComplex.ServiseComplex_id,
ServiseComplex.ServiseComplex_Code
from
v_EdnServise EdnServise with (nolock)
left join ServiseComplex on ServiseComplex.ServiseComplex_id = EdnServise.ServiseComplex_id
where
EdnServise.EdnServise_pid = RFG.E_id
) as EdnUsl
left join v_BOSUnitSet BOSUnitSet with (nolock) on BOSUnitSet.BOSUnitSet_id = R.BOSUnitSet_id
inner join dbo.DiagFinance DF with (nolock) on RFG.Diag_id = DF.Diag_id
inner join v_ClientState PS with (NOLOCK) on PS.Client_id = RFG.Client_id
inner join v_EdnPS EdnPS with (NOLOCK) on EdnPS.EdnPS_id = RFG.Edn_rid
inner join Diag D with (nolock) on D.Diag_id = DF.Diag_id
left join v_ClientState ps2 with (NOLOCK) on ps2.Client_id = (case when (EdnPS.Client_age=0 and (dateadd(month, 3, PS.Client_BirthDay)>RFG.Edn_disDate)) then RFG.Client_id else 0 end)
left join ClientAgeGroup PA with (nolock) on PA.ClientAgeGroup_id = DF.ClientAgeGroup_id
where r.Reg_id = @Reg_id
and case when Edn.EdnClass_id=61 then cast(RFG.BOSSectionProfile_Code AS char)+'835'
else EdnUsl.ServiseComplex_Code end not in ('684830', '684831', '658830', '658831', '584830', '584831', '558830', '558831')
and RIGHT(case when Edn.EdnClass_id=61 then cast(RFG.BOSSectionProfile_Code AS char)+'835' else EdnUsl.ServiseComplex_Code end, 3) != 862
and
DF.DiagFinance_IsAlien = 1 and RFG.OrgCMO_id= 8
and (substring(case when Edn.EdnClass_id=61 then cast(RFG.BOSSectionProfile_Code AS char)+'835' else EdnUsl.ServiseComplex_Code end,2,2) != '94'
or RIGHT(case when Edn.EdnClass_id=61 then cast(RFG.BOSSectionProfile_Code AS char)+'835' else EdnUsl.ServiseComplex_Code end,3) not in ('910', '915')
or D.Diag_Code != 'Z00.8')
insert into d6.RegeR with (ROWLOCK) (Reg_id
,E_id
,RegeRType_id
,BOSSection_id
,pUs_insID
,pUs_updID
,RegeR_insDT
,RegeR_updDT
,comments
)
select distinct
@Reg_id as Reg_id,
RFG.E_id as E_id,
@RegeRType_id as RegeRType_id,
RFG.BOSSection_id as BOSSection_id,
RFG.pUs_insID as pUs_insID,
RFG.pUs_updID as pUs_updID,
GETDATE() as RegeR_insDT,
GETDATE() as RegeR_updDT
,'ДКО финансируется только для мужчин' as Tesstext
from d6.v_Reg r with (nolock)
inner join d6.RegData RFG on R.Reg_id = RFG.Reg_id
inner join Edn with (NOLOCK) on Edn.E_id = RFG.E_id and Edn.BOS_id = R.BOS_id
inner join v_ClientState Client with (NOLOCK) on Client.Client_id = RFG.Client_id
left join v_Polis Polis with (NOLOCK) on Polis.Polis_id = Client.Polis_id
outer apply (
select top 1
ServiseComplex.ServiseComplex_id,
ServiseComplex.ServiseComplex_Code
from
v_EdnServise EdnServise with (nolock)
left join ServiseComplex on ServiseComplex.ServiseComplex_id = EdnServise.ServiseComplex_id
where
EdnServise.EdnServise_pid = RFG.E_id
) as EdnUsl
left join v_BOSUnitSet BOSUnitSet with (nolock) on BOSUnitSet.BOSUnitSet_id = R.BOSUnitSet_id
inner join dbo.DiagFinance DF with (nolock) on RFG.Diag_id = DF.Diag_id
inner join v_ClientState PS with (NOLOCK) on PS.Client_id = RFG.Client_id
inner join v_EdnPS EdnPS with (NOLOCK) on EdnPS.EdnPS_id = RFG.Edn_rid
inner join Diag D with (nolock) on D.Diag_id = DF.Diag_id
left join v_ClientState ps2 with (NOLOCK) on ps2.Client_id = (case when (EdnPS.Client_age=0 and (dateadd(month, 3, PS.Client_BirthDay)>RFG.Edn_disDate)) then RFG.Client_id else 0 end)
left join ClientAgeGroup PA with (nolock) on PA.ClientAgeGroup_id = DF.ClientAgeGroup_id
where r.Reg_id = @Reg_id
and case when Edn.EdnClass_id=61 then cast(RFG.BOSSectionProfile_Code AS char)+'835'
else EdnUsl.ServiseComplex_Code end not in ('684830', '684831', '658830', '658831', '584830', '584831', '558830', '558831')
and RIGHT(case when Edn.EdnClass_id=61 then cast(RFG.BOSSectionProfile_Code AS char)+'835' else EdnUsl.ServiseComplex_Code end, 3) != 862
and
PS.Sex_id = 2 and DF.Sex_id = 1
insert into d6.RegeR with (ROWLOCK) (Reg_id
,E_id
,RegeRType_id
,BOSSection_id
,pUs_insID
,pUs_updID
,RegeR_insDT
,RegeR_updDT
,comments
)
select distinct
@Reg_id as Reg_id,
RFG.E_id as E_id,
@RegeRType_id as RegeRType_id,
RFG.BOSSection_id as BOSSection_id,
RFG.pUs_insID as pUs_insID,
RFG.pUs_updID as pUs_updID,
GETDATE() as RegeR_insDT,
GETDATE() as RegeR_updDT
,'ДКО финансируется только для женщин' as Tesstext
from d6.v_Reg r with (nolock)
inner join d6.RegData RFG on R.Reg_id = RFG.Reg_id
inner join Edn with (NOLOCK) on Edn.E_id = RFG.E_id and Edn.BOS_id = R.BOS_id
inner join v_ClientState Client with (NOLOCK) on Client.Client_id = RFG.Client_id
left join v_Polis Polis with (NOLOCK) on Polis.Polis_id = Client.Polis_id
outer apply (
select top 1
ServiseComplex.ServiseComplex_id,
ServiseComplex.ServiseComplex_Code
from
v_EdnServise EdnServise with (nolock)
left join ServiseComplex on ServiseComplex.ServiseComplex_id = EdnServise.ServiseComplex_id
where
EdnServise.EdnServise_pid = RFG.E_id
) as EdnUsl
left join v_BOSUnitSet BOSUnitSet with (nolock) on BOSUnitSet.BOSUnitSet_id = R.BOSUnitSet_id
inner join dbo.DiagFinance DF with (nolock) on RFG.Diag_id = DF.Diag_id
inner join v_ClientState PS with (NOLOCK) on PS.Client_id = RFG.Client_id
inner join v_EdnPS EdnPS with (NOLOCK) on EdnPS.EdnPS_id = RFG.Edn_rid
inner join Diag D with (nolock) on D.Diag_id = DF.Diag_id
left join v_ClientState ps2 with (NOLOCK) on ps2.Client_id = (case when (EdnPS.Client_age=0 and (dateadd(month, 3, PS.Client_BirthDay)>RFG.Edn_disDate)) then RFG.Client_id else 0 end)
left join ClientAgeGroup PA with (nolock) on PA.ClientAgeGroup_id = DF.ClientAgeGroup_id
where r.Reg_id = @Reg_id
and case when Edn.EdnClass_id=61 then cast(RFG.BOSSectionProfile_Code AS char)+'835'
else EdnUsl.ServiseComplex_Code end not in ('684830', '684831', '658830', '658831', '584830', '584831', '558830', '558831')
and RIGHT(case when Edn.EdnClass_id=61 then cast(RFG.BOSSectionProfile_Code AS char)+'835' else EdnUsl.ServiseComplex_Code end, 3) != 862
and
PS.Sex_id = 1 and DF.Sex_id = 2
end
end try
begin catch
set @eR_Code = eR_number()
set @eR_Tessage = eR_Tessage()
if @@trancount>0
rollback
end catch
set nocount off
_____________
HTML, CSS (Bootstrap), JS(JQuery, ExtJS), PHP, MySQL, MSSql, Posgres, (TSql, BI OLAP, MDX), Mongo, Git, SVN, CodeIgnater, Symfony, Yii 2, JiRA, Redmine, Bitbucket, Composer, Rabbit MQ, Amazon (SQS, S3, Transcribe), Docker
Здесь расположена полная версия этой страницы.