/*
Alias: WORK
LiveAnswer: FALSE

*/

SELECT InPatTotals.SessionID, InPatTotals.DentistID, 
		InPatTotals.Total AS InTotal, InPatTotals.TotalEURO AS InTotalEURO, 
		OutPatTotals.Total AS OutTotal, OutPatTotals.TotalEURO AS OutTotalEURO
FROM "PatTotals2.sql" AS InPatTotals
				INNER JOIN "PatTotals2.sql" AS OutPatTotals ON 
			 InPatTotals.DentistID = OutPatTotals.DentistID AND
			 InPatTotals.SessionID = OutPatTotals.SessionID
WHERE InPatTotals.Sign = -1 AND OutPatTotals.Sign = 1
UNION
SELECT InPatTotals.SessionID, InPatTotals.DentistID, 
		InPatTotals.Total AS InTotal, InPatTotals.TotalEURO AS InTotalEURO, 
		0 AS OutTotal, 0 AS OutTotalEURO
FROM "PatTotals2.sql" AS InPatTotals
WHERE InPatTotals.Sign = -1 AND 
			NOT EXISTS (SELECT OutPatTotals.SessionID
							FROM "PatTotals2.sql" AS OutPatTotals
							WHERE OutPatTotals.Sign = 1 AND
									OutPatTotals.DentistID = InPatTotals.DentistID AND
									OutPatTotals.SessionID = InPatTotals.SessionID)
UNION
SELECT OutPatTotals.SessionID, OutPatTotals.DentistID, 
		0 AS InTotal, 0 AS InTotalEURO, 
		OutPatTotals.Total AS OutTotal, OutPatTotals.TotalEURO AS OutTotalEURO
FROM "PatTotals2.sql" AS OutPatTotals
WHERE OutPatTotals.Sign = 1 AND 
			NOT EXISTS (SELECT InPatTotals.SessionID
							FROM "PatTotals2.sql" AS InPatTotals
							WHERE InPatTotals.Sign = -1 AND
									OutPatTotals.DentistID = InPatTotals.DentistID AND
									OutPatTotals.SessionID = InPatTotals.SessionID)
