ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,496 through 3,510 (of 10,143 total)

RE: Select Top x% From Group Based on Total

tfendt (3/24/2014)
...
The problem with this query is it returns data for dealer id 3. The business rule I was given was that they only want to see the top 80%....
^{“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 24, 2014 at 8:17 am

#1699538
RE: Help me optimise this query

SELECT
c.claim_id,
c.claim_created,
x.[order],
x.[order_created]
FROM #claim c
CROSS APPLY (
SELECT
[order] = MIN(order_id),
[order_created] = MIN(created_dtm)
FROM dbo.[order] o
WHERE o.claim_id = c.claim_id
) x
^{“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 24, 2014 at 8:04 am

#1699528
RE: Reaping the benefits of the Window functions in T-SQL

Eirikur Eiriksson (3/24/2014)
ChrisM@Work (3/24/2014)
The article isn't always easy to follow and might be helped by this:
DECLARE @pString VARCHAR(8000), @pDelimiter CHAR(1);
SELECT @pString = 'The,quick,brown,fox', @pDelimiter = ',';
WITH E1(N) 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

March 24, 2014 at 5:40 am

#1699483
RE: Reaping the benefits of the Window functions in T-SQL

The article isn't always easy to follow and might be helped by this:
DECLARE @pString VARCHAR(8000), @pDelimiter CHAR(1);
SELECT @pString = 'The,quick,brown,fox', @pDelimiter = ',';
WITH E1(N) 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

March 24, 2014 at 4:27 am

#1699450
RE: Efficient way to get the lowest unused value of a nvarchar field being used as a primary key

micalo (3/23/2014)
Hi Scott,
Thanks for trying but didn't work unfortunately. I had another crack at it but in the end decided the that its really in the best interest of 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

March 24, 2014 at 3:07 am

#1699426
RE: Select Top x% From Group Based on Total

Gotcha - think you're looking for this:
;WITH
Preagg AS (
SELECT DealerID, Tire, RepairsOfThisTyre,
RepairsForThisDealer = SUM(RepairsOfThisTyre) OVER(PARTITION BY DealerID)
FROM (
SELECT DealerID, Tire,
RepairsOfThisTyre = COUNT(*)
FROM #Table1 d
INNER JOIN #Table2 r...
^{“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, 2014 at 10:46 am

#1699186
RE: Query returns no records unless I add a date range to the WHERE clause

dana 6761 (3/21/2014)
I have a query in a stored procedure that I want to put into a view to improve response time...
It won't improve response 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

March 21, 2014 at 9:39 am

#1699138
RE: Select Top x% From Group Based on Total

What do you mean by "Total # of tires for the size"?
Here's your data set up in a consumable format:
DROP TABLE #Table1
CREATE TABLE #Table1 (DealerID INT, RepairID INT)
INSERT 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

March 21, 2014 at 9:31 am

#1699130
RE: Tuning SP (Left Outer Join)

ramana3327 (3/21/2014)
Hi,
I am working on tuning of sp. They wrote several left outer join. Is there any option to avoid them. Any suggestions please
Left joins are a logic decision, too...
^{“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, 2014 at 8:41 am

#1699104
RE: Really Slow query when i add a query to select statement

Then you might want to look at this next:
where convert(varchar,DESPATCH_NOTE_DATE,111) >= @date
This expression means that any index on DESPATCH_NOTE_DATE can't help. Rather than converting DESPATCH_NOTE_DATE to match @date, 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 21, 2014 at 8:16 am

#1699098
RE: Really Slow query when i add a query to select statement

It's the estimated plan - the actual plan is more useful.
The estimated plan you've posted carries this index recommendation:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[DESPATCH_REPORTS] ([SourceWarehouse])
INCLUDE ([CUSTOMER],[SALES_ORDER],[DESPATCH_NOTE_DATE],[TargetWarehouse])
It would...
^{“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, 2014 at 6:42 am

#1699063
RE: Really Slow query when i add a query to select statement

Yes, thanks.
(
select top 1 (sm2.SalesOrder)
from SorDetail sd2
left join SorMaster sm2
on sm2.SalesOrder = sd2.SalesOrder
where sd2.LoadNumber = sd.LoadNumber
and sm2.Customer = ar.Customer
)
Top of what exactly, it's missing ORDER...
^{“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, 2014 at 6:31 am

#1699060
RE: Really Slow query when i add a query to select statement

Can you please post the actual execution plan (of the query including the subquery) as a .sqlplan file attachment? Cheers.
^{“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, 2014 at 6:12 am

#1699055
RE: Efficient way to get the lowest unused value of a nvarchar field being used as a primary key

micalo (3/21/2014)
Hi Lempster,
People can put whatever they want in there unfortunately. There no really way without going through all the data that i can think of. I personally think its...
^{“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, 2014 at 5:49 am

#1699044
RE: Strange "multi-part identifier could not be bound" error; "select" works but "insert" doesn't

It's an old thread, Wesley - so it's quite likely that none of the respondents will reply. Are you experiencing a similar issue? If so, your best option might 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 21, 2014 at 4:43 am

#1699019

Viewing 15 posts - 3,496 through 3,510 (of 10,143 total)