It was bugging me that I did a PIVOT using a crosstab and then did an UNPIVOT. That just somehow seemed unnecessary. Sure enough, that led to a...

Heh, heh. Flattery will get you everywhere.

Note...

Interesting and challenging problem! Thanks for a great start to the morning.

My solution, for which I will not make any claims of trying to optimize it for performance, is:

WITH...

DDL and sample data in a consumable form would be nice.

PiMané (9/4/2013)

---

ChrisM@Work (9/4/2013)

---

Or CROSS APPLY VALUES:

-- sample data

;WITH gennum (tollnum, n1, n2, n3) AS (

SELECT 800123, 1234, 1235, 1236 UNION ALL

SELECT 999123, 9876, 9875, 9874

)

-- solution

SELECT g.tollnum, d.code

FROM gennum...

Paul - I get fairly stable results with this test harness. Can you confirm?

I am also running on a SQL Server that is fairly quiescent (no other processes running).

--=====...

My personal preference is to do this with a CROSS APPLY:

WITH SampleData AS (

SELECT RowID, DateStamp=CAST(DateStamp AS DATE), PrevStatus, CurrentStatus

FROM (VALUES

(1, '6/1/2012',...

Perhaps it is overkill but is this a case where "fudge rounding" would help?

http://www.sqlservercentral.com/articles/Financial+Rounding/88067/

Interesting that this related thread should appear just now:

http://www.sqlservercentral.com/Forums/Topic1492737-3077-1.aspx?Update=1

I think I'm missing something here but for what it's worth:

WITH SampleData (start_time, end_time) AS (

SELECT '22:00', '05:59'

UNION ALL SELECT '12:00', '18:59'

)

SELECT start_time, end_time

,ElapsedMin=

...

You might want to consider using the CROSS APPLY VALUES approach to UNPIVOT (see my signature links) and do it this way (works on SQL 2008 and uses Alan's set...

Paul.S here: http://www.sqlservercentral.com/Forums/FindPost1493158.aspx

Try running this 3-4 times and you'll see that the winner bounces around among the 3 (you forgot my version in your test harness).

--===== Conditionally drop the test table to...

Jeff Moden (9/9/2013)

---

PRINT '========== Another Integer Math Method =========='

SET STATISTICS TIME ON;

SELECT @BitBucket = RIGHT(1000000000 + C1*1000 + C2, 9)

FROM #TestTable;

SET STATISTICS TIME OFF;

Oh,...

As you said, in the interests of science, I removed the duplicate scenario in your test harness, added back my original at the end and included an extra one based...