Bob Hovious (9/15/2009)

---

This is exactly why you DON'T want screen display time in the mix. It includes system processes that...

Here's my "piecemeal" solution that works with any positive integer:

DECLARE @IntVal int;

SET @IntVal = 2147483647;

SELECT @IntVal / POWER(2,16), @IntVal % POWER(2,16);

SELECT

REPLACE(STR(ISNULL((SELECT SUM(POWER(CAST(10 AS bigint),h.number))

FROM master.dbo.spt_values h

WHERE h.type = 'P' AND...

Bob Hovious (9/15/2009)

---

Okay.... I knocked this out before taking off for lunch. Now anybody reading can satisfy themselves using Jonathan's measurement of time to execute, since he doesn't seem...

I'm confused by your code snippets, but I think I understand your text.

If you want the result set to have multiple columns that you will subsequently concatenate, you could use...

Does this mean you've found a method that's faster than the...

SQLBOT (9/15/2009)

---

Jonathan,

Brilliant! Thanks.

I like the power and bitmask method of conversion.

That beats the modulus and division method (I think).

I should have specified that I wanted to use a tally...

Bob Hovious (9/15/2009)

---

True, and I used to code that way before better techniques were made available in...

Bob Hovious (9/15/2009)

---

;with cte as (select *,ROW_NUMBER() over(partition by vehicle order by dueHours) as seqID from @sample...

Dave Ballantyne (9/15/2009)

---

This is the output i get from set statistics io on .

Non Cte

Table 'SalesOrderDetail'. Scan count 31466, logical reads 103081, physical reads 0, read-ahead reads 0, lob logical...

If you're fixed on using a "tally table," I guess you could try something like this:

DECLARE @IntVal int;

SET @intVal = 54321;

SELECT SUM(POWER(CAST(10 AS bigint),v.number))

FROM master.dbo.spt_values v

WHERE v.type = 'P' AND...

And here's the reminder about that keyword from SQL Server 2008 BOL:

ALL

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in...

HowardW (9/15/2009)

---

The thing is I suspect it would be slower with a large dataset. If you want to preserve both rows in the event of a tie, use rank() rather...

If you have control over the schema design, please take Bob's advice. And read up on database normalization.

I'm afraid I cannot agree. I'd still use something like this:

SELECT ID, Vehicle, DueHours, DueName

FROM @sample s

WHERE DueHours IN

(SELECT...

SELECT ID, Dep, [Key]

FROM #Temp t

WHERE [Key] = 0 AND NOT EXISTS

(SELECT *

FROM #Temp

WHERE [Key] t.[Key] AND Dep = t.Dep)