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
yanci1179Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
yanci1179Author Commented:
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 GairolaSr. Application Support Engg.Commented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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

yanci1179Author Commented:
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!
PortletPaulEE Topic AdvisorCommented:
SELECT DISTINCT
      e1.id 
    , case when e1.Number1 = 3000 then NULL else e2.id end
    , case when e1.Number1 = 3000 then e1.Number1 else (e1.Number1 + e2.NUmber1) end
FROM Example e1
CROSS JOIN Example e2
WHERE e1.Number1 = 3000
OR (e1.Number1 + e2.NUmber1) between 2700 and 3300 -- 10% either side
ORDER BY
      e1.id 
    , case when e1.Number1 = 3000 then NULL else e2.id end

Open in new window

Using this data:
CREATE TABLE Example
    ([Id] int, [Number1] int)
;
    
INSERT INTO Example
    ([Id], [Number1])
VALUES
    (1, 1000),
    (2, 1500),
    (3, 3000),
    (4, 50),
    (5, 100),
    (6, 2500),
    (7, 350),
    (8, 500),
    (9, 750),
    (10, 2300),
    (11, 900)
;

Open in new window

Produced this result
| id |        |      |
|----|--------|------|
|  1 |     10 | 3300 |
|  2 |      2 | 3000 |
|  3 | (null) | 3000 |
|  4 |      3 | 3050 |
|  5 |      3 | 3100 |
|  6 |      7 | 2850 |
|  6 |      8 | 3000 |
|  6 |      9 | 3250 |
|  7 |      6 | 2850 |
|  8 |      6 | 3000 |
|  8 |     10 | 2800 |
|  9 |      6 | 3250 |
|  9 |     10 | 3050 |
| 10 |      1 | 3300 |
| 10 |      8 | 2800 |
| 10 |      9 | 3050 |
| 10 |     11 | 3200 |
| 11 |     10 | 3200 |

Open in new window

This treats the pair order as significant e.g. 1, 10 and 10,1 are both included in the result

also see: http://sqlfiddle.com/#!6/bcdcc/5

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.