Please Help - - SQL Server Forums

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

Transact-SQL (2000)

Please Help -

Author

Topic

azmark30
Starting Member

11 Posts

Posted - 2008-03-04 : 15:34:06

Ok heres what I have-

ID NAME TIME POINTS TOTAL POINTS
3 mark 28915 97
6 tom 26390 112
14 bill 25225 89
34 mike 6762 115
56 jake 28210 52

the TIME is in seconds..
I need to add up all the points for the last 24 hours.
then i need a another column or row says TOTAL POINTS

i need it do run thru the entire list over 1000 names with times and points.

if you dont get what im saying let me know.. thank you for any help..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-04 : 15:39:04

You can use the SUM aggregate function to add up your data. Check out SUM in SQL Server Books Online for details.

To create a new column in the result set, you can use AS TotalPoints or TotalPoints = .

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

azmark30
Starting Member

11 Posts

Posted - 2008-03-04 : 15:52:36

yup i know about the sum(points) as 'total points' but
I need it to sum only the points for the last 24 hours.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 15:55:24

WHERE SomeDateTimeColumn >= DATEADD(HOUR, -24, GETDATE())

E 12°55'05.25"
N 56°04'39.16"

azmark30
Starting Member

11 Posts

Posted - 2008-03-04 : 16:12:50

ok i see what i did wrong..
this is what my table looks like

id name time points
3 mark 28915 15
3 mark 26390 56
3 mark 25225 89
3 mark 6762 115
3 mark 34761 25
10 bill 34872 56
10 bill 45637 156
10 bill 23476 256

The TIME is in seconds.
I need to add all the time up for 24 hours. then add only those points
for that 24 hours..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-04 : 16:18:41

What would be your expected out given the sample data that you provided?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

azmark30
Starting Member

11 Posts

Posted - 2008-03-04 : 16:59:34

the time is in seconds-
24 hours = 86 400 seconds
want it to add time and not go over 86400 and then add those points

id name totaltime totalpoints
3 mark 80530 160
3 mark 41523 204
10 bill 80509 212
10 bill 23476 256

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-04 : 17:51:53

Do you have a date column which will identify how to get the "last 24 hours"?

Poor planning on your part does not constitute an emergency on my part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 18:16:50

Oh I see..
You want row by row processing, and as soon as an accumulation has reached 86400 second as time, "print" that row and continue?

The only problem with this approach is "how do you define order of records"?

Here is the logic of OP's problem...

RowNo	id	name	time	points 
1	3	mark	28915	15
2	3	mark	26390	56
3	3	mark	25225	89
4	3	mark	6762	115
5	3	mark	34761	25
6	10	bill	34872	56
7	10	bill	45637	156
8	10	bill	23476	256


RowNo	id	name	time	points 
1	3	mark	28915	15	28915	15	-- Not reached 86400 yet

1	3	mark	28915	15
2	3	mark	26390	56	55305	71	-- Not reached 86400 yet

1	3	mark	28915	15
2	3	mark	26390	56
3	3	mark	25225	89	80530	160	-- Reached 86400 beacuse adding next row will cause "overflow"

4	3	mark	6762	115	6762	115	-- Not reached 86400 yet

4	3	mark	6762	115
5	3	mark	34761	25	41523	140	-- Reached 86400 because no more records for same id & name

6	10	bill	34872	56	34872	56	-- Not reached 86400 yet

6	10	bill	34872	56
7	10	bill	45637	156	80509	212	-- Reached 86400 beacuse adding next row will cause "overflow"

8	10	bill	23476	256	23476	256	-- Reached 86400 because no more records for same id & name

EDIT: Text in red is expected output

E 12°55'05.25"
N 56°04'39.16"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-04 : 18:19:23

quote:

The only problem with this approach is "how do you define order of records"?

And that is the question indeed~!

In his results example, the order of records is not in the order of his sample to achieve the same results (and also, does the total include any overlap as long as it's the 1st total over 86,400, or is it closest to 86,400 without going over...)

Poor planning on your part does not constitute an emergency on my part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 18:27:42

His example Totalpoints for second record of Mark was wrong.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 19:10:13

Here is a simple way to do this

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		RowNo TINYINT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		ID TINYINT,
		[Name] VARCHAR(4),
		[Time] INT,
		Points SMALLINT,
		Grp INT
	)

-- Populate sample data
INSERT	@Sample
	(
		ID,
		[Name],
		[Time],
		Points
	)
SELECT	3, 'mark', 28915, 15 UNION ALL
SELECT	3, 'mark', 26390, 56 UNION ALL
SELECT	3, 'mark', 25225, 89 UNION ALL
SELECT	3, 'mark', 6762, 115 UNION ALL
SELECT	3, 'mark', 34761, 25 UNION ALL
SELECT	10, 'bill', 34872, 56 UNION ALL
SELECT	10, 'bill', 45637, 156 UNION ALL
SELECT	10, 'bill', 23476, 256

-- Initialize control variables
DECLARE	@Time INT,
	@ID TINYINT,
	@Grp INT

-- Set initial control values
SELECT	@Time = 0,
	@ID = ID,
	@Grp = 1
FROM	@Sample
WHERE	RowNo = 1

-- Do the magic
UPDATE	@Sample
SET	@Time = @Time + [Time],
	@Grp = Grp = CASE WHEN @Time >= 86400 OR @ID <> ID THEN @Grp + 1 ELSE @Grp END,
	@Time = CASE WHEN @Time >= 86400 OR @ID <> ID THEN [Time] ELSE @Time END,
	@ID = ID

-- Show the expected result
SELECT		ID,
		[Name],
		SUM([Time]) AS [Time],
		SUM(Points) AS Points
FROM		@Sample
GROUP BY	ID,
		[Name],
		Grp
ORDER BY	Grp

Output from above query is

ID	Name	Time	Points
--	----	-----	------
3	mark	80530	160
3	mark	41523	140
10	bill	80509	212
10	bill	23476	256

E 12°55'05.25"
N 56°04'39.16"

azmark30
Starting Member

11 Posts

Posted - 2008-03-05 : 13:00:18

THANKS PESO!
Thats what I'm looking for .. thanks thanks

PERFECT..

thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources