Exercise #60

Solution 2.3.1

SELECT i.point, 
CASE inc
    WHEN NULL
    THEN 0
    ELSE inc
    END -
    CASE out
    when NULL
    THEN 0
    ELSE out
END
FROM (SELECT point, SUM(inc) inc
      FROM Income_o
      WHERE '20010415' > date
      GROUP BY point
     ) AS I 
    FULL JOIN
     (SELECT point, SUM(out) out
      FROM Outcome_o
      WHERE '20010415' > date
      GROUP BY point
     ) AS III ON III.point = I.point;

This task is very similar to the previous one, exercise 59. Basically, it just adds a selection by date. Therefore, we’d like to draw your attention to the way it’s represented in the query. As you can see, the predicate compares a string value with a datetime field. MS SQL Server has the CONVERT function that allows converting a string representation of date and time to the datetime type using various date formats. However, the string representation in the “year month day” format used in the reviewed solution is always correctly cast to datetime by implicit conversion regardless of server settings [5].

Those who understood the mistake in the previous task will certainly see an attempt to correct it here. Unfortunately, it wasn’t very successful, which, on the other hand, gives you another chance to test yourself.

T&S

To solve the problem on SQL-EX.RU