Exercise 128

Find inlets with greater total payout in different pairs of inlets with equal inlet No's from outcome and outcome_o tables considering only those dates when waste was collected at least at one of the inlets involved. Result set: Inlet #, date, text:- "once a day", if daily reporting inlet is in the lead; - "more than once a day", if several times a day reporting inlet is in the lead; - "both", for draw cases.

As it was written in the description of a subject domain, the table with a suffix "_o" and without it are two different databases. So, we can consider that they describe business of different firms.

As we compare the points (inlets) having identical numbers, it is necessary to exclude from result set a variant when one table includes a point with number which is absolutely absent in other table. How is it possible to compare something to that which is not present?

Words "day when waste was collected" mean, that if for some date at point in the first table there is a record, but in the second is not present, the first table's point wins. This situation is different, in my opinion, from previous one where the point was simply absent.

Absence of the information in this case means that the point simply did not work or idled. In this case the victory is awarded justifiably.

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.