ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,971 through 2,985 (of 10,143 total)

RE: TEMP TABLE

michael.petrone (9/1/2014)
1. Start of with a blank temp table.
2. Select
3. Check Temp Table - SSN not in temp table send to Report Builder 3.0 ELSE insert into temp table.
4....
^{“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

September 1, 2014 at 4:07 am

#1742197
RE: Trimming Last 3 Numbers from some Records

;WITH SampleData AS (
SELECT MyString = 'ABC DEF 210 ' UNION ALL
SELECT 'ggh / sah ggh 590' UNION ALL
SELECT 'ggh / sah gghXPT' UNION ALL
SELECT 'ggh...
^{“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

September 1, 2014 at 3:40 am

#1742193
RE: Trimming Last 3 Numbers from some Records

Guitar_player (9/1/2014)
Hi ,
I have column which are having data as Alphabets and also Numbers like to say
ID Column1
1 ABCD 123
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

September 1, 2014 at 1:51 am

#1742165
RE: Query works static, but does not dynamically

joseph.robinson83 (8/29/2014)
Wow, thanks Chris! I can't believe I forgot to N' the string. That single oversight fixed the entire routine.
Good information to know on the CHAR/Unicode/ASCII differences. I've never had...
^{“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

August 29, 2014 at 9:47 am

#1741967
RE: Query works static, but does not dynamically

Solved:
DECLARE @Pattern NVARCHAR(MAX)
DECLARE @sSQL NVARCHAR(MAX)
DECLARE @1 nvarchar(max)
DECLARE @2 nvarchar(max)
DECLARE @loop int
SET @Pattern = '%['
SET @Pattern = @Pattern + NCHAR(0) + NCHAR(1) + NCHAR(2) + NCHAR(3) + NCHAR(4) +...
^{“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

August 29, 2014 at 9:34 am

#1741962
RE: Query works static, but does not dynamically

Have you tried using an SC collation? There's info here. Your ? might be the first of a surrogate pair.
^{“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

August 29, 2014 at 8:30 am

#1741929
RE: Splitting one row into multiple

jonatan.carlberg (8/29/2014)
Hi again!
Thank you all for welcoming me to the forum. As you probably see, I've posted a clarification on my previous post.
Thanks for all input so far.
From what...
^{“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

August 29, 2014 at 5:59 am

#1741860
RE: recursive sum

-- create sample data
DROP TABLE #ChartOfAccount
CREATE TABLE #ChartOfAccount (Account INT, [Description] VARCHAR(30), Parent INT)
INSERT INTO #ChartOfAccount (Account, [Description], Parent)
SELECT 1, 'ASSETS', 0 UNION ALL
SELECT 101, 'CURRENT ASSETS', 1 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

August 29, 2014 at 5:32 am

#1741851
RE: Abbreviated SELECT FROM output

LHendren (8/28/2014)
Thanks to both of you. All three solutions work for my query.
Taking this a step further, what if I do not want to see the jNMBR and sNMBR...
^{“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

August 28, 2014 at 9:03 am

#1741678
RE: Splitting one row into multiple

-- Prepare some sample data to code against
DROP TABLE #Sample
CREATE TABLE #Sample (
ID INT,
[Customer name] VARCHAR(20),
[Customer address] VARCHAR(20),
[Something] INT,
[Product number] INT,
[Product name] VARCHAR(20),
Quantity VARCHAR(20),
Price...
^{“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

August 28, 2014 at 8:48 am

#1741673
RE: Abbreviated SELECT FROM output

Two other commonly-used methods:
SELECT jNMBR, sNMBR
FROM #myTable
WHERE cDate IS NOT NULL
GROUP BY jNMBR, sNMBR
SELECT jNMBR, sNMBR
FROM (
SELECT jNMBR, sNMBR, rn = ROW_NUMBER() OVER(PARTITION BY jNMBR, sNMBR ORDER BY (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

August 28, 2014 at 8:26 am

#1741665
RE: OPENQERY Syntax to Insert into SQL Server Table from Oracle Linked Server.

Lynn Pettis (8/28/2014)
ChrisM@Work (8/28/2014)
Welsh Corgi (8/28/2014)
ChrisM@Work (8/28/2014)
Welsh Corgi (8/28/2014)
The example that I provided is not actually what I need. I want the Insert columns to be all from the Oracle...
^{“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

August 28, 2014 at 7:57 am

#1741650
RE: OPENQERY Syntax to Insert into SQL Server Table from Oracle Linked Server.

Welsh Corgi (8/28/2014)
ChrisM@Work (8/28/2014)
Welsh Corgi (8/28/2014)
The example that I provided is not actually what I need. I want the Insert columns to be all from the Oracle Table.
Any ideas would...
^{“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

August 28, 2014 at 7:48 am

#1741647
RE: Select records with different values in the same column

Lots of options for this:
;WITH PrimaryDiagList AS (SELECT * FROM (VALUES
('402.01'),('402.11'),('402.91'),('404.01'),('404.03'),('404.11'),('404.13'),('404.91'),('404.93'),('428.0'),('428.10'),('428.20'),
('428.21'),('428.22'),('428.23'),('428.30'),('428.31'),('428.32'),('428.33'),('428.40'),('428.41'),('428.42'),('428.43'),('428.90')
) d (diagnosis))
SELECT t.*
FROM dbo.TEST t
INNER JOIN PrimaryDiagList p
ON p.diagnosis = t.diagnosis
WHERE t.DiagnosisSeqID = '1'
AND EXISTS (
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

August 28, 2014 at 7:41 am

#1741642
RE: OPENQERY Syntax to Insert into SQL Server Table from Oracle Linked Server.

Welsh Corgi (8/28/2014)
The example that I provided is not actually what I need. I want the Insert columns to be all from the Oracle Table.
Any ideas would be greatly appreciated.
Remove...
^{“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

August 28, 2014 at 7:12 am

#1741634

Viewing 15 posts - 2,971 through 2,985 (of 10,143 total)