SQL Query for Customer Order

[geraldhans]

Hi All

I need a help in creating a query for obtaining List of Customers, their POs, when they place their first order, when they placed the latest order, alongside with the item name, and quantity. I managed to write this query, however I not be able to filter the date

Here are the query I wrote

SELECT T0.CardCode AS 'Customer Code', T0.CardName AS
'Customer Name', T3.SlpName AS 'Sales Person', T0.CreateDate AS 'BP Creation
Date', MIN(T1.DocDate) AS 'Customer First Order', MAX(T1.DocDate) AS 'Customer Latest Order Date', T2.ItemCode
AS 'Item Code', T2.Dscription AS 'Item Description', T2.Quantity AS 'Purchased
Quantity', T2.DocDate AS 'Invoice Date', T2.DocNum AS 'Invoice Number', CASE WHEN MAX(T1.Docdate) < Year(T2.DocDate) THEN 'New Customer' ELSE 'Existing Customer' END AS 'Customer Status'
FROM OCRD T0
INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode
INNER JOIN (SELECT GHa.ItemCode, Gha.Dscription, GHb.DocNum,
GHb.DocDate, GHa.Quantity, Ghb.CardCode 
FROM OINV GHb 
INNER JOIN INV1 GHa ON GHb.DocEntry = GHa.DocEntry 
LEFT JOIN RIN1 GHc ON Ghc.BaseEntry = GHb.DocEntry AND
GHc.BaseLine = GHa.LineNum
LEFT JOIN ORIN GHd ON GHc.DocEntry = GHd.DocEntry
GROUP BY GHb.CardCode, GHa.ItemCode, GHa.Dscription,
GHb.DocNum, GHb.DocDate, GHa.Quantity) T2 ON T2.CardCode = T0.CardCode
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode
INNER JOIN OITM T4 ON T2.ItemCode = T4.ItemCode 
WHERE T0.CardType = 'C' AND T4.InvntItem = 'Y'
GROUP BY T0.CardCode, T0.CardName,T3.SlpName,
T0.[CreateDate], T2.ItemCode, T2.Dscription, T2.DocNum, T2.DocDate, t2.Quantity
ORDER BY T0.CardCode

Beside that I also want to filter customer status based on selection. For example If I choose date from 01-01-2022 to 02-08-2022, any customer that has last order greater then 1 year from and placed a new order will marked as New Customer, while if the customer has order less then 1 year will marked as "Existing Customer"

Appreciate your help on this.

[LoHa · Accepted solution]

Hi Gerald,

to be honest I can't help you to get what you want. I can't put it together in rules. You say a customer buys something and suddenly he buys something else. That would means that he buys everytime the same. Our Customers buy hundreds of different things.

I think the query could help you to figure out what you want. But be aware:
I couldn't run this query as here provided because of too much customers and invoice. I don't know how much data you got, but you could cause a deadlock on the database.

So because of that I just tested it with one customer.You can see it in the first with (temptable:BPDates) I commented it out yet. I suggest you to use it.
[OCRD].[CardType] = 'C' AND [OCRD].[validFor] = 'Y' /*AND [OCRD].[CardCode] = 'xxxx'*/

To get a view in the past with the invoices I substracted a year from StartDate that is the period of the invoices.

At least I put all in a second with (temptable AllData). So you can filter/order on your own.

There are examples given Just uncomment what you want.

reagards Lothar

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

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

/*Subtract a year from StartDate*/
DECLARE @FirstStartDate AS Date
SET @FirstStartDate = (SELECT DATEADD(year,-1,@StartDate))

--/*Debug only*/
--DECLARE @StartDate AS Date
--DECLARE @EndDate AS Date
--DECLARE @FirstStartDate AS Date
--SET @StartDate = '20220101'
--SET @EndDate = '20220131'
--SET @FirstStartDate = (SELECT DATEADD(year,-1,@StartDate))

