Since the numbers are no longer random, why not break the randomness rules again and use this very simple algorithm?

;WITH SampleData AS (

SELECT ID = RIGHT('0'+CAST(n AS VARCHAR(2)),2)

FROM (SELECT TOP...

dwain.c (2/8/2013)

---

It must have been. That or a wristwatch. It resolved something like 100000 rows in...

greg.bull (2/8/2013)

---

OK best I can think of then is to use a calendar table to plug holes in the data - processing each staffid one by one in a supervisory...

vinu512 (2/7/2013)

---

hi Chris, it definitely runs like a Rocket. It would be really nice if you could explain the following code :

SELECT df = LEFT(b.AllLevels, CHARINDEX('»',b.AllLevels,1)-1)

UNION ALL

SELECT df =...

Sean Lange (2/7/2013)

---

How about using the DelimitedSplit8k? This seems to be super fast to me.

Select Id,

MAX(Case When ItemNumber = 1 Then Item Else '' End) As...

-- replace this

SELECT Percentage = CAST(q.Amount*1.00/q.GroupTotal AS NUMERIC(5,2))

-- with this...

Sean Lange (2/7/2013)

---

I am not sure...

Not quite...

WITH SampleData ([Group], [Type], Amount) AS (

SELECT 'Dog', 'Typea', 1 UNION ALL

SELECT 'Dog', 'Typeb', 2 UNION ALL

SELECT 'Dog', 'Typec', 3 UNION ALL

SELECT 'Cat', 'Typea', 10...

mjbkm (2/7/2013)

---

That is where I was stuck before. How would I change this?

SELECT

[Group],

[Type],...

It can be easier to visualise complex filters by switching them into a CROSS APPLY for testing. Shamelessly nicking Orlando's setup, it might look like this;

USE tempdb;

IF OBJECT_ID(N'tempdb..#CLIENT_IDENTIFIER_TYPE') IS NOT...

Aggregate Amount by Group and type, then feed the result into Sean's original query.

mjbkm (2/7/2013)

---

This is working great too. What if I add multiple types per animal in first. How do I group and total first and then get the same...

Sean Lange (2/7/2013)

---

ChrisM@Work (2/7/2013)

---

;WITH SampleData ([Group], [Type], Amount) AS (

SELECT 'Dog', 'Typea', 1 UNION ALL

SELECT 'Dog', 'Typeb', 2 UNION ALL

SELECT 'Dog', 'Typec', 3 UNION ALL

SELECT 'Cat', 'Typea', 10 UNION ALL

SELECT...

;WITH SampleData ([Group], [Type], Amount) AS (

SELECT 'Dog', 'Typea', 1 UNION ALL

SELECT 'Dog', 'Typeb', 2 UNION ALL

SELECT 'Dog', 'Typec', 3 UNION ALL

SELECT 'Cat', 'Typea', 10 UNION ALL

SELECT 'Cat', 'Typeb', 20

)

SELECT...

carlosaamaral (2/7/2013)

---

OPS... living and learning ....

   changed to level 100 - perfect ..

; WITH THE Strings (

SELECT String = 'AJKY.GSEFD6, 7345535HNB, BBN4343.434'

UNION ALL SELECT '11-908 / *** 88AB; '

)

SELECT

s.String,

x.NewString

FROM Strings...