good start Knives;

so here's my same query, with your updated data in it;

so if you run this and look at the data, does it produce the desired results?

code]

;WITH

TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),

TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM THOUSANDS)

,

myCTE(reference,StartDate,Pattern,PatternLength) AS

(

SELECT 'TestRef-AA',convert(datetime,'01/01/2013 10:45'),' 1111 111 11','15' UNION ALL

SELECT 'TestRef-BB',convert(datetime,'10/11/2013 09:00'),'11 11 111 ','12' UNION ALL

SELECT 'TestRef-CC',convert(datetime,'09/09/2013 13:00'),'11111 11 ','9' UNION ALL

SELECT 'TestRef-DD',convert(datetime,'02/08/2013 12:00'),'1 11 1 ','11'

)

SELECT CASE WHEN SUBSTRING([pattern],N,1) = '1'

THEN DATEADD(wk,Tally.N,[startdate])

ELSE NULL

END As CalcDate,*,

SUBSTRING([pattern],N,1)

FROM MyCTE

CROSS JOIN Tally

WHERE N <= LEN([pattern])

--AND SUBSTRING([pattern],N,1) = '1'

order by reference, N

[/code]