All Forums Database
emilahlstrom 11 posts Joined 11/13
09 Oct 2014
match against next row

Hi everyone,
I'm trying to create a derived table that has a dimension that calculates either next row's value or the previous row's value (next or previous doesn't matter).
And the dimension should do this when the next/previous row meets some requirments e.g. the product_name, product_code and the job_descirption is the same as the row I'm matching it with.
If requirements aren't met, the value should be "------"
Best regards

Raja_KT 1246 posts Joined 07/09
09 Oct 2014

Maybe you can show your sample data? what you have and what you expect.
Right now  can think of putting something like this, just the logic(min/max) or it can be rows between 1 following and  1 following:
select .....,your_field, max(your_field) over (partition  by  fields order by your_field rows between 1 preceding and 1 preceding) m
qualify m<your_field

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

emilahlstrom 11 posts Joined 11/13
09 Oct 2014

If the condition product_no, Product_desc and product_code is the same on the next row and the job_no differ then the diff-column will calculate the differens in cost between (in this case row 1 and row 2).
Is this possible to create in a derived table?
Best regards
id        product_no    product desc    product_code     job_no    cost     diff
1          123456         Terra              1234                 1            1000    100
2          123456         Terra              1234                 2            900      ----
3          234567         Optim             2359                 1            500      ----
4          124856         Zetab              5698                1             850      ----
5          564896         Zynga             4521                 1            522      ----

ulrich 816 posts Joined 09/09
09 Oct 2014

       case when job_no <> coalesce(max(job_no) over (partition by product_no, 
       product_desc order by job_no rows between 1 following and 1 following),0) then cost - max(cost) over (partition by product_no, 
       product_desc order by job_no rows between 1 following and 1 following)
from table

should work

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.