Running Sum Total For Each Rows

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

General SQL Server Forums

New to SQL Server Programming

Running Sum Total For Each Rows

Author

Topic

mahsa_mr
Starting Member

22 Posts

Posted - 2008-11-11 : 06:13:22

Hi,

I have a table with 2 columns Time Format . i want to keep Sum of diff them in a view to use it in BO ( Business objects ) as a sample

sample:
Row No Time In Time Out Diff Sum
1 07:36:00 16:02:00 08:26:00 08:26:00
2 11:35:00 18:12:00 06:37:00 15:03:00 =(06:37:00 +08:26:00)

any idea PLS?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-11 : 06:18:14

declare @time_in varchar(10), @time_out varchar(10)
select @time_in='06:37:00',@time_out='08:26:00'
select convert(varchar(10),dateadd(day,0,@time_in)+dateadd(day,0,@time_out),108)

Madhivanan

Failing to plan is Planning to fail

mahsa_mr
Starting Member

22 Posts

Posted - 2008-11-11 : 06:25:54

Thanks in general it is correct but I need to have a each row plus pervious row diff in running time .
for example if I have 100 row in my table the result shows 100 row and last row contain of sum of 99 pervious row + amount of row number 100.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 06:36:21

[code]SELECT t.RowNo,t.TimeIn,t.TimeOut,CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,t.TimeIn,t.TimeOut)+COALESCE(t1.Total,0),0),108) AS DiffSum
FROM table t
OUTER APPLY (SELECT SUM(DATEDIFF(ss,TimeIn,TimeOut)) AS Total
FROM Table
WHERE RowNo<t.RowNo)t1[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 06:41:47

[code]declare @test table
(
RowNo int,
TimeIn varchar(8),
TimeOut varchar(8)
)
insert into @test
select 1, '07:36:00' ,'16:02:00' union all
select 2, '11:35:00', '18:12:00'

SELECT t.RowNo,t.TimeIn,t.TimeOut,CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,t.TimeIn,t.TimeOut),0),108) AS Diff,
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,t.TimeIn,t.TimeOut)+COALESCE(t1.Total,0),0),108) AS Sum
FROM @test t
OUTER APPLY (SELECT SUM(DATEDIFF(ss,TimeIn,TimeOut)) AS Total
FROM @test
WHERE RowNo<t.RowNo)t1

output
-----------------------------------
RowNo TimeIn TimeOut Diff Sum
1 07:36:00 16:02:00 08:26:00 08:26:00
2 11:35:00 18:12:00 06:37:00 15:03:00[/code]

mahsa_mr
Starting Member

22 Posts

Posted - 2008-11-11 : 07:24:46

Thanks a lot , it seems that can run in SQL 2005 ?
i need to run it in 2000 ....

and also i need to keep SUM in nomber format because of avoiding following problem :

insert into @test
select 1, '07:36:00' ,'16:02:00' union all
select 2, '11:35:00', '18:12:00' union all
select 3, '07:35:00', '18:12:00'

SELECT t.RowNo,t.TimeIn,t.TimeOut,CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,t.TimeIn,t.TimeOut),0),108) AS Diff,
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,t.TimeIn,t.TimeOut)+COALESCE(t1.Total,0),0),108) AS Sum
FROM @test t
OUTER APPLY (SELECT SUM(DATEDIFF(ss,TimeIn,TimeOut)) AS Total
FROM @test
WHERE RowNo<t.RowNo)t1

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-13 : 04:30:02

CREATE TABLE #time(rowno INT IDENTITY(1,1) ,timein VARCHAR(120), timeout VARCHAR(120),diff VARCHAR(120),sumof VARCHAR(120))

INSERT INTO #time SELECT '07:36:00' ,'16:02:00', '08:26:00', '08:26:00'

DECLARE @t VARCHAR(120), @t1 VARCHAR(120),@str VARCHAR(120),@str1 VARCHAR(120)
SELECT @t = '13:35:00' ,@t1 = '19:12:00'
SELECT @str = CONVERT(VARCHAR(120),CAST(@t1 AS DATETIME)- CAST(@t AS DATETIME),108)
SELECT @str = REPLACE(SUBSTRING(@str,len(@str)-7,len(@str)),'AM','')
SELECT @str1 = CONVERT(VARCHAR(120),(SELECT MAX(t1.value) FROM (SELECT CAST(MAX(sumof)AS DATETIME) + CAST(@str AS DATETIME) AS 'value' FROM #time GROUP BY rowno) AS t1 ),108)
SELECT @str1 = REPLACE(SUBSTRING(@str1,len(@str1)-7,len(@str1)),'AM','')

INSERT INTO #time
SELECT @t,@t1,@str,@str1

SELECT * FROM #time

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources