Need to Add time and them group with total time

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

Need to Add time and them group with total time

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2013-05-13 : 06:58:35

I have a table with time1 and time2 datatype is varchar and the time format is hh:mm. I want to put the sum of time1 and time 2 in TOT with same format hh:mm.

After this i need the sum of TOT for each ID

Need help. Thanx a lot in advance.

ID Date Time 1 Time2 TOT
19 10/05/2013 00:00 05:58
19 02/05/2013 01:50 06:24
19 11/05/2013 01:54 06:06
19 07/05/2013 01:50 06:11
19 01/05/2013 00:00 06:00
19 04/05/2013 01:42 05:57
19 05/05/2013 01:46 06:08
19 08/05/2013 01:52 06:13
19 03/05/2013 00:00 06:06
20 10/05/2013 00:00 05:58
20 02/05/2013 01:50 06:24
20 11/05/2013 01:54 06:06
20 07/05/2013 01:50 06:11
20 01/05/2013 00:00 06:00
20 04/05/2013 01:42 05:57
20 05/05/2013 01:46 06:08
20 08/05/2013 01:52 06:13
20 03/05/2013 00:00 06:06

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-13 : 07:02:14

[code]
SELECT ID,[Date],CONVERT(varchar(8),DATEADD(ss,SUM(DATEDIFF(ss,Time1,Time2)),0),108) AS TOT
FROM Table
GROUP BY ID,[Date]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pnasz
Posting Yak Master

101 Posts

Posted - 2013-05-13 : 07:20:06

thanx but i want to put the total of time1 and time 2 in the coloumn TOT.

Like

ID Time1 Time 2 Tot
19 02:00 06:00 08:00
19 02:00 07:00 09:00
19 04:00 06:00 10:00

Later I want for ID 19 tot hr to be 08:00+09:00+10:00 i.r 27:00

ID TOT

19 27:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-13 : 07:34:07

thats exactly what suggestion i gave will do. DO you mean you need to update value back into the table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pnasz
Posting Yak Master

101 Posts

Posted - 2013-05-13 : 07:41:33

yes when i am updating its giving error

update Table set TOT=
CONVERT(varchar(8),DATEADD(ss,SUM(DATEDIFF(ss,Time1,Time2)),0),108)

An aggregate may not appear in the set list of an UPDATE statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-13 : 07:46:49

in that case do like


UPDATE t
SET TOT= TOT1
FROM
(
SELECT TOT,CONVERT(varchar(8),DATEADD(ss,SUM(DATEDIFF(ss,Time1,Time2) OVER (PARTITION BY ID,[Date]),0),108) AS TOT1
FROM Table
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pnasz
Posting Yak Master

101 Posts

Posted - 2013-05-13 : 07:52:52

Getting error

"DATEDIFF" is not a valid windowing function, and cannot be used with the OVER clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-13 : 07:59:15

quote:
Originally posted by pnasz

Getting error

"DATEDIFF" is not a valid windowing function, and cannot be used with the OVER clause.

missed a braces


UPDATE t
SET TOT= TOT1
FROM
(
SELECT TOT,CONVERT(varchar(8),DATEADD(ss,SUM(DATEDIFF(ss,Time1,Time2)) OVER (PARTITION BY ID,[Date]),0),108) AS TOT1
FROM Table
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pnasz
Posting Yak Master

101 Posts

Posted - 2013-05-13 : 08:16:38

After running the query not getting the correct value in TOT

this is what i am getting

ID Date Time1 Time2 TOT
19 01/05/2013 00:00 06:00 06:00:00
19 02/05/2013 01:50 06:24 04:34:00
19 03/05/2013 00:00 06:06 06:06:00
19 04/05/2013 01:42 05:57 04:15:00
19 05/05/2013 01:46 06:08 04:22:00
19 06/05/2013 01:47 06:04 04:17:00
19 07/05/2013 01:50 06:11 04:21:00
19 08/05/2013 01:52 06:13 04:21:00
19 09/05/2013 02:08 06:17 04:09:00
19 10/05/2013 00:00 05:58 05:58:00
19 11/05/2013 01:54 06:06 04:12:00
19 12/05/2013 01:46 06:13 04:27:00

pnasz
Posting Yak Master

101 Posts

Posted - 2013-05-13 : 08:18:22

data type of Time1 and time 2 is varchar

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-13 : 22:22:07

like this?


update @a
set TOT = dateadd(s, datediff(s, cast('00:00' as time), cast(Time2 as time)), cast(Time1 as time))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 00:14:33

[code]
UPDATE t
SET TOT= TOT1
FROM
(
SELECT TOT,CONVERT(varchar(8),DATEADD(ss,SUM(DATEDIFF(ss,0,Time1)+DATEDIFF(ss,0,Time2)) OVER (PARTITION BY ID,[Date]),0),108) AS TOT1
FROM Table
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pnasz
Posting Yak Master

101 Posts

Posted - 2013-05-14 : 01:57:06

Its working. Thanx a lot for help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 02:12:06

quote:
Originally posted by pnasz

Its working. Thanx a lot for help

Which one? mine or other suggestion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources