Trying to get my head around a tricky SQL and maybe somebody can help me
out here:
CREATE TABLE TEST(
[ID] INTEGER,
[DATE] TEXT,
[VALUE] REAL
)
Example data:
ID DATE VALUE
-----------------------------
1 2015-01-01 14
1 2015-02-01 16
1 2015-03-01 11
1 2015-04-01 2
1 2015-05-01 12
1 2015-06-01 14
1 2015-07-01 30
1 2015-08-01 15
2 2015-01-01 6
2 2015-02-01 11
2 2015-03-01 14
2 2015-04-01 14
2 2015-05-01 50
2 2015-06-01 14
2 2015-07-01 14
2 2015-08-01 14
Now, what I need is for every row the count of preceding rows where
the ID is the same as the ID of the current row, the date is less than the
date of the current row and the value is >= 10 and <= 20. If the value is
outside
this range then it should stop the count for that current row. If the value
of the current row is outside this 10 to 20 range than the result is 0 and
there
is no need to count preceding rows.
This is what the result should be:
ID DATE VALUE COUNT_PRECEDING_IN_RANGE
---------------------------------------------------------------------------------
1 2015-01-01 14 0
1 2015-02-01 16 1
1 2015-03-01 11 2
1 2015-04-01 2 0
1 2015-05-01 12 0
1 2015-06-01 14 1
1 2015-07-01 30 0
1 2015-08-01 15 0
2 2015-01-01 6 0
2 2015-02-01 11 0
2 2015-03-01 14 1
2 2015-04-01 14 2
2 2015-05-01 50 0
2 2015-06-01 14 0
2 2015-07-01 14 1
2 2015-08-01 14 2
Hopefully this is clear enough. Note that preceding is a defined by date
and that
the above dates are in the format yyyy-mm-dd.
RBS
