match against next row

Database

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

Emil

Tags (1)
3 REPLIES

Re: match against next row

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

Re: match against next row

Hi,

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

Emil

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      ----

N/A

Re: match against next row

select 
id,
product_no,
product_desc,
product_code,
job_no,
cost,
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