ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,426 through 1,440 (of 10,143 total)

RE: Help with SQL please

dandrews 45259 (7/4/2016)
Hello,
I hoping somebody can help with a sql issue I have. WE have sales orders and works orders. I am looking at the works order history...
^{“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 4, 2016 at 5:01 am

#1888265
RE: Help creating a dynamic table of numbers

SELECT
MinNum = @MinNum + (n * @Span),
MaxNum = @MinNum + ((n+1) * @Span)
FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) d (n)
WHERE n < @Interval
^{“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 4, 2016 at 3:13 am

#1888253
RE: Pattern match postcode area lookup

gkrs (7/1/2016)
Yep I know what you mean, the dbase design/input validation could be a lot better. Thank-you for all the replies. The following LIKE seems to work in...
^{“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 1, 2016 at 9:10 am

#1888114
RE: Concatenate strings

In case that was missed in translation (!), here it is again:
DECLARE @database2 NVARCHAR(100);
DECLARE @DBs TABLE (DBname NVARCHAR(100));
INSERT INTO @DBs VALUES ('?'),('?'),('?');
SELECT * FROM @DBs
^{“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 1, 2016 at 8:47 am

#1888109
RE: Pattern match postcode area lookup

BWFC (7/1/2016)
gkrs (7/1/2016)
I regularly have to mass update customers address areas when the postcode lookups for those areas are altered. e.g the lookup table can be something like below.
I usually...
^{“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 1, 2016 at 6:51 am

#1888078
RE: UNION with CASE statement

Newbi (7/1/2016)
Hi
I am getting error when trying to run your script.
Help pls.
...
Why are you attempting to include the sample data setup in your report definition? It's only in...
^{“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 1, 2016 at 2:54 am

#1888019
RE: Are the posted questions getting worse?

J Livingston SQL (6/30/2016)
oops...wrong post !
apologies
You have a spare turtle?
^{“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 30, 2016 at 10:05 am

#1887926
RE: Are the posted questions getting worse?

Beatrix Kiddo (6/30/2016)
I'm sure you are very good: it's absolutely not a slight on anybody who doesn't work in London, it's just saying that very good people can vote with...
^{“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 30, 2016 at 9:53 am

#1887923
RE: Splitting a delimited field

Here's some stuff for you to explore. Your query can be simplified a lot and made much faster...
IF 0 = 1
WITH MyDates AS (
SELECT * FROM (VALUES
('1500', '20160423'),('0020',...
^{“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 30, 2016 at 9:15 am

#1887912
RE: Splitting a delimited field

Can you provide a few examples of *exactly* what they look like?
^{“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 30, 2016 at 8:21 am

#1887894
RE: An error occurred while processing log encryption

lcaradon (5/10/2016)
We periodically get this message below in the SQL Server log on SQL 2012 Enterprise edition. The database is encrypted using TDE. The encrypted databases on the server were...
^{“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 30, 2016 at 8:19 am

#1887893
RE: Splitting a delimited field

When you say it's slower, is it 4s vs 2s or 104s vs 102s?
This is more important: what datatype are q2.DateofOnsetofLabour and q2.TimeofOnsetofLabour?
^{“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 30, 2016 at 8:07 am

#1887887
RE: Splitting a delimited field

richardmgreen1 (6/30/2016)
Hi Chris
I've had a look at the updated Splitter code and it actually runs a few seconds slower than my original.
Not entirely sure why as I would have thought...
^{“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 30, 2016 at 7:59 am

#1887882
RE: Splitting a delimited field

Two changes worth trying. Firstly, introducing the splitter:
OUTER APPLY (
SELECT MedicalInduction = ds.Item
FROM [MeditechDR01-M1].[livefocdb_daily].[dbo].[RegAcctQuery_Result] aq1
CROSS APPLY DelimitedSplit8K (replace(replace(aq1.[ValueInfo],'}',''),'{',''),'|') ds
WHERE aq1.[Query_MisQryID] IN ('MAT.MEDIND')
AND aq1.SourceID = ad.SourceID
AND aq1.VisitID = ad.VisitID
) q3
Secondly, the...
^{“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 30, 2016 at 6:40 am

#1887847
RE: Splitting a delimited field

richardmgreen1 (6/30/2016)
Thanks Chris
I've now got it running as expected.
Cool, well done.
There are issues with the fragment of query you posted - do you want help with resolving them?
The...
^{“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 30, 2016 at 5:32 am

#1887826

Viewing 15 posts - 1,426 through 1,440 (of 10,143 total)