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?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 16, 2013 at 8:07 am

  #1650459

  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: 7201

  More actions

  September 16, 2013 at 8:11 am

  #1650462

  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

  September 16, 2013 at 8:17 am

  #1650466

  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