add two column in one row - 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

General SQL Server Forums

New to SQL Server Programming

add two column in one row

Author

Topic

immad
Posting Yak Master

230 Posts

Posted - 2013-06-04 : 05:12:24

how to calculate two columns from different row

for example

this is actual data

date--------------------------spendtime
2013-01-08 00:00:00.000-----03:20:00--first row
2013-01-08 00:00:00.000-----01:19:00--second row
2013-01-09 00:00:00.000-----04:33:00--third row

this is what i want
-------------date------------spendtime
2013-01-08 00:00:00.000-----04:39

only that date 2013-01-08 00:00:00.000 spend time calculate and show in another column

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 05:18:20

how many times you will ask the same question?
What have you understood from solutions provided here?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=185683

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-04 : 05:42:21

[code]-- Query to sum up TIME column
DECLARE @tab TABLE( date DATETIME , spendtime TIME)
INSERT INTO @tab
SELECT '2013-01-08 00:00:00.000', '03:20:00' union all
SELECT '2013-01-08 00:00:00.000', '01:19:00' union all
SELECT '2013-01-09 00:00:00.000', '04:33:00'
/*this is what i want
-------------date------------spendtime
2013-01-08 00:00:00.000-----04:39*/
;WITH CTE
AS (SELECT [date], SUM(DATEDIFF(s, 0, spendtime)) TotalSecond
FROM @tab
GROUP BY [date]
HAVING COUNT(*) >1
)
SELECT DATE
, CONVERT(VARCHAR(10),TotalSecond/3600)+ '.' +
CONVERT(VARCHAR(20),TotalSecond%3600/60) + '.' +
CONVERT(VARCHAR(20),TotalSecond%3600%60) AS [Time] --Total of Time
FROM CTE;

--Alternate is:
SELECT DATE,
CAST( DATEADD(s, SUM(DATEDIFF(s, 0, spendtime)), 0) AS TIME)
FROM @tab
GROUP BY DATE[/code]

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 06:08:05

quote:

Originally posted by bandi

-- Query to sum up TIME column
DECLARE @tab TABLE( date DATETIME , spendtime TIME)
INSERT INTO @tab
SELECT '2013-01-08 00:00:00.000', '03:20:00' union all
SELECT '2013-01-08 00:00:00.000', '01:19:00' union all
SELECT '2013-01-09 00:00:00.000', '04:33:00' 
/*this is what i want
-------------date------------spendtime
2013-01-08 00:00:00.000-----04:39*/
;WITH CTE 
AS (SELECT [date], SUM(DATEDIFF(s, 0, spendtime)) TotalSecond
		FROM @tab
		GROUP BY [date]
		HAVING COUNT(*) >1
	)
SELECT DATE
	, CONVERT(VARCHAR(10),TotalSecond/3600)+ '.' + 
	  CONVERT(VARCHAR(20),TotalSecond%3600/60) + '.' + 
	  CONVERT(VARCHAR(20),TotalSecond%3600%60) AS [Time] --Total of Time
FROM CTE

--
Chandu

why split it up to components and then concat again? its anyways for a day (date value) so you can very well use dateadd datediff logic as in posted link as its not possible to spend more than 24 hrs on same day.

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-04 : 06:14:40

quote:
Originally posted by visakh16

how many times you will ask the same question?
What have you understood from solutions provided here?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=185683

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

this is another topic and the link that u showing to me is another i want to sum these two rows column

the link that showed me that problem is solved

immad uddin ahmed

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-04 : 06:26:53

quote:
Originally posted by immad

quote:
Originally posted by visakh16

how many times you will ask the same question?
What have you understood from solutions provided here?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=185683

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

this is another topic and the link that u showing to me is another i want to sum these two rows column

the link that showed me that problem is solved

immad uddin ahmed

thats good news, but may we ask what you've attempted so far to solve this issue? Can you share your attempt(s)? Since I believe if you've understood the logic of that (earlier) solution, you can solve this problem too.

Cheers
MIK

immad
Posting Yak Master

230 Posts

Posted - 2013-06-04 : 06:29:38

quote:
Originally posted by visakh16

quote:
Originally posted by bandi

-- Query to sum up TIME column
DECLARE @tab TABLE( date DATETIME , spendtime TIME)
INSERT INTO @tab
SELECT '2013-01-08 00:00:00.000', '03:20:00' union all
SELECT '2013-01-08 00:00:00.000', '01:19:00' union all
SELECT '2013-01-09 00:00:00.000', '04:33:00'
/*this is what i want
-------------date------------spendtime
2013-01-08 00:00:00.000-----04:39*/
;WITH CTE
AS (SELECT [date], SUM(DATEDIFF(s, 0, spendtime)) TotalSecond
FROM @tab
GROUP BY [date]
HAVING COUNT(*) >1
)
SELECT DATE
, CONVERT(VARCHAR(10),TotalSecond/3600)+ '.' +
CONVERT(VARCHAR(20),TotalSecond%3600/60) + '.' +
CONVERT(VARCHAR(20),TotalSecond%3600%60) AS [Time] --Total of Time
FROM CTE

--
Chandu

why split it up to components and then concat again? its anyways for a day (date value) so you can very well use dateadd datediff logic as in posted link as its not possible to spend more than 24 hrs on same day.

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

