Please help on reformatting this table into another format using TSQL

  • 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!

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

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

  • 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

  • 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.

  • 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

  • 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......  

  • 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

  • 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

  • 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.

  • 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

  • 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
  • Humbled.


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

  • 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
  • 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)

Viewing 15 posts - 1 through 15 (of 17 total)

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