Glad to hear my off-the-cuff idea worked!

Thanks for the feedback as I don't believe I've actually tried it myself. 🙂

Easy for you to say. 🙂

Like I said, I...

I don't think there's a need to use ROW_NUMBER(), unless something like this doesn't get it for you:

;WITH ModuleHieararchy AS (

SELECT [SModuleId], [ModuleId], [SMName]

...

This is a self-help forum. 😀

Help us to help you help yourself...

zerko (1/17/2013)

---

... if anyone could point me to a way of using tally tables to process nested loops I would be very grateful. I need to generate the above sequence...

Maybe you need to:

PARTITION BY LEFT(CourseDate, 4)

?

It's possible to script a new job start time in the scheduler (SQL Agent) but that's probably a pain.

Something like this might do it for you:

DECLARE @TryCount INT = 0

WHILE...

matak (1/17/2013)

---

I decided to write my own for a business requirement and also after reading this great article from Dwain.

http://www.sqlservercentral.com/articles/Excel/91179/

As everyone keeps saying if you don't understand the code then...

This should also work:

SELECT newColumn

FROM yourTable as yt

CROSS APPLY (

VALUES (LEFT(yt.yourColumn,1)+'C')

,(LEFT(yt.yourColumn,1)+'D') new (newColumn)

dwilliscp (1/17/2013)

---

Thanks I will try and see what I get, but not sure that it will show much if I am just checking for deadlocks. All the job (at that...

opc.three (1/17/2013)

---

I think my second code-effort on this post is equivalent to the CROSS APPLY VALUES approach you showed except that the technique is also usable on SQL Server 2005....

Incorrect. SQL Server kills only the transaction that costs the least to rollback, so the competing transaction could have...

opc.three (1/12/2013)

---

Not a problem. I hadn't played with unpivoting data in a long time so I took it on as a learning challenge. It took me about a half hour...

You could try turning on the deadlocks trace flag:

DBCC TRACEON (1222, -1)

And then checking the log to see if it reports deadlocks during the period the job is running.

ChrisM@Work (1/16/2013)

---

;WITH SampleData AS (

SELECT CId = 1, CCId = 'a', CCount = 3, totalCount = 6 UNION ALL

SELECT 1, 'a', 3, 6 UNION ALL

SELECT 1, 'b', 3, 6 UNION...