SQL Query help. Get min colA where sum colB < x

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Query help. Get min colA where sum colB < x

Author

Topic

Mr. Flibble
Starting Member

7 Posts

Posted - 2008-05-24 : 08:39:33

I've got a table like so

date | number
___________|_____________
2007-01-01 | 43
2007-01-02 | 65
2007-01-03 | 23
2007-01-04 | 65
2007-01-05 | 23
2007-01-06 | 11
2007-01-07 | 52
2007-01-08 | 83
2007-01-09 | 44

and I want to get the most recent date in the past which I must go back where the sum of the numbers of the number column if greater than a certain number.

EG. From the above data, the date which I must go back to get a sum of 195 is 2007-01-05
as 44+83+52+11+23 is the least amount that is greater than 195.

Can anyone help, is this even possible? I'm flummoxed!

Mr. Flibble
Starting Member

7 Posts

Posted - 2008-05-24 : 09:49:16

That should read 'where the sum of the numbers of the number column _is_ greater than a certain number'.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-24 : 09:55:14

There is probably a better way but here is one way:
( i think the answer should be 1/4 though rather than 1/5, right?)


declare @t table (dt datetime,  n int)
insert @t
select '2007-01-01' , 43 union all
select '2007-01-02' , 65 union all
select '2007-01-03' , 23 union all
select '2007-01-04' , 65 union all
select '2007-01-05' , 23 union all
select '2007-01-06' , 11 union all
select '2007-01-07' , 52 union all
select '2007-01-08' , 83 union all
select '2007-01-09' , 44

;with 
rws (rn,dt,n) as (
       select rn = row_number() over (order by dt)
              ,dt
              ,n
       from   @t
       )
,cte (rn,dt,n,tot) as 
       (
       select rn
              ,dt
              ,n
              ,n
       from   rws
       where  rn = 1
       union all
       select rws.rn
              ,rws.dt
              ,rws.n
              ,cte.tot+rws.n
       from   rws
       join   cte on cte.rn+1 = rws.rn
       )
select *
from cte
where  rn = (select min(rn) from cte where tot > 195)

output:
rn                   dt                      n           tot
-------------------- ----------------------- ----------- -----------
4                    2007-01-04 00:00:00.000 65          196

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-24 : 10:32:47

Here's one way:-

SELECT MIN(t.date)
FROM Table t
CROSS APPLY (SELECT SUM(number) AS Amount
             FROM Table
             WHERE date >=t.date) amt
WHERE amt.Amount >195

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-24 : 10:34:12

quote:

Originally posted by TG

There is probably a better way but here is one way:
( i think the answer should be 1/4 though rather than 1/5, right?)


declare @t table (dt datetime,  n int)
insert @t
select '2007-01-01' , 43 union all
select '2007-01-02' , 65 union all
select '2007-01-03' , 23 union all
select '2007-01-04' , 65 union all
select '2007-01-05' , 23 union all
select '2007-01-06' , 11 union all
select '2007-01-07' , 52 union all
select '2007-01-08' , 83 union all
select '2007-01-09' , 44

;with 
rws (rn,dt,n) as (
       select rn = row_number() over (order by dt)
              ,dt
              ,n
       from   @t
       )
,cte (rn,dt,n,tot) as 
       (
       select rn
              ,dt
              ,n
              ,n
       from   rws
       where  rn = 1
       union all
       select rws.rn
              ,rws.dt
              ,rws.n
              ,cte.tot+rws.n
       from   rws
       join   cte on cte.rn+1 = rws.rn
       )
select *
from cte
where  rn = (select min(rn) from cte where tot > 195)

output:
rn                   dt                      n           tot
-------------------- ----------------------- ----------- -----------
4                    2007-01-04 00:00:00.000 65          196

Be One with the Optimizer
TG

I think the OP was looking at date from latest back until the total exceeds 195.So it should be the other way around.

Mr. Flibble
Starting Member

7 Posts

Posted - 2008-05-24 : 15:19:57

Thanks.

I got TG's query working (changed order by dt to order by dt desc) but it only works for small data sets. I get the error
'Msg 530, The statement terminated. The maximum recursion 100 has been exhausted before statement completion.'
for large sets.

visakh16, I don't quite understatd your query. Haven't used the CROSS command before. The query seems to always return the minimum date in the list or null.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-24 : 23:21:26

Thanks Visakh - Looks like Mr. Flibble figured that out.
Regarding the maximum recursion error you can specify unlimited (or any number) with an OPTION clause ie:


....
select *
from cte
where  rn = (select min(rn) from cte where tot > 195)
OPTION (MAXRECURSION 0); --0 = unlimited

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-25 : 01:08:09

quote:
Originally posted by Mr. Flibble

Thanks.

I got TG's query working (changed order by dt to order by dt desc) but it only works for small data sets. I get the error
'Msg 530, The statement terminated. The maximum recursion 100 has been exhausted before statement completion.'
for large sets.

visakh16, I don't quite understatd your query. Haven't used the CROSS command before. The query seems to always return the minimum date in the list or null.

Sorry it should have been MAX

SELECT MAX(t.date)
FROM Table t
CROSS APPLY (SELECT SUM(number) AS Amount
             FROM Table
             WHERE date >=t.date) amt
WHERE amt.Amount >195

Mr. Flibble
Starting Member

7 Posts

Posted - 2008-05-25 : 06:57:11

Works great visakh16, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources