Same Date and Various Time Select in Single Row in sql server

Question

Post reply

Same Date and Various Time Select in Single Row in sql server

tamil.selvanmca

Ten Centuries

Points: 1367
More actions
September 2, 2011 at 5:44 am

#256703

EmpCode ShiftDateTime
2058 01/02/2011 13:43
2058 01/02/2011 20:12
2058 01/03/2011 14:04
2058 01/03/2011 20:13
2058 01/04/2011 14:09
2058 01/04/2011 20:12
I Want Show Like This
EmpCode ShiftDateTime TimeDetail
2058 01/02/2011 13:43 13:43,20:12
2058 01/03/2011 14:04 14:04,20:13
2058 01/04/2011 14:09 14:19,20:12
Please Help me

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Loundy SSCertifiable Points: 6080 More actions · Answer 1

What version of SQL you using? If 2008 you can use the TIME data type:

http://msdn.microsoft.com/en-us/library/bb677243.aspx

[font="Times New Roman"]There's no kill switch on awesome![/font]

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Use the rownumber function to create a unique row number for the starting / ending times.

Then use pivot functionality , i would prefer a manual pivot as shown here http://www.sqlservercentral.com/articles/T-SQL/73887/ over PIVOT , but the choice is yours, to bring the two rows into one.

Clear Sky SQL
My Blog[/url]

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 3

Probably should this kind of formatting in the front end, but if you must do this is sql, maybe this will help:SELECT

EmpCode,

MIN(ShiftDateTime),

CAST(CAST(MIN(ShiftDateTime) AS TIME) AS CHAR(5))

+ ','

+ CAST(CAST(MAX(ShiftDateTime) AS TIME) AS CHAR(5))

FROM

TableName

GROUP BY

EmpCode,

CAST(ShiftDateTime AS DATE)Mis-read teh requirement before, but I think this edit will work.

R.P.Rozema SSChampion Points: 12317 More actions · Answer 4

Like Dave suggested, use row_number(), then add a little math, a crosstab and some string manipulation...

declare @tbl table (

EmpCode int not null,

ShiftDateTime datetime not null

);

insert @tbl(EmpCode,ShiftDateTime)

select 2058, '01/02/2011 13:43'

union all select 2058, '01/02/2011 20:12'

union all select 2058, '01/03/2011 14:04'

union all select 2058, '01/03/2011 20:13'

union all select 2058, '01/04/2011 14:09'

union all select 2058, '01/04/2011 20:12';

with cte as (

select -1 + row_number() over (

partition by

EmpCode

order by t.ShiftDateTime) as Nr,

t.EmpCode,

t.ShiftDateTime

from @tbl t

)

select Shifts.EmpCode,

Shifts.StartTime as ShiftDateTime,

right( '0' + convert(varchar(2), datepart(hour, Shifts.StartTime)), 2) + ':' + right( '0' + convert(varchar(2), datepart(minute, Shifts.StartTime)), 2) +

',' + right( '0' + convert(varchar(2), datepart(hour, Shifts.EndTime)), 2) + ':' + right( '0' + convert(varchar(2), datepart(minute, Shifts.EndTime)), 2)

from (

select EmpCode,

t.Nr / 2 as ShiftNr,

max(case t.Nr % 2 when 0 then t.ShiftDateTime end) as StartTime,

max(case t.Nr % 2 when 1 then t.ShiftDateTime end) as EndTime

from cte t

group by t.EmpCode,

t.Nr / 2

) Shifts

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

tamil.selvanmca (9/2/2011)
EmpCode ShiftDateTime
2058 01/02/2011 13:43
2058 01/02/2011 20:12
2058 01/03/2011 14:04
2058 01/03/2011 20:13
2058 01/04/2011 14:09
2058 01/04/2011 20:12
I Want Show Like This
EmpCode ShiftDateTime TimeDetail
2058 01/02/2011 13:43 13:43,20:12
2058 01/03/2011 14:04 14:04,20:13
2058 01/04/2011 14:09 14:19,20:12
Please Help me

What do you want to do for someone who works past midnight into the next day?

Also, how are you guaranteeing that the pairs of times always start which a "Start" and are followed by an "End"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)