on 10-06-2017 9:06 AM
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
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]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
10 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.