SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need to Add time and them group with total time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pnasz
Posting Yak Master

101 Posts

Posted - 05/13/2013 :  06:58:35  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 05/13/2013 :  07:02:14  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

pnasz
Posting Yak Master

101 Posts

Posted - 05/13/2013 :  07:20:06  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 05/13/2013 :  07:34:07  Show Profile  Reply with Quote
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
Go to Top of Page

pnasz
Posting Yak Master

101 Posts

Posted - 05/13/2013 :  07:41:33  Show Profile  Reply with Quote
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.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 05/13/2013 :  07:46:49  Show Profile  Reply with Quote
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
Go to Top of Page

pnasz
Posting Yak Master

101 Posts

Posted - 05/13/2013 :  07:52:52  Show Profile  Reply with Quote
Getting error

"DATEDIFF" is not a valid windowing function, and cannot be used with the OVER clause.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 05/13/2013 :  07:59:15  Show Profile  Reply with Quote
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
Go to Top of Page

pnasz
Posting Yak Master

101 Posts

Posted - 05/13/2013 :  08:16:38  Show Profile  Reply with Quote


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
Go to Top of Page

pnasz
Posting Yak Master

101 Posts

Posted - 05/13/2013 :  08:18:22  Show Profile  Reply with Quote
data type of Time1 and time 2 is varchar
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
964 Posts

Posted - 05/13/2013 :  22:22:07  Show Profile  Reply with Quote
like this?

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

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 05/14/2013 :  00:14:33  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

pnasz
Posting Yak Master

101 Posts

Posted - 05/14/2013 :  01:57:06  Show Profile  Reply with Quote
Its working. Thanx a lot for help
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 05/14/2013 :  02:12:06  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000