query of sales analysis

[Former Member]

Sales someone has a query analysis to modify and add some fields?

would like to have information in a query to get the customer's balance too.

[Former Member]

This is the best query I've come up with. Note what it does is pulls Sales by Local Currency.. because the "Display Amounts in System Currency" doesn't seem to do anything. 

This query is not 100% correct as I see a $12 varation in sales on 30 M and $6 on Profits but close enough for business needs.  I'll post another one if we can improve it.

@D1 as date, @D2 as Date

, @C1 as varchar(50)

, @BP1 as Varchar(50), @BP2 as Varchar(50)

, @I1 as varchar(50),@I2 as Varchar(50)

As

Begin 

-- Select SUM(Quantity) Qty, SUM(Sales) Sales, SUM(GrossProfit) GP FROM (

SELECT

S.ItemCode, OITM.ItemName, S.CardCode, CardName, Cast(Quantity as int) Quantity, Cast(Sales as money) as Sales_LocCur, Cast(GrossProfit as money) GrossProfit, Cast(CASE when Sales-GrossProfit=0 then 0 else (GrossProfit/(Sales-GrossProfit))*100 END as Money) as GrossProfitPrcnt

FROM (

  SELECT ItemCode, CardCode, CardName, SUM(Quantity) Quantity, Round(SUM(Sales),2) Sales, Round(SUM(GrossProfit),2) GrossProfit

  FROM (

  Select INV1.ItemCode, OINV.CardCode, OCRD.CardName, SUM(Quantity) Quantity

  , SUM(

  Case when DocCur = '$' then LineTotal- case when (DocTotal-OINV.VatSum-TotalExpns+DiscSum) = 0 then 0 else DiscSum*LineTotal/(DocTotal-OINV.VatSum-TotalExpns+DiscSum) END

  else TotalFrgn- case when (DocTotalFC-OINV.VatSumFC-TotalExpFC+DiscSumFC) = 0 then 0 else DiscSumFC*TotalFrgn/(DocTotalFC-OINV.VatSumFC-TotalExpFC+DiscSumFC) END 

  END

  ) as 'Sales'

  , SUM(Round(GrssProfit,2)) as GrossProfit

  -- select top 5 INV1.*

  FROM INV1 inner join OINV ON INV1.DocEntry = OINV.DocEntry

  Inner JOIN OCRD ON OINV.CardCode = OCRD.CardCode

  INNER JOIN OCRG ON OCRG.GroupCode = OCRD.GroupCode

  WHERE OINV.DocDate >= CASE when len(@D1)>1 then @D1 else '29990101' END

   and OINV.DocDate <= CASE when len(@D2)>1 then @D2 else '29990101' END

   and IsNull(ItemCode,'') >= CASE when len(@I1)>1 then @I1 else IsNull(ItemCode,'') END

   and IsNull(ItemCode,'') <= CASE when len(@I2)>1 then @I1 else IsNull(ItemCode,'') END

   and GroupName = CASE when len(@C1)>1 then @C1 else GroupName END

   and IsNull(OINV.CardCode,'') >= CASE when len(@BP1)>1 then @BP1 else IsNull(OINV.CardCode,'') END

   and IsNull(OINV.CardCode,'') >= CASE when len(@BP2)>1 then @BP2 else IsNull(OINV.CardCode,'') END

  Group by INV1.ItemCode, OINV.CardCode, OCRD.CardName

  UNION ALL

  Select RIN1.ItemCode,ORIN.CardCode, OCRD.CardName, -1*Sum(case when NoInvtryMv = 'Y' then 0 else Quantity END )

  , -1*SUM(

  CASE WHEN DocCur = '$' THEN LineTotal- case when (DocTotal-ORIN.VatSum-TotalExpns+DiscSum) = 0 then 0 else DiscSum*LineTotal/(DocTotal-ORIN.VatSum-TotalExpns+DiscSum) END

  ELSE TotalFrgn- case when (DocTotalFC-ORIN.VatSumFC-TotalExpFC+DiscSumFC) = 0 then 0 else DiscSumFC*TotalFrgn/(DocTotalFC-ORIN.VatSumFC-TotalExpFC+DiscSumFC) END

  END

  ) as 'Sales'

  , -1*Sum(ROUND(GrssProfit,2)) as GrossProfit

  FROM RIN1 inner join ORIN ON RIN1.DocEntry = ORIN.DocEntry

  Inner JOIN OCRD ON ORIN.CardCode = OCRD.CardCode

  INNER JOIN OCRG ON OCRG.GroupCode = OCRD.GroupCode

  WHERE ORIN.DocDate >= CASE when len(@D1)>1 then @D1 else ORIN.DocDate END

   and ORIN.DocDate <= CASE when len(@D1)>1 then @D2 else ORIN.DocDate END

   and IsNull(ItemCode,'') >= CASE when len(@I1)>1 then @I1 else IsNull(ItemCode,'') END

   and IsNull(ItemCode,'') <= CASE when len(@I2)>1 then @I1 else IsNull(ItemCode,'') END

   and GroupName = CASE when len(@C1)>1 then @C1 else GroupName END

   and IsNull(ORIN.CardCode,'') >= CASE when len(@BP1)>1 then @BP1 else IsNull(ORIN.CardCode,'') END

   and IsNull(ORIN.CardCode,'') >= CASE when len(@BP2)>1 then @BP2 else IsNull(ORIN.CardCode,'') END

  GROUP BY RIN1.ItemCode, ORIN.CardCode, OCRD.CardName

  ) AS ItmRoll

  WHERE ItemCode is not null

  GROUP BY ItemCode, CardCode, CardName

) AS S

INNER JOIN OITM

ON S.ItemCode = OITM.ItemCOde

[Former Member]

Hi,

Check this first:

http://scn.sap.com/thread/1703788

Thanks,

Gordon

[Former Member]

Mmm its not complete....

I mean.....must be   invoices - credit memo+debit memo+ downPayment+ or - manual journal entry

Its possible see gross profits in ojdt/jdt1 ?

[Former Member]

It is possible but need a long query to define.