I'm afraid that doesn't make things any clearer. Remember, I can't see these tables and I don't know what you want for a result. The words...

That has some nasty potential performance with the OR in the JOIN. As we don't know much about the actual desired result, I decided making a guess such...

With the following code, you can at least get the most recent historical record for each personid:

;WITH MOST_RECENT AS (

SELECT PH.fkPersonId, MAX(PH.DateRecordAdded) AS DateRecordAdded

FROM tblPersonInstitutionHistorical AS PH

GROUP BY PH.fkPersonId

)

SELECT H.*

FROM...

Without some sample data to work with, I'm guessing here, and I suspect you have an INNER JOIN where you need a LEFT OUTER JOIN. Also, your query...

I'm not even sure you need a row number function. You appear to just be selecting the distinct values for the CUSTOMER, PLANNBR, SUBPLAN, STARTDATE, and ENDDATE, which...

I don't have a lot of experience in this area, but some things to start looking at include checking for use of DEPRECATED SQL. I'm not sure...

Glad I could help, and thanks for the update.

Jeff Moden (7/10/2014)

Oh... be careful now... Simpler isn't always better. That uses an rCTE that "Counts" and it's a known performance and resource usage issue. There are many...

RTovey1 (7/10/2014)

---

I had the exact same problem as the OP. I am relatively new to SQL and don't fully understand why but I was able to get my dynamic query...

There's a much simpler query:

DECLARE @DATE AS datetime = '07/10/2014';

DECLARE @TIME_START_INT AS int = 820;

DECLARE @TSV AS varchar(4) = RIGHT('00' + CAST(@TIME_START_INT AS varchar(4)), 4);

DECLARE @TIME_END_INT AS int = 1000;

DECLARE...

Try this:

DECLARE @myTable AS TABLE (

cust_id int PRIMARY KEY CLUSTERED,

net_id int,

notes_CDATA varchar(20)

)

INSERT INTO @myTable VALUES(1, 1, 'description')

DECLARE @XF AS XML

SELECT @XF = (

SELECT *

FROM (SELECT 1 AS Tag

, NULL AS...

Given your additional info, this suggests that once you have the query plan, take a close look at the indexes on the B table. See if there are...

Have you looked at the query's execution plan? This would likely reveal the reason. Also, is there more than one record in the table with...

You might want to actually compare the suggested index creation text with the actual index's creation script, and see if the existing index actually covers the predicate(s?) you have in...

WITH ROLLUP is the option you need for a GROUP BY query.

DECLARE @tblEmployee AS table (

id int primary key identity (1, 1),

salaryLastYear int,

salaryCurrentYear int

)

insert into @tblEmployee

values (10, 20), (20, 30),...