;
with BPDates as
(
SELECT
DISTINCT
[OCRD].[CardCode]
,MIN(OINV.DocDate) OVER (Partition BY [OCRD].[CardCode]) AS [FirstPurchaseBP]
,MAX(OINV.DocDate) OVER (Partition BY [OCRD].[CardCode]) AS [LastPurchaseBP]
,[OCRD].[CreateDate] AS [BPCreationDate]
FROM
[OCRD]
/*Join to not Canceled Invoices via CardCode*/
LEFT JOIN [OINV] ON [OINV].[CardCode] = [OCRD].[CardCode] AND [OINV].[CANCELED] = 'N'
WHERE
/*All active Customer*/
[OCRD].[CardType] = 'C' AND [OCRD].[validFor] = 'Y' /*AND [OCRD].[CardCode] = 'xxxx'*/
)
, AllData AS
(
SELECT 
BPDates.* 
, [INV1].[ItemCode] AS [ItemCode]
, [INV1].[Dscription] AS [ItemDescription]
, [INV1].[Quantity] AS [PurchasedQuantity]
, [OINV].[DocDate] AS [InvoiceDate]
, CASE 
  WHEN [OINV].[DocDate] IS NULL THEN NULL
  WHEN [OINV].[DocDate] < @StartDate THEN 'BeforeChoosenPeriod' 
  ELSE 'InChoosenPeriod' END AS [Period]
, [OINV].[DocNum] AS [InvoiceNumber]
, (SELECT MIN([T1].[DocDate]) FROM [INV1] [T0] INNER JOIN [OINV] [T1] ON [T1].[DocEntry] = [T0].DocEntry WHERE [T0].[ItemCode] = [INV1].[ItemCode] AND [T1].[CardCode] = [OINV].[CardCode] AND [OINV].[CANCELED] = 'N') AS [FirstItemPurchaseEver]
, (SELECT MAX([T1].[DocDate]) FROM [INV1] [T0] INNER JOIN [OINV] [T1] ON [T1].[DocEntry] = [T0].DocEntry WHERE [T0].[ItemCode] = [INV1].[ItemCode] AND [T1].[CardCode] = [OINV].[CardCode] AND [OINV].[CANCELED] = 'N') AS [LastItemPurchaseEver]
FROM 
[BPDates]
LEFT JOIN [OINV] ON [OINV].[CardCode] = [BPDates].[CardCode] AND ([OINV].[DocDate] >= @FirstStartDate OR @StartDate = '') AND ([OINV].[DocDate] <= @EndDate OR @EndDate = '')
LEFT JOIN [INV1] ON [INV1].[DocEntry] = [OINV].[DocEntry]

)

--/*Get all unsorted and Filtered*/
--SELECT 
--* 
--FROM 
--[AllData]

--/*First ItemCode than InvoiceDate*/
--SELECT 
--* 
--FROM 
--[AllData]
--ORDER BY [AllData].[CardCode] ASC,[AllData].[ItemCode] ASC,[AllData].[InvoiceDate] ASC

--/*First InvoiceDate than ItemCode*/
--SELECT 
--* 
--FROM 
--[AllData]
--ORDER BY [AllData].[CardCode] ASC,[AllData].[InvoiceDate] ASC ,[AllData].[ItemCode] ASC 

[geraldhans · Accepted solution]

Hi Lothar

Thanks!!! Let me give a try. Temporarily I will mark your answer as Accepted Answer. I will revert back again if I need more help. Thanks a lot

[geraldhans · Accepted solution]

Btw adding to my comment above. Is there any way to place a field this scenario?

• Lets say the customer A haven't made any purchase. Once they made their first purchase, this customer will be categorise as "New Customer". So there is specific field where I think we can use SELECT CASE scenario.
• Customers which start buying a new item that was not purchased before in the past two years will categorized as "New Customer"

Here are some mockup that can give you ideas (which once I ran the query, it should look like this)

[LoHa]

Hi Gerald,

the Group is added.

The "blub" line was only for test purposes, i forgot to delete it before copyind 😉

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

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

/*Ask for ItemGroup*/
/** SELECT FROm OITB T2 **/
DECLARE @ItmsGrpNam AS NVARCHAR(max)
/* WHERE */
SET @ItmsGrpNam = /* T2.ItmsGrpNam */ '[%2]'

/*Subtract a year from StartDate*/
DECLARE @FirstStartDate AS Date
SET @FirstStartDate = (SELECT DATEADD(year,-1,@StartDate))



