CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Saroj Kumar
Saroj Kumar

Reputation: 157

How to combine two rows into one?

How to combine two rows into one.

EMPCode  actHours    actDate     rowno
107 8.00        2013-01-21  1
107 8.30        2013-01-22  1
107 4.00        2013-01-23  1
107 4.00        2013-01-23  2
107 4.10        2013-01-24  1
107 4.20        2013-01-24  2
107 4.30        2013-01-25  1
107 4.30        2013-01-25  2

I want the result as follows:

actHours    actDate 
8.00        2013-01-21 
8.30        2013-01-22
8.00        2013-01-23 
8.30        2013-01-24
9.00        2013-01-25

Any help will be appreciated. Thanks in advance :-)

Upvotes: 0

Views: 359

Answers (3)

Ram Das
Ram Das

Reputation: 358

use this then,

with datecte
as
(
 select  actDate, SUM(DATEDIFF(MINUTE, '0:00:00', actHours)) actHours
 from (
   SELECT actDate,CAST(REPLACE(actHours,'.',':') as time) actHours
   FROM #tb1
      ) k 
 group by actDate
)


SELECT actDate,
actHours = RTRIM(actHours/60) + ':' + RIGHT('0' + RTRIM(actHours%60),2)
FROM datecte

Hope this gives you solution..

OK @praveen..

Upvotes: 0

Ram Das
Ram Das

Reputation: 358

what if data contains these..

108 3.40        2013-01-26  1
108 3.50        2013-01-26  2

if datatype is time, then u can use this query..

with datecte
as
(
 SELECT actDate,  SUM(DATEDIFF(MINUTE, '0:00:00', actHours)) actHours
 FROM Tb1
 GROUP BY actDate
)
SELECT actDate,
actHours = RTRIM(actHours/60) + ':' + RIGHT('0' + RTRIM(actHours%60),2)
FROM datecte

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this: 

SELECT SUM(actHours) actHours, actDate FROM Tbl GROUP BY actDate

Actually, if you provide datatypes of the columns, the answer can be more concrete.

Upvotes: 2

Related Questions