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 "------"
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
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?
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 ----
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)