Split total sum into categories based on range

Question

I am trying to split my total count of sales into categories using MSSQL.

So for example the total sales for October was 557.361. I would need to split that sum into three categories, calculating the bonus price based on each category, while the remainder overflows into the next category.

Is this possible using MSSQL?

See the below sample:

IF OBJECT_ID('tempdb..#sales') IS NOT NULL
    DROP TABLE #sales;

CREATE TABLE #sales
(
    year_month VARCHAR(10),
    total_sales INT
);


INSERT INTO #sales
(
    year_month,
    total_sales
)
VALUES
('2019-10-01', 557361),
('2019-11-01', 621801);

This would be the desired results, see the image below.

One option could be `recursive CTE` - It is a variation of capping sum: https://stackoverflow.com/a/52936314/5070879 — Lukasz Szozda, Dec 12 '19 at 15:22
@zip - I've added some sample data. This would be an example of data I have to work with. — theOGloc, Dec 12 '19 at 15:38
Great @theOGloc, if we take October, why didn't we go up to 400000 for medium and we stopped at 350000? — zip, Dec 12 '19 at 15:43
@zip - Because we already have 50000 in the first category, and the second category has the range between 50000 up to 400000. Therefore we can only fit 350000 sales in that category — theOGloc, Dec 12 '19 at 15:44

score 3 · Accepted Answer · answered Dec 12 '19 at 16:24

In the table category I replace the Range with the spread (max - min), which is more usable in a query that a string with a iphen in it. I added a lso a Catergory order to determine the order of overflow, its called CategoyOrder Try this and let us know please:

/************* DATA SOURCE **********************/

IF OBJECT_ID('tempdb..#sales') IS NOT NULL
    DROP TABLE #sales;

CREATE TABLE #sales
(
    year_month VARCHAR(10),
    total_sales INT
);


INSERT INTO #sales
(
    year_month,
    total_sales
)
VALUES
('2019-10-01', 557361),
('2019-11-01', 621801);

select * from #sales

IF OBJECT_ID('tempdb..#categories') IS NOT NULL
    DROP TABLE #categories;

CREATE TABLE #categories
(
    CategoryOrder int ,
    Category VARCHAR(10),
    price decimal(5,2),
    discount decimal(5,2),
    RangeSpread int ,
    CumulRangeSpread int 
);

INSERT INTO #categories
(
    CategoryOrder,
    Category,
    price,
    discount,
    RangeSpread,
    CumulRangeSpread  
)
VALUES
(1,'Small', 15, 0, 50000, 50000),
(2,'Medium', 13, 15, 350000, 400000),
(3,'Big', 11, 25, 400000, 800000);

select * from #categories

/************* END *****************/

/************* QUERY *****************/

;with cte as
(select c1.CategoryOrder, c1.Category, c1.price, c1.discount, c1.RangeSpread, isnull(c2.CumulRangeSpread, 0) as CumulRangeSpread from #categories c1 left join #categories c2 on c1.CategoryOrder -1 = c2.CategoryOrder) 

select c.CategoryOrder, c.Category, c.price, c.discount, total_sales, 
case when c.RangeSpread < total_sales - CumulRangeSpread  then c.RangeSpread else total_sales - CumulRangeSpread end as [CountIncategory], 
c.price*(case when c.RangeSpread < total_sales - CumulRangeSpread  then c.RangeSpread else total_sales - CumulRangeSpread end) as [Count*Price]
from cte c cross join #sales s order by total_sales, 2 desc

score 1 · Answer 2 · answered Dec 12 '19 at 16:30

I don't think this solution requires anything fancy, just some conditional logic and arithmetic. Here's a script that performs the calculation. You could package this up into a function for convenience. The function could accept the @total_sales_base as a parameter.

declare @total_sales_base int = 557361
declare @total_sales decimal(10,3) = @total_sales_base / 1000.0

drop table if exists #tmp
create table #tmp (Category varchar(10), Total decimal(10,3))

declare @sub decimal(10,3) = 0.0
declare @bonus decimal(10,3) = 0.0

set @sub = @total_sales - 50.0

if (@sub > 0.0)
begin
  set @bonus = 750.0
  set @total_sales = @total_sales - 50.0
end
else
begin
  set @bonus = @total_sales * 15
  set @total_sales = 0.0
end

insert into #tmp select 'Small', @bonus

set @sub = @total_sales - 350.0

if (@sub > 0.0)
begin
  set @bonus = 4550.0
  set @total_sales = @total_sales - 350.0
end
else
begin
  set @bonus = @total_sales * 13
  set @total_sales = 0.0
end

insert into #tmp select 'Medium', @bonus

set @bonus = @total_sales * 11

insert into #tmp select 'Big', @bonus

select * from #tmp

Output:

+----------+----------+
| Category | Total    |
+----------+----------+
| Small    |  750.000 |
| Medium   | 4550.000 |
| Big      | 1730.971 |
+----------+----------+

Split total sum into categories based on range

2 Answers2