[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Stop when SUM Count =

Karch

3/26/2007 9:02:00 PM

I have a table that looks something like this:

CREATE TABLE [dbo].[Counter](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Date] [datetime] NULL,
[Count] [int] NULL,
[Product] [int] NULL
) ON [PRIMARY]

Given a specific date (today), I want to sum the values in the [Count]
column (starting with a given date and moving backward in time) until I
reach the row where the SUM is equal to a given value. I then want to return
that date. I am using SQL 2005 - what is the best way to acheive this?
10 Answers

Anith Sen

3/26/2007 9:18:00 PM

0

Something along the lines of:

SELECT t1.dt
FROM tbl t1
WHERE ( SELECT COUNT( t2.sum )
FROM tbl t2
WHERE t2.dt <= t1.dt ) <= @p ;

--
Anith


Karch

3/26/2007 10:26:00 PM

0

This doesn't work - the query needs to sum the Count column going backward
from a given date and return the date when the total of the Count column is
greater than or equal to a given value. There is no 'sum' column as
indicated in your answer.

"Anith Sen" <anith@bizdatasolutions.com> wrote in message
news:uFh3rw%23bHHA.3616@TK2MSFTNGP05.phx.gbl...
> Something along the lines of:
>
> SELECT t1.dt
> FROM tbl t1
> WHERE ( SELECT COUNT( t2.sum )
> FROM tbl t2
> WHERE t2.dt <= t1.dt ) <= @p ;
>
> --
> Anith
>


Anith Sen

3/26/2007 10:47:00 PM

0

That was a typo. It should be SUM( t2.count ).

If the summation is not done in the intended manner of date ordering,
perhaps you should try adjusting the comparison operators.

In case you need a tested solution, please refer to www.aspfaq.com/5006 and
post a repro with sample data & expected results.

--
Anith


Karch

3/27/2007 12:23:00 PM

0

Given the set of data below and an input of 150 (the sum total required),
the query should return '12/23/2007 12:00:00 AM' (because 75 + 25 +25 +25 =
150)

1 12/22/2007 12:00:00 AM 50
2 12/23/2007 12:00:00 AM 25
3 3/25/2007 12:00:00 AM 25
4 3/26/2007 12:00:00 AM 25
5 3/27/2007 12:00:00 AM 75


"Anith Sen" <anith@bizdatasolutions.com> wrote in message
news:%230esui$bHHA.4808@TK2MSFTNGP04.phx.gbl...
> That was a typo. It should be SUM( t2.count ).
>
> If the summation is not done in the intended manner of date ordering,
> perhaps you should try adjusting the comparison operators.
>
> In case you need a tested solution, please refer to www.aspfaq.com/5006
> and post a repro with sample data & expected results.
>
> --
> Anith
>


Damien

3/27/2007 12:43:00 PM

0

On Mar 27, 1:23 pm, "Karch" <nos...@absotutely.com> wrote:
> Given the set of data below and an input of 150 (the sum total required),
> the query should return '12/23/2007 12:00:00 AM' (because 75 + 25 +25 +25 =
> 150)
>
> 1 12/22/2007 12:00:00 AM 50
> 2 12/23/2007 12:00:00 AM 25
> 3 3/25/2007 12:00:00 AM 25
> 4 3/26/2007 12:00:00 AM 25
> 5 3/27/2007 12:00:00 AM 75
>
> "Anith Sen" <a...@bizdatasolutions.com> wrote in message
>

Rewritten to force sample data to fit original table definition, fixed
sample data since presumably the 12/2x/2007 data whould presumably
been from 2006:

insert into Counter (Date,Count,Product)
select '20061222', 50 ,null union all
select '20061223', 25 ,null union all
select '20070325', 25 ,null union all
select '20070326', 25 ,null union all
select '20070327', 75 ,null

declare @p int
set @p = 150

select MAX(Date) from
(
select c1.Date,(select SUM(Count) from Counter c2 where c2.Date >=
c1.Date) as Cnt
from Counter c1
) t
where
Cnt >= @p

Could probably be neater

Damien

Roy Harvey

3/27/2007 12:52:00 PM

0

SELECT MAX(Date)
FROM (SELECT A.Date, SUM(B.[Count]) as SummedCount
FROM Counter as A
JOIN Counter as B
ON A.Date <= B.Date
GROUP BY A.Date) as X
WHERE SummedCount >= 150

Note that will large sets of data the performance can be terrible.

Roy Harvey
Beacon Falls, CT

On Tue, 27 Mar 2007 07:23:26 -0500, "Karch" <nospam@absotutely.com>
wrote:

>Given the set of data below and an input of 150 (the sum total required),
>the query should return '12/23/2007 12:00:00 AM' (because 75 + 25 +25 +25 =
>150)
>
> 1 12/22/2007 12:00:00 AM 50
> 2 12/23/2007 12:00:00 AM 25
> 3 3/25/2007 12:00:00 AM 25
> 4 3/26/2007 12:00:00 AM 25
> 5 3/27/2007 12:00:00 AM 75
>
>
>"Anith Sen" <anith@bizdatasolutions.com> wrote in message
>news:%230esui$bHHA.4808@TK2MSFTNGP04.phx.gbl...
>> That was a typo. It should be SUM( t2.count ).
>>
>> If the summation is not done in the intended manner of date ordering,
>> perhaps you should try adjusting the comparison operators.
>>
>> In case you need a tested solution, please refer to www.aspfaq.com/5006
>> and post a repro with sample data & expected results.
>>
>> --
>> Anith
>>
>

