ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,741 through 4,755 (of 10,143 total)

RE: Delete other than top 2 rows for each foreign key element in table.

faheemahmad14 (4/25/2013)
thanks a lot for your prompt response
but my requirement is not to use RowNumber or any other builtin function.
ROW_NUMBER is ideal for this - can you explain why you...
^{“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

April 25, 2013 at 7:04 am

#1609920
RE: Need help on creating Left outer Join

SELECT *
FROM #Table1 t1
CROSS APPLY (
SELECT TheDate = CONVERT(DATE,'01/'+LEFT(t1.Period,2)+'/'+RIGHT(t1.Period,4),103)
) x
OUTER APPLY (
SELECT *
FROM #Table2 t2
WHERE t2.Name = t1.Name
AND CONVERT(DATE,'01/'+LEFT(t2.Period,2)+'/'+RIGHT(t2.Period,4),103)
BETWEEN DATEADD(mm,-6,x.TheDate) AND x.TheDate
) y
^{“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

April 25, 2013 at 6:47 am

#1609912
RE: Delete other than top 2 rows for each foreign key element in table.

Thanks Ron.
^{“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

April 25, 2013 at 6:30 am

#1609902
RE: column update based on calculation and group by

Abu Dina (4/25/2013)
Was waiting for your solution 😀 Nice!
Thank you! 🙂
^{“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

April 25, 2013 at 6:11 am

#1609896
RE: The maximum recursion 100 has been exhausted

Use the MAXRECURSION hint:
SELECT columns
FROM recursivecte
OPTION (MAXRECURSION 0); -- unlimited
In practice you might want to set it to a sensible value then increment, JIC your CTE loops.
^{“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

April 25, 2013 at 5:59 am

#1609889
RE: column update based on calculation and group by

-- check the query
SELECT *, UpdateMe = CASE WHEN a.SummaryType <> '0' THEN 'Y' ELSE 'N' END
FROM Table_A a
OUTER APPLY (
SELECT Result = (SUM(ai.Pass)/NULLIF(SUM(ai.VM_Id)*1.0,0)) * 100
FROM Table_A ai
WHERE...
^{“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

April 25, 2013 at 5:51 am

#1609887
RE: Delete other than top 2 rows for each foreign key element in table.

-- Always run a SELECT first to see which rows are affected.
-- This query should return the rows you want to keep
SELECT MemberId, [Event], [Event Date]
FROM (
SELECT MemberId, [Event], [Event...
^{“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

April 25, 2013 at 5:29 am

#1609879
RE: Compare data between rows of same table

-- Since there are no matches on col1 between the two tables,
-- surely the starting point of the results table should reflect this?
t.col1 ...
^{“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

April 25, 2013 at 4:44 am

#1609864
RE: Compare data between rows of same table

iam777here4u (4/25/2013)
Sorry for the trouble. I hope below code will give better idea....
No worries. I like this better, can we use it? I think folks will find it easier 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

April 25, 2013 at 4:40 am

#1609859
RE: to find specific position string

chalam87 (4/25/2013)
hi,
before finding sub string you should trim the data otherwise it takes empty data also.
select id,SUBSTRING(Reverse(LTRIM(RTRIM(name))),3,1) from emp
;WITH Sampledata (id, name) AS (
SELECT 1 , 'srinivas ' 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

April 25, 2013 at 4:31 am

#1609855
RE: Error converting varchar to numeric only in where clause

tom.wauters (4/25/2013)
@Lynn, indeed, I have read it, I also did a test and concatenated 'Value' with 'e0', and still the IsNumeric evaluates too 1, so it really always is a...
^{“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

April 25, 2013 at 4:19 am

#1609851
RE: Compare data between rows of same table

iam777here4u (4/25/2013)
That query is having a problem, lets say the records with id value 4 in column mytab1col1 doesn't have any mismatch. The result still displays id 4, which it...
^{“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

April 25, 2013 at 4:08 am

#1609850
RE: Error converting varchar to numeric only in where clause

This works too:
WITH CteComplement AS (
SELECT *, CONVERT(numeric(14,2),Value) as Amount
FROM #Test
WHERE PathId IN (30,31)
)
-- Cte to add a flag to dataset, which indicates if the Amount <> 0 (flag =...
^{“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

April 24, 2013 at 9:32 am

#1609583
RE: Error converting varchar to numeric only in where clause

Tom, this CTE method does exactly the same thing - it reverses the order in which the two predicates are evaluated. The CTE's aren't evaluated in order, they're more or...
^{“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

April 24, 2013 at 9:22 am

#1609579
RE: Error converting varchar to numeric only in where clause

tom.wauters (4/24/2013)
Chris, I can see the little differences in the plan.
However, I am not a dba-expert, and not that good at analysing query plans.
Can you please explain me a bit...
^{“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

April 24, 2013 at 8:29 am

#1609536

Viewing 15 posts - 4,741 through 4,755 (of 10,143 total)