cancel
Showing results for 
Search instead for 
Did you mean: 

Purhase Analysis

former_member224367
Participant
0 Kudos

Dear All,

Anyone can help me to provide query how to generate Individual Monthly Vendor Purchase analyst. Standard SAP report already there but very tedious for user to create one by one for each 14 entities database. Please help

Output as below:

Entity, Vendor Name, Jan Amount, Feb Amount, Mac Amount, Apr Amount, May Amount, Jun Amount, Jul Amount, Aug Amount, untill Dec 2015


Thanks,

Saufil

Accepted Solutions (0)

Answers (1)

Answers (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this query:

SELECT [Code] as CustomerCode, [CustName] as CustName, isnull([1],0) as Jan, isnull([2],0) as Feb, [3] as Mar, isnull([4],0) as Apr,  isnull([5],0) as May, isnull([6],0) as june, isnull([7],0) as July, isnull([8],0) as Aug, isnull([9],0) as Sept, isnull([10],0) as Oct, isnull([11],0) as Nov, isnull([12],0) as Dec


from
(SELECT T0.[CardCode] as Code,T0.[CardName] as CustName, sum(T1.[lineTotal]) as Total, month(T0.[DocDate]) as month FROM OPCH T0 inner join PCH1 T1 on t0.docentry = t1.docentry WHERE year(T0.[DocDate]) = 2015 and T0.[CardCode] = 'SAKL01'  GROUP BY T0.[CardName],T0.[DocDate],T0.[CardCode]

union all

(SELECT T0.[CardCode] as Code,T0.[CardName] as CustName, -sum(T1.[lineTotal]) as Total, month(T0.[DocDate]) as month FROM ORPC T0 inner join RPC1 T1 on t0.docentry = t1.docentry WHERE year(T0.[DocDate]) = 2015 and T0.[CardCode] = 'SAKL01'  GROUP BY T0.[CardName],T0.[DocDate],T0.[CardCode] ))S


Pivot
(sum(S.total) For  Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Thanks & Regards,

Nagarajan