Item Price Change Log Query Update Date Range

[kedalenechong]

Hi All

How to include a selection criteria for Update Date range in this Query from SAP Note 1165947?

/*This query uses Common Table Expression (CTE) to return only those rows

where there is a price change per item per price list.*/

With PriceTrack (ItemCode, Pricelist_No, Pricelist_Name, Currency, Price, UpdateDate, LogInstanc) as

(

/* Select each item's price, per log instance, in a specific price

list and return those rows where the price is different */

Select distinct T0.itemcode, T1.PriceList, T2.ListName, t1.currency, T1.Price, T0.UpdateDate, T1.loginstanc

FROM

AITM T0

Inner Join AIT1 T1 on T0.itemcode = T1.Itemcode

and t0.loginstanc = t1.loginstanc

left outer join AIT1 T3 on T1.itemcode = T3.itemcode

and t1.pricelist = T3.pricelist

and t1.loginstanc > t3.loginstanc

INNER JOIN OPLN T2 on T1.PriceList = T2.ListNum

where (t1.price <> t3.price )

/*select price changes where only the currency changes*/

union

Select distinct T0.itemcode, T1.PriceList , T2.ListName, t1.currency, T1.Price, T0.UpdateDate, T1.loginstanc

FROM

AITM T0

Inner Join AIT1 T1 on T0.itemcode = T1.Itemcode

and t0.loginstanc = t1.loginstanc

left outer join AIT1 T3 on T1.itemcode = T3.itemcode

and t1.pricelist = T3.pricelist

and t1.loginstanc > t3.loginstanc

INNER JOIN OPLN T2 on T1.PriceList = T2.ListNum

where (t1.price = t3.price and T1.currency <> T3.currency )

/*Select factors which have not been manually updated.*/

union

Select distinct T5.itemcode, T5.PriceList , T2.ListName, t5.currency, T5.Price, '', T5.loginstanc

FROM

AITM T0

Inner Join AIT1 T1 on T0.itemcode = T1.Itemcode

and t0.loginstanc = t1.loginstanc

left outer join AIT1 T3 on T1.itemcode = T3.itemcode

and t1.pricelist = T3.pricelist

and t1.loginstanc > t3.loginstanc

Right Outer join OPLN T4 on T1.PriceList <> T4.ListNum

Inner join ITM1 T5 on T4.listnum = T5.pricelist

INNER JOIN OPLN T2 on T1.PriceList = T2.ListNum

where

T4.Base_Num <> T4.listNum

and

T5.price <> 0

/*Exclude all those incidents where the price has not changed.

e.g. log isntance 5 shows a change to the price of $20 but there is no subsequent price

change until log instance 10. This section removes all the irrelevant rows from the result

set */

except

Select distinct T0.itemcode,T1.PriceList, T2.ListName, t1.currency, T1.Price, T0.UpdateDate, T1.loginstanc

FROM

AITM T0

Inner Join AIT1 T1 on T0.itemcode = T1.Itemcode

and t0.loginstanc = t1.loginstanc

left outer join AIT1 T3 on T1.itemcode = T3.itemcode

and t1.pricelist = T3.pricelist

and t1.loginstanc > t3.loginstanc

INNER JOIN OPLN T2 on T1.PriceList = T2.ListNum

where t1.price = t3.price and t1.currency =T3.currency

)

/*Select all data from the common table expression*/

select top 100000 * from pricetrack ORDER BY "ItemCode", "Pricelist_No", "LogInstanc"

Kedalene Chong

[kedalenechong]

Hi All

I managed to edit the Query myself.

/*This query uses Common Table Expression (CTE) to return only those rows

where there is a price change per item per price list.*/

With PriceTrack (ItemCode, Pricelist_No, Pricelist_Name, Currency, Price, UpdateDate, LogInstanc) as

(

/* Select each item's price, per log instance, in a specific price

list and return those rows where the price is different */

Select distinct T0.itemcode, T1.PriceList, T2.ListName, t1.currency, T1.Price, T0.UpdateDate, T1.loginstanc

FROM

AITM T0

Inner Join AIT1 T1 on T0.itemcode = T1.Itemcode

and t0.loginstanc = t1.loginstanc

left outer join AIT1 T3 on T1.itemcode = T3.itemcode

and t1.pricelist = T3.pricelist

and t1.loginstanc > t3.loginstanc

INNER JOIN OPLN T2 on T1.PriceList = T2.ListNum

where (t1.price <> t3.price ) and T0.UpdateDate between [%0] and [%1]

/*select price changes where only the currency changes*/

union

Select distinct T0.itemcode, T1.PriceList , T2.ListName, t1.currency, T1.Price, T0.UpdateDate, T1.loginstanc

FROM

AITM T0

Inner Join AIT1 T1 on T0.itemcode = T1.Itemcode

and t0.loginstanc = t1.loginstanc

left outer join AIT1 T3 on T1.itemcode = T3.itemcode

and t1.pricelist = T3.pricelist

and t1.loginstanc > t3.loginstanc

INNER JOIN OPLN T2 on T1.PriceList = T2.ListNum

where (t1.price = t3.price and T1.currency <> T3.currency ) and T0.UpdateDate between [%0] and [%1]

/*Select factors which have not been manually updated.*/

union

Select distinct T5.itemcode, T5.PriceList , T2.ListName, t5.currency, T5.Price, '', T5.loginstanc

FROM

AITM T0

Inner Join AIT1 T1 on T0.itemcode = T1.Itemcode

and t0.loginstanc = t1.loginstanc

left outer join AIT1 T3 on T1.itemcode = T3.itemcode

and t1.pricelist = T3.pricelist

and t1.loginstanc > t3.loginstanc

Right Outer join OPLN T4 on T1.PriceList <> T4.ListNum

Inner join ITM1 T5 on T4.listnum = T5.pricelist

INNER JOIN OPLN T2 on T1.PriceList = T2.ListNum

where

T4.Base_Num <> T4.listNum

and

T5.price <> 0

and T0.UpdateDate between [%0] and [%1]

/*Exclude all those incidents where the price has not changed.

e.g. log isntance 5 shows a change to the price of $20 but there is no subsequent price

change until log instance 10. This section removes all the irrelevant rows from the result

set */

except

Select distinct T0.itemcode,T1.PriceList, T2.ListName, t1.currency, T1.Price, T0.UpdateDate, T1.loginstanc

FROM

AITM T0

Inner Join AIT1 T1 on T0.itemcode = T1.Itemcode

and t0.loginstanc = t1.loginstanc

left outer join AIT1 T3 on T1.itemcode = T3.itemcode

and t1.pricelist = T3.pricelist

and t1.loginstanc > t3.loginstanc

INNER JOIN OPLN T2 on T1.PriceList = T2.ListNum

where t1.price = t3.price and t1.currency =T3.currency

and T0.UpdateDate between [%0] and [%1]

)

/*Select all data from the common table expression*/

select top 10* from pricetrack ORDER BY "ItemCode", "Pricelist_No", "LogInstanc"