There may very well be an easier way to do this, but this is what I came up with:

WITH AllDepts AS

(

SELECT DeptID, JobNo

FROM #Sales

UNION

SELECT DeptID, JobNo

FROM #Costs

)

SELECT b.CustNo, a.JobNo, a.DeptID

,TotalSales=ISNULL(c.TotalSales, 0), SplitSales=ISNULL(c.Split, e.Split)

,TotalCosts=ISNULL(d.Costs, 0), SplitCosts=ISNULL(c.Split, e.Split)*ISNULL(d.Costs, 0)

FROM AllDepts a

CROSS APPLY

(

SELECT DISTINCT CustNo

FROM #Sales b

WHERE a.JobNo = b.JobNo -- AND a.DeptID = b.DeptID

) b

LEFT JOIN #Sales c ON b.CustNo = c.CustNo AND a.JobNo = c.JobNo AND a.DeptID = c.DeptID

LEFT JOIN #Costs d ON a.JobNo = d.JobNo AND a.DeptID = d.DeptID

CROSS APPLY

(

SELECT TOP 1 Split

FROM #Sales e

WHERE e.CustNo = b.CustNo AND e.JobNo = a.JobNo

) e

ORDER BY b.CustNo, a.JobNo, a.DeptID;

I have two concerns about it though:

1.

-- See where I have this commented code?

-- AND a.DeptID = b.DeptID

-- My concern is that without this (additional limiter) I'm afraid your record set

-- will explode with more jobs/depts but I only get all the rows you need

-- with it commented out.

2. The way you're deciding to allocate your costs to your customers seems a bit odd. I've figured out how to get the 40/60 split that your expected results implies, but I'm not sure this is going to work well in cases where there's more customers, departments or jobs.

More test data (like for multiple jobs and different combinations of depts and sales/costs might help to further flush out your requirements. Don't forget, when you expand your test data, you need to add corresponding information to your expected results.

But perhaps this will get you on the road to a solution.