Please help on reformatting this table into another format using TSQL

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  July 6, 2017 at 2:25 pm

  #1949823

  Luis Cazares - Thursday, July 6, 2017 6:16 AM

  Read the table only once 😉
  
SELECT CAST( WorkDate AS datetime) + CAST(WorkTime AS datetime)
   + CASE WHEN Starttime > Endtime THEN 1 ELSE 0 END AS WorkDateTime,
  EmpID,
  EmployeeName,
  Code + '-' + TimeName AS Code
FROM TimeTable
CROSS APPLY( VALUES(1,StartTime, 'Start' ),(2,EndTime, 'End'))x(id,WorkTime,TimeName)
ORDER BY EmpID, WorkDate, StartTime;


  Will that case statement give the right results for start date?  That work time frame spans both days, but your data will add 1 for both records would it not?

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• dcw8888

  SSC Eights!

  Points: 885

  More actions

  July 7, 2017 at 12:40 pm

  #1950014

  sgmunson - Thursday, July 6, 2017 2:19 PM

  Here you go:
  CREATE TABLE dbo.TimeTable (
      WorkDate date,
      EmpID int,
      EmployeeName Varchar(20),
      Code Varchar(20),
      Starttime time,
      Endtime time
  );
  INSERT INTO TimeTable (WorkDate, EmpID, EmployeeName, Code, Starttime, Endtime)
      VALUES    ('6/19/2017', 15, 'Tammy', 'ECDISP', '13:00:00', '20:30:00'),
              ('6/19/2017', 15, 'Tammy', 'Break', '20:30:00', '21:00:00'),
              ('6/19/2017', 15, 'Tammy', 'Lunch', '21:00:00', '22:00:00'),
              ('6/19/2017', 15, 'Tammy', 'ECDISP', '22:00:00', '01:00:00'),
              ('6/19/2017', 24, 'Dalstrom', 'Open', '7:00:00', '10:45:00'),
              ('6/19/2017', 24, 'Dalstrom', 'Break', '10:45:00', '11:15:00'),
              ('6/19/2017', 24, 'Dalstrom', 'Lunch', '11:15:00', '11:30:00'),
              ('6/19/2017', 24, 'Dalstrom', 'Open', '11:30:00', '15:00:00'),
              ('6/19/2017', 24, 'Dalstrom', 'Lunch', '15:00:00', '15:45:00'),
              ('6/19/2017', 24, 'Dalstrom', 'Open', '15:45:00', '19:00:00');

  SELECT X.WorkDateTime, X.EmpID, X.EmployeeName,
      CASE X.RN WHEN 1 THEN X.Code + '-Start' WHEN 2 THEN X.Code + '-End' END AS Code
  FROM (
      SELECT
          CONVERT(datetime, TS.WorkDate) + CONVERT(datetime, TS.Starttime) AS WorkDateTime,
          TS.EmpID,
          TS.EmployeeName,
          TS.Code,
          1 AS RN
      FROM dbo.TimeTable AS TS
      UNION ALL
      SELECT
          CASE
              WHEN TS.Endtime < TS.Starttime THEN DATEADD(day, 1, CONVERT(datetime, TS.WorkDate) + CONVERT(datetime, TS.Endtime))
              ELSE CONVERT(datetime, TS.WorkDate) + CONVERT(datetime, TS.Endtime)
          END AS WorkDateTime,
          TS.EmpID,
          TS.EmployeeName,
          TS.Code,
          2 AS RN
      FROM dbo.TimeTable AS TS
      ) AS X
  ORDER BY X.EmpID, X.WorkDateTime, LEFT(X.Code, CHARINDEX('-', X.Code)), X.RN DESC

  DROP TABLE dbo.TimeTable;

  Thank you so much Steve.  This query gave the correct result set!  You guys are great.  Thank you so much!

• Luis Cazares

  SSC Guru

  Points: 183700

  More actions

  July 7, 2017 at 1:13 pm

  #1950025

  sgmunson - Thursday, July 6, 2017 2:25 PM

  Luis Cazares - Thursday, July 6, 2017 6:16 AM

  Read the table only once 😉
  
SELECT CAST( WorkDate AS datetime) + CAST(WorkTime AS datetime)
   + CASE WHEN Starttime > Endtime THEN 1 ELSE 0 END AS WorkDateTime,
  EmpID,
  EmployeeName,
  Code + '-' + TimeName AS Code
FROM TimeTable
CROSS APPLY( VALUES(1,StartTime, 'Start' ),(2,EndTime, 'End'))x(id,WorkTime,TimeName)
ORDER BY EmpID, WorkDate, StartTime;


  Will that case statement give the right results for start date?  That work time frame spans both days, but your data will add 1 for both records would it not?

  You're right, I forgot to include a condition. It's easy to fix and there's still no need to read the table twice.
  
SELECT CAST( WorkDate AS datetime) + CAST(WorkTime AS datetime)
  + CASE WHEN Starttime > Endtime AND TimeName = 'End' THEN 1 ELSE 0 END AS WorkDateTime,
EmpID,
EmployeeName,
Code + '-' + TimeName AS Code
FROM TimeTable
CROSS APPLY( VALUES(1,StartTime, 'Start' ),(2,EndTime, 'End'))x(id,WorkTime,TimeName)
ORDER BY EmpID, WorkDate, StartTime;


  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