The WHERE clause is evaluated before the SELECT clause (which is where the ROW_NUMBER is defined), therefore the WHERE cannot reference the ROW_NUMBER in the same (sub)query where the ROW_NUMBER...

SQLBill (9/14/2016)

---

While probable not quite accurate...the way to think about is this way...when using a single # think of it as being owned by the schema that made it:

Conn1.temp01

Conn2.temp01

When using...

The Dixie Flatline (9/15/2016)

---

What about those two tables is not normalized?

select p.id, p.FirstName, p.LastName, p.dob, pf.pos,pf.Hobby, pf.Color, pf.SQLConference

from @Person p

join @Person_Favorites pf on p.ID = pf.ID

--where pf.SQLConference like 'SQL%'

--where color...

Michael L John (9/15/2016)

---

It also appears that you may have a logic error in your code:

WHERE

CONVERT(varchar(20), H.TransactionDate, 101) >= @StartDate

AND CONVERT(varchar(20), H.TransactionDate, 101) <= @EndDate

If...

Both Alan.B and Gazareth started with the summarized data. Here is how you would do it starting with the raw data.

DECLARE @table TABLE (stateName varchar(20), Amount int);

INSERT @table

VALUES

('Alabama',200),

('Alabama',200),

('Alabama',200),

('Alabama',200),

('Alabama',200),

('Arizona',2000),

('Arizona',2000),

('Arkansas',2000),

('Arkansas',2000),

('Arkansas',1000)

;

SELECT...

John Mitchell-245523 (9/15/2016)

---

Looks fairly simple - you're just joining all three tables together, and using a MAX or MIN to get the top sale per invoice. Is there any...

A TOP(n) requires an order, which you haven't specified, but there is only one field that will give your specified results, so I have used that field.

SELECT InvRef, Amount, Date,...

j-1064772 (9/15/2016)

---

tripleAxe (9/15/2016)

---

Some basic testing on my VM using the original table as posted but with 12 million rows of data has the UNION ALL query taking 6 seconds with...

Clean up your data before submitting it to your function.

DECLARE @input VARCHAR(250) = 'abc,def,ghi,123,jkl,345,678,mno,901,234,567,pqr'

WHILE PATINDEX('%[0-9],[0-9]%', @input) > 0

SET @input = STUFF(@input, PATINDEX('%[0-9],[0-9]%', @input) + 1, 1, '')

You should also change...

This is his most recent question:...

Jason A. Long (9/14/2016)

---

Jeff Moden (9/14/2016)

---

Although that's great use of the CROSS APPLY trick to unpivot, the output isn't the same as the requested output, which appears to be a...

I think someone is in way over their head. Maybe we should just tell him to find another line of work.

Drew

Dude! This goes well beyond the scope of a free forum.

Drew

This is T-SQL, not some idealized theoretical version of SQL. T-SQL...

This solution requires 2012, since it uses LEAD, FIRST_VALUE, and LAST_VALUE.

WITH holding_ends AS (

SELECT *,

CASE

WHEN h.Amount = 0 THEN h.EffectiveDate

WHEN h.EffectiveDate = FIRST_VALUE(h.EffectiveDate) OVER(PARTITION BY h.Holder ORDER BY h.EffectiveDate ROWS...