I think this query will give you what you need in separate rows. If so, let us know, and we can pivot it to a single row.

DECLARE @control_date datetime

SET...

I suggest:

1) be sure to pad the month and day values so that the final format is a full yyyymmdd.

2) apply column names to make the code easier to understand...

WHERE

column_name LIKE '[a-z]%'

Lynn Pettis (4/17/2014)

---

ScottPletcher (4/17/2014)

---

Lynn Pettis (4/17/2014)

---

ScottPletcher (4/17/2014)

---

You need to do further research before you change the existing index to a filtered index, because that index will no longer satisfy queries...

SpeedySQL (4/17/2014)

---

If you're considering upgrading to SQL 2012, be aware that the licensing model is changing to per core. Another factor you may want to look at apart from...

Lynn Pettis (4/17/2014)

---

ScottPletcher (4/17/2014)

---

You need to do further research before you change the existing index to a filtered index, because that index will no longer satisfy queries originally using it...

You need to do further research before you change the existing index to a filtered index, because that index will no longer satisfy queries originally using it if you filter...

gbritton1 (4/16/2014)

---

Or, in that same snippet, take advantage of the fact that you initialize the variable @CompleteSetFound to 0 in the declaration. Then you can simplify to:

IF EXISTS (

...

I think this coding could theoretically perform better:

...

IF (@AnimalsFound = 1)

BEGIN

SET @CompleteSetFound =

CASE WHEN EXISTS(SELECT

1

FROM @FarmMatches ri

INNER JOIN [dbo].[Animal] ani ON ani.[FeatureID] = ri.[OverallID]

GROUP BY

FeatureID, Code

HAVING

MAX(CASE WHEN TypeName...

I suggest changing one of your existing indexes to also cover this query:

CREATE NONCLUSTERED INDEX [AI_OperatorCBGEstimate_14947]

ON OperatorCBGEstimate ( DateEndedStandard, UpdOperation )

...

I've coded checks for just Animal table groupings. Please check and see if it produces the results you want.

Edit: This is doing only full matches but partial matches could...

It's possible but it's huge overhead you should probably avoid.

You can use ROW_NUMBER() to sequentially number the data when you read it rather than having to physically update the values...

You could try something like this and see if it helps:

WITH cte_OrderProjectType AS

(

select A.Orderid, min(B.TypeID) , min(C.CTType) , MIN(D.Area)

from tableA A inner join (

select PID,...

Please add an alias to columns in the SELECT:

select ?.Orderid, min(?.TypeID) , min(?.CTType) , MIN(?.Area)

Otherwise we can't really rewrite any of the code.

What specifically does the data in the "Verdate" look like?

For example, is it 'yyyymmdd hh:mm'?