ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,191 through 2,205 (of 10,143 total)

RE: Getting error on 'Gail Shaw' script!!

Tac11 (9/3/2015)
doesn't tell me that, I posted the error
Can't you tell by looking at the data? Can you post a few random samples 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

September 3, 2015 at 7:58 am

#1824297
RE: Getting error on 'Gail Shaw' script!!

Tac11 (9/3/2015)
Hi all,
I am able to create the table below but trying to execute 'Insert' query below I get this :
Msg 8152, Level 16, State 10, Line 2
String or binary...
^{“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 3, 2015 at 7:47 am

#1824294
RE: Parameter conversion

Eirikur Eiriksson (9/3/2015)
patilar (9/3/2015)
you can use Fn_split () function . The Code for it is available in google
Quick suggestion, don't use the Fn_split function, use DelimitedSplit8K [/url]instead
😎
For your amusement, here's...
^{“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 3, 2015 at 7:45 am

#1824293
RE: Covering date error

tt-615680 (9/3/2015)
CONVERT(date, col1) AS 'DateTimeStamp', is the line I've chabged unless I need to change it to something else?
and I get the following error:
Conversion failed when converting date and/or time...
^{“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 3, 2015 at 7:14 am

#1824282
RE: best practice to do this join

-- sample data:
DROP TABLE #source_table_1
CREATE TABLE #source_table_1 ([contract] VARCHAR(2), [date] DATE, amount INT)
INSERT INTO #source_table_1 ([contract], [date], amount) VALUES
('XX','20160101',100),
('XX','20160104',200),
('YY','20160101',300),
('YY','20160104',400),
('ZZ','20160101',500),
('ZZ','20160104',600)
DROP TABLE #source_table_2
CREATE TABLE #source_table_2 ([date] DATE)
INSERT INTO #source_table_2 ([date]) VALUES...
^{“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 3, 2015 at 6:19 am

#1824254
RE: Covering date error

tt-615680 (9/3/2015)
I have now changed the date time to be converted to date but I still get the following message:
Conversion failed when converting date and/or time from character string.
-- 9...
^{“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 3, 2015 at 6:02 am

#1824250
RE: Covering date error

tt-615680 (9/3/2015)
Thank you for your reply!
I commented out the insert into statement but all I get in return is the details about the files, e.g. file name file extension. LSN...
^{“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 3, 2015 at 4:25 am

#1824229
RE: Covering date error

in section 9
-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList
(DateTimeStamp,
LSN,
FileSize,
FileName1)
SELECT LTRIM(SUBSTRING (Col1, 1, 20)) 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

September 3, 2015 at 2:30 am

#1824208
RE: Delete statement taking too much time..?

-- Your query looks like this...
DELETE FROM CSV_Details
WHERE CSVID NOT IN
(
-- level 2
SELECT CSVID
FROM CSV_Details d2
INNER JOIN Transmittal_Planning_MarkNo m2
ON m2.Mark_No = d2.Mark_Rev_No_fab
WHERE d2.DeleteFlag IS 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

September 3, 2015 at 1:48 am

#1824201
RE: Help with a join

yb751 (9/1/2015)
You shouldn't have multiple CompanyID's stored this way. You'll want to normalize your data. You can't do joins that way unless the actual ID in tableB was...
^{“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, 2015 at 7:37 am

#1823625
RE: Excluding records

WHERE [DETERMINATION] <> 'Denied'
AND [Number] ='A150731000039'
---- Removes incorrect records where these names match----
AND (
[Name] IS NULL
OR [PName] IS NULL
OR ([Name] NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND [PName] = 'DEFAULT')
)
Avoid using...
^{“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, 2015 at 7:31 am

#1823622
RE: Help with a join

TSQL Tryer (9/1/2015)
So, I have a table "table a"
This has two columns. "username" and "companyid"
A sample of data for "companyid" is - "1,2,3,4".
How would I go about join...
^{“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, 2015 at 7:23 am

#1823617
RE: udf with tally table runs slower than while loop

Jeff Moden (8/25/2015)
ChrisM@Work (8/25/2015)
Hi Josh
IIRC a hard tally table is usually slightly faster than an inline one. Google will tell you for sure.
No need to Google. Here's a chart...
^{“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 26, 2015 at 2:17 am

#1822403
RE: Metrics Table or Performance Tuning

ScottPletcher (8/25/2015)
ChrisM@Work (8/25/2015)
ScottPletcher (8/25/2015)
ChrisM@Work (8/25/2015)
ScottPletcher (8/25/2015)
ChrisM@Work (8/25/2015)
ScottPletcher (8/21/2015)
ChrisM@Work (8/21/2015)
ScottPletcher (8/21/2015)
ChrisM@Work (8/21/2015)
The inner select can be accelerated with this index:
CREATE INDEX ix_Helper ON [cts].[exception_Main]
(productArea, reportable, reportYear, reportMonth, queueID)
INCLUDE (volume,...
^{“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 25, 2015 at 10:01 am

#1822246
RE: Metrics Table or Performance Tuning

ScottPletcher (8/25/2015)
ChrisM@Work (8/25/2015)
ScottPletcher (8/25/2015)
ChrisM@Work (8/25/2015)
ScottPletcher (8/21/2015)
ChrisM@Work (8/21/2015)
ScottPletcher (8/21/2015)
ChrisM@Work (8/21/2015)
The inner select can be accelerated with this index:
CREATE INDEX ix_Helper ON [cts].[exception_Main]
(productArea, reportable, reportYear, reportMonth, queueID)
INCLUDE (volume, cost, exceptionDateTime)
But...
^{“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 25, 2015 at 9:40 am

#1822234

Viewing 15 posts - 2,191 through 2,205 (of 10,143 total)