--/*Debug only*/
--DECLARE @StartDate AS Date
--DECLARE @EndDate AS Date
--DECLARE @FirstStartDate AS Date
--DECLARE @ItmsGrpNam AS NVARCHAR(max)
--SET @StartDate = '20220101'
--SET @EndDate = '20220221'
--SET @FirstStartDate = (SELECT DATEADD(year,-1,@StartDate))
--SET @ItmsGrpNam = ''

;
with BPDates as
(
SELECT
DISTINCT
[OCRD].[CardCode]
,MIN(OINV.DocDate) OVER (Partition BY [OCRD].[CardCode]) AS [FirstPurchaseBP]
,MAX(OINV.DocDate) OVER (Partition BY [OCRD].[CardCode]) AS [LastPurchaseBP]
,[OCRD].[CreateDate] AS [BPCreationDate]
FROM
[OCRD]
/*Join to not Canceled Invoices via CardCode*/
LEFT JOIN [OINV] ON [OINV].[CardCode] = [OCRD].[CardCode] AND [OINV].[CANCELED] = 'N'
WHERE
/*All active Customer*/
[OCRD].[CardType] = 'C' AND [OCRD].[validFor] = 'Y' /*AND [OCRD].[CardCode] = 'XXX'*/
)
, AllData AS
(
SELECT 
BPDates.* 
, [INV1].[ItemCode] AS [ItemCode]
, [INV1].[Dscription] AS [ItemDescription]
, [INV1].[Quantity] AS [PurchasedQuantity]
, [OINV].[DocDate] AS [InvoiceDate]
, CASE 
  WHEN [OINV].[DocDate] IS NULL THEN NULL
  WHEN [OINV].[DocDate] < @StartDate THEN 'BeforeChoosenPeriod' 
  ELSE 'InChoosenPeriod' END AS [Period]
, [OINV].[DocNum] AS [InvoiceNumber]
, (SELECT MIN([T1].[DocDate]) FROM [INV1] [T0] INNER JOIN [OINV] [T1] ON [T1].[DocEntry] = [T0].[DocEntry] WHERE [T0].[ItemCode] = [INV1].[ItemCode] AND [T1].[CardCode] = [OINV].[CardCode] AND [T1].[CANCELED] = 'N') AS [FirstItemPurchaseEver]
, (SELECT MAX([T1].[DocDate]) FROM [INV1] [T0] INNER JOIN [OINV] [T1] ON [T1].[DocEntry] = [T0].[DocEntry] WHERE [T0].[ItemCode] = [INV1].[ItemCode] AND [T1].[CardCode] = [OINV].[CardCode] AND [T1].[CANCELED] = 'N') AS [LastItemPurchaseEver]
, (SELECT COUNT([T1].[DocDate]) FROM [INV1] [T0] INNER JOIN [OINV] [T1] ON [T1].[DocEntry] = [T0].[DocEntry] WHERE [T0].[ItemCode] = [INV1].[ItemCode] AND [T1].[CardCode] = [OINV].[CardCode] AND [T1].[CANCELED] = 'N') AS [CounterBoughtItemEver]
, (SELECT COUNT([T1].[DocDate]) FROM [INV1] [T0] INNER JOIN [OINV] [T1] ON [T1].[DocEntry] = [T0].[DocEntry] WHERE [T0].[ItemCode] = [INV1].[ItemCode] AND [T1].[CardCode] = [OINV].[CardCode] AND [T1].[CANCELED] = 'N' AND [T0].[DocDate] between DATEADD(YEAR,-2,[OINV].[DocDate]) AND [OINV].[DocDate]) AS [CounterBoughtItemLast2YearsFromThisInvoice]

FROM 
[BPDates]
LEFT JOIN [OINV] ON [OINV].[CardCode] = [BPDates].[CardCode] AND ([OINV].[DocDate] >= @FirstStartDate OR @StartDate = '') AND ([OINV].[DocDate] <= @EndDate OR @EndDate = '')
LEFT JOIN [INV1] ON [INV1].[DocEntry] = [OINV].[DocEntry]
LEFT JOIN [OITM] ON [INV1].[ItemCode] = [OITM].[ItemCode]
LEFT JOIN [OITB] ON [OITB].[ItmsGrpCod] = [OITM].[ItmsGrpCod]
WHERE
[OINV].[DocType] = 'I'
AND
[OINV].[CANCELED] = 'N'
AND
([OITB].[ItmsGrpNam] = @ItmsGrpNam OR @ItmsGrpNam = '')
)
--/*Get all unsorted and Filtered*/
--SELECT 
--* 
--FROM 
--[AllData]

--/*First ItemCode than InvoiceDate*/
--SELECT 
--* 
--FROM 
--[AllData]
--ORDER BY [AllData].[CardCode] ASC,[AllData].[ItemCode] ASC,[AllData].[InvoiceDate] ASC

--/*First InvoiceDate than ItemCode*/
--SELECT 
--* 
--FROM 
--[AllData]
--ORDER BY [AllData].[CardCode] ASC,[AllData].[InvoiceDate] ASC ,[AllData].[ItemCode] ASC 

SELECT 
*
,CASE 
	WHEN [CounterBoughtItemEver] = 1 OR [CounterBoughtItemLast2YearsFromThisInvoice] = 1 THEN 'New Customer'
	ELSE 'Existing Customer'
	END AS [Customer]
FROM 
[AllData]
ORDER BY [AllData].[CardCode] ASC, [AllData].[ItemCode], [AllData].[InvoiceDate]

So that should be all 😉

regards Lothar

[geraldhans]

Hi Lothar

Super Thanks!!! Sorry for my late response. As I were very sick for past 1 week and unable to logged in to computer. Let me give a try and will let you know the results

Thanks a lot for your help!!!

[geraldhans]

Hi Lothar

Many thanks. The Queries work perfectly. Thank you for your help. Just one last question. I need to define if the invoice is less then 1 year then it will be categorize as New Customer as well. Here are the logic

Example 1 : Customer A is a new customer and start buys Item Z for 1MT starting in Feb 2021 and continue to purchase again as follows :

1) 2MT in Apr 2021

2) 1MT in Sep 2021

3) 3MT in Dec 2021

4) 1MT in Mar 2021

(1) to (3) is new business. (4) will become existing business since it is > 1 year

I try to use logic

SELECT 
*
,CASE 
	WHEN [CounterBoughtItemEver] = 1 OR [CounterBoughtItemLast2YearsFromThisInvoice] = 1 OR [FirstPurchaseBP] > [InvoiceDate]  THEN 'New Customer'
	ELSE 'Existing Customer'
	END AS [Customer]

But its not working. Basically If the Invoice Date is within 365 days or 1 Year from the First Purchase Date, then it will be categorize as New Customer, otherwise it will be categorize as Existing Customer.

Thanks again for your help

Regards

Gerald

[LoHa]

Hi Gerald,

I tried something, I hope it matches.

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

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

/*Subtract a year from StartDate*/
DECLARE @FirstStartDate AS Date
SET @FirstStartDate = (SELECT DATEADD(year,-1,@StartDate))

--/*Debug only*/
--DECLARE @StartDate AS Date
--DECLARE @EndDate AS Date
--DECLARE @FirstStartDate AS Date
--SET @StartDate = '20220101'
--SET @EndDate = '20220531'
--SET @FirstStartDate = (SELECT DATEADD(year,-1,@StartDate))

;
with BPDates as
(
SELECT
DISTINCT
[OCRD].[CardCode]
,MIN(OINV.DocDate) OVER (Partition BY [OCRD].[CardCode]) AS [FirstPurchaseBP]
,MAX(OINV.DocDate) OVER (Partition BY [OCRD].[CardCode]) AS [LastPurchaseBP]
,[OCRD].[CreateDate] AS [BPCreationDate]
FROM
[OCRD]
/*Join to not Canceled Invoices via CardCode*/
LEFT JOIN [OINV] ON [OINV].[CardCode] = [OCRD].[CardCode] AND [OINV].[CANCELED] = 'N'
WHERE
/*All active Customer*/
[OCRD].[CardType] = 'C' AND [OCRD].[validFor] = 'Y' /*AND [OCRD].[CardCode] = 'XXX'*/
)
, AllData AS
(
SELECT 
BPDates.* 
, [INV1].[ItemCode] AS [ItemCode]
, [INV1].[Dscription] AS [ItemDescription]
, [INV1].[Quantity] AS [PurchasedQuantity]
, [OINV].[DocDate] AS [InvoiceDate]
, CASE 
  WHEN [OINV].[DocDate] IS NULL THEN NULL
  WHEN [OINV].[DocDate] < @StartDate THEN 'BeforeChoosenPeriod' 
  ELSE 'InChoosenPeriod' END AS [Period]
, [OINV].[DocNum] AS [InvoiceNumber]
, (SELECT MIN([T1].[DocDate]) FROM [INV1] [T0] INNER JOIN [OINV] [T1] ON [T1].[DocEntry] = [T0].[DocEntry] WHERE [T0].[ItemCode] = [INV1].[ItemCode] AND [T1].[CardCode] = [OINV].[CardCode] AND [T1].[CANCELED] = 'N') AS [FirstItemPurchaseEver]
, (SELECT MAX([T1].[DocDate]) FROM [INV1] [T0] INNER JOIN [OINV] [T1] ON [T1].[DocEntry] = [T0].[DocEntry] WHERE [T0].[ItemCode] = [INV1].[ItemCode] AND [T1].[CardCode] = [OINV].[CardCode] AND [T1].[CANCELED] = 'N') AS [LastItemPurchaseEver]
, (SELECT COUNT([T1].[DocDate]) FROM [INV1] [T0] INNER JOIN [OINV] [T1] ON [T1].[DocEntry] = [T0].[DocEntry] WHERE [T0].[ItemCode] = [INV1].[ItemCode] AND [T1].[CardCode] = [OINV].[CardCode] AND [T1].[CANCELED] = 'N') AS [CounterBoughtItemEver]
, (SELECT COUNT([T1].[DocDate]) FROM [INV1] [T0] INNER JOIN [OINV] [T1] ON [T1].[DocEntry] = [T0].[DocEntry] WHERE [T0].[ItemCode] = [INV1].[ItemCode] AND [T1].[CardCode] = [OINV].[CardCode] AND [T1].[CANCELED] = 'N' AND [T0].[DocDate] between DATEADD(YEAR,-2,[OINV].[DocDate]) AND [OINV].[DocDate]) AS [CounterBoughtItemLast2YearsFromThisInvoice]
,DATEADD(YEAR,-2,[OINV].DocDate) AS blub
FROM 
[BPDates]
LEFT JOIN [OINV] ON [OINV].[CardCode] = [BPDates].[CardCode] AND ([OINV].[DocDate] >= @FirstStartDate OR @StartDate = '') AND ([OINV].[DocDate] <= @EndDate OR @EndDate = '')
LEFT JOIN [INV1] ON [INV1].[DocEntry] = [OINV].[DocEntry]
WHERE
[OINV].[DocType] = 'I'
AND
[OINV].[CANCELED] = 'N'

)

--/*Get all unsorted and Filtered*/
--SELECT 
--* 
--FROM 
--[AllData]

--/*First ItemCode than InvoiceDate*/
--SELECT 
--* 
--FROM 
--[AllData]
--ORDER BY [AllData].[CardCode] ASC,[AllData].[ItemCode] ASC,[AllData].[InvoiceDate] ASC

--/*First InvoiceDate than ItemCode*/
--SELECT 
--* 
--FROM 
--[AllData]
--ORDER BY [AllData].[CardCode] ASC,[AllData].[InvoiceDate] ASC ,[AllData].[ItemCode] ASC 

SELECT 
*
,CASE 
	WHEN [CounterBoughtItemEver] = 1 OR [CounterBoughtItemLast2YearsFromThisInvoice] = 1 THEN 'New Customer'
	ELSE 'Existing Customer'
	END AS [Customer]
FROM 
[AllData]
ORDER BY [AllData].[CardCode] ASC, [AllData].[ItemCode], [AllData].[InvoiceDate]<br>

regards Lothar

[geraldhans]

Hi Lothar

Pardon me for my late reply. Thank you so so much. This what I'm looking for. You're my savior.
Just a quick questions,

1. supposedly I want to add Item Category or Filter it based on item Category may I know which part of the Query I need to change? Its the same conditions, only this time it filtered through Item Category
2. I noticed that you put this syntax DATEADD(YEAR,-2,[OINV].DocDate) AS blub may I know what blub used for?

