Dynamics Community Forum Thread Details

Difficulty to get number of new customer only who has transaction

Hi Experts,

Requirement is to get number(count) of new customer only who has transaction in date range. Below is my SQL. In output, Highlighted row "CustomerCount" is 2 and "TotalInvoiceAmount" is 11040 but TotalInvoiceAmount = 11040 is only for one customer out of "CustomerCount"=2. So I want "CustomerCount" should be 1.

And also I want to get "CustomerCount" & "TotalInvoiceAmount" for previous month only if customer is created in previous month but there first invoice happen in current month.

How to do that.

SELECT 
    CustTable.MainContactWorker,
    hcmWorker.RecId AS MainContactWorkerRecId,
    dirPartyTable.Name AS MainContactWorkerName,
    COUNT(DISTINCT CustTable.RecId) AS CustomerCount,  -- Use DISTINCT to avoid duplicates
    SUM(ISNULL(CustTrans.AmountMST, 0)) AS TotalInvoiceAmount
FROM 
    CustTable
LEFT JOIN 
    CustTrans ON CustTrans.AccountNum = CustTable.AccountNum
    AND CustTrans.TransDate >= '2023-12-01' 
    AND CustTrans.TransDate <= '2023-12-31'  -- Ensure the date range includes transactions
AND (CUSTTRANS.TRANSTYPE=2 OR CUSTTRANS.TRANSTYPE=36)
INNER JOIN 
    HcmWorker ON HcmWorker.RecId = CustTable.MainContactWorker
INNER JOIN 
    DirPartyTable ON DirPartyTable.RecId = HcmWorker.Person
WHERE 
    CustTable.CREATEDDATETIME >= '2023-12-01' 
    AND CustTable.CREATEDDATETIME <= '2023-12-31'
GROUP BY 
    CustTable.MainContactWorker,
    HcmWorker.RecId,
    DirPartyTable.Name;

output