Please help on reformatting this table into another format using TSQL

• dcw8888

  SSC Eights!

  Points: 885

  More actions

  July 5, 2017 at 3:08 pm

  #338657

  Hi guys,

  I will really need you guys help on reformatting a table into another format for export in CSV file.

  I have a following table with sample data like below:
  
  
CREATE TABLE TimeTable
(WorkDate date,
EmpID int,
EmployeeName Varchar(20),
Code Varchar(20),
Starttime time,
Endtime time)
GO
INSERT INTO TimeTable
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', '1: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');


  And I want the output to be in the following format:
  

  Would you guys give me a hand on how to achieve this with T-SQL?

  Thanks a lot!

• pietlinden

  SSC Guru

  Points: 63428

  More actions

  July 5, 2017 at 3:27 pm

  #1949577

  Do you mean as HTML? "This format" and showing a picture doesn't really help.

• dcw8888

  SSC Eights!

  Points: 885

  More actions

  July 5, 2017 at 3:31 pm

  #1949578

  no, not html format.  As long as the query result is the same as what's in the picture, I am good.

• autoexcrement

  SSCertifiable

  Points: 5951

  More actions

  July 5, 2017 at 3:31 pm

  #1949579

  This?
  SELECT
    CAST(WorkDate AS VARCHAR(25)) + ' ' + CAST(Starttime AS VARCHAR(25)) AS WorkDateTime, 
     EmpID, 
     EmployeeName, 
     Code + '-Start' AS Code
FROM TimeTable
UNION ALL
SELECT 
    CAST(WorkDate AS VARCHAR(25)) + ' ' + CAST(Endtime AS VARCHAR(25)) AS WorkDateTime, 
     EmpID, 
     EmployeeName, 
     Code + '-End' AS Code
FROM TimeTable 

  ---

  "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

• dcw8888

  SSC Eights!

  Points: 885

  More actions

  July 5, 2017 at 3:37 pm

  #1949580

  autoexcrement - Wednesday, July 5, 2017 3:31 PM

  This?
  SELECT
    CAST(WorkDate AS VARCHAR(25)) + ' ' + CAST(Starttime AS VARCHAR(25)) AS WorkDateTime, 
     EmpID, 
     EmployeeName, 
     Code + '-Start' AS Code
FROM TimeTable
UNION ALL
SELECT 
    CAST(WorkDate AS VARCHAR(25)) + ' ' + CAST(Endtime AS VARCHAR(25)) AS WorkDateTime, 
     EmpID, 
     EmployeeName, 
     Code + '-End' AS Code
FROM TimeTable 

  Thanks.  But the order of the records are not the same as original table.  And the end date time needs to be on June 20, 2017 @1:00am instead of June 19, 2017 @1:00am for Tammy.

• autoexcrement

  SSCertifiable

  Points: 5951

  More actions

  July 5, 2017 at 3:47 pm

  #1949582

  If you need help ordering the results, try the ORDER BY statement. 

  To accomplish the other part, you will probably need to use a CASE statement in the second Select statement. Something like:

  CASE
     WHEN DATEDIFF(second, Startime, Endtime) < 0 THEN CAST(DATEADD(day, 1, WorkDate) AS VARCHAR(25)) + ' ' + CAST(Endtime AS VARCHAR(25)) 
     ELSE CAST(WorkDate AS VARCHAR(25)) + ' ' + CAST(Endtime AS VARCHAR(25))
END AS WorkDateTime

  ---

  "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

• dcw8888

  SSC Eights!

  Points: 885

  More actions

  July 5, 2017 at 4:01 pm

  #1949584

  autoexcrement - Wednesday, July 5, 2017 3:47 PM

  If you need help ordering the results, try the ORDER BY statement. 

  To accomplish the other part, you will probably need to use a CASE statement in the second Select statement. Something like:

  CASE
     WHEN DATEDIFF(second, Startime, Endtime) < 0 THEN CAST(DATEADD(day, 1, WorkDate) AS VARCHAR(25)) + ' ' + CAST(Endtime AS VARCHAR(25)) 
     ELSE CAST(WorkDate AS VARCHAR(25)) + ' ' + CAST(Endtime AS VARCHAR(25))
END AS WorkDateTime

  Thank you so much autoexcrement!!  The case statement for the date work wonderfully!  I did try order by and different combination of columns however, I still can't get it to order the way I want it to be......  

• autoexcrement

  SSCertifiable

  Points: 5951

  More actions

  July 5, 2017 at 4:10 pm

  #1949585

  I'm really not sure how you want it sorted, would you like to explain it to us?

  ---

  "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

• autoexcrement

  SSCertifiable

  Points: 5951

  More actions

  July 5, 2017 at 4:16 pm

  #1949586

  Maybe you need to cast the whole "date + time" as a proper DateTime and sort by that? 

  
WITH CTE AS
(
SELECT
  CAST(CAST(WorkDate AS VARCHAR(25)) + ' ' + CAST(Starttime AS VARCHAR(25)) AS DATETIME2(0)) AS WorkDateTime,
  EmpID,
  EmployeeName,
  Code + '-Start' AS Code
FROM #TimeTable
UNION ALL
SELECT
  CAST(
    CASE
     WHEN DATEDIFF(second, Starttime, Endtime) < 0 THEN CAST(DATEADD(day, 1, WorkDate) AS VARCHAR(25))
     ELSE CAST(WorkDate AS VARCHAR(25))
    END
         + ' ' + CAST(Endtime AS VARCHAR(25))
  AS DATETIME2(0)) AS WorkDateTime,
  EmpID,
  EmployeeName,
  Code + '-End' AS Code
FROM #TimeTable
)
SELECT * FROM CTE ORDER BY EmpID, WorkDateTime


  ---

  "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

• dcw8888

  SSC Eights!

  Points: 885

  More actions

  July 5, 2017 at 4:17 pm

  #1949587

  autoexcrement - Wednesday, July 5, 2017 4:10 PM

  I'm really not sure how you want it sorted, would you like to explain it to us?

  The output and sorting should look like this:
  
  Order by the datetime stamp and by employeename and Code.

• autoexcrement

  SSCertifiable

  Points: 5951

  More actions

  July 5, 2017 at 4:20 pm

  #1949588

  Well refer to my last post and see if you can sort out the ORDER BY on your own. I'm sure you can. 🙂

  ---

  "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  July 6, 2017 at 6:16 am

  #1949676

  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;


  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

• autoexcrement

  SSCertifiable

  Points: 5951

  More actions

  July 6, 2017 at 7:58 am

  #1949720

  Humbled.

  ---

  "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  July 6, 2017 at 10:44 am

  #1949768

  autoexcrement - Thursday, July 6, 2017 7:58 AM

  Humbled.

  We all had to learn at some point. I learned this technique from the following article: An Alternative (Better?) Method to UNPIVOT (SQL Spackle) - SQLServerCentral

  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

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  July 6, 2017 at 2:19 pm

  #1949822

  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;

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