Purchase Price Variance Report

[Former Member]

I am trying to write a query to understand whether we have a purchase price variation from the latest invoice to the second latest invoice. I am having a problem gaining the information for the second purchase invoice as SAP Query generator seems to state that The sub query for the second to last invoice is bringing through more than 1 value. Could someone give me some help on how to do this better.

SELECT DISTINCT T0.[ItemCode], T0.[Dscription],

(Select  Top 1 T2.[Price] FROM PCH1 T2 WHERE T2.[ItemCOde]=T0.[ItemCode] ORDER BY T2.DocDate) AS " LAST PURCHASE PRICE",

(Select  Top 1 T2.[DocDate] FROM PCH1 T2 WHERE T2.[ItemCode]=T0.[ItemCode] ORDER BY T2.DocDate) AS " LAST DATE PURCHASE PRICE",

(select Top 1(Select Top 2 T3.[Price] FROM PCH1 T3 WHERE T3.[ItemCode]=T0.[ItemCode] ORDER BY T3.DocDate) ) AS " 2^nd LAST PURCHASE PRICE"

FROM PCH1 T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE T1.[PrcrmntMtd] ='b'

ORDER BY T0.[ItemCode]

[KennedyT21 · Accepted solution]

Hi Stephen Johnson...

Try this

/* Purchase price change */

declare @price dec(19,6)

declare @prprice dec(19,6)

declare @dat datetime

declare @prdat datetime

declare @item char(20)

declare @pritem char(20)

Create Table #w

(dat datetime,mind datetime,maxd datetime,

  item char(20),Name char(100),pr dec(19,6))

Insert into #W

SELECT  T0.[DocDate],T0.[DocDate],T0.[DocDate],

T1.[ItemCode],T1.Dscription,

T1.[Price]

FROM OPCH T0  INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry

Where T0.DocType='I' --and T1.[Itemcode]='[%0]'

ORDER BY T1.[ItemCode], T0.[DocDate],T1.[Price]

Declare cu cursor for

Select item,dat,pr from #W

  for update

Open cu

Fetch next from cu into @pritem,@prdat,@prprice

Fetch next from cu into @item,@dat,@price

While @@FETCH_STATUS = 0

Begin

If @pritem=@item and @prprice=@price

Update #W

    set mind=@Prdat

   where current of cu

else

begin      set @Pritem=@item

     set @prdat=@dat

     set @Prprice=@price

end

Fetch next from cu into @item,@dat,@price

End

deallocate cu

select item Item,min(Name),  pr Price,mind 'From',max(maxd) 'To'

from #w

group by item,mind,pr

drop table #w

Regards

Kennedy

[Former Member · Accepted solution]

Hi Kennedy

Thanks for your help on this,

I wanted it in column format Iike the below:

Item Code     Item Des     Last Pur Price     Last Pur Date     2nd Last Pur Price     2nd Last Pur Date

[kothandaraman_nagarajan]

Hi,

Try this :

SELECT DISTINCT T0.[ItemCode], T0.[Dscription],

(Select  Top 1 T2.[Price] FROM PCH1 T2 WHERE T2.[ItemCOde]=T0.[ItemCode] ORDER BY T2.DocDate) AS " LAST PURCHASE PRICE",

(Select  Top 1 T2.[DocDate] FROM PCH1 T2 WHERE T2.[ItemCode]=T0.[ItemCode] ORDER BY T2.DocDate) AS " LAST DATE PURCHASE PRICE",

(select Top 1 TA.[PRICE] FROM (Select Top 2 T3.[Price] FROM PCH1 T3 WHERE T3.[ItemCode]=T0.[ItemCode] ORDER BY T3.DocDate) TA ) AS " 2nd LAST PURCHASE PRICE"

FROM PCH1 T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE T1.[PrcrmntMtd] ='b'

ORDER BY T0.[ItemCode]

Thanks & Regards,

Nagarajan

[Former Member]

SELECT DISTINCT T0.[ItemCode], T0.[Dscription],

(Select  Top 1 T2.[Price] FROM PCH1 T2 WHERE T2.[ItemCOde]=T0.[ItemCode] ORDER BY T2.DocDate DESC) AS " LAST PURCHASE PRICE",

(Select  Top 1 T2.[DocDate] FROM PCH1 T2 WHERE T2.[ItemCode]=T0.[ItemCode] ORDER BY T2.DocDate DESC ) AS " LAST DATE PURCHASE PRICE",

(select Top 1 TA.[PRICE] FROM (Select Top 2 T3.[Price] FROM PCH1 T3 WHERE T3.[ItemCode]=T0.[ItemCode] ORDER BY T3.DocDate DESC) TA ) AS " 2nd LAST PURCHASE PRICE",

(select Top 1 TA.[DocDate] FROM (Select Top 2 T3.[DocDate] FROM PCH1 T3 WHERE T3.[ItemCode]=T0.[ItemCode] ORDER BY  T3.DocDate DESC) TA ) AS " 2nd LAST PURCHASE PRICE DATE"

FROM PCH1 T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE T1.[PrcrmntMtd] ='b'

ORDER BY T0.[ItemCode]

I have ammended the sql to desc the order by. and this is now giving me the same results in both columns. I need to reverse the order by in 2nd last columns but cant seem to get this to work in SQL. Another option would be to take the bottom value of the Top 2. But i do not know how to do this could someone help me out?Either option is fine.

[kothandaraman_nagarajan]

Hi,

If you main objective is to compare price, you can below query with more details per year per item.

SELECT t0.docdate,T0.[DocNum] as Doc#, T0.[NumAtCard] as VendorRef,T0.[CardName] as 'Vendor Name', T1.[ItemCode] as Part#, T1.[Dscription] as Descr, T1.[Quantity] as OrdQty,

case when month(T0.[DocDate]) = '1' then  T1.[Price]  end as  'Jan',
case when month(T0.[DocDate]) = '2' then T1.[Price]end as  'Feb',
case when month(T0.[DocDate]) = '3' then T1.[Price] end as  'Mar',
case when month(T0.[DocDate]) = '4' then T1.[Price] end as  'April',
case when month(T0.[DocDate]) = '5' then T1.[Price] end as  'May',
case when month(T0.[DocDate]) = '6' then T1.[Price] end as  'June',
case when month(T0.[DocDate]) = '7' then T1.[Price] end as  'July',
case when month(T0.[DocDate]) = '8' then T1.[Price] end as  'Aug',
case when month(T0.[DocDate]) = '9' then T1.[Price] end as  'Sept',
case when month(T0.[DocDate]) = '10' then T1.[Price] end as  'Oct',
case when month(T0.[DocDate]) = '11' then T1.[Price] end as  'Nov',
case when month(T0.[DocDate]) = '12' then T1.[Price] end as  'Dec'

FROM OPCH T0  INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry WHERE  t1.itemcode = [%1] and year(t0.docdate) = 2014  ORDER BY T0.DOCDATE

Thanks & Regards,

Nagarajan