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

Analytics

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_CODEPLANT_CODEVENDOR_CODEMATERIALPOLine_ItemPO_DATEPricePO_QTYQTY_RECEIVEDDELIVERED_QTY
RO02440046003400155494501888190207/1/20155.05562784.007840.00
RO02440046003400155494501914559307/15/20154.9561,120.001,120.000.00
RO02440046003400155494501959036408/12/20154.945921,456.00784672.00
RO02440046003400155494501959036408/12/20154.945921,456.00671785.00
RO02440046003400155494501996435409/8/20154.95712560.005600.00
RO02440046003400155494502005040309/14/20154.94592764.007640.00
RO024400460034001554945020409674010/13/20154.945923363360
RO024400460034001554945020557591010/23/20154.960481121120

RO02

4400460034001554945020608323010/27/20154.966081,195.001,195.000

  

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_CODEPLANT_CODEVENDOR_CODEMATERIALPOLine_ItemPO_DATEPricePO_QTYQTY_RECEIVEDDELIVERED_QTYException Group
RO02440046003400155494501959036408/12/20154.945921,456.00784672.001
RO02440046003400155494501959036408/12/20154.945921,456.00671785.001
RO02440046003400155494501996435409/8/20154.95712560.005600.001
RO02440046003400155494502005040309/14/20154.94592764.007640.002
RO024400460034001554945020409674010/13/20154.9459233633602
RO024400460034001554945020557591010/23/20154.9604811211202
RO024400460034001554945020608323010/27/20154.966081,195.001,195.0002

 

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

1 REPLY

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

@dnoeth Can you please help