Sql-ex Solutions

Exercise: 29

Under the assumption that receipts of money (inc) and payouts (out) are registered not more than once a day for each collection point [i.e. the primary key consists of (point, date)], write a query displaying cash flow data (point, date, income, expense). Use Income_o and Outcome_o tables.

Solution

SELECT 
    CASE 
		WHEN income_o.point IS NULL THEN outcome_o.point
		ELSE income_o.point
	END, 
    CASE 
		WHEN income_o.date IS NULL THEN outcome_o.date
		ELSE income_o.date
	END, 
    income_o.inc, outcome_o.out
FROM income_o
	FULL JOIN outcome_o
	ON income_o.point = outcome_o.point
		AND income_o.date = outcome_o.date

References

1. https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15

2. https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15