Consult the bull of a SQL problem

For a simple and efficient Query:

Input table:
ID Number
1 2
2 1
3 6
4 3
5 5

Input TotalNumber: 10

How to find the latest ID with number sum is 10
Step1: Starting from ID 5 find number 5, 10-5=5
Step2: From ID 4 find number 3, 5-3=2
Step3: From ID 3 find number 6, since 6 >2 so no more ID searching.

Output table should be:
ID Number
3 2
4 3
5 5

----------------
More complex case
Input table:
Category ID Number
1 1 2
1 2 1
1 3 6
1 4 3
1 5 5
2 1 2
2 2 6
2 3 2

Input TotalNumber for category 1 is 10, for category 2 is 5

Output table should be:
Category ID Number
1 3 2
1 4 3
1 5 5
2 2 3
2 3 2

Started by Myron at November 28, 2016 - 7:52 AM

select 1 Category,	1 ID,	2 Number
into #temp
union all select 1,	2,1
union all select 1,	3,6
union all select 1,	4,3
union all select 1,	5,5
union all select 2,	1,2
union all select 2,	2,6
union all select 2,	3,2

with t1 as
(
select Category,ID
,Number=case 
         when Category=1 
		      then case when sumNumber<10 then Number
			            else 10- (sumNumber-Number)
				   end
         when Category=2
		      then case when sumNumber<5 then Number
			            else 5- (sumNumber-Number)
				   end
		end
from
(
select *,sum(Number) over(partition by category order by id desc) sumNumber
from #temp 
) t
) 
select *
from t1
where Number>0
order by Category,id

Posted by Colin at December 10, 2016 - 8:18 AM

create  table #tb(id int,number int)
insert into #tb 
select 1,2 union all
select 2,1 union all
select 3,6 union all
select 4,3 union all
select 5,5 
select * from #tb 

;with cte as 
( select top 1 id,case when number>=10 then 10 else number end as 'number' ,case when (10-number)<=0 then 0 else (10-number)end  as x  from #tb order by id desc
 union all
 select b.id,case when b.number>=a.x then a.x else b.number end ,case when (a.x -b.number)<=0 then 0 else (a.x-b.number ) end     from cte a join #tb b on b.id =a.id -1 
)
select id,number  from cte where number <>0
order by id

drop table #tb

Posted by Ted at December 12, 2016 - 8:33 AM

-- table1
create table hg1
(ID	int,Number int)

insert into hg1
 select 1,2 union all
 select 2,1 union all
 select 3,6 union all
 select 4,3 union all
 select 5,5


-- test1
declare @TotalNumber int
select @TotalNumber=10;

with t1 as
(select a.ID,a.Number,
        (select sum(b.Number) from hg1 b where b.ID>=a.ID) 'tn'
 from hg1 a),
t2 as
(select ID,case when tn<=@TotalNumber then Number
                else Number-(tn-@TotalNumber) end 'Number'
 from t1)
select ID,Number from t2 where Number>0

/*
ID          Number
----------- -----------
3           2
4           3
5           5

(3 row(s) affected)
*/



-- table2
create table hg2
(Category int,ID int,Number int)

insert into hg2
 select 1,1,2 union all
 select 1,2,1 union all
 select 1,3,6 union all
 select 1,4,3 union all
 select 1,5,5 union all
 select 2,1,2 union all
 select 2,2,6 union all
 select 2,3,2


-- test2
declare @TotalNumber1 int,@TotalNumber2 int
select @TotalNumber1=10,@TotalNumber2=5;

with t1 as
(select a.Category,a.ID,a.Number,
        (select sum(b.Number) from hg2 b where b.Category=a.Category and b.ID>=a.ID) 'tn',
        case a.Category when 1 then @TotalNumber1 
                        when 2 then @TotalNumber2 end 'Total'
 from hg2 a),
t2 as
(select Category,ID,
        case when tn<=Total then Number
             else Number-(tn-Total) end 'Number'
 from t1)
select Category,ID,Number from t2 where Number>0

/*
Category    ID          Number
----------- ----------- -----------
1           3           2
1           4           3
1           5           5
2           2           3
2           3           2

(5 row(s) affected)
*/

Posted by Tony at December 16, 2016 - 9:06 AM

A simple and efficient it may not be possible. You belong to the aggregate value of screening, if you can write simple sentence, but the aggregation of multiple, if efficient can establish multilayer aggregate cache, but the statement complex.

Posted by Amanda at December 24, 2016 - 10:04 AM

Thank you reply.

If there are one million CategoryID, how to write faster execution.?

Posted by Myron at December 27, 2016 - 10:08 AM

Such as floor 3 example, the variable @TotalNumber1, @TotalNumber2.. Saved as tables, and query results.

Posted by Tony at January 02, 2017 - 11:06 AM