I think you can simplify the query something like below, although some tweaking might need done:

SELECT

s.state_name,

derived.NumOfDupPermits, derived.AmendedDups, derived.DistinctPermits

FROM (

...

andrew gothard (3/27/2014)

---

ScottPletcher (3/27/2014)

---

yuvipoy (3/27/2014)

---

Sean Lange (3/27/2014)

---

True. I have worked with this OP on several threads and the problem is performance because they have several related tables and the primary...

Hmm, I think the log file should...

yuvipoy (3/27/2014)

---

Sean Lange (3/27/2014)

---

True. I have worked with this OP on several threads and the problem is performance because they have several related tables and the primary key (and clustered...

I think you're looking for LEFT OUTER JOIN, something like this:

SELECT

c.CaseID, c.Resolution,

COALESCE(CAST(s.CaseID AS varchar(10)), 'DUMMY-' + c.Resolution) AS SubCaseID

...

I don't see any need for a pre-built proc for:

Get All of Foo

Just SELECT the columns you need from the table instead.

For the rest, if you're willing to use dynamic...

Based on the query plan, I think two these things 100% need done and should help:

1)

Table: SubOrderTrans

Index: idx_subordertransday

INCLUDE column SubOrderTransDeleted in that index

2)

Table: SUBORDER_ETA_CORRECTIONS

Index: IDX_NC_SubOrderID

Change it to a clustered index,...

Current query is not SARGable and is somewhat convoluted as well.

SELECT DISTINCT TOP 7

Convert(DateTime, Datediff(Day, 0, DateCreated)) AS DateCreated,

Datepart(dw,DateCreated) AS WeekNum...

Grant Fritchey (3/25/2014)

---

ScottPletcher (3/25/2014)

---

Grant Fritchey (3/25/2014)

---

ScottPletcher (3/25/2014)

---

Grant Fritchey (3/25/2014)

---

I can't see the query since it exceeds the size of what's stored in the plan, so suggestions from the plan alone...

Roughly like below. You may have to adjust the "SINGLE_NCLOB" to "SINGLE_CLOB", depending on the format of the input file.

DECLARE @file_text varchar(max)

SELECT @file_text = file_text

FROM OPENROWSET(BULK 'd:\full\path\to\file\filename.txt', SINGLE_NCLOB) AS...

Gotta' admit, I prefer working in SQL/TSQL, so even for (moderately) large text files I'll often just do an:

OPENROWSET(BULK '<path_and_file_name>', SINGLE_CLOB) --or NCLOB, if req'd

to load the whole file at...

Grant Fritchey (3/25/2014)

---

ScottPletcher (3/25/2014)

---

Grant Fritchey (3/25/2014)

---

I can't see the query since it exceeds the size of what's stored in the plan, so suggestions from the plan alone are going to...

Hard to provide more details without the rest of the query and with no index info on the tables.

Edit: Looks like a covering index is needed on SubOrderTrans at least,...

Grant Fritchey (3/25/2014)

---

I can't see the query since it exceeds the size of what's stored in the plan, so suggestions from the plan alone are going to be a bit...

Best guess with what's known so far:

Create an index on "order":

key = ( claim_id ); include = ( order_id, created_dtm )