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.