ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,406 through 9,420 (of 10,143 total)

RE: INSERT INTO

Hi Phil
This should do the trick, because it supplies the value of ProductCategory:
INSERT INTO [Training].[dbo].[ProductProductCategories]
([Product], ProductCategory)
SELECT p.Product, 5 AS...
^{“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

November 18, 2008 at 5:44 am

#899835
RE: Retrieving latest date from 2 fields in table

Lorna Wheelhouse (11/18/2008)
Thank you very much - will try this and post an example in case anyone else is interested 🙂
You're welcome Lorna. Incidentally, both statements produce the same result...
ISNULL(DateAmended,...
^{“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

November 18, 2008 at 5:37 am

#899828
RE: INSERT INTO

Hi Phil
When performing an UPDATE ... FROM or an INSERT ... FROM, it will save you heaps of time to check the SELECT separately:
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

November 18, 2008 at 5:32 am

#899821
RE: Help writing function to get decimal from string

Dooza (11/18/2008)
Jeff, your solution is working perfikly for me, thank you once again for the excellent help and advice. And thank you to Seph and Chris too!
Steve
You're welcome Steve, thanks...
^{“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

November 18, 2008 at 5:20 am

#899811
RE: Retrieving latest date from 2 fields in table

Hi Lorna
something like this should do the trick:
-- If the row has been amended, then the amended date will not be null; use the amend date.
-- If the row has...
^{“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

November 18, 2008 at 4:28 am

#899776
RE: Help with Money and Decimal datatype

Karthik
From BOL:
In the following table, the column on the left represents the style value for money or smallmoney conversion to character data.
Value Output
0 (default) No commas every three digits...
^{“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

November 18, 2008 at 4:14 am

#899770
RE: insert multiple times...

carlos.tapadinhas (11/18/2008)
Hello to all,
I have a big problem and i cant resolve, i have one sp that insert on a table, but sometimes inserts two rows with the same results...
^{“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

November 18, 2008 at 3:48 am

#899762
RE: Help writing function to get decimal from string

Jeff Moden (11/17/2008)
Chris Morris (11/17/2008)
Jeff, perfick solution as always!
Serious or sarcasm? Can't tell from here.
Aw Jeff you should know me better than that!
This...
WHERE t.N BETWEEN CHARINDEX('(',@String) +1
...
^{“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

November 18, 2008 at 1:08 am

#899716
RE: Adding sum and distinct to case query

Christopher Stobbs (11/17/2008)
There you have it Lynn beat me to the code post 🙂
You had it on the nose though! I'd still like to see the data, reckon there's something...
^{“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

November 17, 2008 at 10:21 am

#899449
RE: Error - Could not find server 'Database' in sysservers

eflynn (11/17/2008)
Nearly worked! I got rid of the column, but now it gives me the error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'VOID'.
hmm..
PS: Thanks...
^{“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

November 17, 2008 at 10:17 am

#899448
RE: Adding sum and distinct to case query

Hi Chris, don't mean to tread on your toes but the group by requires the whole expression used to generate the new column, rather than the name of the new...
^{“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

November 17, 2008 at 10:11 am

#899440
RE: Adding sum and distinct to case query

Christopher Stobbs (11/17/2008)
could you please post the whole error message:-)
Some sample data from the table "timberstands" would be very handy too!
^{“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

November 17, 2008 at 10:01 am

#899429
RE: Error - Could not find server 'Database' in sysservers

Hello
The syntax for UPDATE is
UPDATE tablename SET columnname
You have a columnname in place of a tablename:
db1.dbo.Users.WebLoginID
This will probably work, though it's untested:
UPDATE u SET WebLoginID = "VOID"
FROM db1.dbo.Users u...
^{“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

November 17, 2008 at 9:42 am

#899403
RE: Possible Join of One Table Field to Two Master Table Fields?

Liam, you need to join the states table twice:
SELECT m.member, h.HomeState, m.WorkState
FROM master m
LEFT JOIN States h ON h.[ID] = m.HomeState
LEFT JOIN States w ON w.[ID] = m.WorkState
Cheers
ChrisM
^{“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

November 17, 2008 at 9:36 am

#899397
RE: Trouble with query. Kind of lost here

Something like this should do the trick:
[font="Courier New"]DROP TABLE #Order
CREATE TABLE #Order (Client VARCHAR(3), Product VARCHAR(3))
INSERT INTO #Order (Client, Product)
SELECT 'XXX','001' UNION ALL -- B
SELECT 'XXX','003' UNION ALL --...
^{“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

November 17, 2008 at 9:21 am

#899386

Viewing 15 posts - 9,406 through 9,420 (of 10,143 total)