Return previous non zero value and following non zero values

[MajP]

I am helping someone in thread705-1575770

They have a bunch of concrete columns, in groups and placed in sequential rows. Then they want to do some interpolation. I can do this in code, but I was thinking a sql solution would be more reliable. I am not very good in my SQL. Lets call the data table tblA.

tblA
ID (PK of the concrete column)
location (location id)
intGroup (group designator integer)
intColumn (column designator integer)
intRow (row designator integer)
snglValue (measurement of depth of column. 0 represents no current measurement)

so here is some data. Shows 2 columns from location 4 and group 15. Each column has 12 rows. (The OP misentered row 9 as row 10)

ID intGroup location intColumn intRow snglValue
1	15	4	1	1	0
2	15	4	1	2	0
3	15	4	1	3	0
4	15	4	1	4	4.2
5	15	4	1	5	0
6	15	4	1	6	4.9
7	15	4	1	7	0
8	15	4	1	8	0
9	15	4	1	10	6
10	15	4	1	11	7
11	15	4	1	12	0
12	15	4	1	13	7.6

13	15	4	2	1	3.5
14	15	4	2	2	0
15	15	4	2	3	0
16	15	4	2	4	5
17	15	4	2	5	0
18	15	4	2	6	5.3
19	15	4	2	7	0
20	15	4	2	8	0
21	15	4	2	10	0
22	15	4	2	11	0
23	15	4	2	12	8.2
24	15	4	2	13	0

(what is the trick using tgml to keep the above formatted?)

1) I want to do a ranking query for each column. The reason is as you can see from intRow (the OP made an entry error and there is no row 9). For consistency lets call that "calcRow".
2) Then return the previous non zero value for each zero value. Call this "beginValue"
ID 7 and 8 would return 4.9
3) Return the calcRow of the previous non zero value
ID 7 and 8 would return 6
4) Return the following non zero value for each zero value
ID 7 and 8 would return 6
5) Return the calcRow of the following non zero value
ID 7 and 8 would return 9 (not the misentered row value)

My query results would look something like (group, location, column not displayed to conserve room)

ID  intRow snglValue  CalcRow BeginVal BeginRow EndVal EndRo
1	1	0      1                         4.2   4        
2	2	0      2                         4.2   4 
3	3	0      3                         4.2   4
4	4	4.2    4
5	5	0      5        4.2     4        4.9   6      
6	6	4.9    6
7	7	0      7        4.9     6        6     9
8	8	0      8        4.9     6        6     9
9	10	6      9

in the above I showed the records where there was no beginValue (IDs 1,2, and 3). I also showed where the original snglValue was non zero. In the real query I do not need these records. So probably results more like:

ID  intRow snglValue  CalcRow BeginVal BeginRow EndVal EndRo
5	5	0      5        4.2     4        4.9   6      
7	7	0      7        4.9     6        6     9
8	8	0      8        4.9     6        6     9

These are the only records for interpolation. With the above information I can interpolate the snglValue and update the original table.

This may be easier to do in a sequence of queries. Any help would be appreciated.

Also the original thread has a link to the db with the tables and the code that does the interpolation.

 

[traingamer]

(what is the trick using tgml to keep the above formatted?)

Try a monospaced font [ tt ] [/ tt ]


Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[MajP]

Thanks. Also I figured out the SQL to do this.