on 02-09-2015 3:36 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
86 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.