hello,
can someone tell me how to make the following table
I want supplier name, supplier number, manufacturer, spend (net invoice value) and date.
Can someone put a query how to make it
Hi,
Try this query,
SELECT T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],T3.[FirmName], T0.[DocTotal] FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode] WHERE T0.[DocDate] between [%0] and [%1]
Regards,
Nagarajan
Hi Sir,
To get spend (net invoice) value can we subtract values between OPCH table & ORPC table.
Also, Can you suggest what joint to put on OMRC table to get values for manufacturer
Thankful for your replies.
Try this,
SELECT T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],T3.[FirmName], T0.[DocTotal] FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode] WHERE T0.[DocDate] between [%0] and [%1]
UNION ALL
SELECT T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],T3.[FirmName], -T0.[DocTotal] FROM ORPC T0 INNER JOIN RPC1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode] WHERE T0.[DocDate] between [%0] and [%1]
Hi
When entered dates and a supplier number and which has the following columns:
Supplier number
thanks but both query not woring
Could you share your query and its result? What you mean supplier number?
Thanks for reply
T0.[CardCode] 'supplier number'....
Hi
Can you create a new report which allows you to enter dates and a supplier number and which has the following columns:
Supplier number
Thanks
Hi Sir,
The below query is working fine in SQL but some of the values are coming in negative. Can you suggest why? Also can you suggest how to make this query work in SAPB1
SELECT T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],T3.[FirmName], T0.[DocTotal] FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] LEFT JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode] WHERE T0.[DocDate] between '2006-01-01' and '2017-01-01' and T0.[CardCode]= 'V1010' UNION ALL SELECT T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],T3.[FirmName], -T0.[DocTotal] FROM ORPC T0 INNER JOIN RPC1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] LEFT JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode] WHERE T0.[DocDate] between '2006-01-01' and '2017-01-01' and T0.[CardCode]= 'V1010'
Any error message while running above query in Query generator? Could you share your query result here?