I have an SQL table that looks like this:
Flow Head Series_Name
0 null A
0 null B
10 null A
10 null B
20 null A
22.5 88 B
20 null B
30 null A
30 null B
39.42 60.1 A
40 null A
40 null B
etc... etc... etc...
5000 null A
5000 null B
Basically, the idea of this table is to be able to use it to plot Flow vs Head in a Line Chart element in Cognos Report Studio. This works nicely, since I can drag and drop the Flow column into the X-Axis, the Head column into the Y-Axis, and the Series_Name column into the Legend. For the example table above, this produces two curves, A and B.
Notice that Flow values are ranging from 0 to 5000, incrementing by 10, for both series. Notice also that Head values are mostly null, except when a data point is available. (Typically, data points' Flow values don't fall exactly on numbers that are divisible by 10, but if they do, it shouldn't matter for the problem I have.)
So, here's the problem I have. When I generate the Line Chart, everything looks great, except for those assets whose data points have lower Flow values. For these, the fixed X-Axis range of 0 to 5000 doesn't produce a nice looking Line Chart, since the curve is scrunched up against the Y-Axis. (The Y-Axis, by the way, looks just fine, since the Line Chart element automatically scales its Y-Axis.)
I know what needs to happen to fix this problem. I need to be able to write an SQL query to this table that filters out all rows with Head = null past whatever the last Head != null row is. It would also be nice to have an offset so that it didn't start to cut off until a few rows past the last Head != null row. This would make the chart look even nicer.
For the example table I gave above, if you assume that the Flow = 39.42 & Head = 60.1 row is the last Head != null row in the table, then a nice query result from this table would be the following, which excludes all the rows (by an offset of 6 rows) past it:
Flow Head Series_Name
0 null A
0 null B
10 null A
10 null B
20 null A
22.5 88 B
20 null B
30 null A
30 null B
39.42 60.1 A
40 null A
40 null B
50 null A
50 null B
60 null A
60 null B
I know this requires a WHERE clause at the end of the SELECT statement, but I'm not sure how to phrase such a clause in order to exclude rows only if they're part of the unnecessary latter part of the query result. The SQL query needs to be written in Native SQL syntax.
This sort of query result, which cuts off all unnecessary data, would graph beautifully! I appreciate the help!
I make this example in SQL SERVER same code should work in oracle
SELECT *
from Events
WHERE Flow <= (SELECT max(Flow) + 3*10 FROM Events WHERE [HEAD] IS NOT NULL)
3*10 to offset three more rows.
If you want something more generic and scalable you can use row_number() but need to be carefull with the order by
WITH addRow_id as (
SELECT *, ROW_NUMBER() OVER ( ORDER BY [Flow], [Series_Name]) as rn
FROM Events
),
lastID as (
SELECT MAX(rn) as last_row
FROM addRow_id
WHERE [HEAD] IS NOT NULL
)
SELECT *
FROM addRow_id A
CROSS JOIN lastID L
WHERE A.rn <= L.last_row + 6;