loading..
Русский    English
17:17

Exercise #60

Calculate the remainder at each outlet by 04/15/01 for the database with accounts for not more than once a day. Result set: point, remainder.

Solution 2.3.1

Console
Execute
  1. SELECT i.point, CASE inc
  2. WHEN NULL
  3. THEN 0
  4. ELSE inc
  5. END -
  6. CASE out
  7. WHEN NULL
  8. THEN 0
  9. ELSE out
  10. END
  11. FROM (SELECT point, SUM(inc) inc
  12. FROM Income_o
  13. WHERE '20010415' > date
  14. GROUP BY point
  15. ) AS I FULL JOIN
  16. (SELECT point, SUM(out) out
  17. FROM Outcome_o
  18. WHERE '20010415' > date
  19. GROUP BY point
  20. ) AS III ON III.point = I.point;

This problem is very similar to the previous problem 59. In fact, there is additionally only date selection. In this regard, we want to draw attention to its representation in the query. The predicate compares the string with a field of datetime type. In  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server there is a function CONVERT, which allows you to convert a string representation of date/time to this type, using different date formats. However, the string representation in the form of year month day, which is used in the present decision, will always be correctly converted implicitly to datetime, regardless of server settings [5].

Those we also managed to mistake in the previous problem, for sure, see here an attempt to correct it. Unfortunately, the attempt failed, that, on the other hand, gives you the chance to test self once again.

T&S

To solve the problem on SQL-EX.RU

Bookmark and Share
Tags
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.