You should consider using CTE's for this query, to make it more readable if nothing else:

;WITH

A AS (SELECT rn = ROW_NUMBER() OVER(PARTITION BY NIF_ANTIGO ORDER BY Cod_Bairro),

* -- substitute...

hnkumar (6/26/2012)

---

Hi,

I'm facing a strange problem. We have 2 queries to fetch data between dates. However both queries are giving different values. First query is giving 8987...

You can't use dynamic SQL in a function - if it was valid dynamic SQL, it would return an error. What it's doing is creating a string:

A.NIF_ANTIGO NOT IN

('SELECT...

DATEADD(dd, -1, @year + '-' + 'select * from mon_allstat('+ b.shpdate + ')' + '-01')

This isn't valid syntax.

Can you post the structure of function mon_allstat()?

Duncan Pryde (6/26/2012)

---

Thanks both. I didn't think there would be any way to improve it much with an index alone, but it's good to get confirmation I'm not missing anything...

sambireddy chirra (6/26/2012)

---

Hi Vijayarani,

In my Requirement EmpId in Employee table have 101, same refearence table Employee_Details have 1011 (1012,1013 ) like . how it possible for the below query.

select *...

SELECT *

FROM

(SELECT

rn = ROW_NUMBER() OVER(PARTITION BY NIF_ANTIGO ORDER BY Cod_Bairro),

[NIF_ANTIGO],...

Lowell (6/26/2012)

---

a query with Column1 > Column2 best plan is going to be a index scan, right? it has to use the index to get all values greater than to...

Why return it at all, if you don't care about the value?

river1 (6/26/2012)

---

I have seens that they have only a diferente value. It's the cod_bairro. It the three records only this field has diferente values....

How can I return a distinct...

Jeff Moden (6/25/2012)

---

ChrisM@Work (6/25/2012)

---

This works quite nicely too:

SELECT

EmployeeID,

Email,

DomainLHS = SUBSTRING(Email, x.PosStart,x.PosEnd-x.PosStart),

DomainRHS = SUBSTRING(Email, y.PosStart,y.PosEnd-y.PosStart)

FROM @t

CROSS APPLY (

SELECT PosStart = 1+CHARINDEX('@',EMAIL,1),

PosEnd = NULLIF(CHARINDEX('.',EMAIL,CHARINDEX('@',EMAIL,1)),0)

) x

CROSS APPLY (

SELECT PosStart =...

How many rows are returned by each of the following queries:

-- query 1

SELECT vw.SourceSiteID

FROM dbo.PNTSOURCEM_PLYSOURCEM vw

WHERE vw.SubHabitat LIKE N'%\WUS'

-- query 2

SELECT vw.SourceSiteID

FROM dbo.PNTSOURCEM_PLYSOURCEM vw

INNER JOIN sde.POLYNPDESSURVEY ps ON...

;WITH SampleData AS (

SELECT InputString = 'UMA Titanium Temporary Hex Cylinder Product Code: 11-000300 <span style="color:#f00;">Availability: 6/15/2012</span>' UNION ALL

SELECT 'UMA Hex Cylinder Product Code: 11-000200 <span style="color:#f00;">Availability: 8/2012</span>' UNION...

If the query above doesn't match the spec, then you can still make a big improvement to the design by rearranging the two correlated subquery filters. You don't need to...

The correlated subqueries in the output, and the correlated subqueries in the WHERE clause, are correlated on different columns. Focussing on Author, here's the output query:

SELECT BookAuthorName= LTrim(Rtrim(STUFF((

SELECT ',' +...