Final working code, thanks, Seth.

DELETE FROM ptSchedule

INSERT INTO ptSchedule

([MRN]

, [ApptTypeID]

, [ResourceId]

, [StartDtTm]

, [EndDtTm]

, [Description]

, [Notes]

...

Take a look at my code to do the insert, that should show you what I'm trying to do.

I don't see what that does for me.

Creation and dummy data scripts avail here:

cross_reference_problem.sql

While that helps me some, I can't make it work in this:

DELETE FROM ptSchedule

WITH MyAppointments (ResourceName)

AS (SELECT REPLACE(REPLACE(REPLACE(Resource, '-SOUTH', ''), '-NORTH', ''), 'CHRISTOPHER J ', '')

...

will post the exec plans and index data tomorrow morn.

Here is all data for the MRN 5079

TestData.zip

I guess I can but getting past the speed issue seems to be the biggest problem.

smunson (10/15/2008)

---

Ok, how about the following:

CREATE PROCEDURE dbo.PTENCOUNTER_STUFF (

@MRN varchar(5), -- '5079'

@DIC_Status varchar(10),

@ApptTypeID smallint

)

AS

BEGIN

SET NOCOUNT ON

;WITH DICTATION AS (

SELECT MRN, Status, DOS

FROM ptDictation

WHERE MRN = @MRN AND

Status = @DIC_Status

),

SCHEDULE AS...

I will try but there are 340,000 ptEncounter records, 610,000 ptDictation records...

Matt Miller (10/15/2008)

---

MrB -

Did you ever get a chance to try my version? Assuming you get the right results - it should be a little faster than your...

Gary...

This one returned 1481416 records in 23 secs...

HOWEVER, there are only 340501 records in the ptEncounter table.

Gary Johnson (10/10/2008)

---

SELECT e.MRN,

CASE

...

Yes, just as Gary showed but it didn't help.

HUH?

Essentially, I will be providing indicators in the UI based on the values returned in the

DictationState column returned from the stored procedure based on this criteria:

•Green – An entry in...