ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,786 through 7,800 (of 10,143 total)

RE: Result table join based on active or inactive flag in table two

Have a look at the output from the following query.
I reckon you're looking for all rows where [source] = 0, and also all rows where the value of the...
^{“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 18, 2010 at 2:39 am

#1181981
RE: Today's Random Word!

dbowlin (6/17/2010)
Excelsior!
Fat Freddy's Cat.
Fertility Pills!
^{“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 18, 2010 at 2:06 am

#1181965
RE: Output based on group by and sum values

This should give you a few ideas
DROP TABLE #TABLE_A
CREATE TABLE #TABLE_A (DEl_No VARCHAR(6), L_Amt INT, T_Amt INT, A_Name VARCHAR(6))
INSERT INTO #TABLE_A (DEl_No, L_Amt, T_Amt, A_Name)
SELECT '087NT1', 24000, 28699, 'Physic'...
^{“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 17, 2010 at 10:18 am

#1181695
RE: insert multiple values

delwarinc (6/17/2010)
thanks for your reply, please can you show how to do this
Of course!
You will have to write this bit though - Write a query to retrieve the ingredient...
^{“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 17, 2010 at 9:30 am

#1181653
RE: Getting Run-around on well deserved raise

Unreserved apologies, jvanderberg, for appearing shallow - however the remark passes the logic test. It appears your employers have no intention of raising your salary at the current threat level:...
^{“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 17, 2010 at 8:23 am

#1181592
RE: Today's Random Word!

jcrawf02 (6/17/2010)
Chris Morris-439714 (6/17/2010)
Before Sing4you whines "Not Yet" and Jason protests "Wasn't me!", this is about football and not pregnancy, right? 😀
You mean Hand-Egg?
Jon, you always lower the tone of...
^{“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 17, 2010 at 7:54 am

#1181569
RE: Getting Run-around on well deserved raise

jvanderberg (6/17/2010)
What do you think I should do?
--J
Resign from BP and get a job with TexasOil.
^{“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 17, 2010 at 7:53 am

#1181567
RE: Today's Random Word!

Before Sing4you whines "Not Yet" and Jason protests "Wasn't me!", this is about football and not pregnancy, right? 😀
^{“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 17, 2010 at 7:40 am

#1181561
RE: insert multiple values

This looks too simplistic to be a real world problem.
Step by step:
Write a query to retrieve the ingredient rows of the recipe which you are cloning.
Hard-code the new RecipeID into...
^{“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 17, 2010 at 7:32 am

#1181552
RE: to find string between special characters appearing twice in a string

-- query 1
SELECT
MyString,
--CHARINDEX('_', MyString),
--SUBSTRING(MyString, CHARINDEX('_', MyString)+1, LEN(MyString)),
--CHARINDEX('_', SUBSTRING(MyString, CHARINDEX('_', MyString)+1, LEN(MyString))),
MySubstring = LEFT(SUBSTRING(MyString, CHARINDEX('_', MyString)+1, LEN(MyString)),
CHARINDEX('_', SUBSTRING(MyString, CHARINDEX('_', MyString)+1, LEN(MyString)))-1)
FROM ( -- sample data
SELECT '00_MECH_UG' AS MyString...
^{“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 17, 2010 at 6:06 am

#1181489
RE: SQL Server 2005 Query View

Hello
There are at least three methods for deriving a running total:
1. Triangular join, as you are attempting here
2. Recursive CTE
3. Quirky update
Method 1 is not recommended as it performs poorly...
^{“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 17, 2010 at 4:47 am

#1181452
RE: Performance tuning on insert query

How long does the select part SELECT
a.policy,
a.form,
a.date,
SUM(a.premium/b.premprcnt) AS premium
FROM b
INNER JOIN a
ON b.policy = a.policy
AND b.form = a.form
AND b.date = a.date
AND...
^{“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 17, 2010 at 4:10 am

#1181437
RE: Multiple updates on the same column

Here's why I asked - a simple and fast way to get your results:
;WITH Sourcedata AS (
SELECT SubID = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY OLD_VAL),
ID, OLD_VAL, NEW_VAL
FROM...
^{“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 17, 2010 at 2:57 am

#1181413
RE: Multiple updates on the same column

Hi Daniel
In your sample data, you have two rows in table #VAL for each row in your update target table. Is this always the case? What's the maximum number of...
^{“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 17, 2010 at 2:31 am

#1181403
RE: Today's Random Word!

Robocat.
Kills 99.9% of all known rodents.
Doesn't dig small holes in your neighbour's herbaceous border.
^{“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 17, 2010 at 2:06 am

#1181397

Viewing 15 posts - 7,786 through 7,800 (of 10,143 total)