I'm not exactly sure I get your requirement, but you might try looking at NTILE for this:

SELECT *

FROM

(

SELECT *, nt=NTILE(10) OVER (PARTITION BY DEPT ORDER BY...

Then there's always the old RIGHT, REVERSE yourvalue trick:

DECLARE @x TABLE (yourvalue varchar(20));

INSERT @x VALUES ('72187-1'),('72187-2'),('72187-12'),('555666-99999');

SELECT *, RIGHT(yourvalue, CHARINDEX('-', REVERSE(yourvalue))-1)

FROM @x;

Just for fun, we'll get a little fancy here. 😛

My lizard-brain recalls a really neat trick I once saw in an article by Jeff Moden.

WITH Letters (letter) AS

(

...

I hate it when you ask questions like that. Makes...

marcia.j.wilson (3/24/2015)

---

Thanks for this article. I had created views to do something similar for several tables. But your solutions are simpler than what I did.

Now I'm hoping...

I see no alternative other than a loop for this. Where the heck is Itzik Ben-Gan when you need him!

-- CREATE sample "__tt_Freight_Product"

IF object_id('tempdb..#__tt_Freight_Product') IS NOT NULL

BEGIN

...

To demonstrate my statement above:

WITH SampleData (Value, CatCode) AS

(

SELECT 1165, 6

UNION ALL SELECT 1173.00, 13

UNION ALL SELECT 9740,...

fahey.jonathan (3/24/2015)

---

SELECTm.ValueField,

m.FilterColumn,-- Either "6" or "13"

r.Result

FROMMyTable m

JOIN(

SELECTt6.CategoryCode,

t13.ValueField - t6.ValueField AS Result

FROM(SELECT CategoryCode, ValueField FROM MyTable WHERE FilterColumn = 6) t6

JOIN(SELECT CategoryCode, ValueField FROM MyTable WHERE FilterColumn = 6) t13

ONt13.CategoryCode =...

Jeff Moden (3/24/2015)

---

Uh huh. And AC electricity wouldn't be the norm if people listened to the...

And then of course there is this article to help you make informed choices about what approach is the best from a performance perspective for identifying your date anomalies:

Identifying Start...

Eirikur Eiriksson (3/24/2015)

---

Good article as always Dwain, thank you very much.

Some thoughts, first of all the 1M test code only produced 100 rows until I adjusted the top clauses. The...

ken.trock (3/24/2015)

---

Interesting article; Dwain requires that, for a given product, the EffectiveStartDT of the next timeline matches the EffectiveEndDT of the previous. This helps to enforce no overlapping dates for...

starunit (3/24/2015)

---

Nice article, Dwain.

I've been using the CTE approach, and hadn't thought about using Cross Apply - but it looks like that's a bit slower anyway. And I'll try to...

My interest in this discussion thread is peaking now.

Isn't training Devs akin to training cats? Not...