ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,181 through 6,195 (of 10,143 total)

RE: Which is the best way to query data between 2 dates

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 26, 2012 at 7:44 am

#1505567
RE: Distinct

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 26, 2012 at 6:58 am

#1505539
RE: Syntax error converting datetime from character string while calling a function

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()?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 26, 2012 at 6:17 am

#1505518
RE: How does one improve the performance of a "colA > colB" query?

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 26, 2012 at 6:06 am

#1505515
RE: How to Join Two tables with row numbers.

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 *...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 26, 2012 at 5:46 am

#1505508
RE: Distinct

river1 (6/26/2012)
It Does not matter what is the value, but it most be a value from the field
SELECT *
FROM
(SELECT
rn = ROW_NUMBER() OVER(PARTITION BY NIF_ANTIGO ORDER BY Cod_Bairro),
[NIF_ANTIGO],...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 26, 2012 at 5:44 am

#1505506
RE: How does one improve the performance of a "colA > colB" query?

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 26, 2012 at 5:18 am

#1505496
RE: Distinct

river1 (6/26/2012)
...
As to the value of cod_bairro it can retrieve any of them. I don't mind
Why return it at all, if you don't care about the value?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 26, 2012 at 4:47 am

#1505483
RE: Distinct

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 26, 2012 at 4:35 am

#1505478
RE: Compare the text in a string (Re-post)

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 =...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 26, 2012 at 3:29 am

#1505463
RE: Inner Join Duplicate Values

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 26, 2012 at 3:23 am

#1505462
RE: What would be the best way to get the date from the following strings?

;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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 26, 2012 at 3:11 am

#1505459
RE: Query Taking ages to execute

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 26, 2012 at 2:13 am

#1505447
RE: Query Taking ages to execute

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 ',' +...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 26, 2012 at 2:09 am

#1505445
RE: Compare the text in a string (Re-post)

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 = NULLIF(1+CHARINDEX('@',EMAIL,x.PosEnd),1),
PosEnd =...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 25, 2012 at 9:37 am

#1505118

Viewing 15 posts - 6,181 through 6,195 (of 10,143 total)