Analytics
Fan

## How to get sequence number generated based on change in sign values?

Hi,

Require your assistance in figuring out the logic as below

Input below is on same company code, plant, vendor & material and is ordered by PO Date such that PO created earlier should have least price and subsequent POs should have prices in ascending order.

 COMPANY_CODE PLANT_CODE VENDOR_CODE MATERIAL PO Line_Item PO_DATE Price PO_QTY QTY_RECEIVED DELIVERED_QTY RO02 4400 4600 340015549 4501888190 20 7/1/2015 5.05562 784.00 784 0.00 RO02 4400 4600 340015549 4501914559 30 7/15/2015 4.956 1,120.00 1,120.00 0.00 RO02 4400 4600 340015549 4501959036 40 8/12/2015 4.94592 1,456.00 784 672.00 RO02 4400 4600 340015549 4501959036 40 8/12/2015 4.94592 1,456.00 671 785.00 RO02 4400 4600 340015549 4501996435 40 9/8/2015 4.95712 560.00 560 0.00 RO02 4400 4600 340015549 4502005040 30 9/14/2015 4.94592 764.00 764 0.00 RO02 4400 4600 340015549 4502040967 40 10/13/2015 4.94592 336 336 0 RO02 4400 4600 340015549 4502055759 10 10/23/2015 4.96048 112 112 0 RO02 4400 4600 340015549 4502060832 30 10/27/2015 4.96608 1,195.00 1,195.00 0

Requirement ---> This data set should end as soon as we get an entry with price out of order and now this complete set will be numbered as Exception Group as given below

 COMPANY_CODE PLANT_CODE VENDOR_CODE MATERIAL PO Line_Item PO_DATE Price PO_QTY QTY_RECEIVED DELIVERED_QTY Exception Group RO02 4400 4600 340015549 4501959036 40 8/12/2015 4.94592 1,456.00 784 672.00 1 RO02 4400 4600 340015549 4501959036 40 8/12/2015 4.94592 1,456.00 671 785.00 1 RO02 4400 4600 340015549 4501996435 40 9/8/2015 4.95712 560.00 560 0.00 1 RO02 4400 4600 340015549 4502005040 30 9/14/2015 4.94592 764.00 764 0.00 2 RO02 4400 4600 340015549 4502040967 40 10/13/2015 4.94592 336 336 0 2 RO02 4400 4600 340015549 4502055759 10 10/23/2015 4.96048 112 112 0 2 RO02 4400 4600 340015549 4502060832 30 10/27/2015 4.96608 1,195.00 1,195.00 0 2

Please help out with SQL logic. I tried to write with below logic, but unable to move forward

sel
COMPANY_CODE
,PLANT_CODE
,VENDOR_CODE
,MATERIAL
,PURCHASE_DOCUMENT as PO
,PO_LINE_ITEM as Line_Item
,PO_DATE
,PER_UNIT_PRICE_IN_LC as Price
,PO_QTY
,DELIVERED_QTY
,MDIFF(Price, 1, PO_DATE) as Mdiff_Price
, case when Mdiff_Price < 0 then -1 when Mdiff_Price >= 0 then 1 else 0 end as sign
from Table
group by
COMPANY_CODE
,PLANT_CODE
,VENDOR_CODE
,MATERIAL
order by
COMPANY_CODE
,PLANT_CODE
,VENDOR_CODE
,MATERIAL
,PO_DATE