sql-serveradventureworks

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
    ... ... ... ... ...