Unable to get Targeted total sales amount

Original Problem

Using AdventureWorks2008R2, write a query to return the salesperson id, number of unique products sold, highest order value, total sales amount, and top 3 orders for each salesperson.

Use TotalDue in SalesOrderHeader when calculating the highest order value and total sales amount. The top 3 orders have the 3 highest total order quantities. If there is a tie, the tie must be retrieved. Exclude orders which don't have a salesperson for this query. Return only the salespersons whose total sales were greater than $9800000.

Return the order value and total sales as int. Sort the returned data by SalesPersonID. The returned data should have a format as displayed below. Use the sample format for formatting purposes only.

Result

SalesPersonID	TotalUniqueProducts	OrderValue	TotalSales	Orders
275	242	165029	10475367	47395, 46666, 46662
276	244	145742	11695019	51721, 47355, 57046
277	246	132728	11342386	51748, 53560, 47027

Code:

WITH SalesData AS 
(
    SELECT 
        soh.SalesPersonID,
        soh.SalesOrderID,
        sod.ProductID,
        soh.TotalDue
    FROM
        Sales.SalesOrderHeader soh
    JOIN 
        Sales.SalesOrderDetail sod ON sod.SalesOrderID = soh.SalesOrderID
    WHERE
        soh.SalesPersonID IS NOT NULL
),
RankedSalesData AS 
(
    SELECT 
        SalesPersonID,
        SalesOrderID,
        TotalDue,
        ROW_NUMBER() OVER (PARTITION BY SalesPersonID ORDER BY TotalDue DESC) AS SalesRank
    FROM
        (SELECT DISTINCT SalesPersonID, SalesOrderID, TotalDue FROM SalesData) AS UniqueSalesData
),
SalesSummary AS 
(
    SELECT
        SalesPersonID,
        COUNT(DISTINCT ProductID) AS UniqueProductsSold,
        Cast(MAX(TotalDue)as int) AS HighestOrderValue,
        Cast(SUM(TotalDue) as int) AS TotalSalesAmount
    FROM
        SalesData
    GROUP BY
        SalesPersonID
)
SELECT
    SS.SalesPersonID,
    SS.UniqueProductsSold,
    SS.HighestOrderValue,
    SS.TotalSalesAmount,
    STUFF((SELECT ',' + CAST(R.SalesOrderID AS VARCHAR)
           FROM RankedSalesData R
           WHERE R.SalesPersonID = SS.SalesPersonID AND R.SalesRank <=3 
           FOR XML PATH('')), 1, 1, '') AS Top3Orders
FROM
    SalesSummary SS
WHERE
    SS.TotalSalesAmount > 9800000
ORDER BY
    SS.SalesPersonID;

My result:

Salesperson ID	Unique Product sold	HighestOrdervalue	Totalsalesamount	Top3 Orders
274	216	126852	45434066	51830,57136,53465
275	242	165029	327853784	47395,53621,50289
276	244	145742	381125561	47355,51822,57186
277	246	132728	347868655	46660,43884,44528
278	234	96937	102841549	44534,43890,58932
279	245	142312	201612866	44518,43875,47455

If you see the TotalSales Amount, it is multiplying multiple orders into the final amount and not matching the answer. I don't know where I am doing it wrong. I already tried to debug it with ChatGPT. So, Please save your answer from that. If you can explain in detail where I go wrong, It will be greatly appreciated in my academic career. I thank you ahead.

Solution

You will have to change the object names in this. This code assumes the existence of two tables:

CREATE TABLE InvoiceItems (InvoiceItemID BIGINT IDENTITY(1,1) NOT NULL, InvoiceID BIGINT NOT NULL, Sequence INT NULL, Quantity INT NULL, Cost DECIMAL(7, 2) NULL, Price DECIMAL(7, 2) NULL, ProductID BIGINT NULL)
CREATE TABLE Invoices (InvoiceID BIGINT IDENTITY(1,1) NOT NULL, InvoiceDateTimeUTC DATETIME2(7) NULL, CustomerID BIGINT NULL, EmployeeID BIGINT NULL, StoreID INT NULL)

(These are part of a script I maintain which generates completely randomized sales data, with proper constraints and all sorts of fun)

The code should provide an example of a way to produce the results you're looking for.

USE RandomSales;

;WITH Sales AS (
SELECT i.InvoiceID, i.InvoiceDateTimeUTC, i.CustomerID, i.EmployeeID, i.StoreID, ii.InvoiceItemID, ii.Sequence, ii.Quantity, ii.Cost, ii.Price, ii.ProductID, 
       ii.Price*ii.Quantity AS ItemTotal, 
       ii.Cost*ii.Quantity AS ItemValue, 
       SUM(ii.Price*ii.Quantity) OVER (PARTITION BY i.InvoiceID ORDER BY @@SPID) AS InvoiceTotal,
       SUM(ii.Cost*ii.Quantity) OVER (PARTITION BY i.InvoiceID ORDER BY @@SPID) AS InvoiceValue,
       SUM(ii.Quantity) OVER (PARTITION BY i.InvoiceID ORDER BY @@SPID) AS QuantityTotal
  FROM dbo.Invoices i
    INNER JOIN dbo.InvoiceItems ii
      ON ii.InvoiceID = i.InvoiceID
), QtyRankByEmployee AS (
SELECT EmployeeID, STRING_AGG(InvoiceId,', ') AS Invoices
  FROM (
        SELECT Sales.EmployeeID, Sales.InvoiceID, MAX(Sales.QuantityTotal) AS Qty, RANK() OVER (PARTITION BY Sales.EmployeeID ORDER BY MAX(Sales.QuantityTotal) DESC) AS QtyRank
          FROM Sales
         GROUP BY Sales.EmployeeID, Sales.InvoiceID
       ) a
 WHERE a.QtyRank <= 3
 GROUP BY a.EmployeeID
), EmployeeTotal AS (
SELECT EmployeeID, COUNT(DISTINCT ProductID) AS TotalUniqueProducts, SUM(InvoiceValue) AS TotalValue, SUM(InvoiceTotal) AS TotalSales
  FROM Sales s
 GROUP BY EmployeeID
)

SELECT e.EmployeeID, e.TotalUniqueProducts, e.TotalValue, e.TotalSales, q.Invoices
  FROM EmployeeTotal e
    LEFT OUTER JOIN QtyRankByEmployee q
      ON e.EmployeeID = q.EmployeeID

EmployeeID	TotalUniqueProducts	TotalValue	TotalSales	Invoices
1	31	6527.00	9082.20	8, 17, 5, 9
2	31	14397.00	19951.10	29, 37, 50
3	31	17304.00	24658.40	80, 86, 90
4	31	9992.50	14061.20	147, 152, 137
5	30	9608.00	13637.30	176, 201, 168
6	31	13396.00	18578.20	261, 257, 219
7	31	13850.50	19199.30	303, 306, 279, 285, 287
8	31	12420.00	17395.10	323, 327, 353, 368
9	31	16013.00	22568.50	409, 383, 382, 394, 370
10	31	12976.50	18055.10	423, 432, 434, 435
11	31	11641.00	16178.70	505, 496, 497, 486
12	31	10915.50	15387.40	529, 542, 544
13	31	14112.00	19650.70	556, 591, 587
14	31	15196.00	21490.90	617, 643, 644, 619, 628, 611
15	31	16220.50	22603.80	667, 652, 656, 686, 688
16	31	13231.50	18714.90	731, 727, 738, 742, 751, 711
17	31	11027.50	15881.30	754, 785, 788
...	...	...	...	...