【Question】

Create a query (group by) to find total revenue by year and YoY change (%). For example I have data as follows:

revenue    Date
616.0984   2001-07-08 00:00:00.000
38.8276    2001-07-15 00:00:00.000
985.553    2002-07-08 00:00:00.000
867.2389   2002-07-08 00:00:00.000
12.5838    2002-07-15 00:00:00.000
732.81     2003-02-08 00:00:00.000
429.9821   2003-01-01 00:00:00.000
151.9921   2003-07-08 00:00:00.000
183.1626   2003-07-08 00:00:00.000

Required Result:

Year - Total - YoY

2001 - -
2002 - -
2003 - -

【Answer】

This is a typical inter-row computing problem. You can make it using SQL window functions. But it’s hard to code the logic because window functions are not intuitive and SQL binds aggregation with each grouping. If the data volume is small, you can do it in SPL (Structured Process Language). Below is the SPL script, which is simple and intuitive:

A1: Retrieve data in SQL.

A2: Calculate total revenue by year(Date).

A3: Add a YOY% column; its values are (total-total[-1])/total[-1].

You can call an SPL script from another application. See How to Call an SPL Sscript in Java.