November 13, 2015 at 12:25 pm
I'm trying to add a field, Anxiety, to an SSRS. The report worked fine before adding the new field. Any thoughts?
Error Detail: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.
This is my sp.
set nocount on
create table #ActivePatients
(MRN varchar(15) not null primary key,
PatientName varchar(100),
DOB datetime,
UnitNumber varchar(6),
PrimIns varchar(50),
PCPProvID char(10),
ProvName varchar(100),
Age int
)
insert into #ActivePatients
select b.MRN, b.PatLastName + ',' + b.PatFirstName as 'PatientName', b.DOB, right(OtherNo, 6) as 'UnitNumber', b.PrimIns, b.PCPProvid, c.ProvName, cast(FLOOR(DateDiff(Day,b.DOB,getdate()))/365.25 as int) as 'Age'
from formatIDX_PastAppts a
inner join formatIDX_Patients b on
a.MRN=b.MRN
inner join (select ProvNPI, ProvName, Specialty
from dbo.formatIDX_ProvNPI
where Specialty in ('PEDIATRICS','INTERNAL MEDICINE', 'INTERNAL MEDICINE (I)', 'FAMILY PRACTICE', 'INTERNAL MEDICINE (II)', 'FAMILY PRACTICE (II)')) c on
b.PCPProvid=c.ProvNPI
where ApptDt>dateadd(yy, - 2, getdate())
and a.STATUS = 'ARR'
and a.MRN <> ''
and a.Deceased <> 'Y'
group by b.MRN, b.PatLastName, b.PatFirstName, b.DOB, b.OtherNo, b.PrimIns, b.PCPProvid, c.ProvName
create table #Problems
(MRN varchar(15),
Problem varchar(100),
Flag char(1)
)
CREATE CLUSTERED INDEX MRNIndex ON #Problems(MRN)
--Dementia
insert into #Problems(MRN, Problem, Flag)
select MRN, 'Dementia' as 'Problem', 'x' as 'Flag'
from xFerProblems a
inner join #ActivePatients b on
a.IDXMRN=b.MRN
where ICD9DiagnosisCode in ('290.0','290.10','290.11','290.20','290.21','290.3','290.40','290.42','294.10','294.11','294.20','294.21','331.0','331.19')
--Chronic Obstructive Pulmonary Disease
insert into #Problems(MRN, Problem, Flag)
select MRN, 'Chronic Obstructive Pulmonary Disease' as 'Problem', 'x' as 'Flag'
from xFerProblems a
inner join #ActivePatients b on
a.IDXMRN=b.MRN
where ICD9DiagnosisCode in ('493.22','493.20','491.21','491.22','491.20','492.8','496')
--Congestive Heart Failure
insert into #Problems(MRN, Problem, Flag)
select MRN, 'Congestive Heart Failure' as 'Problem', 'x' as 'Flag'
from xFerProblems a
inner join #ActivePatients b on
a.IDXMRN=b.MRN
where ICD9DiagnosisCode in ('402.91','404.10','404.11','428.0','428.20','428.21','428.22','428.23','428.30','428.31','428.32','428.33')
--Chronic Renal Failure
insert into #Problems(MRN, Problem, Flag)
select MRN, 'Chronic Renal Failure' as 'Problem', 'x' as 'Flag'
from xFerProblems a
inner join #ActivePatients b on
a.IDXMRN=b.MRN
where ICD9DiagnosisCode in ('585.1','585.2','585.3','585.4','585.5','585.9')
--Coronary Artery Disease
insert into #Problems(MRN, Problem, Flag)
select MRN, 'Coronary Artery Disease' as 'Problem', 'x' as 'Flag'
from xFerProblems a
inner join #ActivePatients b on
a.IDXMRN=b.MRN
where ICD9DiagnosisCode in ('414.01','414.00','414.04','414.02','414.9')
--Diabetes
insert into #Problems(MRN, Problem, Flag)
select MRN, 'Diabetes' as 'Problem', 'x' as 'Flag'
from xFerProblems a
inner join #ActivePatients b on
a.IDXMRN=b.MRN
where left(ICD9DiagnosisCode,3)='250'
--ADHD
insert into #Problems(MRN, Problem, Flag)
select MRN, 'ADHD' as 'Problem', 'x' as 'Flag'
from xFerProblems a
inner join #ActivePatients b on
a.IDXMRN=b.MRN
where ICD9DiagnosisCode in ('314.00','799.51','314.9')
and Age<18
--Anxiety
insert into #Problems(MRN, Problem, Flag)
select MRN, 'Anxiety' as 'Problem', 'x' as 'Flag'
from xFerProblems a
inner join #ActivePatients b on
a.IDXMRN=b.MRN
where ICD9DiagnosisCode in ('300.00','300.02','309.81')
and Age<18
--Depression
insert into #Problems(MRN, Problem, Flag)
select MRN, 'Depression' as 'Problem', 'x' as 'Flag'
from xFerProblems a
inner join #ActivePatients b on
a.IDXMRN=b.MRN
where ICD9DiagnosisCode in ('311','296.90', '309.9', '300.4')
and Age<18
--Asthma
insert into #Problems(MRN, Problem, Flag)
select MRN, 'Asthma' as 'Problem', 'x' as 'Flag'
from xFerProblems a
inner join #ActivePatients b on
a.IDXMRN=b.MRN
where ICD9DiagnosisCode in ('493.00','493.01','493.02','493.10','493.11','493.12','493.20','493.21','493.22','493.81','493.82','493.90','493.91','493.92')
and Age<18
--BMI> 95 percentile
select a.IDXMRN, cast(a.ResultValue as float) as 'BMI'
into #BMI
from xFerVitals a
inner join (select IDXMRN, max(PerformedDTTM) as 'LastTest'
from xFerVitals
where TestName='BMI Calculated'
group by IDXMRN) b on
a.IDXMRN=b.IDXMRN
and a.PerformedDTTM=b.LastTest
where a.TestName='BMI Calculated'
select top 5 percent a.MRN, b.BMI
into #Top5BMI
from #ActivePatients a
inner join #BMI b on
a.MRN=b.IDXMRN
where a.Age<18
and a.MRN<>''
order by b.BMI desc
insert into #Problems(MRN, Problem, Flag)
select MRN, 'BMI> 95 percentile' as 'Problem', 'x' as 'Flag'
from #Top5BMI
--3.Have more than 5 ER visits in the last year
;with ERVisits
as
(
select UnitNumber
from [BRO-DR1].livedb.dbo.AbstractData
where SourceID='BRO'
and ErServiceDateTime is not null
and DischargeDateTime>=dateadd(yy, -1, getdate())
group by UnitNumber
having count(*)>2
)
insert into #Problems(MRN, Problem, Flag)
select a.MRN, '>2 ED Visits' as 'Problem', 'x' as 'Flag'
from #ActivePatients a
inner join ERVisits b on
a.UnitNumber collate database_default=b.UnitNumber collate database_default
--4.Have 1 readmission or more in the last year within 30 days
select a.SourceID, a.AbstractID, a.AccountNumber, a.UnitNumber, b.IDXMRN, a.AdmitDateTime, a.DischargeDateTime
into #Discharges
from [BRO-DR1].livedb.dbo.AbstractData a
inner join (select SourceID, PatientID, right(Response,6) as 'IDXMRN'
from [BRO-DR1].livedb.dbo.MriPatientQueries
where SourceID='BRO'
and QueryID='ADM.BGMRN') b on
a.SourceID=b.SourceID
and a.PatientID=b.PatientID
where a.SourceID='BRO'
and a.DischargeDateTime >= dateadd(yy, -1, getdate())
and a.PtStatus='IN'
and a.LocationID not in ('C3', 'A3/TCU')
;with cte
as
(
select SourceID, AbstractID, AccountNumber,UnitNumber,IDXMRN, DischargeDateTime,AdmitDateTime,
row_number()over(partition by UnitNumber order by AdmitDateTime) as 'RowNumber'
from #Discharges
),
Readmits
as
(
select a.SourceID, a.AbstractID, a.AccountNumber as 'IndexAccountNumber', b.AccountNumber as 'ReadmitAccountNumber', a.UnitNumber, a.IDXMRN, a.DischargeDateTime as 'IndexDischargeDate',
b.AdmitDateTime as 'ReadmitDate'
from cte a
inner join cte b on
a.UnitNumber=b.UnitNumber
where a.AccountNumber<>b.AccountNumber
and a.RowNumber+1=b.RowNumber
and b.AdmitDateTime >= a.DischargeDateTime and b.AdmitDateTime < dateadd(d,31,a.DischargeDateTime )
)
insert into #Problems(MRN, Problem, Flag)
select a.MRN, 'Readmit' as 'Problem', 'x' as 'Flag'
from #ActivePatients a
inner join Readmits b on
a.UnitNumber collate database_default=b.UnitNumber collate database_default
group by a.MRN
--5.Last Appt
select MRN, max(ApptDt2) as 'LastAppt'
into #LastAppt
from formatIDX_PastAppts
where STATUS='ARR'
and ApptDt2>=dateadd(yy, -1, getdate())
and MRN<>''
and SchProvNPI in (select ProvNPI
from dbo.formatIDX_ProvNPI
where Specialty in ('PEDIATRICS','INTERNAL MEDICINE', 'INTERNAL MEDICINE (I)', 'FAMILY PRACTICE', 'INTERNAL MEDICINE (II)', 'FAMILY PRACTICE (II)'))
group by MRN
--6.Next Appt
select MRN, min(ApptDt2) as 'NextAppt'
into #NextAppt
from formatIDX_FutureAppts
where STATUS in ('PEN','RSC')
and ApptDt2>=getdate()
and MRN<>''
and SchProvNPI in (select ProvNPI
from dbo.formatIDX_ProvNPI
where Specialty in ('PEDIATRICS','INTERNAL MEDICINE', 'INTERNAL MEDICINE (I)', 'FAMILY PRACTICE', 'INTERNAL MEDICINE (II)', 'FAMILY PRACTICE (II)'))
group by MRN
--==========================================================================
select a.MRN, PatientName, DOB, PrimIns, ProvName,
max(case when Problem='Congestive Heart Failure' then 1 else '' end) as 'CHF',
max(case when Problem='Chronic Obstructive Pulmonary Disease' then 1 else '' end) as 'COPD',
max(case when Problem='Chronic Renal Failure' then 1 else '' end) as 'CRF',
max(case when Problem='Coronary Artery Disease' then 1 else '' end) as 'CAD',
max(case when Problem='Diabetes' then 1 else '' end) as 'Diabetes',
max(case when Problem='Dementia' then 1 else '' end) as 'Dementia',
max(case when Problem='ADHD' then 1 else '' end) as 'ADHD',
max(case when Problem='Asthma' then 1 else '' end) as 'Asthma',
max(case when Problem='Anxiety' then 1 else '' end) as 'Anxiety',
max(case when Problem='Depression' then 1 else '' end) as 'Depression',
max(case when Problem='BMI> 95 percentile' then 1 else '' end) as 'BMI',
max(case when Problem='>2 ED Visits' then 1 else '' end) as 'EDVisits',
max(case when Problem='Readmit' then 1 else '' end) as 'Readmit',
c.LastAppt, d.NextAppt
into #Final
FROM #Problems a
inner join #ActivePatients b on
a.MRN=b.MRN
left join #LastAppt c on
a.MRN=c.MRN
left join #NextAppt d on
a.MRN=d.MRN
group by a.MRN, PatientName, DOB, PrimIns, ProvName, LastAppt, NextAppt
select MRN, PatientName, DOB, PrimIns, ProvName, CHF, COPD, CRF, CAD, Diabetes, Dementia, ADHD, Asthma, BMI, EDVisits, Readmit,
(CHF+COPD+CRF+CAD+Diabetes+Dementia+ADHD+Asthma+BMI+EDVisits+Readmit) as 'Total', LastAppt, NextAppt, Anxiety
from #Final
order by Total desc
November 16, 2015 at 5:39 am
This query runs fine when executing within SSRS but, times out when creating the fields for the report. Any thoughts? In SSMS the query takes 1 minute to run.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply