Hi All,

I would like to share detailed sales analysis query which will give all Customer wise, Item wise and sales employee wise sales report in one shot.

In this query, From date and To Date are mandatory input parameter where as remaining are optional.

Hope this will helpful for you. Comments/evaluations/Suggestions are appreciated

Declare @FDate DateTime, @TDate DateTime, @CardCode Varchar (20), @ItmGrpN Varchar (100),  @ItemCode Varchar (100)

Select @FDate = min(Q.Refdate) from OJDT Q where Q.Refdate >='[%0]’

Select @TDate = max(R.Refdate) from OJDT R where R.Refdate <='[%1]’

Select @CardCode = Min(S.CardCode) from OCRD S where S.CardCode like N’%[%3]%’

Select @ItmGrpN = Min(T.ItmsGrpNam) from OITB T where T.ItmsGrpNam like N’%[%4]%’

Select @ItemCode = Min(U.ItemCode) from OITM U where U.ItemCode like N’%[%5]%’

–Select @FDate, @TDate, @ItmGrpN, @ItemName

Select ‘AR Invoice'[Type]

,case A.DocType When ‘I’ then ‘Item’ else ‘Service’ end[DocType]

,A.DocCur

,A.DocDate

, A.DocNum

,A.CardCode, C.CardName,D.GroupName[BP group]

,B.ItemCode

,Case A.DocType When ‘I’ then E.ItemName else B.Dscription end [ItemName/Description]

,B.Quantity[Quantity]

,B.Price

,B.Rate

, B.Currency[Price Currency]

,B.LineTotal- (B.LineTotal*isnull(A.DiscPrcnt,0)/100)[LineTotalLC],B.GrssProfit

,B.TotalFrgn- (B.TotalFrgn*isnull(A.DiscPrcnt,0)/100)[LineTotalFC]

,B.GTotalFC

,Case When B.SlpCode<>’-1′ then F.SlpName

  When B.SlpCode=’-1′ and A.SlpCode<>’-1′ then (Select M.SlpName from OSLP M where A.SlpCode = M.SlpCode) else ” end [Sales Person]

from OINV A

  Left Outer Join INV1 B on A.Docentry = B.DocEntry

  Left Outer Join OCRD C on A.CardCode = C.CardCode

  Left Outer Join OCRG D on C.GroupCode = D.GroupCode

  left outer join OITM E on B.ItemCode = E.ItemCode

  Left Outer Join OSLP F on B.SlpCode = F.SlpCode

  Left Outer Join OITB G on E.ItmsGrpCod = G.ItmsGrpCod

Where A.Canceled = ‘N’

  and A.DocDate >=@FDate and A.DocDate  <=@TDate

  and D.GroupName Like N’%[%2]%’

  and C.CardCode  Like ‘%[%3]%’

  and (G.ItmsGrpNam Like N’%[%4]%’ or G.ItmsGrpNam is null)

  and (B.ItemCode Like ‘%[%5]%’ or B.ItemCode is null)

Union All

Select ‘AR Credit Note'[Type]

,case A.DocType When ‘I’ then ‘Item’ else ‘Service’ end[DocType]

,A.DocCur

,A.DocDate

, A.DocNum

,A.CardCode, C.CardName,D.GroupName[BP group]

,B.ItemCode

,Case A.DocType When ‘I’ then E.ItemName else B.Dscription end [ItemName/Description]

, Case when B.NoInvtryMv =’Y’ then 0 else -B.Quantity end [Quantity]

,B.Price

,B.Rate

, B.Currency [Price Currency]

,-B.LineTotal+ (B.LineTotal*isnull(A.DiscPrcnt,0)/100)[LineTotal],-B.GrssProfit

,-B.TotalFrgn+ (B.TotalFrgn*isnull(A.DiscPrcnt,0)/100)[LineTotalFC]

,-B.GTotalFC

,Case When B.SlpCode<>’-1′ then F.SlpName

  When B.SlpCode=’-1′ and A.SlpCode<>’-1′ then (Select M.SlpName from OSLP M where A.SlpCode = M.SlpCode) else ” end [Sales Person]

from ORIN A

  Left Outer Join RIN1 B on A.Docentry = B.DocEntry

  Left Outer Join OCRD C on A.CardCode = C.CardCode

  Left Outer Join OCRG D on C.GroupCode = D.GroupCode

  left outer join OITM E on B.ItemCode = E.ItemCode

  Left Outer Join OSLP F on B.SlpCode = F.SlpCode

  Left Outer Join OITB G on E.ItmsGrpCod = G.ItmsGrpCod

Where A.Canceled = ‘N’

  and A.DocDate >=@FDate and A.DocDate  <=@TDate

  and D.GroupName Like N’%[%2]%’

  and C.CardCode  Like ‘%[%3]%’

  and (G.ItmsGrpNam Like N’%[%4]%’ or G.ItmsGrpNam is null)

  and (B.ItemCode Like ‘%[%5]%’ or B.ItemCode is null)

Order BY 3

Thanks

Unnikrishnan

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Silambu RS

    Good Unnikrishnan,

    You can include one more,     Rate * Price   for other Foreign Currencies.

    Thanks & Regards,

    Silambu

    (0) 
    1. Unnikrishnan Balan Post author

      Hi Silambu,

      Thanks for your suggestions.

      I made changes in query to include a)Document Currency, b)Price currency, c)Exchange Rate, d)Line Total (FC) & e)Gross Profit (FC)

      Regards

      Unnikrishnan

      (0) 

Leave a Reply