not sure a better solution but it's a solution

;WITH cte AS

(

SELECT ROW_NUMBER() OVER ( ORDER BY ResourceID, dt) AS Rno,

x.ResourceID, x.JobNo, Dt, xdt.Type

FROM (

SELECT j.ResourceID, j.JobNo, j.startdate, j.enddate, 'P' AS JType

FROM #Jobs AS j

UNION ALL

SELECT d.ResourceID, d.Reason, d.startdate, d.enddate, 'D' AS Jtype

FROM #Downtime AS d

) AS x

CROSS APPLY

(

VALUES (x.startdate, x.JType), (x.enddate, x.JType)

) AS xdt(Dt, Type)

)

SELECT x.ResourceID,

CASE WHEN x.JobNo > x1.JobNo THEN x.JobNo ELSE x1.JobNo END AS Activity,

x.dt AS StartDate, x1.Dt AS EndDate,

CASE WHEN x.Type > x1.Type THEN x.Type ELSE x1.type END AS activitytype

FROM cte AS x

LEFT OUTER JOIN cte AS x1

ONx.ResourceID = x1.ResourceID AND x.Rno = x1.Rno - 1

WHERE x1.Dt IS NOT NULL AND x1.Dt <> x.Dt;

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]