Many thanks to you for helping me with this. Thank you so so much once again for your help

Regards
Gerald

[LoHa]

Hi Gerald,

I tried something, I hope it matches.

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

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

/*Subtract a year from StartDate*/
DECLARE @FirstStartDate AS Date
SET @FirstStartDate = (SELECT DATEADD(year,-1,@StartDate))

--/*Debug only*/
--DECLARE @StartDate AS Date
--DECLARE @EndDate AS Date
--DECLARE @FirstStartDate AS Date
--SET @StartDate = '20220101'
--SET @EndDate = '20220531'
--SET @FirstStartDate = (SELECT DATEADD(year,-1,@StartDate))

;
with BPDates as
(
SELECT
DISTINCT
[OCRD].[CardCode]
,MIN(OINV.DocDate) OVER (Partition BY [OCRD].[CardCode]) AS [FirstPurchaseBP]
,MAX(OINV.DocDate) OVER (Partition BY [OCRD].[CardCode]) AS [LastPurchaseBP]
,[OCRD].[CreateDate] AS [BPCreationDate]
FROM
[OCRD]
/*Join to not Canceled Invoices via CardCode*/
LEFT JOIN [OINV] ON [OINV].[CardCode] = [OCRD].[CardCode] AND [OINV].[CANCELED] = 'N'
WHERE
/*All active Customer*/
[OCRD].[CardType] = 'C' AND [OCRD].[validFor] = 'Y' /*AND [OCRD].[CardCode] = '90004251'*/
)
, AllData AS
(
SELECT 
BPDates.* 
, [INV1].[ItemCode] AS [ItemCode]
, [INV1].[Dscription] AS [ItemDescription]
, [INV1].[Quantity] AS [PurchasedQuantity]
, [OINV].[DocDate] AS [InvoiceDate]
, CASE 
  WHEN [OINV].[DocDate] IS NULL THEN NULL
  WHEN [OINV].[DocDate] < @StartDate THEN 'BeforeChoosenPeriod' 
  ELSE 'InChoosenPeriod' END AS [Period]
, [OINV].[DocNum] AS [InvoiceNumber]
, (SELECT MIN([T1].[DocDate]) FROM [INV1] [T0] INNER JOIN [OINV] [T1] ON [T1].[DocEntry] = [T0].[DocEntry] WHERE [T0].[ItemCode] = [INV1].[ItemCode] AND [T1].[CardCode] = [OINV].[CardCode] AND [T1].[CANCELED] = 'N') AS [FirstItemPurchaseEver]
, (SELECT MAX([T1].[DocDate]) FROM [INV1] [T0] INNER JOIN [OINV] [T1] ON [T1].[DocEntry] = [T0].[DocEntry] WHERE [T0].[ItemCode] = [INV1].[ItemCode] AND [T1].[CardCode] = [OINV].[CardCode] AND [T1].[CANCELED] = 'N') AS [LastItemPurchaseEver]
, (SELECT COUNT([T1].[DocDate]) FROM [INV1] [T0] INNER JOIN [OINV] [T1] ON [T1].[DocEntry] = [T0].[DocEntry] WHERE [T0].[ItemCode] = [INV1].[ItemCode] AND [T1].[CardCode] = [OINV].[CardCode] AND [T1].[CANCELED] = 'N') AS [CounterBoughtItemEver]
, (SELECT COUNT([T1].[DocDate]) FROM [INV1] [T0] INNER JOIN [OINV] [T1] ON [T1].[DocEntry] = [T0].[DocEntry] WHERE [T0].[ItemCode] = [INV1].[ItemCode] AND [T1].[CardCode] = [OINV].[CardCode] AND [T1].[CANCELED] = 'N' AND [T0].[DocDate] between DATEADD(YEAR,-2,[OINV].[DocDate]) AND [OINV].[DocDate]) AS [CounterBoughtItemLast2YearsFromThisInvoice]
,DATEADD(YEAR,-2,[OINV].DocDate) AS blub
FROM 
[BPDates]
LEFT JOIN [OINV] ON [OINV].[CardCode] = [BPDates].[CardCode] AND ([OINV].[DocDate] >= @FirstStartDate OR @StartDate = '') AND ([OINV].[DocDate] <= @EndDate OR @EndDate = '')
LEFT JOIN [INV1] ON [INV1].[DocEntry] = [OINV].[DocEntry]
WHERE
[OINV].[DocType] = 'I'
AND
[OINV].[CANCELED] = 'N'

)

