Query monthly sales displayed by month

[vhzl123]

Hello,

i am looking for a Query that will show all of our items and the amount sold in orders displaying the result by month.

I would also like to know the stock at the time of using the query and the minimum stock if there is.

So, header would be...

Item #...Jan Qty...Feb Qty...Mar Qty...etc... In Stock... Minimum stock

Does anyone know a Query that can do this?

Thanks!

Virginia

[AlexGourdet]

Thank you for visiting SAP Community to get answers to your questions.

As you're looking to get most out of your community membership, please consider include a profile picture to increase user engagement & additional resources to your reference that can really benefit you:

• Profile https://developers.sap.com/tutorials/community-profile.html
• Tips for Questions: https://community.sap.com/resources/questions-and-answers
• Consider to take our Q&A tutorial at: https://developers.sap.com/tutorials/community-qa.html.

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

All the best,

Alex

[LoHa]

Hi Virginia,

you could do it like this

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)
/* select * from OQUT x */
DECLARE @FROMDATE AS DATETIME
DECLARE @TODATE AS DATETIME
--SET @FROMDATE = /* x.DocDate */ [%0]
--SET @TODATE = /* x.DocDate */ [%1]

SET @FROMDATE = '20210101'
SET @TODATE = '20210825'

IF OBJECT_ID ('tempdb..#TempData','U') IS NOT NULL
DROP TABLE dbo.#TempData

CREATE TABLE #TempData
(
CardCode  nvarchar(max) NOT NULL,
CardName  nvarchar(max),
Notes nvarchar(max),
BAL numeric(19,7),
DateMonth nvarchar(max)
)


;
INSERT INTO #TempData (CardCode,CardName,Notes,BAL,DateMonth)
SELECT 
T0.CARDCODE
, T0.CARDNAME
, T0.[Notes]
, ((((T1.[DocTotal]+T1.[DiscSum])-T1.[VatSum]-T1.[TotalExpns]-T1.[DiscSum])+T1.[DiscSum]) - T1.[DiscSum]) AS [BAL]
,CAST(year(T1.Docdate) AS char(4)) + '-' + CAST(Format(Month(T1.DocDate),'00') AS nvarchar(max)) as [DateMonth] 
FROM 
dbo.OCRD T0
LEFT JOIN dbo.OINV T1 ON T1.CardCode = T0.CardCode  
Where  
T0.[CardType] ='C' and T1.[CANCELED] = 'N' AND T1.[DocDate] BETWEEN @FROMDATE AND @TODATE

UNION ALL

SELECT 
T0.CARDCODE
, T0.CARDNAME
, T0.[Notes]
, ((((T1.[DocTotal]+T1.[DiscSum])-T1.[VatSum]-T1.[TotalExpns]-T1.[DiscSum])+T1.[DiscSum]) - T1.[DiscSum])*-1 AS [BAL]
,CAST(year(T1.Docdate) AS char(4)) + '-' + CAST(Format(Month(T1.DocDate),'00') AS nvarchar(max)) as [DateMonth] 
FROM dbo.OCRD T0
LEFT JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode] 
LEFT JOIN INV1 T2 ON T1.[DocEntry] = T2.[DocEntry]   
Where  T0.[CardType] ='C' and T1.[CANCELED] = 'N' and T2.[AcctCode] <> '31100' AND T1.[DocDate] BETWEEN @FROMDATE AND @TODATE
group by
T0.CARDCODE, T0.CARDNAME, T0.[Notes], T1.Docdate, T1.[DocTotal],T1.[VatSum], T1.[DiscSum], T1.[TotalExpns], T1.[DiscSum], T2.[AcctCode]
having ((((T1.[DocTotal]+T1.[DiscSum])-T1.[VatSum]-T1.[TotalExpns]-T1.[DiscSum])+T1.[DiscSum]) - T1.[DiscSum]) <> '0'

select @cols = STUFF((SELECT ',' + QUOTENAME(DateMonth) 
                    from dbo.#TempData
                    group by DateMonth
                    order by DateMonth ASC
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT CardCode,CardName AS CustName,Notes, ' + @cols + 'from 
             (
                select 
				CardCode,CardName,Notes,BAL,DateMonth
                from dbo.#TempData
            ) x
            pivot 
            (
                sum(BAL)
                for DateMonth in (' + @cols + ')
            ) p '

execute(@query)

regards

Lothar

[SonTran]

Hi,

You can check this Query monthly sales by BP and item code | SAP Community

Hope this helps,

Son Tran