Let's assume that you're interested in a rolling 13 months and you are not tied to the column names you have specified. You can do this by a combination...

YSL,

First you'll need to decide exactly what "measurements" of your sample data will lead you to the conclusions that you seek. That statement sounds a bit ironic but it...

You can try this:

SELECT ID, Date

,Status=CASE WHEN GETDATE() < MAX(Date) OVER (PARTITION BY ID) THEN 'Active' ELSE 'Inactive' END

FROM Table1

ChrisM@Work (9/11/2012)

---

Using the sample data provided, a simple calculation rCTE works fine with both positive and negative numbers.

DECLARE @AmountToAllocate INT = 21

;WITH Calculator AS (

SELECT

BucketID, TotalSize, Amount,

AmountLeftToAllocate =...

You...

The first looks more like a bin packing problem to me:

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/30/bin-packing-part-1-setting-a-baseline.aspx

There's a series of 5 articles by Hugo Kornelis at this link (to the first). Very complicated, but the...

The image does show when you access the link. Interesting problem but it won't be easy to come up with...

Sean Lange (9/10/2012)

---

"Insanity is doing the same thing over and over again and expecting different results."

-- Albert...

Looks like a really, really, really dangerous approach to me.

Use IDENTITY.

Another guess:

select activity

, a.sponsor 'OldSponsor'

, sibling 'OldSibling'

, 4 'NewSponsor'

--, '?' 'NewSibling' --first result should be 3, second 4, etc based on max(sibling)+x for group sponsor=4

, ( SELECT MAX(sibling)

...

Jeff Moden (9/10/2012)

---

For such questions, I ask the person asking the question to write the word or number to be reversed on a piece of paper. Then I flip...

ronmoses (9/10/2012)

---

dwain.c, Mark, and Luis, thank you very much for your solutions. All three are workable and really quite brilliant. Ultimately I believe I'll be going with Luis's...

Better late than never?

DECLARE @num BIGINT = 1234567890

;WITH Tally (n) AS (

SELECT TOP (LEN(@num)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)

SELECT (SELECT SUBSTRING(RTRIM(@Num), 1+LEN(@Num)-n, 1)...

Nasty piece of work this, but you could try tokenizing the string with a rCTE, replacing the token and then putting it all back together. First the setup data:

DECLARE...

Indeed Gus, the new version of your query seems to be fastest.

========== Mark ===========================================

(20000 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob...