SALES ANALYISI SUMMARY FOR YEARLY MONTHLY AND DAILY BASIS

[Former Member]

Hi,

we are using SAP B1 ,MS SQL 2012

Someone help me out to generate sales analysis report Daily,monthly,yearly basis in the following format. from A/R invoice

Thanks in advance.

Document Type invoice/ Credit Memo

Document Number Posting Date Customer/Vendor Code Customer/Vendor Name GST Number Sales Person BP Group name
BP Reference No. Payment Terms Code CGST SGST IGST LineTotal Freight Total Remarks Invoice
1716273 02/07/2018 C06508 CVR Medical Sytems 37BCWPC4678P1ZI Nagaraji.N AP & TELUNGANA Whatsapp 5 2,124.00 17,400.00 200.00 19,724.00 xxx

[fewrose]

Month Wise Sale---------->>>>>>

SELECT T0.ITEMCODE AS "AZ Code",T0.[CodeBars],T2.ItmsGrpNam AS "Family",T0.ItemName,T0.OnHand AS "AZ In Stock", (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'YTD ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'JAN ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'FEB ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'MAR ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'APR ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'MAY ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'JUN ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'JUL ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'AUG ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'SEP ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'OCT ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'NOV ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'DEC ' FROM dbo.OITM T0 LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode INNER JOIN OITB T2 ON T0.[ItmsGrpCod] = T2.[ItmsGrpCod] GROUP BY T0.ItemCode,T2.ItmsGrpNam,T0.[CodeBars], T0.Itemname,T0.OnHand,YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())-0 ORDER BY T0. ITEMCODE

[fewrose]

Sales Query with Date Range-------->>>>>>

Select T0.docentry AS "S.N",T0.DocNum [A/R No.],T0.[Comments], T0.[DocStatus] AS "Status",T1.BaseDocNum as 'S/O No', CONVERT(VARCHAR(2),MONTH(T0.DocDate)) as Month, DatePart(week, T0.DocDate) [Week], T0.DocDate AS "Date", T0.[DocTime] AS "Time", T11.[USER_CODE] AS 'User ID', T11.[U_NAME], T9.[SlpCode] AS "E. ID", T9.SlpName, T6.GroupName AS "Trade Area",T22.WhsName [W/H Name], T0.cardcode AS "BP Code",T0.CardName [BP Name],T1.Itemcode AS "Item Code", T8.ItmsGrpNam AS "Family", T1.Dscription [Item/Service Description], T0.GrosProfit AS "GP A/R", (T1.Price-T1.StockPrice) [GP Item], T1.StockPrice, T1.Quantity [Sales Qty], T1.Price [Sale Price], T1.LINETOTAL [Sales Value], T1.GTotal AS "Gross Sales ( +IVA )", T1.LineTotal AS 'Net Sales ( -IVA )', T1.LineVat AS "IVA Value", ( ( T1.LineVat/T1.LineTotal*100)) As "% IVA", T1.GPTtlBasPr AS "Stocks Value", T1.GrssProfit AS "Gross Profit", ( ( T1.GrssProfit/T1.LineTotal*100)) As "% Profit", T3.Quantity [Return Quantity], T3.Price [Return Price],(T1.Quantity-T3.Quantity) [Total Quantity After Return], (T3.Price*T3.Quantity) [Return Value], ((T1.Quantity*T1.Price)-(T3.Quantity*T1.StockPrice)) [Row Gross Profit], T3.GTotal AS "Gross Sale Return", T3.LineTotal AS "Net Sale Return", T3.VatSum AS "IVA Return", T9.Commission AS "% Of Commission", ( (T0.DocTotal - T0.VatSum)*( T9.Commission/100)) As "Team Commissions", T10.[TrnspCode] AS "D. ID", T10.[TrnspName] AS "Driver Name", T0.[Volume],T0.[Weight], (SELECT CASE ISNULL(CONVERT(NVARCHAR(MAX),T2.Cellular),'') WHEN '' THEN '' ELSE ISNULL(CONVERT(NVARCHAR(MAX),T2.Cellular)+' ','') END +CASE ISNULL(CONVERT(NVARCHAR(MAX),T2.Phone1),'') WHEN '' THEN '' ELSE ISNULL(CONVERT(NVARCHAR(MAX),T2.Phone1)+' ','') END +CASE ISNULL(CONVERT(NVARCHAR(MAX),T2.Phone2),'') WHEN '' THEN '' ELSE ISNULL(CONVERT(NVARCHAR(MAX),T2.phone2)+' ','')END) [Customer Phone], T5.NAME,T5.Address,T0.Address,T0.Address2 From OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T2.CardCode = T0.CARDCODE LEFT OUTER JOIN OWHS T22 ON T22.WhsCode = T1.WhsCode LEFT OUTER JOIN RIN1 T3 ON T1.DocEntry = T3.BaseEntry AND T1.ObjType = T3.BaseType AND T1.LineNum = T3.BaseLine LEFT OUTER JOIN ORIN T4 ON T3.DocEntry = T4.DocEntry LEFT JOIN OCPR T5 ON T0.CntctCode = T5.CntctCode AND T0.CARDCODE = T5.CARDCODE INNER JOIN OCRG T6 ON T2.GROUPCODE = T6.GROUPCODE INNER JOIN OITM T7 ON T1.ITEMCODE = T7.ITEMCODE INNER JOIN OITB T8 ON T7.ItmsGrpCod = T8.ItmsGrpCod INNER JOIN OSLP T9 ON T0.SlpCode = T9.SlpCode LEFT OUTER JOIN OSHP T10 ON T0.TrnspCode = T10.TrnspCode INNER JOIN OUSR T11 ON T0.UserSign = T11.USERID where T0.DocDate BETWEEN [%1] and [%2]