Have a look at the NTILE function in Books Online (SQL Server help).

NTILE(n) splits the data into n equal-sized partitions numbered 1 to n in a user-defined order and...

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%....

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

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 (

...

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 (

...

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...

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...

It won't improve response time.

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...

Left joins are a logic decision, too...

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...

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...

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...

Can you please post the actual execution plan (of the query including the subquery) as a .sqlplan file attachment? Cheers.

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...