--/*Get all unsorted and Filtered*/
--SELECT 
--* 
--FROM 
--[AllData]

--/*First ItemCode than InvoiceDate*/
--SELECT 
--* 
--FROM 
--[AllData]
--ORDER BY [AllData].[CardCode] ASC,[AllData].[ItemCode] ASC,[AllData].[InvoiceDate] ASC

--/*First InvoiceDate than ItemCode*/
--SELECT 
--* 
--FROM 
--[AllData]
--ORDER BY [AllData].[CardCode] ASC,[AllData].[InvoiceDate] ASC ,[AllData].[ItemCode] ASC 

SELECT 
*
,CASE 
	WHEN [CounterBoughtItemEver] = 1 OR [CounterBoughtItemLast2YearsFromThisInvoice] = 1 THEN 'New Customer'
	ELSE 'Existing Customer'
	END AS [Customer]
FROM 
[AllData]
ORDER BY [AllData].[CardCode] ASC, [AllData].[ItemCode], [AllData].[InvoiceDate]

regards Lothar

[LoHa]

Hi Gerald,

give this a try

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

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


/*Debug only*/
--DECLARE @StartDate AS Date
--DECLARE @EndDate AS Date
--SET @StartDate = '20220101'
--SET @EndDate = '20220131'

;
with BPDates as
(
SELECT
DISTINCT
[OCRD].[CardCode]
,MIN(OINV.DocDate) OVER (Partition BY [OCRD].[CardCode]) AS [FirstPurchase]
,MAX(OINV.DocDate) OVER (Partition BY [OCRD].[CardCode]) AS [LastPurchase]
,[OCRD].[CreateDate] AS [BPCreationDate]
FROM
[OCRD]
/*Join to not Canceled Invoices via CardCode*/
LEFT JOIN [OINV] ON [OINV].[CardCode] = [OCRD].[CardCode] AND [OINV].[CANCELED] = 'N'
WHERE
/*All active Customer*/
[OCRD].[CardType] = 'C' AND [OCRD].[validFor] = 'Y'
)
SELECT 
BPDates.* 
, [INV1].[ItemCode] AS [ItemCode]
, [INV1].[Dscription] AS [ItemDescription]
, [INV1].[Quantity] AS [PurchasedQuantity]
, [OINV].[DocDate] AS [InvoiceDate]
, [OINV].[DocNum] AS [InvoiceNumber]
FROM 
[BPDates]
LEFT JOIN [OINV] ON [OINV].[CardCode] = [BPDates].[CardCode] AND ([OINV].[DocDate] >= @StartDate OR @StartDate = '') AND ([OINV].[DocDate] <= @EndDate OR @EndDate = '')
LEFT JOIN [INV1] ON [INV1].[DocEntry] = [OINV].DocEntry
ORDER BY [BPDates].[CardCode] ASC,[OINV].[DocDate] ASC

1. Customers which start buying a new item that was not purchased before in the past two years will categorized as "New Customer"
2. Customers which start buying for the 1st time, will be categorized as "New Customer"
3. Customers who stop buying for more then year when they start to buy again will categorized as "New Customer" (will be based on Last Invoice - Lets say Customer last purchased date is October 2019, and when they start purchasing with us again on November 2020 will be categorized as "New Customer")

In Number 1 you say a new ITEM should it really look for each item?

In Number 3 you say last purchase is October 19 and new in November 20. The last Purchase would be Nov. 20. You need to look in gap between last and the one before.

What is the date to compare with in Number 1 ? Startdate/EndDate/Today

regards Lothar

[geraldhans]

Hi Lothar

Many thanks for your reply. i will give it a try on the query you gave me. Answering to your question

1. Yes. Lets say Customer A usually purchased Item iPhone 12, then suddenly within 2 years time Customer A purchased iPad Pro, then this will be categorised New Customer

