ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,681 through 1,695 (of 10,143 total)

RE: I'm missing something obvious but I'm not sure what.

craig.bobchin (3/21/2016)
I tried that and still got the error . This time it said the error was at the alias not the ).
matak (3/20/2016)
You need to give your
select *
from...
^{“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

March 21, 2016 at 4:23 am

#1866401
RE: Joining Tables with Right Function

ChrisM@Work (1/18/2016)
Jeff Moden (1/18/2016)
ChrisM@Work (1/18/2016)
Jeff Moden (1/18/2016)
ChrisM@Work (1/18/2016)
Jeff Moden (1/18/2016)[hrCan we agree on "SARGable in one direction only" as it's often sufficient to permit tweaking a decent plan?
With the battles...
^{“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

March 18, 2016 at 10:21 am

#1866178
RE: Determining gaps in dates

Couple of minor tweaks:
WITH SequencedRows AS (
SELECT
p.ClientId,
h.StartDate,
h.EndDate,
cp.CoveragePlanName,
rn = ROW_NUMBER() OVER (PARTITION BY d.clientid, d.coverageplanname ORDER BY d.StartDate)
FROM ClientCoverageHistory h
INNER JOIN ClientCoveragePlans p
ON p.ClientCoveragePlanId = h.ClientCoveragePlanId
INNER JOIN CoveragePlans...
^{“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

March 18, 2016 at 6:44 am

#1866121
RE: IN clause type

No; but there are two common ways around this. Either resolve out the string to a table of values and join to it, or write the statement as dynamic sql.
Incidentally,...
^{“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

March 17, 2016 at 10:16 am

#1865922
RE: Dropping primary key improves performance

smithhead (3/17/2016)
Thanks everyone. I can post more details later, but here are some quick comments/questions:
Good to know about compute scalars, I was wondering what that meant.
What do...
^{“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

March 17, 2016 at 9:41 am

#1865909
RE: DATEADD shortcut

Jeff Moden (3/16/2016)
Luis Cazares (3/16/2016)
The new data types don't allow these operations to allow better functionality.
That's a serious matter of opinion. I think they flubbed it in two ways...
1....
^{“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

March 17, 2016 at 7:52 am

#1865859
RE: Dropping primary key improves performance

There's no warning on the result operator to back this up (if the warning string is too long to display in the "hover over" property sheet, it can still be...
^{“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

March 17, 2016 at 2:50 am

#1865802
RE: Getting the error "Maximum stored procedure, function, trigger, or view nesting level exceeded"

I'd strongly urge you to spec out the functionality of
EXEC [dbo].eusp_e5_eSM_SE_GetHistoryForItem
and focus on this. It's the meat on the bones of this report.
^{“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

March 16, 2016 at 10:09 am

#1865660
RE: Dealing with large tables (Financial transactions)

Emil B (3/16/2016)
The main problem is that on our fast drive we are slowly running out of space, we have another, slower, drive that we could use. We are planning...
^{“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

March 16, 2016 at 8:37 am

#1865623
RE: Are the posted questions getting worse?

Phil Parkin (3/15/2016)
yb751 (3/15/2016)
GilaMonster (3/15/2016)
A sig that claims an MCSA and can't use SUM?
You caught that too...makes you wonder if people are using other people's accounts. But why?
Indeed. Why...
^{“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

March 15, 2016 at 9:30 am

#1865291
RE: Are the posted questions getting worse?

Sean Lange (3/15/2016)
ChrisM@Work (3/15/2016)
Have I been reasonable with this guy? He crops up fairly regularly and is quite obviously well out of his depth - but I don't see 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

March 15, 2016 at 9:28 am

#1865287
RE: Are the posted questions getting worse?

Have I been reasonable with this guy? He crops up fairly regularly and is quite obviously well out of his depth - but I don't see a huge amount of...
^{“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

March 15, 2016 at 7:37 am

#1865219
RE: Getting the error "Maximum stored procedure, function, trigger, or view nesting level exceeded"

Junglee_George (3/15/2016)
I have added the statement SELECT @@NESTLEVEL in my query at different points in the stored proc eusp_e5_eSM_SE_GetHistoryForItem.
But everywhere it is returning a value 0, even at the previous...
^{“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

March 15, 2016 at 2:45 am

#1865134
RE: Move to new row

girl_bj (3/14/2016)
Thanks.
Is it possible to include in between new data?
Newrecord
chicago
20pax
Newrecord
Spain
10pax
Newrecord
austria
90pax
Sure:
;WITH sample_data (resultA, resultB, resultC, resultD) AS (
SELECT 'chicago', 'ID123', '20pax', 'ID123' UNION ALL
SELECT 'austria', 'ID999', '90pax', 'ID999' UNION ALL
SELECT 'Spain',...
^{“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

March 15, 2016 at 2:29 am

#1865131
RE: Move to new row

;WITH sample_data (resultA, resultB, resultC, resultD) AS (
SELECT 'chicago', 'ID123', '20pax', 'ID123' UNION ALL
SELECT 'austria', 'ID999', '90pax', 'ID999' UNION ALL
SELECT 'Spain', 'ID777', '10pax', 'ID777')
SELECT x.newcol1, x.newcol2
FROM sample_data
CROSS APPLY (
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

March 14, 2016 at 6:53 am

#1864887

Viewing 15 posts - 1,681 through 1,695 (of 10,143 total)