Try among these, live examples
http://www.sqlfiddle.com/#!3/c3e84/2[
^]
WITH [Collection]
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY UID) AS RowId, Coll.*
FROM Table2 AS Coll
),
LastCollection
AS
(
SELECT MAX(RowId)AS RowId, UID AS LastId
FROM [Collection]
GROUP BY UID
)
SELECT
Bill.*,
Coll.[Collection] AS Coll,
CASE
WHEN(LastColl.RowId IS NULL) THEN(SELECT 0)
ELSE(
COALESCE(Bill.Bill, 0) - COALESCE((SELECT SUM(PreColl.Collection)
FROM [Collection] AS PreColl
WHERE PreColl.UID = Coll.UID
AND PreColl.RowId <= Coll.RowId), 0)
)
END AS Outstd
FROM Table1 AS Bill
INNER JOIN [Collection] AS Coll ON Bill.UID = Coll.UID
LEFT JOIN LastCollection AS LastColl ON Coll.RowId = LastColl.RowId;
or little more interesting
WITH [Collection]
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY UID) AS RowId, Coll.*
FROM Table2 AS Coll
)
SELECT Bill.*, Coll.Collection AS Coll,
COALESCE(Bill.Bill, 0) - COALESCE((SELECT SUM(PreColl.Collection)
FROM [Collection] AS PreColl
WHERE PreColl.UID = Coll.UID
AND PreColl.RowId <= Coll.RowId), 0)AS Outstd
FROM Table1 AS Bill
INNER JOIN [Collection] AS Coll ON Bill.UID = Coll.UID;
But it would be better if
SELECT AllUser.UID,
COALESCE(TtlBill.Bill, 0) AS Bill,
COALESCE(TtlCollection.Coll, 0) AS Coll,
COALESCE(TtlBill.Bill, 0) - COALESCE(TtlCollection.Coll, 0) AS Outstd
FROM(
SELECT *
FROM(
SELECT UID FROM Table1
UNION ALL
SELECT UID FROM Table2
) AS Users
GROUP BY UID
) AS AllUser
LEFT JOIN(
SELECT UID, SUM(Bill) AS Bill
FROM Table1
GROUP BY UID
) AS TtlBill
ON AllUser.UID = TtlBill.UID
LEFT JOIN(
SELECT UID, SUM(Collection) AS Coll
FROM Table2
GROUP BY UID
) AS TtlCollection
ON AllUser.UID = TtlCollection.UID;