Exercise #30

Under the assumption that the income (inc) and expenses (out) of the money at each outlet (point) are registered any number of times a day, get a result set with fields: outlet, date, expense, income. Note that a single record must correspond to each outlet at each date. Use Income and Outcome tables.

This task is required to gather data from two tables in one resultant set so the income and the outcome in one point must be in one row.

Similar task (29) for Income_o and Outcome_o tables doesn`t cause any problems as usual. The problem`s root is demonstrated by following solution.

Solution 2.1.1

  1. SELECT Income.point, Income.date, SUM(out), SUM(inc)
  2. FROM Income LEFT JOIN
  3. Outcome ON Income.point = Outcome.point AND
  4. Income.date = Outcome.date
  5. GROUP BY Income.point, Income.date
  6. UNION
  7. SELECT Outcome.point, Outcome.date, SUM(out), SUM(inc)
  8. FROM Outcome LEFT JOIN
  9. Income ON Income.point = Outcome.point AND
  10. Income.date = Outcome.date
  11. GROUP BY Outcome.point, Outcome.date;

The idea is in the joining of table with incomes and with outcomes by equality of point`s number and a date. Left join guarantees right result in case when there is some days with income only (outcome is NULL). Then it unions with an opposite query which has left join the outcomes table with the incomes table. This takes into account opposite situation when there is outcome and income is NULL. The duplicates are removed by UNION clause.

The query 2.1.1 returns wrong result if many income/outcome operations at one point happens in one day. As example for this situation we get typical day: March 24, 2001. Let`s execute a couple of queries:

  1. SELECT * FROM Income
  2. WHERE date = '2001-03-24 00:00:00.000' AND point = 1;

  1. SELECT * FROM Outcome
  2. WHERE date = '2001-03-24 00:00:00.000' AND point = 1;

We get such result:


code point date inc
3 1 2001-03-24 00:00:00.000 3600.0000
11 1 2001-03-24 00:00:00.000 3400.0000


code point date out
2 1 2001-03-24 00:00:00.000 3663.0000
13 1 2001-03-24 00:00:00.000 3500.0000

In case when there are both: incomes and outcomes, outer join and inner join are the same. Every single row from one table joins with every row from other one if the date and the point`s number are equal. That`s why before data is grouping such result would be returned (the income and outcome columns are only displayed):

inc out
3600.0000 3663.0000
3600.0000 3500.0000
3400.0000 3663.0000
3400.0000 3500.0000

After grouping and summarizing we get doubling of the result as for income as well for outcome. If there would be three incomes, we would get tripling of outcome and so on.

The duplicates aren`t matter, because each query in union returns analogous result: there is only one row for each pair {point, date}.


To solve the problem on SQL-EX.RU

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.