ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,546 through 1,560 (of 10,143 total)

RE: Need to split the row values

p.shabbir (6/3/2016)
Ok. I have modified the query like below. But getting error Like "Invalid length parameter passed to the LEFT or SUBSTRING function".
WITH MyData AS (SELECT SRC_ERROR_OUTPUT FROM REJ.FILE_READ_REJECTS 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

June 3, 2016 at 8:48 am

#1883112
RE: Need to split the row values

p.shabbir (6/3/2016)
ChrisM@Work (6/3/2016)
Do you have a means of detecting the split point?
11157243 1224314 20160523 05/22/2016 20160526 GLOB 4,000 16 72 ISASANTAELLA@HOTMAIL.COM c1e1b966-4233-11d4-85dc-080009daf0d2 373294099 XXXXX4099 ISABEL SANTAELLA DE KUBLER ISABEL SANTAELLA...
^{“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 3, 2016 at 7:02 am

#1883073
RE: Filtering within a group or figuring out how to loop through results

SELECT m.*
FROM mytable m
WHERE EXISTS (
SELECT 1
FROM mytable i
WHERE i.cst_org_name_dn = m.cst_org_name_dn
AND i.mbt_code = 'EM')
AND m.mbt_code <> 'EM'
^{“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 3, 2016 at 6:57 am

#1883070
RE: Need to split the row values

Do you have a means of detecting the split point?
^{“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 3, 2016 at 6:41 am

#1883064
RE: Need to split the row values

WITH MyData AS (SELECT MyString = '11157243 1224314 20160523 05/22/2016 20160526 GLOB 4,000 16 72 ISASANTAELLA@HOTMAIL.COM c1e1b966-4233-11d4-85dc-080009daf0d2 373294099 XXXXX4099 ISABEL SANTAELLA DE KUBLER ISABEL SANTAELLA DE KUBLER Y MORI N...
^{“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 3, 2016 at 6:20 am

#1883055
RE: difficult group question

WITH MyData AS (SELECT * FROM (VALUES (11,1),(12,1),(13,2),(14,3),(22,2),(22,2),(23,4),(24,4)) d (Item, [Count]))
SELECT
[10 to <20] = SUM(CASE WHEN Item >= 10 AND Item < 20 THEN [Count] ELSE 0 END),
[20 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

June 3, 2016 at 3:53 am

#1883022
RE: Inserting rows into the pivoted and updating the date intervals and values based on input.

Duplicate post, the original is here.
Duplicate posts waste time and effort. The original post has useful questions and replies. Responding to this thread will lead to a fragmented domain.
^{“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 3, 2016 at 3:20 am

#1883017
RE: Inserting rows into the pivoted and updating the date intervals and values based on input.

Duplicate post, the original is here.
Duplicate posts waste time and effort. The original post has useful questions and replies. Responding to this thread will lead to a fragmented domain.
^{“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 3, 2016 at 3:20 am

#1883016
RE: Are the posted questions getting worse?

GilaMonster (6/2/2016)
Grant Fritchey (6/2/2016)
jasona.work (6/2/2016)
I read this and suddenly felt millions of DBAs cry out in horror...
(Yes, it's the Daily WTF, scarily it might even be (mostly) true)
Great story. 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

June 3, 2016 at 1:47 am

#1883005
RE: Are the posted questions getting worse?

jasona.work (6/2/2016)
I read this and suddenly felt millions of DBAs cry out in horror...
(Yes, it's the Daily WTF, scarily it might even be (mostly) true)
This story probably is true because...
^{“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 3, 2016 at 1:39 am

#1883003
RE: Temp DB filling on simple update statement?

UPDATE eligibility SET primary_plan = 0
WHERE primary_plan <> 0
AND EXISTS (
SELECT 1
FROM temp_eligibility e
WHERE e.eligibility_id = eligibility.eligibility_id)
Might eliminate some writes.
^{“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 2, 2016 at 9:56 am

#1882869
RE: Convert to CSV

Luis Cazares (6/2/2016)
ChrisM@Work (6/2/2016)
There are several ways to do this, most of them are compared here.
I would suggest that you use the XML PATH option (unless you already use CLR)....
^{“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 2, 2016 at 6:58 am

#1882804
RE: Convert to CSV

There are several ways to do this, most of them are compared here.
^{“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 2, 2016 at 5:32 am

#1882783
RE: Convert to CSV

squvi.87 (6/2/2016)
Hi there,
Following code will do the job for me. But I wonder is there any other way to accomplish.
create table #t1 (id int , names varchar(100))
declare @a varchar(100) ,@i...
^{“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 2, 2016 at 4:44 am

#1882769
RE: Tuning UPDATE query

WhiteLotus (6/2/2016)
Hi Guys ,
I am tuning an UPDATE query by adding an index
UPDATE XYZ
SET Count = Count + 1,
LastOccured = GetDate()...
^{“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 2, 2016 at 4:27 am

#1882765

Viewing 15 posts - 1,546 through 1,560 (of 10,143 total)