3. I think it supposed to be within Start date and End Date. However given the conditions that user may select less than 1 year it will cause errors right? Can we instead used based on System Date (this mean as per today)?

Thanks a lot Lothar for your keen help

Regards
Gerald

[azizelmir]

Hi Gerald,

You can check the same query with selection criteria daterange on SAP (query Generator):

Declare @fromdate as datetime
Declare @Tilldate as datetime
select @fromdate = T0.DocDate from OINV T0 where T0.DocDate = '[%01]' 
select @Tilldate = T0.DocDate from OINV T0 where T0.DocDate = '[%02]' 
set @fromdate =  '[%01]' 
set @Tilldate = '[%02]' 

SELECT W1.* 
FROM
(
SELECT T0.CardCode AS 'Customer Code', T0.CardName AS
'Customer Name', T3.SlpName AS 'Sales Person', T0.CreateDate AS 'BP Creation
Date', MIN(T1.DocDate) AS 'Customer First Order', MAX(T1.DocDate) AS 'Customer Latest Order Date', T2.ItemCode
AS 'Item Code', T2.Dscription AS 'Item Description', T2.Quantity AS 'Purchased
Quantity', T2.DocDate AS 'Invoice Date', T2.DocNum AS 'Invoice Number', CASE WHEN MAX(T1.Docdate) < Year(T2.DocDate) THEN 'New Customer' ELSE 'Existing Customer' END AS 'Customer Status'
FROM OCRD T0
INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode
INNER JOIN (SELECT GHa.ItemCode, Gha.Dscription, GHb.DocNum,
GHb.DocDate, GHa.Quantity, Ghb.CardCode 
FROM OINV GHb 
INNER JOIN INV1 GHa ON GHb.DocEntry = GHa.DocEntry 
LEFT JOIN RIN1 GHc ON Ghc.BaseEntry = GHb.DocEntry AND
GHc.BaseLine = GHa.LineNum
LEFT JOIN ORIN GHd ON GHc.DocEntry = GHd.DocEntry
GROUP BY GHb.CardCode, GHa.ItemCode, GHa.Dscription,
GHb.DocNum, GHb.DocDate, GHa.Quantity) T2 ON T2.CardCode = T0.CardCode
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode
INNER JOIN OITM T4 ON T2.ItemCode = T4.ItemCode 
WHERE T0.CardType = 'C' AND T4.InvntItem = 'Y'
GROUP BY T0.CardCode, T0.CardName,T3.SlpName,
T0.[CreateDate], T2.ItemCode, T2.Dscription, T2.DocNum, T2.DocDate, t2.Quantity

) W1
Where W1.[Customer Latest Order Date]>= @fromdate AND W1.[Customer Latest Order Date]<= @Tilldate
ORDER BY W1.[Customer Code]

Thank you,Aziz

[geraldhans]

Hi Aziz

Thanks a lot for your help. It does resolved the issues I'm facing. I totally forgot that I can use declare query. Btw just quick questions

• I didn't managed to get it sort using DocDate. It give me errors "The multi-part identifier "T2.DocDate" could not be bound"
• Referring to this Query, I think i need to adjust something

CASE WHEN MAX(T1.Docdate) < Year(T2.DocDate) THEN 'New Customer' ELSE 'Existing Customer' END AS 'Customer Status'

Is it possible if We do a scenario like the following?

1. Customers which start buying a new item that was not purchased before in the past two years will categorized as "New Customer"
2. Customers which start buying for the 1st time, will be categorized as "New Customer"
   
3. Customers who stop buying for more then year when they start to buy again will categorized as "New Customer" (will be based on Last Invoice - Lets say Customer last purchased date is October 2019, and when they start purchasing with us again on November 2020 will be categorized as "New Customer")

Thanks a lot for your help.

[LoHa]

Hi Gerald,

there are several questions when I look at your query.

-You said you want to see the first and last order.

In your query you use for min/max OINV (Invoice)

-from T0 to T1 you used a Inner Join.

That means, you won't get a customer that never placed an "Order"

-It could make sense in the Where-Clause to check that the Customer is activ

-In Subquery T2 you join to RIN1 and ORIN but you don't use it

-You said you want to see it in a daterange.

You mean you want only the invoices/orders in that range but the first and last date is needed?

regards Lothar