Skip to Content
0

Sales Analysis Query Replication

Oct 06, 2017 at 08:06 AM

29

avatar image
Former Member

Hi, i am looking to create an sql query which in part replicates the Sales Analysis Report but separates the credits from invoices.

This report needs to display each month from 01/07/16 to current month (DocDate)

I would like the report to look something like:-

CardCode, CardName, ItemGroupNam, July 16 Invoice Qty, July 16 Invoice Vale, July 16 Credit Qty, July 16 Credit Value, Total Qty, Total Value, and so on

I need to take into consideration discounts applied to OINV and not on INV1

If there are no invoices or credits for a particular month / product then 0 is to be displayed.

Has anyone got an example for me to work on?

Kindest regards,

Alan

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

avatar image
Former Member Oct 06, 2017 at 10:59 AM
0

Hi, i have the following query so far as a starting point but having problems adding in T1.Quantity Data, is it possible to have multiple pivots?

Many thanks,

Alan

[Code]

SELECT [BP Code], [CustName], [Item Group Name], isnull([1],0) as [Jan Value], isnull([2],0) as [Feb Value], isnull([3],0) as [Mar Value], isnull([4],0) as [Apr Value], isnull([5],0) as [May Value], isnull([6],0) as [June Value], isnull([7],0) as [July Value], isnull([8],0) as [Aug Value], isnull([9],0) as [Sept Value], isnull([10],0) as [Oct Value], isnull([11],0) as [Nov Value], isnull([12],0) as [Dec Value] from (SELECT T0.[CardCode] AS [BP Code], T0.[CardName] as CustName, T3.[ItmsGrpNam] AS [Item Group Name], SUM(T1.LineTotal-(T1.LineTotal*isnull(T0.DiscPrcnt,0)/100)) as Total, month(T0.[DocDate]) as month FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN OITB T3 ON T2.[ItmsGrpCod] = T3.[ItmsGrpCod] WHERE year(T0.[DocDate]) = 2017 GROUP BY T0.[CardCode], T0.[CardName],T3.[ItmsGrpNam], T0.[DocDate] union all (SELECT T0.[CardCode] AS [BP Code], T0.[CardName] as CustName, T3.[ItmsGrpNam] AS [Item Group Name], -sum(T1.[LineTotal]) as Total, month(T0.[DocDate]) as month FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN OITB T3 ON T2.[ItmsGrpCod] = T3.[ItmsGrpCod] WHERE year(T0.[DocDate]) = 2017 GROUP BY T0.[CardCode],T0.[CardName],T3.[ItmsGrpNam], T0.[DocDate])) S Pivot (sum(S.total) For Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P order by [BP Code] asc

[/Code]

Share
10 |10000 characters needed characters left characters exceeded