DB2 date grouping calculated data values

Recently participated in the project carried out an optimization of the technical transformation, the SQL parameterization of the common method of transformation. Other modules are accepted by the project team. For this transformation, their own modules are to test the end.

Then the testing process found that a SQL report for the date grouping calculation error, the SQL statement to the super-long exception.

For a chestnut, the functional requirements are as follows:

In table Table_test

ID AMOUNT Create_date

1 100 2014-01-01

2 2014-01-09

3 2014-01-11

4 2014-01-12

5 2014-01-21

6            100                        2014-01-22

7 2014-01-24

Press creat_date from the table every 10 days as a grouping, the total amount of statistics

This is the case with the previous SQL

SELECT SUM (amout) as TotalAmount FORM table_test WHERE create_date between ' 2014-01-01 ' and ' 2014-01-10 ' UNION allselect SU M (amout) as TotalAmount FORM table_test WHERE create_date between ' 2014-01-11 ' and ' 2014-01-20 ' UNION allselect SUM (amout) As TotalAmount FORM table_test WHERE create_date between ' 2014-01-21 ' and ' 2014-01-30 '

The problem is that, on the one hand, the logic of the program is more responsible, need to calculate the start date of every 10 days interval, but also an aspect of SQL statement lengthy, the date span is large, it is obvious, directly lead to program throw exception.

The following is an improved SQL

SELECT SUM (AMOUNT) as TotalAmount, (Days (create_date)-days (' 2014-01-01 '))/10 as TIMESAPN from Table_test GROUP by Timesap N

The key point is

(Days (Create_date) – Days (' 2014-01-01 '))/10 as TIMESAPN

A TIMESAPN of 0 indicates that the record is within the first 10-day period, 1 means within the second 10-day cycle, and so on, and, finally, this field is used as the grouping condition to achieve the same effect as the above SQL.

DB2 date grouping calculated data values