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]

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