Splitting row into 2 rows

Question

Post reply

Splitting row into 2 rows

a2zwd

SSCrazy

Points: 2356
More actions
September 16, 2013 at 7:12 am

#282538

Hi,
I have a table witht he following structure.
EmpNo DutyId StartDate EndDate
-------------------------------------------------
101 01 2013-08-31 22:00:00 2013-09-01 06:00:00
The employee duty spanned between 2 months.
I want to split this into individual days and number of hours for each day.
For example,
101 01 2013-08-31 2 Hrs
101 01 2013-09-01 6 Hrs
How can I do this in single query?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

The easiest way is to do it (at least for me) is with a UNION ALL or a CROSS APPLY "unpivot"

WITH Sample_Data(EmpNo, DutyId, StartDate, EndDate) AS(

SELECT 101,

'01',

CAST( '2013-08-31 22:00:00' AS datetime),

CAST( '2013-09-01 06:00:00' AS datetime))

SELECT EmpNo, DutyId, Value

FROM Sample_Data sd

CROSS APPLY( VALUES('Start', StartDate), ('End', EndDate))x(Description, Value);

WITH Sample_Data(EmpNo, DutyId, StartDate, EndDate) AS(

SELECT 101,

'01',

CAST( '2013-08-31 22:00:00' AS datetime),

CAST( '2013-09-01 06:00:00' AS datetime))

SELECT EmpNo, DutyId, StartDate

FROM Sample_Data sd

UNION ALL

SELECT EmpNo, DutyId, EndDate

FROM Sample_Data sd;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 2

Hi A2zwd

Try this:

SELECT [EmpNo]

,[DutyId]

,CAST(FLOOR(CAST([EndDate] as float)) as datetime) as NewDate

,DATEDIFF(hh,[EndDate],CAST(FLOOR(CAST(DATEADD(dd,1,[EndDate]) as float)) as datetime)) as Hrs

FROM [YourTable]

UNION ALL

SELECT [EmpNo]

,[DutyId]

,CAST(FLOOR(CAST([StartDate] as float)) as datetime) as NewDate

,DATEDIFF(hh,CAST(FLOOR(CAST([StartDate] as float)) as datetime),[StartDate]) as Hrs

FROM [YourTable]

Regards

Mike

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I wouldn't rely on the float conversion. Instead you could CAST as DATE and TIME. Those datatypes can give you just the information you need. Or a direct conversion to string data.

WITH Sample_Data(EmpNo, DutyId, StartDate, EndDate) AS(

SELECT 101,

'01',

CAST( '2013-08-31 22:00:00' AS datetime),

CAST( '2013-09-01 06:00:00' AS datetime))

SELECT EmpNo, DutyId, CAST( Value AS Date), CONVERT(char(2), Value, 8) + ' Hrs'

FROM Sample_Data sd

CROSS APPLY( VALUES('Start', StartDate), ('End', EndDate))x(Description, Value);

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2