cancel
Showing results for 
Search instead for 
Did you mean: 

I need a query to turnover of the suppliers

arahmouni014
Explorer
0 Kudos

HI All,

Please I want a query that displays the turnover of the suppliers with two columns, one for the global purchases total and one for the sales total. So that I can have a visibility on the profit made by supplier,

Thanks

Accepted Solutions (0)

Answers (1)

Answers (1)

Johan_H
Active Contributor
0 Kudos

Hi,

Please give this a try:

SELECT c.CardCode
,c.CardName
,(select sum(r.LineTotal - (r.LineTotal * (isnull(h.DiscPrcnt,0)/100)))
  from INV1 r 
       inner join OINV h on r.DocEntry = h.DocEntry
       inner join OITM i on r.ItemCode = i.ItemCode
  where i.CardCode = c.CardCode
    and h.DocDate between [%0] and [%1]) - 
 (select sum(r.LineTotal - (r.LineTotal * (isnull(h.DiscPrcnt,0)/100)))
  from RIN1 r 
       inner join ORIN h on r.DocEntry = h.DocEntry
       inner join OITM i on r.ItemCode = i.ItemCode
  where i.CardCode = c.CardCode
    and h.DocDate between [%0] and [%1]) AS [Sales]
,(select sum(r.LineTotal - (r.LineTotal * (isnull(h.DiscPrcnt,0)/100)))
  from PCH1 r 
       inner join OPCH h on r.DocEntry = h.DocEntry
       inner join OITM i on r.ItemCode = i.ItemCode
  where i.CardCode = c.CardCode
    and h.DocDate between [%0] and [%1]) - 
 (select sum(r.LineTotal - (r.LineTotal * (isnull(h.DiscPrcnt,0)/100)))
  from RPC1 r 
       inner join ORPC h on r.DocEntry = h.DocEntry
       inner join OITM i on r.ItemCode = i.ItemCode
  where i.CardCode = c.CardCode
    and h.DocDate between [%0] and [%1]) AS [Purchase]
FROM OCRD c
WHERE c.CardType = 'S'

Regards,

Johan

arahmouni014
Explorer
0 Kudos

Hello Johan,

Thanks for your feedback, this request works only in some suppliers, I want it to apply to all suppliers, can you help me ?

thanks

Johan_H
Active Contributor
0 Kudos

Hi,

It returns sales and purchase for all suppliers during the given period. If there are no results for a certain supplier, you need to widen the time frame. If you want, you can enter the start date of your database, and today.

Regards,

Johan