Avatar of yanci1179
yanci1179
 asked on

t-sql Group of records whose sum Equal a specific Number

I am trying to get each group of records whose sum equal 3000.....I'll try to explain with an example

Give this records below
Id   Number 1
1    1000
2     2000
3     3000
4    50
5   100
6   2500
7   350


Result would be, where I would find the min and max of the ID where the sum totals 3000 for the records
Min   Max   Sum
1          2       3000
3          3       3000
4          7        3000          

Here is the code to create the example table

create table #Example
(ID int identity(1,1)
,Number1 int)

insert into #Example
(Number1)
select 1000 union
select 2000 union
select 3000 union
select 50 union
select 100 union
select 2500 union
select 350
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
Vitor Montalvão

What happens if I make some changes in your sample to let it be more complex?
Id   Number 1
 1    1000
 2    1500
 3    3000
 4    50
 5    100
 6    2500
 7    350
 8    500
 9    750
10   2300
11   900

Now, which results should be expected?
yanci1179

ASKER
My numbers increment in very small amounts (the ones that I gave are just for an example).  The final sum of each rolling sum will come really close to 3000.  So I guess, it would be best to get the sum that comes closest to 3000 (less than or equal to 3000).  

So given your example, the result set would be

Min       Max            Sum
1      2            2500
3      3           3000
4      7           3000
8      9           1250
10      10           2300
11      11           900
Mayank Gairola

I am sure there is a better way to do it. But this will work as well.

drop table #Example
drop table #temp

create table #Example
(ID int identity(1,1)
,Number1 int)

insert into #Example
(Number1)
select 1000 union
select 2000 union
select 3000 union
select 50 union
select 100 union
select 2500 union
select 350

select * from #Example

select * into #temp
from #Example


select ID as 'min',ID as 'max','3000' as 'sum'
from #Example
where Number1=3000
union
select t.ID as 'min',e.ID as 'max','3000' as'sum'
from #temp t
join #Example e
on t.ID<>e.ID
where t.ID<e.ID
group by t.Number1,e.Number1,t.ID,e.ID
having (t.Number1 + e.Number1)=3000
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Vitor Montalvão

This solution can be a little slow when the table is bigger but couldn't find a better way to do it:
;WITH CTE_SUM (MinID, MaxID, MySum)
AS
(
	SELECT ID, ID, Number1
	FROM #Example
	WHERE ID = 1
	UNION ALL
	SELECT 
		CASE 
			WHEN E.Number1+S.MySum > 3000 THEN E.ID
			ELSE S.MinID
		END, 
		E.ID,
		CASE 
			WHEN E.Number1+S.MySum > 3000 THEN E.Number1
			ELSE E.Number1+S.MySum
		END
	FROM #Example E
		INNER JOIN CTE_SUM S ON E.ID=S.MaxID+1
)
SELECT MinId, MAX(MaxId) MaxId, MAX(MySum) MySum
FROM CTE_SUM
GROUP BY MinId
ORDER BY MinId

Open in new window

yanci1179

ASKER
Hi Mayank,

The result set returns 3 records, it doesn't add the rest of the records
also, try, union all so it can enter the records in correct order....sorry about that

 create table #Example
 (ID int identity(1,1)
 ,Number1 int)

 insert into #Example
 (Number1)
 select 1000 union all
 select 2000 union all
 select 3000 union all
 select 50 union all
 select 100 union all
 select 2500 union all
 select 350

The result returns
min  max  sum
1        2        3000
3        3         3000

it doesn't sum the rest of the records, so the result set would be
min    max   sum
1         2          3000
3         3          3000
4         7         3000

Thanks again for your help!
ASKER CERTIFIED SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.