Sales analysis by supplier

[Former Member]

Dear all,

I want to write a query to display sales analysis by supplier. What i wanted to achieve is the query should show me the suppliername, itemcode, itemname, quantity sold in Jan, Quantity sold in feb, quantity sold in march,.......Quantity sold in december, Stock on hand. Give me a sample of such a query for atleast first three months quantity sold.

Regds

Antony.

[former_member187989 · Accepted solution]

SELECT T0.[CardName] as 'Vendor Name', T1.[ItemCode], T1.[Dscription] as 'Item Description',T2.[OnHand],
SUM(T1.[Quantity]) AS 'Qty' FROM OPCH T0  INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode WHERE T0.[DocDate] BETWEEN '[%0]' AND '[%1]' GROUP BY T0.[CardName], T1.[ItemCode], T1.[Dscription],T2.[OnHand] ORDER BY T0.[CardName], T1.[ItemCode], T1.[Dscription],T2.[OnHand]

Jeyakanthan

[Former Member]

Hi Guys

Antony doesn't need OPCH as this is purchasing information. He is asking for SALES ANALYSIS BY SUPPLIER and multiple columns per month.

Kind regards

Peter Juby

[Former Member]

Hi Antony

To get columns you will need a format as follows:

SELECT T0.ItemCode, SUM(T0.QTY) AS 'JAN QTY', NULL AS 'FEB QTY', NULL AS 'MAR QTY' FROM OITM WHERE DATE BETWEEN....

UNION ALL

SELECT T0.ItemCode, NULL, SUM(T0.QTY), NULL FROM OITM WHERE DATE BETWEEEN ....

UNION ALL

SELECT T0.ItemCode, NULL, NULL, SUM(T0.QTY) FROM OITM WHERE DATE BETWEEN ....

The above doesn't always work so well so I prefer to do it in a Stored Procedure and use Temp Tables. Here is an example of one, and you can modify the select statements to work with the tables and fields you need. The whole purpose of multiple selects is to create the columns for each month, so each select statement moves on by one column. The select statements insert into the temp table and the final select statement then runs the actual report from this temp table. Change the word ALTER to CREATE for the first time you execute, thereafter use ALTER. To run the report in SAP Business One, create a query EXECUTE SUPPLIER_SALES_REPORT. You can pass your parameters from the query to the Stored Procedure, or just put the parameters into the Stored Procedure. I have my dates stored in a separate temp table based on GetDate so you will need to change this. Also change the database name SBODEMO to your database name.

USE [SBODEMO]

GO

