How to Get Rows from value of sum

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 2008 Forums

Transact-SQL (2008)

How to Get Rows from value of sum

Author

Topic

marianux
Starting Member

3 Posts

Posted - 2011-12-27 : 18:11:28

Hi everyone,
id like to know how could i get some rows that some sum of some columns is equal or less than a number and ordered by datetime ASC.
I know i could do it with a cursor but id prefer to do it with a select statement if its possible

Let's see.

I got this table:

ID STAR DATETIME DURATION (HOURS)
627 2011-08-03 17:00:00.000 2
628 2011-08-05 17:00:00.000 2
629 2011-08-08 17:00:00.000 2
630 2011-08-10 17:00:00.000 2
631 2011-08-12 17:00:00.000 2

1º) Well, imagine we are at 2011-08-01, and id like to get all the rows from table 'till sum(duration) <= 4.
Result must get rows with ID 627 and 628

2º) If we were at 2011-08-07 and id like to get all the rows from table 'till sum(duration) <= 4. then rows would be with ID 629, 630

3º) If we were at 2011-08-11 and id like to get all the rows from table 'till sum(duration) <= 4. then rows would be with ID 631

4º) If we were at 2011-08-27 and id like to get all the rows from table 'till sum(duration) <= 4. then no rows would be retrieved

Key factor is that value that is compared with sum(duration) (<= 4) is dinamyc, is not always the same.

Anyone could take a look at this.

Thanks in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-27 : 20:10:11

Probably easiest approach (from the perspective of programming efforts) would be to use a recursive CTE, like this:

DECLARE @x INT; SET @x = 4;
DECLARE @startDate DATETIME; SET @startDate = '20110827';

;WITH cte AS
(
	SELECT TOP 1 id, [DURATION (HOURS)] AS cumulativeHours 
	FROM YourTable 
	WHERE [star datetime] >= @startDate
	ORDER BY [star datetime]
	
	UNION all
	
	SELECT y.id, c.cumulativeHours+y.[DURATION (HOURS)]
	FROM YourTable y INNER JOIN cte c ON c.id+1 = y.ID
	WHERE c.cumulativeHours+y.[DURATION (HOURS)] <= @x
)
SELECT id FROM cte;

The performance may not be great - would be a problem if you have lot of data in the table and each calculation requires the summation of a large number of rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 00:47:45

[code]
DECLARE @startdate datetime,@target int
SELECT @startdate = '2011-08-07', --put your date value here
@target=4

SELECT t.ID
FROM table t
CROSS APPLY (SELECT SUM(Duration) AS RunTime
FROM table
WHERE DATETIME <= t.DATETIME
)t1
WHERE t.DATETIME >= @startdate
AND RunTime <=@target
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

marianux
Starting Member

3 Posts

Posted - 2011-12-28 : 05:38:38

quote:
Originally posted by sunitabeck

Probably easiest approach (from the perspective of programming efforts) would be to use a recursive CTE, like this:
DECLARE @x INT; SET @x = 4;
DECLARE @startDate DATETIME; SET @startDate = '20110827';

;WITH cte AS
(
SELECT TOP 1 id, [DURATION (HOURS)] AS cumulativeHours
FROM YourTable
WHERE [star datetime] >= @startDate
ORDER BY [star datetime]

UNION all

SELECT y.id, c.cumulativeHours+y.[DURATION (HOURS)]
FROM YourTable y INNER JOIN cte c ON c.id+1 = y.ID
WHERE c.cumulativeHours+y.[DURATION (HOURS)] <= @x
)
SELECT id FROM cte;
The performance may not be great - would be a problem if you have lot of data in the table and each calculation requires the summation of a large number of rows.

Hi, thanks for your reply.
It seems work fine, althought i never have worked with recursive CTE so i got some doubts about what it's possible to do or to use.
Id like to get, if it's possible, next step:

I add another column to table:
clientid int

so table data is now in this way

ID CLIENTEID STAR DATETIME DURATION (HOURS)
627 1 2011-08-03 17:00:00.000 2
628 2 2011-08-05 17:00:00.000 2
629 1 2011-08-08 17:00:00.000 2
630 3 2011-08-10 17:00:00.000 2
631 2 2011-08-12 17:00:00.000 2

So now, id like to get results grouped by clientid so f.i. if:

1º) We are at 2011-08-01, and id like to get all the rows from table grouped by clientid 'till sum(duration) <= 4.
Result must get rows:
For Client 1: ID 627 and 629
For Client 2: ID 628 and 631
For Client 3: ID 630

2º) If we were at 2011-08-04 and id like to get all the rows from table 'till sum(duration) <= 4.
For Client 1: ID 629
For Client 2: ID 628 and 631
For Client 3: ID 630

3º) If we were at 2011-08-11 and id like to get all the rows from table 'till sum(duration) <= 4. then rows would be
For Client 2: ID 628 and 631

4º) If we were at 2011-08-27 and id like to get all the rows from table 'till sum(duration) <= 4. then no rows would be retrieved

Performance is not a problem because it's not gonna work with a lot of rows, even in the beginning it only woul work with the same client id, but i'd like to know how much can i get with CTE.
Anyway im gonna investigate by myself too.

Thanks in advance for your help.

marianux
Starting Member

3 Posts

Posted - 2011-12-28 : 06:35:16

quote:
Originally posted by visakh16

DECLARE @startdate datetime,@target int
SELECT @startdate = '2011-08-07', --put your date value here
@target=4

SELECT t.ID
FROM table t
CROSS APPLY (SELECT SUM(Duration) AS RunTime
FROM table
WHERE DATETIME <= t.DATETIME
)t1
WHERE t.DATETIME >= @startdate
AND RunTime <=@target

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks for your replay.

I have been trying with your statement and in the end it worked fine when i added to cross apply where statement:
DATETIME >= @startdate, so Statement is now:


DECLARE @startdate datetime,@target int
SELECT @startdate = '2011-08-07', --put your date value here
@target=4

SELECT t.ID
FROM table t
CROSS APPLY (SELECT SUM(Duration) AS RunTime
             FROM table
             WHERE DATETIME <= t.DATETIME and
             DATETIME >= @startdate
            )t1
WHERE t.DATETIME >= @startdate
AND RunTime <=@target

It works fine now, at least with all the test i've made.
I have applied the change that i have explained in my last post and it works fine with new changes.

Thanks so much for your help!.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 07:20:18

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources