【Question】

I have data like this:

date_1    count_1
2014-12-01    1
2014-12-02    3
2014-12-03    5
2014-12-04    3
2014-12-05    2
2014-12-06    1
2015-01-01    5
2015-01-02    4
2015-01-03    3

I need to write a SQL query to check the status of count_1 (up or down).
Expected result:
date_1    count_1    status_1
2014-12-01    1    Up
2014-12-02    3    Up
2014-12-03    5    Up
2014-12-04    3    Down
2014-12-05    2    Down
2014-12-06    1    Down
2015-01-01    5    Up
2015-01-02    4    Down
2015-01-03    3    Down

【Answer】

You can use window functions to do it by comparing each with its previous in MSSSQL:

select date_1,count_1,case when count_1>lag(count_1) over (order by date_1) then up 'else' down 'end'status_1' from tb

Certain version of SQL SERVER don’t support lag function. In those cases, you need to create sequence numbers for the rows and then perform self-joins. But it’s not necessary to use window function to do that. You can record row numbers with a subquery, which applies to a database that doesn’t support window function. It’s complicated.

SPL (Structured Process Language) is compatible with every type of database and it’s simple to perform order-based calculations in it. Here’s the SPL script for solving your problem:

A1: Retrieve data ordered by date_1 in SQL.

A2: Add a status_1 column. Use count_1[-1] to get count_1 value in the previous record to check whether the value is up or down.

To call an SPL script in another application, you can refer to How to Call an SPL Script in Java.