I am looking for a quick and dirty SQL statement that gives us our top 10 Aftermarket customers (not OEM) by total sales. The date range needs to be from 1-1-2020 to current. I have something pretty rough and ugly, but it just isn’t giving me what I am looking for. If someone could just give me a quick, but accurate way to get this data, I would be forever in your debt! Thanks!
SELECT TOP 10
C.Name,
sum(S.DocUnitPrice) AS Total
FROM
erp.customer as C
INNER JOIN
erp.orderdtl as S
ON
C.Company = S.Company
WHERE
S.RequestDate >= '2020-01-01'
GROUP BY
C.name, C.CustID
ORDER BY
Total DESC
Two things jump out:
You are using Unit Price instead of UnitPrice*Qty (which is the Sales Amount per line)
Your ON criteria must include CustNum on both tables.
Pretty sure I was able to get custnum from orderdtl. It worked in SSMS, but these totals just seem a bit off. I will have to confirm with Sales or Customer service here, but if it is accurate, we must be doing better than I thought!
SELECT TOP 10
C.Name,
sum(S.DocUnitPrice * S.OrderQty) AS Total
FROM
erp.customer as C
INNER JOIN
erp.orderdtl as S
ON
C.Company = S.Company AND
C.CustNum = S.CustNum
WHERE
S.RequestDate >= '2020-01-01'
GROUP BY
C.name, C.CustID
ORDER BY
Total DESC