/****** Object: StoredProcedure [dbo].[SUPPLIER_SALES_REPORT] Script Date: 09/16/2008 11:31:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[SUPPLIER_SALES_REPORT]

--@InvCode as nvarchar(max),

--@RunCode as nvarchar(max)

as

set nocount on

-- Insert statements for procedure here

DECLARE @TodayDate varchar(30);

IF OBJECT_ID(N'tempdb..#TEMPDATE', N'U') IS NOT NULL

DROP TABLE #TEMPDATE

SELECT MAX(JDT1.RefDate) AS [Today],JDT1.DueDate AS [DueDate] INTO #TEMPDATE FROM JDT1 JDT1 WHERE JDT1.RefDate > getDate() -2 GROUP BY JDT1.DueDate;

SELECT @TodayDate = #TEMPDATE.Today FROM #TEMPDATE;

--SELECT @TodayDate;

IF OBJECT_ID(N'tempdb..#CashRpt', N'U') IS NOT NULL

drop table #CashRpt

SELECT CAST('OPENING BALANCE' AS VARCHAR(100)) AS [Description], 1 AS [TransactionType], SUM(T0.[Debit] - T0.[Credit]) AS [OpeningBalance], CAST(NULL AS MONEY) AS [CashReceipts], CAST(NULL AS MONEY) AS [Deposited], CAST(NULL AS MONEY) AS [CashPayments], CAST(NULL AS MONEY) AS [OtherPayments], CAST(NULL AS MONEY) AS [ClosingBalance] INTO #CashRpt FROM JDT1 T0 WHERE T0.Account = '1300' and T0.RefDate < @TodayDate GROUP BY T0.Account

INSERT dbo.#CashRpt SELECT T0.[LineMemo], T0.[TransType], NULL AS [OpeningBalance], SUM(T0.[Debit] - T0.[Credit]) AS [CashReceipts], NULL AS [Deposited], NULL AS [CashPayments], NULL AS [OtherPayments], NULL AS [ClosingBalance] FROM JDT1 T0 WHERE T0.Account = '1300' and T0.RefDate = @TodayDate and T0.[TransType] = 24 GROUP BY T0.[LineMemo], T0.[TransType]

INSERT dbo.#CashRpt SELECT T0.[LineMemo], T0.[TransType], NULL AS [OpeningBalance], NULL AS [CashReceipts], SUM(T0.[Debit] - T0.[Credit]) AS [Deposited], NULL AS [CashPayments], NULL AS [OtherPayments], NULL AS [ClosingBalance] FROM JDT1 T0 WHERE T0.Account = '1300' and T0.RefDate = @TodayDate and T0.[TransType] = 25 GROUP BY T0.[LineMemo], T0.[TransType]

INSERT dbo.#CashRpt SELECT T0.[LineMemo], T0.[TransType], NULL AS [OpeningBalance], NULL AS [CashReceipts], NULL AS [Deposited], SUM(T0.[Debit] - T0.[Credit]) AS [CashPayments], NULL AS [OtherPayments], NULL AS [ClosingBalance] FROM JDT1 T0 WHERE T0.Account = '1300' and T0.RefDate = @TodayDate and T0.[TransType] = 46 GROUP BY T0.[LineMemo], T0.[TransType]

INSERT dbo.#CashRpt SELECT T0.[LineMemo], T0.[TransType], NULL AS [OpeningBalance], NULL AS [CashReceipts], NULL AS [Deposited], NULL AS [CashPayments], SUM(T0.[Debit] - T0.[Credit]) AS [OtherPayments], NULL AS [ClosingBalance] FROM JDT1 T0 WHERE T0.Account = '1300' and T0.RefDate = @TodayDate and T0.[TransType] = 30 GROUP BY T0.[LineMemo], T0.[TransType]

INSERT dbo.#CashRpt SELECT CAST('CLOSING BALANCE' AS VARCHAR(100)), 999, NULL AS [OpeningBalance], NULL AS [CashReceipts], NULL AS [Deposited], NULL AS [CashPayments], NULL AS [OtherPayments], SUM(T0.[Debit] - T0.[Credit]) AS [ClosingBalance] FROM JDT1 T0 WHERE T0.Account = '1300' GROUP BY T0.[Account]

SELECT T1.[Description], T1.[TransactionType], T1.[OpeningBalance], T1.[CashReceipts], T1.[Deposited], T1.[CashPayments], T1.[OtherPayments], T1.[ClosingBalance] FROM dbo.#CashRpt T1 ORDER BY T1.[TransactionType]

--END

Use the above as a basis to start from, and let me know if you get stuck.

Kind regards

Peter Juby

[Former Member]

Hi Antony

The best way to achieve this is to relate the OINV/INV1 tables to OITM and look at the default supplier field (CardCode). Of course, if an item is supplied by more than one supplier this will be a mess as you cannot say for certain which supplier's quantities were sold first, and to which customer and when. If you do want to complicate things a little you can also use the OINM table.

Try the following query, which I just quickly tested using OINM & OITM tables. Will need some more work.

SELECT T1.[CardCode], T0.[ItemCode], MAX(T0.[Dscription]),SUM(T0.[OutQty] - T0.[InQty]), SUM(T0.[Price] * (T0.[OutQty] - T0.[InQty])) FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode WHERE T0.[TransType] = 13 or T0.[TransType] = 14 GROUP BY T1.[CardCode], T0.[ItemCode]

Kind regards

Peter Juby

[Former Member]

peter,

thanx for your resonse, what i actually need is something like below.

Supplier name itemcode _Quantity sold jan_ Quantity sold Feb stock on hand

above are the columns i need, the difficult i am having is getting the quantity sold in jan, quantity sold feb columns.

Regds

Antony.

[Former Member]

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], 
T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], 
T1.[Price], T1.[Quantity] * T1.[Price] as 'Total' FROM OPCH T0  
INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry WHERE 
T0.[DocDate] between [%0] and [%1]