September 8, 2009 at 6:47 pm
Here's our situation:
-We get multiple transaction files every day from many different sites. These files contain personal and location data for people at the sites. As these people move around the sites during the day, or if their personal information is updated, we get files that represent these changes. Some sites also send us a reconcilliation file once a day so we can 'true up' or records in case we miss one of the transaction files
-Via SSIS, we import these records into SQL server. More specifically, we massage the records, move them to a staging table, then move them out of the staging table and into two 'main' tables.
-Each site has their own SSIS package, which is run by an SQL job. We pass in a siteId as a variable in the SSIS package, so only the records from that Site will be manipulated each time that site's job runs. In other words, every time a site's job runs, it executes its own SSIS package, and moves that site's records from the files to the staging table then to the main tables.
-Depending on the contract we sign with the site, and the frequency in which the site's files arrives, we execute the SQL jobs at different intervals - on site's job may run every 3 minutes, and one may run evey 15 minutes.
This has been working pretty well until we added our 9th site the other Friday. After that, we started getting a lot of deadlocks - about 50 over the weekend. We've added NOLOCK hints on the select statements that read from the staging table, and we've added ROWLOCK hints the Update statements that affect the 'main' tables. We did that this morning around 9am, but we've still gotten 3 failures today.
Does anyone have any advice on what we may need to do to get rid of our deadlock problems? I know this is a broad topic, and I can try to be more specific if I need to. The unfortunate thing is that we are going to be adding about one site a month with no end in site for a long time.
Thanks in advance!
September 8, 2009 at 6:55 pm
Yes we can fix that, without resorting to NOLOCK and ROWLOCK. (Don't get me started on that please.)
Personally, I'd like to see a simplified example in SQL code with CREATE TABLE statements, and some sample data creation, together with a concise and clear description of what needs to go where, when. Knowing which version of SQL Server (2005/2008 Standard/Enterprise?) you are running would kinda help too - some facilities are version-specific 🙂
Quite willing to invest some of my personal spare time to this, if you can provide that.
If not, you'll probably still get some general points of advice. Depends what you want really.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 8, 2009 at 7:24 pm
Sounds good. I can get the info tomorrow when I'm back at the office. Another thing I forgot to mention is that all of the sites' records are manipulated differently before they wind up in the staging table. However, the stored procedure that moves them from the staging to the main tables is always the point of deadlock.
Thanks!
September 8, 2009 at 7:40 pm
Clint (9/8/2009)
...However, the stored procedure that moves them from the staging to the main tables is always the point of deadlock.
Awesome, so can we concentrate on just that to start with? It's a juggling act to provide enough information to capture the essence of the problem (this gets people interested) without posting 50,000 lines of mostly-meaningless-to-us SQL script. Something representative of the problem, but reasonably easy to set up, understand and run would be awesome. Good luck.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2009 at 12:30 am
Clint (9/8/2009)
However, the stored procedure that moves them from the staging to the main tables is always the point of deadlock.
Post that code right away...
--Jeff Moden
Change is inevitable... Change for the better is not.
September 9, 2009 at 7:39 am
All right. a little more clarification on what we are doing (I had to clear it with my boss about how much info I should disclose). Also, please forgive the naming conventions. I inherited this DB, and we are in the middle of changing to a standard naming convention:
-We are actually talking about inamtes, not employees. We take care of there healthcare needs.
-Inmate records are stored in the 'Patients' table. Individual inmate incarcerations are stored in the 'tbl_echart_charts' table.
-All SSIS packages import patient records into 'tbl_echart_patientinterfacestaging' (the staging table), then execute this stored procedure to move them out of the 'tbl_echart_patientinterfacestaging' and into 'Patients' and 'tbl_echart_charts'.
-Steps number 7 and 8 in the stored proc I'm posting also update a few other tables if a patient is released
-If you ignore the NOLOCK and ROWLOCK statements, you'll see how it was before yesterday morning when I added those statements
Here is the main stored proc I was talking about (I'm going to post the CREATE TABLE statements in a different post, just so I won't get confused):
USE [Ucare]
GO
/****** Object: StoredProcedure [dbo].[spProcessPatientStaging] Script Date: 09/09/2009 08:22:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spProcessPatientStaging]
(
@pSiteId varchar(50)
) AS
SET NOCOUNT ON
/******************************************************************************
Title : spProcessPatientStaging
Author:: xxx
Created : xxx
Company : xxx
Description : Imports patients from 'tbl_Echart_PatientInterfaceStaging' (the site interface staging table) into 'Patients' and
'tbl_Echart_Charts'
File Types:
I - Represents newly incarcerated inmates
R - Represents recently released inmates
D - Represents a file of all current inmates for a current site
Project : xxx
CHANGES:
INITIALS # DATE REASON
------ --- ---------- --------
xxxx 12/23/09Began capturing new fields from the interfaces, therefore they needed to be added to this stored proc and the
appropriate tables. Fields added are marked in the Insert statements below.
xxxx 22/23/09Added code to update Patient record if missing certain information. Fields are marked in the Update statement below
xxxx 32/23/09Added code to update chart records with the booking number if they are missing it.
xxxx 42/23/09On interface flat files that contain more than one transaction record, if a Patient was discharged
and re-incarcerated on the same day, the release date for the previous incarceration was being associated with the
new incarceration, causing a new, current chart to be created every time the interface ran. A check was added to
ensure that the release date is associated with the proper incarceration date
xxxx 53/25/09Added code to ensure that if two patients in the staging table have the same inmate number, there departments will
be updated correctly
xxxx 63/25/09Added a 'distinct' clause to ensure that if an Patient comes over on a Daily file and an Intake file,
only one chart will be created for them. If the Record is not exactly the same, however, duplicate charts will be
created.
xxxx 76/01/09Added code to change appointment status from 'Scheduled' to 'Released' if the patient is released
xxxx 86/03/09Added code to insert a new Request_Approval record with a 'Complete' status if the patient is released and their
appointment type is cancellable
xxxx 99/07/09Added NOLOCK and ROWLOCK table hints to alleviate the deadlocking problem. Any Select statement that reads from
tbl_echart_patientinterface staging gets a NOLOCK. Any Update statement that affects other tables gets a ROWLOCK.
xxxx109/07/09Put parentheses around "is null or C.BookingNumber = ''". Without the parentheses, the statement would have not
acted correctly with the following AND clause
***********************************************************************************/
-- Updates existing patients with a department change
Update A WITH (ROWLOCK)
SET
SiteDepartmentId = tbl_Echart_PatientInterfaceStaging.DepartmentId
From Patients A
Inner Join tbl_Echart_PatientInterfaceStaging on tbl_Echart_PatientInterfaceStaging.InmateNumber = A.InmateNumber
Where A.SiteId = @pSiteId
And tbl_Echart_PatientInterfaceStaging.SiteId = @pSiteId--#5
And A.SiteDepartmentId tbl_Echart_PatientInterfaceStaging.DepartmentId
And (tbl_Echart_PatientInterfaceStaging.DepartmentId '' or tbl_Echart_PatientInterfaceStaging.DepartmentId is not null)
And tbl_Echart_PatientInterfaceStaging.StagingType 'R'
--Inserts patient records with an 'I' staging type into the Patients table, if they do not already exist
Insert Into Patients
(
SiteId
,EldoradoId
,InmateNumber
,SSN
,NameFirst
,NameMiddle
,NameLast
,Sex
,BirthDate
,Address_Primary
,Address_Secondary
,City
,[State]
,Zip
,CustodyDate
,ReleaseDatePotential--#1 (see above)
,ReleaseDateActual
,IsReleased
,IsInfirmaryHoused
,LastModifiedDate
,ConcurrencyId
,InmateTypeId
,SiteDepartmentId
,IsPresentenced
,IsSentenced
,SentenceDate
,IsJuvenile
,IsDeceased
,Loc_Display--#1 (see above)
,Loc_Facility--#1 (see above)
,Loc_Building--#1 (see above)
,Loc_Floor--#1 (see above)
,Loc_CellBlock--#1 (see above)
,Loc_Unit--#1 (see above)
,Loc_Cell--#1 (see above)
,Loc_Bed--#1 (see above)
,Loc_OffsiteCode--#1 (see above)
)
Select Distinct
tbl_Echart_PatientInterfaceStaging.SiteId
,-1
,tbl_Echart_PatientInterfaceStaging.InmateNumber
,tbl_Echart_PatientInterfaceStaging.SSN
,tbl_Echart_PatientInterfaceStaging.NameFirst
,tbl_Echart_PatientInterfaceStaging.NameMiddle
,tbl_Echart_PatientInterfaceStaging.NameLast
,tbl_Echart_PatientInterfaceStaging.Sex
,tbl_Echart_PatientInterfaceStaging.DOB
,tbl_Echart_PatientInterfaceStaging.LastKnownAddress
,tbl_Echart_PatientInterfaceStaging.LastKnownAddress2
,tbl_Echart_PatientInterfaceStaging.LastKnownCity
,tbl_Echart_PatientInterfaceStaging.LastKnownState
,tbl_Echart_PatientInterfaceStaging.LastKnownZip
,tbl_Echart_PatientInterfaceStaging.IntakeDateTime
,tbl_Echart_PatientInterfaceStaging.PotentialReleaseDate
,tbl_Echart_PatientInterfaceStaging.ReleaseDate
,0
,0
,getdate()
,1
,case when ltrim(rtrim(tbl_Echart_PatientInterfaceStaging.CustodyLevel)) = '' then '1' else (case when ltrim(rtrim(tbl_Echart_PatientInterfaceStaging.CustodyLevel)) is null then '1' else ltrim(rtrim(tbl_Echart_PatientInterfaceStaging.CustodyLevel)) end) end
,tbl_Echart_PatientInterfaceStaging.DepartmentId
,0
,0
,null
,0
,0
,tbl_Echart_PatientInterfaceStaging.Loc_Display
,tbl_Echart_PatientInterfaceStaging.Loc_Facility
,tbl_Echart_PatientInterfaceStaging.Loc_Building
,tbl_Echart_PatientInterfaceStaging.Loc_Floor
,tbl_Echart_PatientInterfaceStaging.Loc_CellBlock
,tbl_Echart_PatientInterfaceStaging.Loc_Unit
,tbl_Echart_PatientInterfaceStaging.SiteCell
,tbl_Echart_PatientInterfaceStaging.Loc_Bed
,tbl_Echart_PatientInterfaceStaging.Loc_OffsiteCode
From tbl_Echart_PatientInterfaceStaging WITH (NOLOCK)
Left Outer Join Patients WITH (NOLOCK) on tbl_Echart_PatientInterfaceStaging.InmateNumber = Patients.InmateNumber and Patients.SiteId = tbl_Echart_PatientInterfaceStaging.SiteId
Left Outer Join tbl_Echart_Charts WITH (NOLOCK) on tbl_Echart_Charts.PatientId = Patients.Id and tbl_Echart_Charts.IsCurrent = 1
Where
tbl_Echart_PatientInterfaceStaging.StagingType = 'I'
And
(
Patients.Id is null
)
And
tbl_Echart_PatientInterfaceStaging.SiteId = @pSiteId
-- Inserts patient records with a 'D' staging type into the Patients table, if they do not already exist
Insert Into Patients
(
SiteId
,EldoradoId
,InmateNumber
,SSN
,NameFirst
,NameMiddle
,NameLast
,Sex
,BirthDate
,Address_Primary
,Address_Secondary
,City
,[State]
,Zip
,CustodyDate
,ReleaseDatePotential--#1 (see above)
,ReleaseDateActual
,IsReleased
,IsInfirmaryHoused
,LastModifiedDate
,ConcurrencyId
,InmateTypeId
,SiteDepartmentId
,IsPresentenced
,IsSentenced
,SentenceDate
,IsJuvenile
,IsDeceased
,Loc_Display--#1 (see above)
,Loc_Facility--#1 (see above)
,Loc_Building--#1 (see above)
,Loc_Floor--#1 (see above)
,Loc_CellBlock--#1 (see above)
,Loc_Unit--#1 (see above)
,Loc_Cell--#1 (see above)
,Loc_Bed--#1 (see above)
,Loc_OffsiteCode--#1 (see above)
)
Select Distinct
tbl_Echart_PatientInterfaceStaging.SiteId
,-1
,tbl_Echart_PatientInterfaceStaging.InmateNumber
,tbl_Echart_PatientInterfaceStaging.SSN
,tbl_Echart_PatientInterfaceStaging.NameFirst
,tbl_Echart_PatientInterfaceStaging.NameMiddle
,tbl_Echart_PatientInterfaceStaging.NameLast
,tbl_Echart_PatientInterfaceStaging.Sex
,tbl_Echart_PatientInterfaceStaging.DOB
,tbl_Echart_PatientInterfaceStaging.LastKnownAddress
,tbl_Echart_PatientInterfaceStaging.LastKnownAddress2
,tbl_Echart_PatientInterfaceStaging.LastKnownCity
,tbl_Echart_PatientInterfaceStaging.LastKnownState
,tbl_Echart_PatientInterfaceStaging.LastKnownZip
,tbl_Echart_PatientInterfaceStaging.IntakeDateTime
,tbl_Echart_PatientInterfaceStaging.PotentialReleaseDate
,tbl_Echart_PatientInterfaceStaging.ReleaseDate
,0
,0
,getdate()
,1
,case when ltrim(rtrim(tbl_Echart_PatientInterfaceStaging.CustodyLevel)) = '' then '1' else (case when ltrim(rtrim(tbl_Echart_PatientInterfaceStaging.CustodyLevel)) is null then '1' else ltrim(rtrim(tbl_Echart_PatientInterfaceStaging.CustodyLevel)) end) end
,tbl_Echart_PatientInterfaceStaging.DepartmentId
,0
,0
,null
,0
,0
,tbl_Echart_PatientInterfaceStaging.Loc_Display
,tbl_Echart_PatientInterfaceStaging.Loc_Facility
,tbl_Echart_PatientInterfaceStaging.Loc_Building
,tbl_Echart_PatientInterfaceStaging.Loc_Floor
,tbl_Echart_PatientInterfaceStaging.Loc_CellBlock
,tbl_Echart_PatientInterfaceStaging.Loc_Unit
,tbl_Echart_PatientInterfaceStaging.SiteCell
,tbl_Echart_PatientInterfaceStaging.Loc_Bed
,tbl_Echart_PatientInterfaceStaging.Loc_OffsiteCode
From tbl_Echart_PatientInterfaceStaging WITH (NOLOCK)
Left Outer Join Patients WITH (NOLOCK) on tbl_Echart_PatientInterfaceStaging.InmateNumber = Patients.InmateNumber and Patients.SiteId = tbl_Echart_PatientInterfaceStaging.SiteId
Left Outer Join tbl_Echart_Charts WITH (NOLOCK) on tbl_Echart_Charts.PatientId = Patients.Id and tbl_Echart_Charts.IsCurrent = 1
Where
tbl_Echart_PatientInterfaceStaging.StagingType = 'D'
And
(
Patients.Id is null
)
And
tbl_Echart_PatientInterfaceStaging.SiteId = @pSiteId
-- Sets the isCurrent flag to False for any patients that have a new release date on their incoming chart. The next step will import a new
-- record into tbl_Echart_Charts, which will become the current record. (e.g. upon patient re-incarceration,
-- that patient gets a new, current chart record, and the previous chart record becomes flagged with a zero)
Update tbl_Echart_Charts WITH (ROWLOCK)
Set
tbl_Echart_Charts.Iscurrent = 0
Where PatientId
in
(
Select patients.Id
From
tbl_Echart_PatientInterfaceStaging
Inner Join Patients on tbl_Echart_PatientInterfaceStaging.InmateNumber = Patients.InmateNumber and Patients.SiteId = tbl_Echart_PatientInterfaceStaging.SiteId
Inner Join tbl_Echart_Charts on tbl_Echart_Charts.patientid = patients.id And tbl_Echart_Charts.iscurrent = 1
where
tbl_Echart_Charts.patientId is not null
And
tbl_Echart_Charts.ReleaseDate is not null
And
tbl_Echart_PatientInterfaceStaging.IntakeDateTime > (select max(releaseDate) from tbl_Echart_Charts where PatientId = Patients.Id)
And
tbl_Echart_Charts.ReleaseDate '01/01/1900'
And
tbl_Echart_PatientInterfaceStaging.SiteId = @pSiteId
And
tbl_Echart_PatientInterfaceStaging.StagingType 'R'
)
--Inserts an instance of the patient into tbl_Echart_Charts if 1) one does not exist OR 2) the patient has a new custody date.
Insert Into tbl_Echart_Charts
(
PatientId
,BookingNumber
,IncarcerationDate
,ReleaseDate
,LastModifiedUser
,lastModifiedDate
,IsCurrent
,DateCreated
)
Select Distinct--#6
Patients.Id
,tbl_Echart_PatientInterfaceStaging.BookingNumber
,tbl_Echart_PatientInterfaceStaging.IntakeDateTime
,case when ltrim(rtrim(tbl_Echart_PatientInterfaceStaging.ReleaseDate)) = '' then null else ltrim(rtrim(tbl_Echart_PatientInterfaceStaging.ReleaseDate)) end
,26
,getdate()
,1
,GetDate()
From
tbl_Echart_PatientInterfaceStaging WITH (NOLOCK)
Inner Join Patients WITH (NOLOCK) on tbl_Echart_PatientInterfaceStaging.InmateNumber = Patients.InmateNumber and Patients.SiteId = tbl_Echart_PatientInterfaceStaging.SiteId
Left Outer Join tbl_Echart_Charts WITH (NOLOCK) on tbl_Echart_Charts.patientid = patients.id And tbl_Echart_Charts.iscurrent = 1
where
(
(tbl_Echart_Charts.patientid is null)
OR
(tbl_Echart_Charts.patientId is not null
And
tbl_Echart_Charts.ReleaseDate is not null
And
tbl_Echart_Charts.ReleaseDate '01/01/1900'
And
tbl_Echart_Charts.ReleaseDate ''
And
1 >= (select count(*) from tbl_Echart_Charts where PatientId = Patients.Id)
And
tbl_Echart_PatientInterfaceStaging.IntakeDateTime > (select max(releaseDate) from tbl_Echart_Charts where PatientId = Patients.Id)
)
)
And
tbl_Echart_PatientInterfaceStaging.StagingType 'R'
And
tbl_Echart_PatientInterfaceStaging.SiteId = @pSiteId
--Sets the release date on a Patient's chart, for a patient with an 'R' staging type, if they do not yet have one
Update A
Set A.ReleaseDate = tbl_Echart_PatientInterfaceStaging.ReleaseDate
From
tbl_Echart_PatientInterfaceStaging WITH (NOLOCK)
Inner Join Patients WITH (NOLOCK) on tbl_Echart_PatientInterfaceStaging.InmateNumber = Patients.InmateNumber and Patients.SiteId = tbl_Echart_PatientInterfaceStaging.SiteId
Inner Join tbl_Echart_Charts A WITH (NOLOCK) on A.PatientId = Patients.Id and A.IsCurrent = 1
Where
tbl_Echart_PatientInterfaceStaging.StagingType = 'R'
And
(A.ReleaseDate is null or a.ReleaseDate = '')
And
tbl_Echart_PatientInterfaceStaging.SiteId = @pSiteId
And A.IncarcerationDate < tbl_Echart_PatientInterfaceStaging.ReleaseDate--#4 (see above)
--#3
--Sets the Booking Number for all current charts on a particular site that do not have an associated Booking Number
Update C
Set C.BookingNumber = tbl_Echart_PatientInterfaceStaging.BookingNumber
From
tbl_Echart_PatientInterfaceStaging WITH (NOLOCK)
Inner Join Patients WITH (NOLOCK) on tbl_Echart_PatientInterfaceStaging.InmateNumber = Patients.InmateNumber and Patients.SiteId = tbl_Echart_PatientInterfaceStaging.SiteId
Inner Join tbl_Echart_Charts C WITH (NOLOCK) on C.PatientId = Patients.Id and C.IsCurrent = 1
Where (C.BookingNumber is null or C.BookingNumber = '')-- #10
Andtbl_Echart_PatientInterfaceStaging.SiteId = @pSiteId
--Updates the patient record if the record is marked with a change
Update A WITH (ROWLOCK)
Set
A.SiteId = tbl_Echart_PatientInterfaceStaging.SiteId
,A.InmateNumber = tbl_Echart_PatientInterfaceStaging.InmateNumber
,A.SSN = tbl_Echart_PatientInterfaceStaging.SSN
,A.NameFirst = tbl_Echart_PatientInterfaceStaging.NameFirst
,A.NameMiddle = tbl_Echart_PatientInterfaceStaging.NameMiddle
,A.NameLast= tbl_Echart_PatientInterfaceStaging.NameLast
,A.Sex = tbl_Echart_PatientInterfaceStaging.Sex
,A.BirthDate = tbl_Echart_PatientInterfaceStaging.DOB
,A.Address_Primary = tbl_Echart_PatientInterfaceStaging.LastKnownAddress
,A.Address_Secondary = tbl_Echart_PatientInterfaceStaging.LastKnownAddress2
,A.City = tbl_Echart_PatientInterfaceStaging.LastKnownCity
,A.[State] = tbl_Echart_PatientInterfaceStaging.LastKnownState
,A.Zip = tbl_Echart_PatientInterfaceStaging.LastKnownZip
,A.InmateTypeId = (case when ltrim(rtrim(tbl_Echart_PatientInterfaceStaging.CustodyLevel)) = '' then '1' else (case when ltrim(rtrim(tbl_Echart_PatientInterfaceStaging.CustodyLevel)) is null then '1' else ltrim(rtrim(tbl_Echart_PatientInterfaceStaging.CustodyLevel)) end) end)
,A.CustodyDate = tbl_Echart_PatientInterfaceStaging.IntakeDateTime
,A.ReleaseDatePotential = tbl_Echart_PatientInterfaceStaging.PotentialReleaseDate--#2 (see above)
,A.ReleaseDateActual = tbl_Echart_PatientInterfaceStaging.ReleaseDate
,A.LastModifiedDate = getdate()
,A.Loc_Display = tbl_Echart_PatientInterfaceStaging.Loc_Display--#2 (see above)
,A.Loc_Facility = tbl_Echart_PatientInterfaceStaging.Loc_Facility--#2 (see above)
,A.Loc_Building = tbl_Echart_PatientInterfaceStaging.Loc_Building--#2 (see above)
,A.Loc_Floor = tbl_Echart_PatientInterfaceStaging.Loc_Floor--#2 (see above)
,A.Loc_CellBlock = tbl_Echart_PatientInterfaceStaging.Loc_CellBlock--#2 (see above)
,A.Loc_Unit = tbl_Echart_PatientInterfaceStaging.Loc_Unit--#2 (see above)
,A.Loc_Cell = tbl_Echart_PatientInterfaceStaging.SiteCell--#2 (see above)
,A.Loc_Bed = tbl_Echart_PatientInterfaceStaging.Loc_Bed--#2 (see above)
,A.Loc_OffsiteCode = tbl_Echart_PatientInterfaceStaging.Loc_OffsiteCode--#2 (see above)
From
tbl_Echart_PatientInterfaceStaging
Inner Join Patients A on tbl_Echart_PatientInterfaceStaging.InmateNumber = A.InmateNumber and A.SiteId = tbl_Echart_PatientInterfaceStaging.SiteId
Inner Join tbl_Echart_Charts on tbl_Echart_Charts.PatientId = A.Id and tbl_Echart_Charts.IsCurrent = 1
Inner Join
(select PIS.Id from tbl_Echart_PatientInterfaceStaging PIS
Inner Join Patients A on PIS.InmateNumber = A.InmateNumber and A.SiteId = PIS.SiteId
where
(
PIS.NameLast A.NameLast
OR
PIS.NameFirst A.NameFirst
OR
PIS.NameMiddle A.NameMiddle
OR
PIS.SSN A.SSN
OR
PIS.DOB A.BirthDate
OR
PIS.Sex A.Sex
OR
PIS.LastKnownAddress A.Address_Primary--#2 (see above)
OR
PIS.LastKnownAddress2 A.Address_Secondary--#2 (see above)
OR
PIS.LastKnownCity A.City--#2 (see above)
OR
PIS.LastKnownState A.[State]--#2 (see above)
OR
PIS.LastKnownZIP A.Zip--#2 (see above)
OR
case when ltrim(rtrim(PIS.CustodyLevel)) = '' then '1' else (case when ltrim(rtrim(PIS.CustodyLevel)) is null then '1' else ltrim(rtrim(PIS.CustodyLevel)) end) end A.InmateTypeId--#2 (see above)
OR
PIS.PotentialReleaseDate A.ReleaseDatePotential--#2 (see above)
OR
PIS.Loc_Display A.Loc_Display--#2 (see above)
OR
PIS.Loc_Facility A.Loc_Facility--#2 (see above)
OR
PIS.Loc_Building A.Loc_Building--#2 (see above)
OR
PIS.Loc_Floor A.Loc_Floor--#2 (see above)
OR
PIS.Loc_CellBlock A.Loc_CellBlock--#2 (see above)
OR
PIS.Loc_Unit A.Loc_Unit--#2 (see above)
OR
PIS.SiteCell A.Loc_Cell--#2 (see above)
OR
PIS.Loc_Bed A.Loc_Bed--#2 (see above)
OR
PIS.Loc_OffsiteCode A.Loc_Offsitecode--#2 (see above)
)
AND
PIS.InmateNumber = A.InmateNumber
AND
PIS.SiteId = @pSiteId
)AS PISTemp on PISTemp.Id = tbl_Echart_PatientInterfaceStaging.Id
Where
tbl_Echart_PatientInterfaceStaging.SiteId = @pSiteId
And
tbl_Echart_Charts.IsCurrent = 1
And
tbl_Echart_PatientInterfaceStaging.StagingType 'R'
--#7
UPDATE a
SET a.AppointmentStatusTypeId = 7 --7 is the ID for the 'Released' appointment status type
FROM REQUESTS r
INNER JOIN Appointments a ON a.RequestId = r.Id
INNER JOIN tbl_echart_charts c ON c.id = r.chartid
INNER JOIN Patients p ON c.PatientId = p.Id
INNER JOIN LU_Service_Types st ON st.id = r.ServiceTypeId
INNER JOIN LU_TREATMENT_TYPES tt ON tt.id = r.TreatmentTypeId
WHERE (c.ReleaseDate '' AND c.ReleaseDate IS NOT NULL AND c.ReleaseDate '1900-01-01 00:00:00.000' AND c.ReleaseDate < GETDATE())
AND a.appointmentStatusTypeId = 1
AND tt.isCCSChangeable = 1
AND p.SiteId = @pSiteId
--#8
--Gets the Request IDs needing updated in the Request_Approvals table
DECLARE @RequestApprovalUpdates TABLE
(
RequestApprovalId int
)
INSERT INTO @RequestApprovalUpdates
SELECT r.id
FROM REQUESTS r
INNER JOIN request_approvals ra ON ra.RequestId = r.id
INNER JOIN tbl_echart_charts c on c.id = r.chartid
INNER JOIN Patients p on c.PatientId = p.Id
INNER JOIN LU_Service_Types st on st.id = r.ServiceTypeId
INNER JOIN LU_TREATMENT_TYPES tt on tt.id = r.TreatmentTypeId
WHERE (c.ReleaseDate '' AND c.ReleaseDate IS NOT NULL AND c.ReleaseDate '1900-01-01 00:00:00.000' AND c.ReleaseDate < GETDATE())
AND tt.isCCSChangeable = 1
AND ra.iscurrent = 1
AND ra.StatusTypeId 8--8 is 'complete' status; we only want to complete requests if they are not already complete
AND p.SiteId = @pSiteId
--Makes all previous Request Approval records 'not current' for the patients in question. The next Insert statement will insert the new current record
UPDATE ra
SET ra.Iscurrent = 0
FROM @RequestApprovalUpdates rau
INNER JOIN request_approvals ra ON rau.RequestApprovalId = ra.RequestId
WHERE ra.iscurrent = 1
--Inserts the new current Request Approval record with a 'Complete' status
INSERT INTO request_approvals
(
RequestId
,UserId
,StatusTypeId
,Comment
,RevisionDateTime
,IsFinal
,ConcurrencyId
,IsCurrent
)
SELECT
rau.RequestApprovalId
,1
,8
,'Closed due to patient release'
,GETDATE()
,0
,1
,1
FROM @RequestApprovalUpdates rau
--Clears the table after imports are done
Delete tbl_Echart_PatientInterfaceStaging Where SiteId = @pSiteId And StagingType 'D'
--If the the patient does not exist with a 'D' staging type, the patient is considered to be released, so add a discharge date if one is not present
Update A WITH (ROWLOCK)
set A.ReleaseDate = getdate()
From
tbl_Echart_Charts A
Inner Join Patients on A.PatientId = Patients.Id
left Outer Join tbl_Echart_PatientInterfaceStaging WITH (NOLOCK) on tbl_Echart_PatientInterfaceStaging.InmateNumber = Patients.InmateNumber
Where
A.IsCurrent = 1
And
Patients.SiteId = @pSiteId
And
tbl_Echart_PatientInterfaceStaging.InmateNumber is null
And
(A.ReleaseDate is null or A.ReleaseDate = '')
--Means there was a staging file present, If this check was not present it would discharge the whole site if a 'D' record was not present
And (Select count(*) from tbl_Echart_PatientInterfaceStaging where StagingType = 'D' And SiteId = @pSiteId) > 0
And getdate() > A.IncarcerationDate
Delete tbl_Echart_PatientInterfaceStaging Where SiteId = @pSiteId And StagingType = 'D'
September 9, 2009 at 7:56 am
--CREATE statement for tbl_echart_patientinterfacestaging:
USE [Ucare]
GO
/****** Object: Table [dbo].[tbl_Echart_PatientInterfaceStaging] Script Date: 09/09/2009 08:42:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Echart_PatientInterfaceStaging](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[InmateNumber] [varchar](50) NULL,
[BookingNumber] [varchar](50) NULL,
[NameLast] [varchar](50) NULL,
[NameFirst] [varchar](50) NULL,
[NameMiddle] [varchar](50) NULL,
[SSN] [varchar](11) NULL,
[DOB] [varchar](50) NULL,
[Sex] [varchar](50) NULL,
[Race] [varchar](50) NULL,
[LastKnownAddress] [varchar](50) NULL,
[LastKnownAddress2] [varchar](50) NULL,
[LastKnownCity] [varchar](50) NULL,
[LastKnownState] [varchar](50) NULL,
[LastKnownZip] [varchar](50) NULL,
[CustodyLevel] [varchar](50) NULL,
[IntakeDateTime] [varchar](50) NULL,
[PotentialReleaseDate] [varchar](50) NULL,
[ReleaseDate] [varchar](50) NULL,
[SiteId] [varchar](50) NULL,
[DepartmentId] [varchar](50) NULL,
[Loc_Display] [varchar](25) NULL,
[Loc_Facility] [varchar](25) NULL,
[Loc_Building] [varchar](25) NULL,
[Loc_Floor] [varchar](15) NULL,
[Loc_CellBlock] [varchar](15) NULL,
[Loc_Unit] [varchar](15) NULL,
[SiteCell] [varchar](15) NULL,
[Loc_Bed] [varchar](15) NULL,
[Loc_OffsiteCode] [varchar](15) NULL,
[MentalHealthClassification] [varchar](50) NULL,
[DateTime] [datetime] NULL,
[StagingType] [varchar](5) NULL,
[PatientId] [int] NULL,
[Transaction_DateTime] [varchar](50) NULL,
CONSTRAINT [PK_tbl_Echart_PatientInterfaceStaging] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--CREATE statement for the Patients:
USE [Ucare]
GO
/****** Object: Table [dbo].[PATIENTS] Script Date: 09/09/2009 08:43:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PATIENTS](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SiteId] [int] NOT NULL,
[EldoradoId] [int] NULL,
[InmateNumber] [nvarchar](15) NOT NULL,
[SSN] [nvarchar](11) NOT NULL,
[NameFirst] [nvarchar](25) NOT NULL,
[NameMiddle] [nvarchar](25) NULL,
[NameLast] [nvarchar](25) NOT NULL,
[NameFull] AS (((([NameFirst]+N' ')+[NameMiddle])+N' ')+[NameLast]),
[Sex] [nchar](1) NOT NULL,
[BirthDate] [datetime] NULL,
[Address_Primary] [nvarchar](50) NULL,
[Address_Secondary] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[State] [nvarchar](20) NULL,
[Zip] [nvarchar](20) NULL,
[CustodyDate] [datetime] NULL,
[ReleaseDatePotential] [datetime] NULL,
[ReleaseDateActual] [datetime] NULL,
[IsReleased] [bit] NOT NULL CONSTRAINT [DF_PATIENTS_IsReleased] DEFAULT ((0)),
[IsInfirmaryHoused] [bit] NOT NULL CONSTRAINT [DF_PATIENTS_IsInfirmaryHoused] DEFAULT ((0)),
[LastModifiedDate] [datetime] NULL CONSTRAINT [DF_PATIENTS_LastModifiedDate] DEFAULT (getdate()),
[ConcurrencyId] [int] NULL CONSTRAINT [DF_PATIENTS_ConcurrencyId] DEFAULT ((1)),
[InmateTypeId] [int] NOT NULL CONSTRAINT [DF_PATIENTS_InmateTypeId] DEFAULT ((1)),
[SiteDepartmentId] [int] NOT NULL,
[IsPreSentenced] [bit] NULL,
[IsSentenced] [bit] NULL,
[SentenceDate] [datetime] NULL,
[IsJuvenile] [bit] NULL,
[IsDeceased] [bit] NULL,
[Loc_Display] [nvarchar](25) NULL,
[Loc_Facility] [nvarchar](25) NULL,
[Loc_Building] [nvarchar](25) NULL,
[Loc_Floor] [nvarchar](15) NULL,
[Loc_CellBlock] [nvarchar](15) NULL,
[Loc_Unit] [nvarchar](15) NULL,
[Loc_Cell] [nvarchar](15) NULL,
[Loc_Bed] [nvarchar](15) NULL,
[Loc_OffsiteCode] [nvarchar](50) NULL,
[EldoradoSSN_Id] [varchar](50) NULL,
CONSTRAINT [PK_PATIENTS] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PATIENTS] WITH NOCHECK ADD CONSTRAINT [FK_PATIENTS_LU_INMATE_TYPES] FOREIGN KEY([InmateTypeId])
REFERENCES [dbo].[LU_INMATE_TYPES] ([ID])
GO
ALTER TABLE [dbo].[PATIENTS] CHECK CONSTRAINT [FK_PATIENTS_LU_INMATE_TYPES]
GO
ALTER TABLE [dbo].[PATIENTS] WITH CHECK ADD CONSTRAINT [FK_PATIENTS_PATIENTS] FOREIGN KEY([Id])
REFERENCES [dbo].[PATIENTS] ([Id])
GO
ALTER TABLE [dbo].[PATIENTS] CHECK CONSTRAINT [FK_PATIENTS_PATIENTS]
GO
ALTER TABLE [dbo].[PATIENTS] WITH NOCHECK ADD CONSTRAINT [FK_PATIENTS_SITE_DEPARTMENTS] FOREIGN KEY([SiteDepartmentId])
REFERENCES [dbo].[SITE_DEPARTMENTS] ([Id])
GO
ALTER TABLE [dbo].[PATIENTS] CHECK CONSTRAINT [FK_PATIENTS_SITE_DEPARTMENTS]
GO
ALTER TABLE [dbo].[PATIENTS] WITH NOCHECK ADD CONSTRAINT [FK_PATIENTS_SITES] FOREIGN KEY([SiteId])
REFERENCES [dbo].[SITES] ([Id])
GO
ALTER TABLE [dbo].[PATIENTS] CHECK CONSTRAINT [FK_PATIENTS_SITES]
--CREATE statement for tbl_echart_charts(incarcerations table):
USE [Ucare]
GO
/****** Object: Table [dbo].[tbl_Echart_Charts] Script Date: 09/09/2009 08:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Echart_Charts](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[PatientId] [int] NOT NULL,
[BookingNumber] [varchar](50) NULL,
[IncarcerationDate] [datetime] NOT NULL,
[ReleaseDate] [datetime] NULL,
[LastModifiedUser] [varchar](50) NULL,
[LastModifiedDate] [datetime] NULL,
[IsCurrent] [bit] NULL,
[DateCreated] [datetime] NULL,
CONSTRAINT [PK_tbl_Echart_Charts] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Echart_Charts] WITH CHECK ADD CONSTRAINT [FK_tbl_Echart_Charts_PATIENTS] FOREIGN KEY([PatientId])
REFERENCES [dbo].[PATIENTS] ([Id])
GO
ALTER TABLE [dbo].[tbl_Echart_Charts] CHECK CONSTRAINT [FK_tbl_Echart_Charts_PATIENTS]
--The following tables only apply to steps #7 and #8 in the stored procedure
--CREATE statement for Appointments:
USE [Ucare]
GO
/****** Object: Table [dbo].[APPOINTMENTS] Script Date: 09/09/2009 08:46:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[APPOINTMENTS](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[RequestId] [int] NOT NULL,
[AppointmentRecurrenceId] [int] NULL,
[AppointmentStatusTypeId] [int] NOT NULL,
[AppointmentDateTime] [datetime] NOT NULL,
[ConcurrencyId] [int] NULL CONSTRAINT [DF_APPOINTMENTS_ConcurrencyId] DEFAULT (1),
[AuditUserId] [nvarchar](250) NOT NULL CONSTRAINT [DF_APPOINTMENTS_AuditUserId] DEFAULT (suser_sname()),
[EldoradoInserted] [bit] NULL,
[TreatmentTypeId] [int] NULL,
CONSTRAINT [PK_APPOINTMENTS] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[APPOINTMENTS] WITH CHECK ADD CONSTRAINT [FK_APPOINTMENTS_APPOINTMENT_RECURRENCES] FOREIGN KEY([AppointmentRecurrenceId])
REFERENCES [dbo].[APPOINTMENT_RECURRENCES] ([Id])
GO
ALTER TABLE [dbo].[APPOINTMENTS] CHECK CONSTRAINT [FK_APPOINTMENTS_APPOINTMENT_RECURRENCES]
GO
ALTER TABLE [dbo].[APPOINTMENTS] WITH CHECK ADD CONSTRAINT [FK_APPOINTMENTS_LU_APPOINTMENT_STATUS_TYPES] FOREIGN KEY([AppointmentStatusTypeId])
REFERENCES [dbo].[LU_APPOINTMENT_STATUS_TYPES] ([Id])
GO
ALTER TABLE [dbo].[APPOINTMENTS] CHECK CONSTRAINT [FK_APPOINTMENTS_LU_APPOINTMENT_STATUS_TYPES]
GO
ALTER TABLE [dbo].[APPOINTMENTS] WITH NOCHECK ADD CONSTRAINT [FK_APPOINTMENTS_REQUESTS] FOREIGN KEY([RequestId])
REFERENCES [dbo].[REQUESTS] ([Id])
GO
ALTER TABLE [dbo].[APPOINTMENTS] CHECK CONSTRAINT [FK_APPOINTMENTS_REQUESTS]
--CREATE statement for LU_Service_Types:
USE [Ucare]
GO
/****** Object: Table [dbo].[LU_SERVICE_TYPES] Script Date: 09/09/2009 08:47:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LU_SERVICE_TYPES](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Name] [nvarchar](260) NOT NULL,
[Description] [nvarchar](500) NOT NULL CONSTRAINT [DF_LU_SERVICE_TYPES_Description] DEFAULT (N'N\A'),
[ConcurrencyId] [int] NULL CONSTRAINT [DF_LU_SERVICE_TYPES_ConcurrencyId] DEFAULT (1),
CONSTRAINT [PK_LU_SERVICE_TYPES] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
--CREATE statement for LU_Treatment_Types:
USE [Ucare]
GO
/****** Object: Table [dbo].[LU_TREATMENT_TYPES] Script Date: 09/09/2009 08:48:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LU_TREATMENT_TYPES](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Code] [nvarchar](50) NULL,
[Name] [nvarchar](260) NOT NULL,
[Description] [nvarchar](500) NOT NULL CONSTRAINT [DF_LU_TREATMENT_TYPES_Description] DEFAULT (N'N\A'),
[DisplayOrder] [int] NOT NULL CONSTRAINT [DF_LU_TREATMENT_TYPES_DisplayOrder] DEFAULT (0),
[ConcurrencyId] [int] NULL CONSTRAINT [DF_LU_TREATMENT_TYPES_ConcurrencyId] DEFAULT (1),
[OutpatientFlag] [bit] NULL,
[ServiceTypeId] [int] NULL,
[IsCCSChangeable] [bit] NULL,
CONSTRAINT [PK_LU_TREATMENT_TYPES] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
--CREATE statement for Requests:
USE [Ucare]
GO
/****** Object: Table [dbo].[REQUESTS] Script Date: 09/09/2009 08:50:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[REQUESTS](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SiteId] [int] NOT NULL,
[PatientId] [int] NOT NULL,
[ServiceTypeId] [int] NOT NULL,
[PlaceOfServiceTypeId] [int] NULL,
[UrgencyLevelTypeId] [int] NULL,
[TransportationTypeId] [int] NULL,
[FormularyExceptionTypeId] [int] NULL,
[TreatmentTypeId] [int] NULL,
[TreatmentNumber] [int] NULL,
[TreatmentPreviousResponse] [nvarchar](500) NULL,
[ProviderName] [nvarchar](75) NULL,
[ProviderTaxId] [nvarchar](20) NULL,
[ProcedureRequested] [nvarchar](450) NULL,
[ExamResults] [nvarchar](500) NULL,
[AbilityADLs] [nvarchar](400) NULL,
[Diagnosis] [nvarchar](500) NULL,
[MedicalNecessity] [nvarchar](500) NULL,
[NotProvidedResult] [nvarchar](500) NULL,
[ReasonForVisit] [nvarchar](500) NULL,
[HospitalName] [nvarchar](100) NULL,
[HospitalTaxId] [nvarchar](20) NULL,
[HospitalPhone] [nvarchar](20) NULL,
[EstimatedCost] [money] NULL,
[IsEmergencyRoomVisit] [bit] NULL,
[IsInpatientStay] [bit] NULL,
[DurationOfStay] [int] NULL,
[IsFirstConsultation] [bit] NULL,
[IsCharge] [bit] NULL,
[AnticipatedReleaseDate] [datetime] NULL,
[ServiceDate] [datetime] NULL,
[InitialEvaluationDate] [datetime] NULL,
[SurgeryDate] [datetime] NULL,
[AdmissionDate] [datetime] NULL,
[DischargeDate] [datetime] NULL,
[FormularyExceptionJustification] [nvarchar](500) NULL,
[MedicationRequested] [nvarchar](500) NULL,
[SupportingEvidence] [nvarchar](500) NULL,
[IsDrugNotInCategory] [bit] NULL,
[IsDrugInadequate] [bit] NULL CONSTRAINT [DF_REQUESTS_IsDrugInadequate] DEFAULT ((0)),
[DrugInadequateDetails] [nvarchar](500) NULL,
[IsDrugAllergy] [bit] NULL CONSTRAINT [DF_REQUESTS_IsDrugAllergy] DEFAULT ((0)),
[DrugAllergyDetails] [nvarchar](500) NULL,
[RequestCode] [nvarchar](20) NULL,
[AuthorizationCode] [nvarchar](20) NULL,
[ReviewerId] [int] NULL,
[ReviewDate] [datetime] NULL,
[ReviewComments] [nvarchar](max) NULL,
[LastModifiedDate] [datetime] NULL CONSTRAINT [DF_REQUESTS_LastModifiedDate] DEFAULT (getdate()),
[ConcurrencyId] [int] NULL CONSTRAINT [DF_REQUESTS_ConcurrencyId] DEFAULT ((1)),
[InjuryIllnessHistory] [nvarchar](500) NULL,
[RequestingProvider] [nvarchar](75) NULL,
[LastFUDate] [datetime] NULL,
[IsPrebookingEvent] [bit] NOT NULL CONSTRAINT [DF_REQUESTS_IsPrebookingEvent] DEFAULT ((0)),
[IsPrebookingEventLiable] [bit] NULL,
[ForwardingLevel] [int] NULL,
[IsInfirmaryHoused] [bit] NOT NULL CONSTRAINT [DF_REQUESTS_IsInfirmaryHoused] DEFAULT ((0)),
[HistoryStatusId] [int] NULL,
[IsPreExisting] [bit] NULL,
[IsSentenced] [bit] NULL,
[IsPreSentenced] [bit] NULL,
[SentenceDate] [datetime] NULL,
[EstimatedCharges] [money] NULL,
[EMREncounterID] [varchar](50) NULL,
[IsInmateViolence] [bit] NULL,
[ChartId] [int] NULL,
[IsWorkerComp] [bit] NULL,
[IsPsychotropic] [bit] NULL,
[isIce] [bit] NULL,
[isprobableinmateviolence] [bit] NULL,
[SitedepartmentId] [int] NULL,
CONSTRAINT [PK_REQUESTS] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[REQUESTS] WITH NOCHECK ADD CONSTRAINT [FK_REQUESTS_LU_FORMULARY_EXCEPTION_TYPES] FOREIGN KEY([FormularyExceptionTypeId])
REFERENCES [dbo].[LU_FORMULARY_EXCEPTION_TYPES] ([ID])
GO
ALTER TABLE [dbo].[REQUESTS] CHECK CONSTRAINT [FK_REQUESTS_LU_FORMULARY_EXCEPTION_TYPES]
GO
ALTER TABLE [dbo].[REQUESTS] WITH NOCHECK ADD CONSTRAINT [FK_REQUESTS_LU_PLACE_OF_SERVICE_TYPES] FOREIGN KEY([PlaceOfServiceTypeId])
REFERENCES [dbo].[LU_PLACE_OF_SERVICE_TYPES] ([Id])
GO
ALTER TABLE [dbo].[REQUESTS] CHECK CONSTRAINT [FK_REQUESTS_LU_PLACE_OF_SERVICE_TYPES]
GO
ALTER TABLE [dbo].[REQUESTS] WITH NOCHECK ADD CONSTRAINT [FK_REQUESTS_LU_SERVICE_TYPES] FOREIGN KEY([ServiceTypeId])
REFERENCES [dbo].[LU_SERVICE_TYPES] ([Id])
GO
ALTER TABLE [dbo].[REQUESTS] CHECK CONSTRAINT [FK_REQUESTS_LU_SERVICE_TYPES]
GO
ALTER TABLE [dbo].[REQUESTS] WITH NOCHECK ADD CONSTRAINT [FK_REQUESTS_LU_TRANSPORTATION_TYPES] FOREIGN KEY([TransportationTypeId])
REFERENCES [dbo].[LU_TRANSPORTATION_TYPES] ([Id])
GO
ALTER TABLE [dbo].[REQUESTS] CHECK CONSTRAINT [FK_REQUESTS_LU_TRANSPORTATION_TYPES]
GO
ALTER TABLE [dbo].[REQUESTS] WITH NOCHECK ADD CONSTRAINT [FK_REQUESTS_LU_TREATMENT_TYPES] FOREIGN KEY([TreatmentTypeId])
REFERENCES [dbo].[LU_TREATMENT_TYPES] ([Id])
GO
ALTER TABLE [dbo].[REQUESTS] CHECK CONSTRAINT [FK_REQUESTS_LU_TREATMENT_TYPES]
GO
ALTER TABLE [dbo].[REQUESTS] WITH NOCHECK ADD CONSTRAINT [FK_REQUESTS_LU_URGENCY_LEVEL_TYPES] FOREIGN KEY([UrgencyLevelTypeId])
REFERENCES [dbo].[LU_URGENCY_LEVEL_TYPES] ([Id])
GO
ALTER TABLE [dbo].[REQUESTS] CHECK CONSTRAINT [FK_REQUESTS_LU_URGENCY_LEVEL_TYPES]
GO
ALTER TABLE [dbo].[REQUESTS] WITH NOCHECK ADD CONSTRAINT [FK_REQUESTS_PATIENTS] FOREIGN KEY([PatientId])
REFERENCES [dbo].[PATIENTS] ([Id])
GO
ALTER TABLE [dbo].[REQUESTS] CHECK CONSTRAINT [FK_REQUESTS_PATIENTS]
GO
ALTER TABLE [dbo].[REQUESTS] WITH NOCHECK ADD CONSTRAINT [FK_REQUESTS_SITES] FOREIGN KEY([SiteId])
REFERENCES [dbo].[SITES] ([Id])
GO
ALTER TABLE [dbo].[REQUESTS] CHECK CONSTRAINT [FK_REQUESTS_SITES]
GO
ALTER TABLE [dbo].[REQUESTS] WITH NOCHECK ADD CONSTRAINT [FK_REQUESTS_USERS] FOREIGN KEY([ReviewerId])
REFERENCES [dbo].[USERS] ([Id])
GO
ALTER TABLE [dbo].[REQUESTS] CHECK CONSTRAINT [FK_REQUESTS_USERS]
--CREATE table for Request_Approvals:
USE [Ucare]
GO
/****** Object: Table [dbo].[REQUEST_APPROVALS] Script Date: 09/09/2009 08:51:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REQUEST_APPROVALS](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[RequestId] [int] NOT NULL,
[UserId] [int] NOT NULL,
[StatusTypeId] [int] NOT NULL,
[Comment] [nvarchar](max) NULL,
[RevisionDateTime] [datetime] NOT NULL CONSTRAINT [DF_REQUEST_APPROVALS_RevisionDateTime] DEFAULT (getdate()),
[IsFinal] [bit] NULL CONSTRAINT [DF_REQUEST_APPROVALS_IsFinal] DEFAULT (0),
[ConcurrencyId] [int] NULL CONSTRAINT [DF_REQUEST_APPROVALS_ConcurrencyId] DEFAULT (1),
[IsCurrent] [bit] NULL,
[RequestsRefferalId] [int] NULL,
CONSTRAINT [PK_REQUEST_APPROVALS] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[REQUEST_APPROVALS] WITH CHECK ADD CONSTRAINT [FK_REQUEST_APPROVALS_LU_STATUS_TYPES] FOREIGN KEY([StatusTypeId])
REFERENCES [dbo].[LU_STATUS_TYPES] ([Id])
GO
ALTER TABLE [dbo].[REQUEST_APPROVALS] CHECK CONSTRAINT [FK_REQUEST_APPROVALS_LU_STATUS_TYPES]
GO
ALTER TABLE [dbo].[REQUEST_APPROVALS] WITH NOCHECK ADD CONSTRAINT [FK_REQUEST_APPROVALS_REQUESTS] FOREIGN KEY([RequestId])
REFERENCES [dbo].[REQUESTS] ([Id])
GO
ALTER TABLE [dbo].[REQUEST_APPROVALS] CHECK CONSTRAINT [FK_REQUEST_APPROVALS_REQUESTS]
All of the steps in the stored procedure I posted are in the right order. They need to happen in that order for each site's files. We need to process a file as soon as possible after it arrives. That's one reason why we run the jobs on a separate schedule for each site. Another reason we run on a separate schedule for each site is because different sites can use the same inmate numbers and booking numbers - by passing in a site ID, we can ensure uniqueness
Thanks again for you offer to help guys! I really appreciate it! 🙂
Thanks again for the offer to help guys! I really appreciate it.
September 9, 2009 at 10:43 am
Sorry to keep piling stuff on you, but I thought you may want to know this. We've actually refactored step #8 in the sotred procedure to look like this:
INSERT INTO request_approvals
(
RequestId
,UserId
,StatusTypeId
,Comment
,RevisionDateTime
,IsFinal
,ConcurrencyId
,IsCurrent
)
SELECT
ra.RequestId
,1
,8
,'Closed due to patient release'
,GETDATE()
,0
,1
,1
FROM REQUESTS r WITH (NOLOCK)
INNER JOIN request_approvals ra WITH (NOLOCK) ON ra.RequestId = r.id
INNER JOIN tbl_echart_charts c WITH (NOLOCK) on c.id = r.chartid
INNER JOIN Patients p WITH (NOLOCK) on c.PatientId = p.Id
INNER JOIN LU_Service_Types st WITH (NOLOCK) on st.id = r.ServiceTypeId
INNER JOIN LU_TREATMENT_TYPES tt WITH (NOLOCK) on tt.id = r.TreatmentTypeId
WHERE (c.ReleaseDate '' AND c.ReleaseDate IS NOT NULL AND c.ReleaseDate '1900-01-01 00:00:00.000' AND c.ReleaseDate < GETDATE())
AND tt.isCCSChangeable = 1
AND ra.iscurrent = 1
AND ra.StatusTypeId 8--8 is 'complete' status; we only want to complete requests if they are not already complete
AND R.SiteId = @pSiteId
There was no need to hold the values in a table variable to use with the update statement, because a trigger already does the same thing as the update statement (no one bothered to tell me when I wrote that code).
September 10, 2009 at 10:21 am
50 deadlocks over a weekend is not insane. (It's not terrific, but not insane)
You realize you can now (>=SQL2005) trap deadlocks in your TSQL with a try/catch (hereafter TC) block, right?
If I were working through the details on this-- I'd wrap each INSERT/UPDATE/DELETE in a TC.
If you want to do this in a very granular way you could start by just having the TC block let you know which part of your routine is deadlocking, then zero in on it for tuning.
If you are of the "ton of bricks" school of thought, build each TC block so it checks for deadlock and retries after a random time delay*, n** times. If it does not succeed after n attempts let it drop out as a deadlock. To see what's happening you probably should include some logging code.
* "random time delay" This will depend on your environment. You'll need to put together a random number udf or usp to set the random wait time. Start with a small delay... something like a randomized 0-4 second delay.
** "n times" Again depends on your world-- maybe retry 3 times.
September 16, 2009 at 6:33 am
Actually, I don't think I did realize we could use Try-Catch (I did mention I'm a relatively new DBA, right?).
Any way, that's exactly what I've done for now; put each batch inside a Try-Catch and write the error to the application event log. The "wait-time stuff" could probably be useful too. We may still need to refactor some stuff later, and if we keep adding site interfaces at this rate, we're going to have to split up our database anyway. However, I think this is a great first step for us.
Thanks for the suggestion!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply