Query Sold items per Business partner per week per item group

[former_member798634]

Hi All,

I need some help to build a query. I would like to see the following in a sheet:

- itemcode

- itemname

- item group

- business partner

- sold per week

So i can send our selling persons to the clients to ask why didnt bought product X in the last few weeks.

Please let me know if you can help me.

Regards Mark

[Johan_Hakkesteegt]

Hi Mark,

Can you please elaborate a little more on what result you are expecting to see?

"...why didnt bought product X ..."

If a BP did not buy an item, it will not show up in the list, or we show all items, and it assumes that every BP buys every item. That last one would mean that you get a list with BPs x items number of records. Say you have 50 BPs and 100 items, you get a report with 5000 rows.

"...in the last few weeks."

What should "sold per week" show?

Regards,

Johan

[former_member798634]

Hi Johan,

About the BP, a pop up menu where you can fill in the cardcode would be great. So i can make a report per BP

About the items, true don't need to see all the items, but if i can have an history about the items a BP started buying lets say 52 weeks ago and 3 weeks after that they didnt, and then bought it for a few weeks again and again there is a gap of a few weeks, would be great. So i can send our sales to the client to be more specific on these items.

Now i can only see it per month in the sales analisys, but would like to see it per week, so i can be a little more agrassive on the sales.

Hopefully you understand what i mean.

Regards

Mark

[LoHa · Accepted solution]

Hi Mark,

you can try this as well. The query given by Johan shows the values vertical, mine shows them horizontal. It depends on what you prefer.

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

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)

/**SELECT FROM [OFPR] T0 **/
DECLARE @DocDateFrom as datetime
/* WHERE */
Set @DocDateFrom = /* T0.F_RefDate */ '[%0]'

/**SELECT FROM [OFPR] T1 **/
DECLARE @DocDateTo as datetime
/* WHERE */
Set @DocDateTo = /* T1.T_RefDate */ '[%1]'

/**SELECT FROM [OINV] T2 **/
DECLARE @CardCode as nvarchar(max)
/* WHERE */
Set @CardCode = /* T2.CardCode */ '[%2]'

CREATE TABLE [#TempData]
(
	[CardCode]  nvarchar(max) NOT NULL,
	[CardName]  nvarchar(max),
	[WeekYearDate] nvarchar(max),
	[ItemCode] nvarchar(max),
	[ItemName] nvarchar(max),
	[Qty] numeric(19,7),
)
;
INSERT INTO [#TempData] ([CardCode],[CardName],[WeekYearDate],[ItemCode],[ItemName],[Qty])
SELECT
	 [OINV].[CardCode]
	,[OINV].[CardName]
	,'W: ' + CAST(DATEPART(ISO_WEEK,[OINV].[DocDate]) AS nvarchar(max)) + ' - ' + CAST(DATEPART(Year,[OINV].[DocDate]) AS char(4))  + ' (' + CAST(DATEADD(Day,DATEPART(WeekDay,DATEADD(WEEK, DATEPART(ISO_WEEK, [OINV].[DocDate]),CAST(CAST(DATEPART(Year, [OINV].[DocDate]) AS CHAR(4))+'0101' AS Date)))*-1+2,DATEADD(WEEK, DATEPART(ISO_WEEK, [OINV].[DocDate]),CAST(CAST(DATEPART(Year, [OINV].[DocDate]) AS CHAR(4))+'0101' AS Date))) AS nvarchar(max)) + ' - ' + CAST(DATEADD(Day,6,DATEADD(Day,DATEPART(WeekDay,DATEADD(WEEK, DATEPART(ISO_WEEK, [OINV].[DocDate]),CAST(CAST(DATEPART(Year, [OINV].[DocDate]) AS CHAR(4))+'0101' AS Date)))*-1+2,DATEADD(WEEK, DATEPART(ISO_WEEK, [OINV].[DocDate]),CAST(CAST(DATEPART(Year, [OINV].[DocDate]) AS CHAR(4))+'0101' AS Date)))) AS nvarchar(max))+')' AS [WeekYearDate]
	,[INV1].[ItemCode]
	,[INV1].Dscription
	,[INV1].[Quantity]
FROM
/*Invoices*/
	[OINV]
		INNER JOIN [INV1] ON [INV1].[DocEntry] = [OINV].[DocEntry]
WHERE
	/*No Cancelled Invoices*/
	[OINV].[CANCELED] = 'N'
	AND
	[OINV].[CardCode] = @CardCode
	AND
	[OINV].[DocDate] >= @DocDateFrom 
	AND 
	[OINV].[DocDate] <= @DocDateTo



SELECT @cols = 
				STUFF
					(
						(SELECT 
							',' + QUOTENAME(WeekYearDate) 
						 FROM #TempData
						 GROUP BY [WeekYearDate]
						 ORDER BY [WeekYearDate] ASC
						 FOR XML PATH(''), TYPE
						).value('.', 'NVARCHAR(MAX)'),1,1,''
					)

set @query = 'SELECT [CardCode],[CardName],[ItemCode],[ItemName], ' + @cols + 'from 
             (
                SELECT 
				[CardCode],[CardName],[ItemCode],[ItemName],[WeekYearDate],[Qty]
                FROM #TempData
            ) x
            pivot 
            (
                sum(Qty)
                for [WeekYearDate]  in (' + @cols + ')
            ) p '

execute(@query)

regards Lothar

[LoHa]

Hi Mark,

I don't know if I Understand you right. I sorted the ItemCodes ascending and the weeks correct if there is a switch of years. I can't really understand why yo got the item I00530 twice in your list.

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


DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)

/**SELECT FROM [OFPR] T0 **/
DECLARE @DocDateFrom as datetime
/* WHERE */
Set @DocDateFrom = /* T0.F_RefDate */ '[%0]'

/**SELECT FROM [OFPR] T1 **/
DECLARE @DocDateTo as datetime
/* WHERE */
Set @DocDateTo = /* T1.T_RefDate */ '[%1]'

/**SELECT FROM [OINV] T2 **/
DECLARE @CardCode as nvarchar(max)
/* WHERE */
Set @CardCode = /* T2.CardCode */ '[%2]'

CREATE TABLE [#TempData]
(
	[CardCode]  nvarchar(max) NOT NULL,
	[CardName]  nvarchar(max),
	[WeekYearDate] nvarchar(max),
	[ItemCode] nvarchar(max),
	[ItemName] nvarchar(max),
	[Qty] numeric(19,7),
	[DocDate] DateTime,
	
)
;
INSERT INTO [#TempData] ([CardCode],[CardName],[WeekYearDate],[ItemCode],[ItemName],[Qty],[DocDate])

SELECT
	 [OINV].[CardCode]
	,[OINV].[CardName]
	,CAST(DATEPART(Year,[OINV].[DocDate]) AS char(4)) + CAST(FORMAT(DATEPART(ISO_WEEK,[OINV].[DocDate]),'00') AS nvarchar(max)) + 'W: ' + CAST(FORMAT(DATEPART(ISO_WEEK,[OINV].[DocDate]),'00') AS nvarchar(max)) + ' - ' + CAST(DATEPART(Year,[OINV].[DocDate]) AS char(4))  + ' (' + CAST(DATEADD(Day,DATEPART(WeekDay,DATEADD(WEEK, DATEPART(ISO_WEEK, [OINV].[DocDate]),CAST(CAST(DATEPART(Year, [OINV].[DocDate]) AS CHAR(4))+'0101' AS Date)))*-1+2,DATEADD(WEEK, DATEPART(ISO_WEEK, [OINV].[DocDate]),CAST(CAST(DATEPART(Year, [OINV].[DocDate]) AS CHAR(4))+'0101' AS Date))) AS nvarchar(max)) + ' - ' + CAST(DATEADD(Day,6,DATEADD(Day,DATEPART(WeekDay,DATEADD(WEEK, DATEPART(ISO_WEEK, [OINV].[DocDate]),CAST(CAST(DATEPART(Year, [OINV].[DocDate]) AS CHAR(4))+'0101' AS Date)))*-1+2,DATEADD(WEEK, DATEPART(ISO_WEEK, [OINV].[DocDate]),CAST(CAST(DATEPART(Year, [OINV].[DocDate]) AS CHAR(4))+'0101' AS Date)))) AS nvarchar(max))+')' AS [WeekYearDate]
	,[INV1].[ItemCode]
	,[INV1].Dscription
	,[INV1].[Quantity]
	,[OINV].[DocDate]
	
FROM
/*Invoices*/
	[OINV]
		INNER JOIN [INV1] ON [INV1].[DocEntry] = [OINV].[DocEntry]
WHERE
	/*No Cancelled Invoices*/
	[OINV].[CANCELED] = 'N'
	AND
	[OINV].[CardCode] = @CardCode
	AND
	[OINV].[DocDate] >= @DocDateFrom 
	AND 
	[OINV].[DocDate] <= @DocDateTo
ORDER BY DocDate Asc

SELECT @cols = 
				STUFF
					(
						(SELECT 
							',' + QUOTENAME(SUBSTRING(WeekYearDate,CHARINDEX('W',WeekYearDate),Len(WeekYearDate)-CHARINDEX('W',WeekYearDate)+1)) 
						 FROM 
						 #TempData
						 GROUP BY [WeekYearDate]
						 ORDER BY [WeekYearDate] ASC
						 FOR XML PATH(''), TYPE
						).value('.', 'NVARCHAR(MAX)'),1,1,''
					)

set @query = 'SELECT [CardCode],[CardName],[ItemCode],[ItemName], ' + @cols + 'from 
             (
                SELECT 
				[CardCode],[CardName],[ItemCode],[ItemName],[WeekYearDate],[Qty]
                FROM #TempData
            ) x
            pivot 
            (
                sum(Qty)
                for [WeekYearDate]  in (' + @cols + ')
            ) p  ORDER BY ItemCode ASC'

execute(@query)

regards Lothar

[former_member798634]

Hi Lothar,

Very strange indeed, because i retried it again and now its week after week nicely put behind each item number.

Thanks guys

[Johan_Hakkesteegt]

Hi Mark,

Please give this a try and see if it works for you. The parameter needs only a partial name, so you do not have to remember the exact Business Partner number, or the whole name:

/* select * from OCRD x */
DECLARE @BP_NAME AS NVARCHAR(100)
SET @BP_NAME = /* x.CardName */ '[%0]'

SELECT sales.CardCode
      ,sales.CardName
      ,i.ItemCode
      ,i.ItemName
      ,DATEPART(year, sales.DocDate) AS [Jaar]
      ,DATEPART(wk, sales.DocDate) AS [Week nr.]
      ,CAST(SUM(sales.Quantity) AS INT) AS [Totaal aantal]
FROM OITM i
INNER JOIN (select h.CardCode
                  ,h.CardName
                  ,h.DocDate
                  ,r.ItemCode
		  ,r.Quantity
                 from INV1 r
                      inner join OINV h on r.DocEntry = h.DocEntry
                 where h.CardName like '%' + @BP_NAME + '%'
                   and h.DocDate >= DATEADD(YEAR, -1, GETDATE())) sales ON i.ItemCode = sales.ItemCode
GROUP BY sales.CardCode
        ,sales.CardName
        ,i.ItemCode
        ,i.ItemName
        ,DATEPART(year, sales.DocDate)
        ,DATEPART(wk, sales.DocDate)
ORDER BY 4, 5, 6

Regards,

Johan

[former_member798634]

Hi Guys,

Both are awesome!! loh is it also possible to see per item every week behind each other? Now you see per week, and the items are under each other.

If thats possible that would be great.

Regards