{"id":42,"date":"2018-05-23T07:57:13","date_gmt":"2018-05-23T07:57:13","guid":{"rendered":"http:\/\/rarathemesdemo.com\/perfect-portfolio\/?post_type=rara-portfolio&#038;p=42"},"modified":"2023-07-03T21:55:17","modified_gmt":"2023-07-03T21:55:17","slug":"sql-abc","status":"publish","type":"rara-portfolio","link":"https:\/\/lizano.live\/index.php\/portfolio\/sql-abc\/","title":{"rendered":"Optimized ABC Classification: Enhance Product Management with SQL"},"content":{"rendered":"<p>This SQL Project is an optimized ABC classification solution is a powerful tool for retail businesses aiming to maximize efficiency in product management. By utilizing SQL and a combination of key criteria such as Sales, Profit, and Units Sold, our classification algorithm provides valuable insights into the relative importance of your products, empowering you to make informed strategic decisions.<\/p>\n<p>Key Benefits:<\/p>\n<p>Precise identification of critical products: Our ABC classification enables accurate and automated identification of the most important products for your business. By considering criteria like Sales, Profit, and Units Sold, you can determine which products contribute the most to your revenue and focus your efforts on managing and promoting them effectively.<\/p>\n<p>Inventory optimization: With our ABC classification solution, you can efficiently optimize your inventory. By understanding the importance of each product, you can allocate appropriate resources, manage stock levels, and forecast demand more accurately. This helps you avoid costly inventory excesses and ensures sufficient stock of critical products at all times.<\/p>\n<p>Data-driven strategic decision-making: Our ABC classification algorithm provides a clear and comprehensive overview of your products. By comprehending the importance of each product in terms of Sales, Profit, and Units Sold, you can make informed strategic decisions regarding pricing, promotions, new product launches, and supplier management. This alignment with the most valuable and profitable products gives you a competitive edge.<\/p>\n<p style=\"text-align: left;\">Improved profitability and performance: By focusing on the most important products, you can enhance overall profitability and performance of your business. Increasing sales of your high-value products and effectively managing their inventory enables you to boost profit margins and achieve sustainable long-term growth.<\/p>\n<p>In summary, this optimized ABC classification solution with SQL empowers you to efficiently understand and manage the significance of your products based on Sales, Profit, and Units Sold. Gain a competitive advantage, optimize your inventory, and make data-driven strategic decisions to maximize profitability and performance in your retail business.<\/p>\n<p>My Github Repo: <a href=\"https:\/\/github.com\/ricardo-lizano\/sqlportfolio\">https:\/\/github.com\/ricardo-lizano\/sqlportfolio<\/a><\/p>\n<p>SQL Server Code:<\/p>\n\n\n<div class=\"wp-block-group is-layout-flow wp-block-group-is-layout-flow\"><div class=\"wp-block-group__inner-container\">\n<pre class=\"wp-block-code\"><code>\n--Optimized ABC Classification: Enhance Product Management with SQL\n--Author: Ricardo Lizano Monge https:\/\/lizano.live\/\n--Tables and data structures from ContosoRetailDW dabatese from Microsoft Corporatiion: https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=18279\n\nWITH ProductSales AS\n(\n\tSELECT\n\t\tYEAR(DateKey) AS YEAR,\n\t\tMONTH(DateKey) AS MONTH,\n\t\t1 AS CompanyKey,\n\t\tStoreKey,\n\t\tProductKey,\n\t\tSUM(SalesAmount) AS SalesAmount,\n\t\tSUM(SalesAmount) - SUM(TotalCost) AS ProfitAmount,\n\t\tSUM(SalesQuantity) AS UnitsSold\n\tFROM FactSales\n\tGROUP BY\n\t\tYEAR(DateKey),\n\t\tMONTH(DateKey),\n\t\tStoreKey,\n\t\tProductKey\n)\n\nSELECT\n\t'Sales' AS Criteria,\n\tStoreKey,\n\tProductKey,\n\tSUM(ps.SalesAmount) AS SalesAmount,\n\tSUM(ps.ProfitAmount) AS ProfitAmount, \n\tSUM(ps.UnitsSold) AS UnitsSold, \n\tSUM(ps.SalesAmount) OVER (partition by ps.StoreKey ORDER BY ps.SalesAmount DESC) \/ SUM(NULLIF(ps.SalesAmount,0)) OVER (partition by ps.StoreKey) AS PorcetajeAcumulado,\n\tCASE\n\t\tWHEN SUM(ps.SalesAmount) OVER (partition by ps.StoreKey ORDER BY ps.SalesAmount DESC) \/ SUM(NULLIF(ps.SalesAmount,0)) OVER (partition by ps.StoreKey) &lt;= 0.8 THEN 'A'\n\t\tWHEN SUM(ps.SalesAmount) OVER (partition by ps.StoreKey ORDER BY ps.SalesAmount DESC) \/ SUM(NULLIF(ps.SalesAmount,0)) OVER (partition by ps.StoreKey) &lt;= 0.95 THEN 'B'\n\t\tELSE 'C'\n\tEND AS ABC\nFROM ProductSales ps\nGROUP BY\n\tStoreKey,\n\tProductKey,\n\tSalesAmount\n\nUNION ALL\n\nSELECT\n\t'Porfit' AS Criteria,\n\tStoreKey,\n\tProductKey,\n\tSUM(ps.SalesAmount) AS SalesAmount,\n\tSUM(ps.ProfitAmount) AS ProfitAmount,\n\tSUM(ps.UnitsSold) AS UnitsSold,\n\tSUM(ps.ProfitAmount) OVER (partition by ps.StoreKey ORDER BY ps.ProfitAmount DESC) \/ SUM(NULLIF(ps.ProfitAmount,0)) OVER (partition by ps.StoreKey) AS PorcetajeAcumulado,\n\tCASE\n\t\tWHEN SUM(ps.ProfitAmount) OVER (partition by ps.StoreKey ORDER BY ps.ProfitAmount DESC) \/ SUM(NULLIF(ps.ProfitAmount,0)) OVER (partition by ps.StoreKey) &lt;= 0.8 THEN 'A'\n\t\tWHEN SUM(ps.ProfitAmount) OVER (partition by ps.StoreKey ORDER BY ps.ProfitAmount DESC) \/ SUM(NULLIF(ps.ProfitAmount,0)) OVER (partition by ps.StoreKey) &lt;= 0.95 THEN 'B'\n\t\tELSE 'C'\n\tEND AS ABC\nFROM ProductSales ps\nGROUP BY\n\tStoreKey,\n\tProductKey,\n\tProfitAmount\n\nUNION ALL\n\nSELECT\n\t'Sold Units' AS Criteria,\n\tStoreKey,\n\tProductKey,\n\tSUM(ps.SalesAmount) AS SalesAmount,\n\tSUM(ps.ProfitAmount) AS ProfitAmount,\n\tSUM(ps.UnitsSold) AS UnitsSold,\n\tSUM(ps.UnitsSold) OVER (partition by ps.StoreKey ORDER BY ps.UnitsSold DESC) \/ SUM(NULLIF(ps.UnitsSold,0)) OVER (partition by ps.StoreKey) AS PorcetajeAcumulado,\n\tCASE\n\t\tWHEN SUM(ps.UnitsSold) OVER (partition by ps.StoreKey ORDER BY ps.UnitsSold DESC) \/ SUM(NULLIF(ps.UnitsSold,0)) OVER (partition by ps.StoreKey) &lt;= 0.8 THEN 'A'\n\t\tWHEN SUM(ps.UnitsSold) OVER (partition by ps.StoreKey ORDER BY ps.UnitsSold DESC) \/ SUM(NULLIF(ps.UnitsSold,0)) OVER (partition by ps.StoreKey) &lt;= 0.95 THEN 'B'\n\t\tELSE 'C'\n\tEND AS ABC\nFROM ProductSales ps\nGROUP BY\n\tStoreKey,\n\tProductKey,\n\tUnitsSold\n\nGO<\/code><\/pre>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>This SQL Project is an optimized ABC classification solution is a powerful tool for retail businesses aiming to maximize efficiency in product management. By utilizing SQL and a combination of key criteria such as Sales, Profit, and Units Sold, our classification algorithm provides valuable insights into the relative importance of your products, empowering you to &hellip; <\/p>\n","protected":false},"author":1,"featured_media":598,"comment_status":"closed","ping_status":"closed","template":"","rara_portfolio_categories":[4],"_links":{"self":[{"href":"https:\/\/lizano.live\/index.php\/wp-json\/wp\/v2\/rara-portfolio\/42"}],"collection":[{"href":"https:\/\/lizano.live\/index.php\/wp-json\/wp\/v2\/rara-portfolio"}],"about":[{"href":"https:\/\/lizano.live\/index.php\/wp-json\/wp\/v2\/types\/rara-portfolio"}],"author":[{"embeddable":true,"href":"https:\/\/lizano.live\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/lizano.live\/index.php\/wp-json\/wp\/v2\/comments?post=42"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/lizano.live\/index.php\/wp-json\/wp\/v2\/media\/598"}],"wp:attachment":[{"href":"https:\/\/lizano.live\/index.php\/wp-json\/wp\/v2\/media?parent=42"}],"wp:term":[{"taxonomy":"rara_portfolio_categories","embeddable":true,"href":"https:\/\/lizano.live\/index.php\/wp-json\/wp\/v2\/rara_portfolio_categories?post=42"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}