Top Categories

🧑‍🏭 Product Requirement

🎯 Objective

Calcualation Total sales (Net sales + tax) largest in period time to view

🤔 Assumptions

View top 5 Categories bán chạy nhất

🌟 Milestones

• Category: get category name
• Items: total count items đã bán được
• Total = sum(Nets sales + Tax) for each categories

🗒️ Requirements

🎨 User interaction and design

❓️ Open Questions

⚠️ Out of Scope

SQL checked from db DECLARE @startDate datetime SET @startDate = ‘02/23/2024 08:00:00 AM’; DECLARE @endDate datetime SET @endDate = ‘02/24/2024 08:00:00 AM’;

WITH Categories AS ( SELECT c.DepartmentID, c.DepartmentName FROM Departments c WHERE c.DepartmentID != ‘-4’
AND c.[Level] = 1 ),

SaleDetails AS (  -- get sale details from/to with conditions
        SELECT 
                d.SaleReceiptDetailID, 
                d.Quantity, 
                d.GroupID, 
                d.ItemID, 
                i.DeptDeptID,
                s.CashDiscountAmount,
                s.SVCFeeAmount 
        FROM 
                SaleReceiptDetails d 
                INNER JOIN SaleReceipts s ON s.SaleReceiptID = d.SaleReceiptID 
                INNER JOIN Items i ON i.ItemID = d.ItemID 
        WHERE 
                s.TransactionStatusID IN ('FIN', 'RET') 
                AND s.DateCreated BETWEEN @startDate AND @endDate 
                AND s.SaleTypeID != 28
                AND s.IsDeposit = 0 
                AND d.Status != 2 
                AND d.IsCompItem = 0 
                --{employeeFilter} 
                AND ( d.PriceInitial IS NULL OR d.PriceInitial = 'P' ) 
), 

CategoryItems AS (  -- get item quantity sales by category
        SELECT 
                d.DeptDeptID AS DepartmentID, 
                SUM(d.Quantity) AS ItemQuantity 
        FROM 
                SaleDetails d 
        GROUP BY d.DeptDeptID 
), 

CategorySales AS (  -- get total sales by category
        SELECT 
                d.DeptDeptID AS DepartmentID, 
                SUM ( d2.DiscountAmount + (CASE WHEN (d.CashDiscountAmount > d.SVCFeeAmount) THEN d2.CashDiscountPerItem ELSE 0 END) ) AS DiscountAmount, 
                SUM ( d2.TaxAmount ) AS TaxAmount, 
                SUM ( d2.ExtendedPrice - (CASE WHEN (d.CashDiscountAmount > d.SVCFeeAmount) THEN d2.CashDiscountPerItem ELSE 0 END) ) AS NetSales 
        FROM 
                SaleDetails d 
                INNER JOIN SaleReceiptDetails d2 ON d2.GroupID = d.GroupID 
        WHERE 
                d2.Status != 2 
                AND d2.IsCompItem = 0 
                AND EXISTS ( 
                        SELECT SaleReceiptID 
                        FROM SaleReceipts 
                        WHERE 
                                SaleReceiptID = d2.SaleReceiptID 
                                AND TransactionStatusID IN ('FIN', 'RET') 
                                AND SaleTypeID != 28 
                                AND IsDeposit = 0 
                ) 
        GROUP BY d.DeptDeptID 
) 

SELECT TOP (10) 
        c.DepartmentID AS ID, 
        c.DepartmentName AS Name,
        i.ItemQuantity AS Quantity, 
        s.NetSales, 
        s.TaxAmount, 
        s.DiscountAmount 
FROM 
        Categories c 
        INNER JOIN CategorySales s ON s.DepartmentID = c.DepartmentID 
        INNER JOIN CategoryItems i ON i.DepartmentID = c.DepartmentID 
WHERE 
        (s.NetSales + s.TaxAmount) != 0 AND i.ItemQuantity != 0 
ORDER BY (s.NetSales + s.TaxAmount) DESC