Here is your code posted so others can see it easily.

-- Using SQL2008 R2

IF OBJECT_ID('tempdb..#Sales') IS NOT NULL

DROP TABLE #Sales

IF OBJECT_ID('tempdb..#Costs') IS NOT NULL

DROP TABLE #Costs

IF OBJECT_ID('tempdb..#Sales_vs_Costs') IS NOT NULL

DROP TABLE #Sales_vs_Costs

CREATE TABLE #Sales

(

CustNo INT

,JobNo VARCHAR(12)

,DeptID VARCHAR(10)

,TotalSales float

,Split float

)

CREATE TABLE #Costs

(

JobNo VARCHAR(12)

,DeptID VARCHAR(10)

,Costs float

)

CREATE TABLE #Sales_vs_Costs

(

CustNo INT

,JobNo VARCHAR(12)

,DeptID VARCHAR(10)

,TotalSales float

,SplitSales FLOAT

,TotalCosts FLOAT

,SplitCosts float

)

-- Have sales for 2 depts for J1.

-- Revenue is split between Cust1 and Cust 2 40/60%

INSERT INTO #Sales

SELECT 1,'J1','D1',1000, 0.4

UNION

SELECT 1,'J1','D2',2000, 0.4

UNION

SELECT 2,'J1','D1',1000, 0.6

UNION

SELECT 2,'J1','D2',2000, 0.6

-- Sales

SELECT

[CustNo]

,[JobNo]

,[DeptID]

,[TotalSales]

,[Split]

,TotalSales * Split AS SplitSales

FROM #Sales

-- Now I have costs, but costs only belong to 1 customer that may not even be a customer that the revenue was recorded for, but we need link costs and sales, according to revenue split

-- So if costs are $100 and split 60/40% Cust 1 get $60 and Cust 2 gets $40

-- Further the departments that revenue is recorded against may not be same as costs. For example record costs for Dept 1 and 2, but sales assinged to Dept's 1 and 3

INSERT INTO #Costs

(

JobNo

,DeptID

,Costs

)

SELECT 'J1','D1',500

UNION

SELECT 'J1','D3',1000

SELECT * FROM #Costs

-- Expected Results

-- All departments that have costs or sales should be listed with respected values

-- I cant for the life of me figure out how to get customer in every row and all depts and respective costs and sales

INSERT INTO [#Sales_vs_Costs]

(

[CustNo]

,[JobNo]

,[DeptID]

,[TotalSales]

,[SplitSales]

,[TotalCosts]

,[SplitCosts]

)

SELECT 1,'J1','D1',1000, 0.4, 500, 200

UNION

SELECT 1,'J1','D2',2000, 0.4,0,0

UNION

SELECT 1,'J1','D3',0, 0.4,1000,400

UNION

SELECT 2,'J1','D1',1000, 0.6,500,300

UNION

SELECT 2,'J1','D2',2000, 0.6,0,0

UNION

SELECT 2,'J1','D3',0, 0.6,1000,600

SELECT * FROM #Sales_vs_Costs AS svc

IF OBJECT_ID('tempdb..#Sales') IS NOT NULL

DROP TABLE #Sales

IF OBJECT_ID('tempdb..#Costs') IS NOT NULL

DROP TABLE #Costs

IF OBJECT_ID('tempdb..#Sales_vs_Costs') IS NOT NULL

DROP TABLE #Sales_vs_Costs

You just need to use the SQL IFCode Shortcut (tag) that appears to the left of the posting window.

I'll take a look now to see if I can make heads or tails.