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
Thanks,
Faiz