Karch

3/27/2007 1:48:00 PM

0

Thanks - sorry about the data :-)

"Damien" <Damien_The_Unbeliever@hotmail.com> wrote in message
news:1174999403.140318.160310@p15g2000hsd.googlegroups.com...
> On Mar 27, 1:23 pm, "Karch" <nos...@absotutely.com> wrote:
>> Given the set of data below and an input of 150 (the sum total required),
>> the query should return '12/23/2007 12:00:00 AM' (because 75 + 25 +25 +25
>> =
>> 150)
>>
>> 1 12/22/2007 12:00:00 AM 50
>> 2 12/23/2007 12:00:00 AM 25
>> 3 3/25/2007 12:00:00 AM 25
>> 4 3/26/2007 12:00:00 AM 25
>> 5 3/27/2007 12:00:00 AM 75
>>
>> "Anith Sen" <a...@bizdatasolutions.com> wrote in message
>>
>
> Rewritten to force sample data to fit original table definition, fixed
> sample data since presumably the 12/2x/2007 data whould presumably
> been from 2006:
>
> insert into Counter (Date,Count,Product)
> select '20061222', 50 ,null union all
> select '20061223', 25 ,null union all
> select '20070325', 25 ,null union all
> select '20070326', 25 ,null union all
> select '20070327', 75 ,null
>
> declare @p int
> set @p = 150
>
> select MAX(Date) from
> (
> select c1.Date,(select SUM(Count) from Counter c2 where c2.Date >=
> c1.Date) as Cnt
> from Counter c1
> ) t
> where
> Cnt >= @p
>
> Could probably be neater
>
> Damien
>


Karch

3/27/2007 1:48:00 PM

0

Thanks, in my case the data shouldn't be too bad, maybe 1-2K rows.

"Roy Harvey" <roy_harvey@snet.net> wrote in message
news:kp4i03le13vq6d0l087u7ugooksq9cotvt@4ax.com...
> SELECT MAX(Date)
> FROM (SELECT A.Date, SUM(B.[Count]) as SummedCount
> FROM Counter as A
> JOIN Counter as B
> ON A.Date <= B.Date
> GROUP BY A.Date) as X
> WHERE SummedCount >= 150
>
> Note that will large sets of data the performance can be terrible.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 27 Mar 2007 07:23:26 -0500, "Karch" <nospam@absotutely.com>
> wrote:
>
>>Given the set of data below and an input of 150 (the sum total required),
>>the query should return '12/23/2007 12:00:00 AM' (because 75 + 25 +25 +25
>>=
>>150)
>>
>> 1 12/22/2007 12:00:00 AM 50
>> 2 12/23/2007 12:00:00 AM 25
>> 3 3/25/2007 12:00:00 AM 25
>> 4 3/26/2007 12:00:00 AM 25
>> 5 3/27/2007 12:00:00 AM 75
>>
>>
>>"Anith Sen" <anith@bizdatasolutions.com> wrote in message
>>news:%230esui$bHHA.4808@TK2MSFTNGP04.phx.gbl...
>>> That was a typo. It should be SUM( t2.count ).
>>>
>>> If the summation is not done in the intended manner of date ordering,
>>> perhaps you should try adjusting the comparison operators.
>>>
>>> In case you need a tested solution, please refer to www.aspfaq.com/5006
>>> and post a repro with sample data & expected results.
>>>
>>> --
>>> Anith
>>>
>>


Roy Harvey

3/27/2007 2:51:00 PM

0

On Tue, 27 Mar 2007 08:48:23 -0500, "Karch" <nospam@absotutely.com>
wrote:

>Thanks, in my case the data shouldn't be too bad, maybe 1-2K rows.

That should be fine I would think, but keep in mind that before the
GROUP BY the intermediate result set will be rather large. For one
thousand (1000) rows in the table, the set before grouping will be
half a million ( 500000). For two thousand rows (2000) it will be 2
million (2000000). The calculation is to multiple the number of rows
by itself, then divide by 2 - half a cartesian product. That should
be no big deal, but is worth understanding.

Roy Harvey
Beacon Falls, CT

Andy Steenson [MSFT]

3/28/2007 1:02:00 AM

0

Karch,

This will work, although it doesn't take into account what you want to do if
there are multiple rows that have the end date in them, or what you want to
do if there is no exact sum that matches the sum that you are looking for.

set nocount on
declare @StartDate DateTime
declare @EndDate DateTime
declare @SumTofind int
set @StartDate = GetDate() -- '3/20/2007'
set @SumToFind = 65
declare @count int
declare @date datetime
declare @sum int
set @sum = 0
Declare count_cursor CURSOR
FOR
select date, count
from counter
where date <= @StartDate
order by date desc
Open count_cursor
Fetch next from count_cursor
into @date, @count
while ( @@fetch_status = 0 and @SumToFind > @sum)
begin
set @Sum = @sum + @count
set @EndDate = @Date
Fetch Next from count_cursor
into @date, @count
end
Close count_cursor
deallocate count_cursor
select @Sum SumOfCounts, @StartDate StartDate, @EndDate EndDate

select @startdate, @enddate, sum(count)
from counter
WHERE date <= @startdate
and date >= @enddate

Enjoy,
Andy Steenson
Software Design Engineer
Deployment Technology Group, Microsoft


====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
====================================================