ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,351 through 4,365 (of 10,143 total)

RE: previous week query 0700 - 0700

-- step-by-step workings: calculate 7am of monday last week and 7am of monday this week
SELECT *
FROM (SELECT DTToday = GETDATE()) d
CROSS APPLY (SELECT NoOfDaysSinceMonday = DATEDIFF(DD,0,DTToday)%7) x1
CROSS APPLY (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

July 18, 2013 at 3:42 am

#1633573
RE: need sqlserver query to give similar output like dec command in sqlserver

Split from this thread.
^{“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

July 18, 2013 at 1:53 am

#1633550
RE: delete top 100

kapil_kk (7/18/2013)
If you specify alias with TOP in delete it will always gives you an error:
e.g.
DELETE TOP (2)
FROM tbl1 t
Your syntax is incorrect:
DELETE TOP (2) t
FROM tbl1 t
delete top(1000)...
^{“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

July 18, 2013 at 1:08 am

#1633542
RE: Today's Random Word!

The Dixie Flatline (7/17/2013)
opc.three (7/17/2013)
L' Eomot Inversé (7/17/2013)
Revenant (7/17/2013)
The Dixie Flatline (7/17/2013)
bcsims 90437 (7/17/2013)
JAZZ Master (7/17/2013)
SQLRNNR (7/17/2013)
petrified
putrefied
purified
perforated
penetrated
defenestrated
immolated
disintegrated
disinterred
^{“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

July 18, 2013 at 12:52 am

#1633539
RE: Join of 2 subqueries

If all you are looking for is matching keys between the two queries, then this should work:
SELECT
ClaimId,
LineNum,
EncounterLineNum,
EncounterType,
InternalICN
FROM IntEncTracking.EncounterList el
WHERE e1.bypassflag = 1
INTERSECT
SELECT
ClaimId,
LineNum,
EncounterLineNum,...
^{“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

July 17, 2013 at 9:18 am

#1633341
RE: delete rows

riya_dave (7/17/2013)
total i have 60 millions rows from there i need to insert 30 millions to some another table.
once it is insert i need to delete it from original table
You...
^{“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

July 17, 2013 at 8:38 am

#1633323
RE: parsing data in the middle of a field??

Thanks for the feedback Joe. I'm in Hampshire for this gig, perhaps 50 miles west and south a bit from London, and it's about 30^oC. Absolutely awesome!
^{“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

July 17, 2013 at 8:37 am

#1633321
RE: parsing data in the middle of a field??

Try this, Joe:
SELECT
ASSESSMENT_DATA,
start.pos,
[end].pos,
SUBSTRING(ASSESSMENT_DATA,start.pos,[end].pos-start.pos) as ss
FROM USER_DEFINED_DATA
CROSS APPLY (SELECT pos = LEN('@@T_700_ = ') + CHARINDEX('@@T_700_ = ',ASSESSMENT_DATA,1)) start
CROSS APPLY (SELECT pos = 1+CHARINDEX('"@@T_301',assessment_data,start.pos)) [end]
WHERE ASSESSMENT_MONIKER = '09D27E579F9344A1BBD442E70CA1241F'...
^{“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

July 17, 2013 at 8:17 am

#1633307
RE: parsing data in the middle of a field??

It looks ok to me, Joe. Have you tried it yet?
You might benefit from filtering out rows where the column is empty.
^{“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

July 17, 2013 at 8:03 am

#1633301
RE: any way this can avoid of index scan?

GilaMonster (7/17/2013)
ChrisM@Work (7/17/2013)
Really? What type of join is shown for Query 1 and Query 2?
NOT IN and NOT EXISTS do perform identically with identical execution plans if the columns...
^{“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

July 17, 2013 at 7:59 am

#1633300
RE: Join of 2 subqueries

daniness (7/17/2013)
The columns that are in the order by: ClaimId, LineNum, EncounterLineNum, EncounterType, and InternalICN. These make up the primary key that results of both queries are being inserted 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

July 17, 2013 at 7:51 am

#1633295
RE: any way this can avoid of index scan?

sqlnes (7/17/2013)
Thanks Chris. And sorry I forgot to mention that I've followed your first reply and compared the explains and they are identical. Then I checked the column on my...
^{“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

July 17, 2013 at 7:48 am

#1633291
RE: parsing data in the middle of a field??

Ah, ok.
DROP TABLE #SAMPLE
CREATE TABLE #SAMPLE (MyString VARCHAR(200))
INSERT INTO #SAMPLE (MyString)
SELECT '...@@T_700_ = "v2012.08.10.1_1.7"@@T_301_ = "...' UNION ALL
SELECT '...@@T_700_ = "Client Name here"@@T_301_ = "03/22/2013"@@T_069_ = "1"......... thru @@T_850...'
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

July 17, 2013 at 7:43 am

#1633289
RE: Join of 2 subqueries

daniness (7/17/2013)
Chris,
What I'm trying to get from this query are records that are in common to both subqueries. This would tell me which records are responsible for the duplicate error...
^{“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

July 17, 2013 at 7:33 am

#1633281
RE: Join of 2 subqueries

daniness (7/17/2013)
Hi Hanshi,
Actually, originally the group by was part of the 1st subquery, but it wasn't working, so this is why I tried placing it at the end, but obviously...
^{“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

July 17, 2013 at 7:26 am

#1633272

Viewing 15 posts - 4,351 through 4,365 (of 10,143 total)