ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,076 through 9,090 (of 10,143 total)

RE: Person data- Multiple rows due to more than 1 name. Automatic Numbering of rows

[font="Courier New"]DROP TABLE #SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY
CREATE TABLE #SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY
([fi pin] INT, Alias VARCHAR(100))
INSERT INTO #SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY
([fi pin], Alias)
SELECT 118007, 'Sara Jane smith'UNION ALL
SELECT 118007, 'Sarah Smith'UNION ALL
SELECT 118007, 'Sara Jayne Smith'UNION ALL
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

December 19, 2008 at 4:30 am

#915103
RE: string function

CREATE PROCEDURE [dbo].[MakeNumbersTable]
-- Courtesy of Jeff Moden, SSC
AS
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[Numbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.Numbers
--===== Create and populate the Tally...
^{“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

December 19, 2008 at 4:21 am

#915099
RE: string function

swapna.katare (12/19/2008)
thx but the query did not work. it is showing the error Invalid object name 'numbers'.what if i have 500 rows how to insert the values
Click on the highlighted...
^{“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

December 19, 2008 at 3:49 am

#915091
RE: Multi line Table-value function much slower then executing body SQL (2008)

Can you post the code for the function?
^{“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

December 19, 2008 at 3:39 am

#915082
RE: Count number of occurancies

bhushanhegde (12/18/2008)
Hi,
I dont have rights to create any objects in the data base since that is in production.
I want to do with any sys defined functions....is it possible?
So...does it work?
^{“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

December 19, 2008 at 3:26 am

#915077
RE: string function

Here's a tally-table solution which will run fast.
[font="Courier New"]DROP TABLE #Indus
CREATE TABLE #Indus (serial INT, qh11 VARCHAR(255), qh21 VARCHAR(255))
INSERT INTO #Indus (serial, qh11, qh21)
SELECT 1, '234567890', '45890' UNION ALL
SELECT 2,...
^{“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

December 19, 2008 at 3:22 am

#915074
RE: The Best Computer

Richard Hayes (12/19/2008)
ORAC from Blake's 7 was surely the most amazing computer ever? It could predict the future, penetrate other computers and even change its own size, all that...
^{“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

December 19, 2008 at 2:49 am

#915065
RE: Conversion failed when converting the varchar value

r.hensbergen (12/18/2008)
After reading that 5 times, it sure comes through a lot better.. :crazy: Are you happy now with your query though? If not, please post the table structure, sample...
^{“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

December 19, 2008 at 2:35 am

#915057
RE: Conversion failed when converting the varchar value

WHEN NoEMail = '' THEN '0'
I'm getting confused now between NoMail, which is bit, and Email, which is some kinda string. But whichever, you're trying to match one 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

December 18, 2008 at 10:43 am

#914750
RE: Conversion failed when converting the varchar value

What is the datatype of the column NoMail in the customer table? If it's bit, then that's the source of your error message.
Do any of the values of EMail not...
^{“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

December 18, 2008 at 10:20 am

#914725
RE: How to display the data fields in the french language in the rdl

p.nagaravikumar (12/18/2008)
Thanks for response,
Yes,i am looking the same.Ex customer table is having the customer names.I want to give the report in french language.
Regards
Ravi
Can you help us with a few samples...
^{“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

December 18, 2008 at 9:28 am

#914669
RE: Are the posted questions getting worse?

Jack Corbett (12/18/2008)
Well, I know a little Redneck (I have a mentor in Florida, in NH now so not getting any lessons), and I know a little SQL Server technical...
^{“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

December 18, 2008 at 9:14 am

#914659
RE: Query to fill in missing dates in a series of rows

Jeff Moden (12/18/2008)
bnordberg (12/18/2008)
The data in this format will allow for contract tracing of MRSA infections. Our statisticians can pipe this through some algorithims in SAS to see if we...
^{“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

December 18, 2008 at 9:12 am

#914657
RE: left join help

What have you tried so far, ALI?
Thanks for providing the sample data and table creation scripts, top work.
^{“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

December 18, 2008 at 9:04 am

#914647
RE: Temp table usage in dynamic sql

GilaMonster (12/18/2008)
Ah. I thought that the end of the dynamic SQL would mean that the last reference to the global temp table was gone.
BOL doesn't make it clear - at...
^{“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

December 18, 2008 at 8:30 am

#914601

Viewing 15 posts - 9,076 through 9,090 (of 10,143 total)