some times employee go out from a factory and come back
for example
employee come 9:17am and for some reason he timeout 12:37pm
then he come 17:21pm and timeout for go home at 18:40pm

so the data display like this

--------------date--------------------timein---------------------------timeout--------------------spendtime
2013-01-08 00:00:00.000------2013-01-08 09:17:00.000----2013-01-08 12:37:00.000----03:20:00
2013-01-08 00:00:00.000------2013-01-08 17:21:00.000----2013-01-08 18:40:00.000---------01:19:00

i want this

--------------date--------------------timein---------------------------timeout--------------------spendtime
2013-01-08 00:00:00.000------2013-01-08 09:17:00.000----2013-01-08 12:37:00.000----04:39:00
2013-01-08 00:00:00.000------2013-01-08 17:21:00.000----2013-01-08 18:40:00.000----

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 06:37:23

quote:
Originally posted by immad
some times employee go out from a factory and come back
for example
employee come 9:17am and for some reason he timeout 12:37pm
then he come 17:21pm and timeout for go home at 18:40pm

so the data display like this

--------------date--------------------timein---------------------------timeout--------------------spendtime
2013-01-08 00:00:00.000------2013-01-08 09:17:00.000----2013-01-08 12:37:00.000----03:20:00
2013-01-08 00:00:00.000------2013-01-08 17:21:00.000----2013-01-08 18:40:00.000---------01:19:00

i want this

--------------date--------------------timein---------------------------timeout--------------------spendtime
2013-01-08 00:00:00.000------2013-01-08 09:17:00.000----2013-01-08 12:37:00.000----04:39:00
2013-01-08 00:00:00.000------2013-01-08 17:21:00.000----2013-01-08 18:40:00.000----

immad uddin ahmed

I understood that
My post was not for you. I was just asking on Bandi's suggestion
You could have very well used earlier suggestion itself in the other link for this.
Understand and try to learn posted solutions. Dont always rely upon spoonfed answers for every issues. As MIK pointed out you could have easily applied the earlier suggestion here instead of again posting for the same.

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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-04 : 07:23:07

quote:
Originally posted by immad

quote:
Originally posted by visakh16

quote:
Originally posted by bandi

-- Query to sum up TIME column
DECLARE @tab TABLE( date DATETIME , spendtime TIME)
INSERT INTO @tab
SELECT '2013-01-08 00:00:00.000', '03:20:00' union all
SELECT '2013-01-08 00:00:00.000', '01:19:00' union all
SELECT '2013-01-09 00:00:00.000', '04:33:00'
/*this is what i want
-------------date------------spendtime
2013-01-08 00:00:00.000-----04:39*/
;WITH CTE
AS (SELECT [date], SUM(DATEDIFF(s, 0, spendtime)) TotalSecond
FROM @tab
GROUP BY [date]
HAVING COUNT(*) >1
)
SELECT DATE
, CONVERT(VARCHAR(10),TotalSecond/3600)+ '.' +
CONVERT(VARCHAR(20),TotalSecond%3600/60) + '.' +
CONVERT(VARCHAR(20),TotalSecond%3600%60) AS [Time] --Total of Time
FROM CTE

--
Chandu

why split it up to components and then concat again? its anyways for a day (date value) so you can very well use dateadd datediff logic as in posted link as its not possible to spend more than 24 hrs on same day.

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

some times employee go out from a factory and come back
for example
employee come 9:17am and for some reason he timeout 12:37pm
then he come 17:21pm and timeout for go home at 18:40pm

so the data display like this

--------------date--------------------timein---------------------------timeout--------------------spendtime
2013-01-08 00:00:00.000------2013-01-08 09:17:00.000----2013-01-08 12:37:00.000----03:20:00
2013-01-08 00:00:00.000------2013-01-08 17:21:00.000----2013-01-08 18:40:00.000---------01:19:00

i want this

--------------date--------------------timein---------------------------timeout--------------------spendtime
2013-01-08 00:00:00.000------2013-01-08 09:17:00.000----2013-01-08 12:37:00.000----04:39:00
2013-01-08 00:00:00.000------2013-01-08 17:21:00.000----2013-01-08 18:40:00.000----

immad uddin ahmed

Immad, the solution does make sense and is correct in my openion. In case if its wrong or should be a bit different, then at least you've got an idea how to make necessary changes. Google it and find, and still if you face any issue you can ask for help on that specific part. However the ouput you're looking for does not make sense at all. One of the basic concept in DBMS is that a table's record should provide a complete information. Information is composed of several unit pieces (columns).

To that end, when you say that you want this ouput --> representing the total time for a date only against the first row and nothing against the second row. Which logically make no sense - since looking at the corresponding time in and out for the same row, the spent time should have been 3:20 not 4:39. This would have made sense only if you specified the total time against the second row as well. Or to exclude the "in" and "outs" and just day/date level information (this is what the query is doing).

Cheers
MIK

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-04 : 08:11:43

quote:
Originally posted by immad

Dear Sir,

i under stand what you r saying actullay i am new in this field that why i am making some mistakes.i search this problem from google.

so far thanks for supporting me

immad uddin ahmed

That's alright, keep trying and if you face any problem, post it here (but at least after trying once yourself) :)

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources