July 5, 2017 at 3:08 pm
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!
July 5, 2017 at 3:27 pm
Do you mean as HTML? "This format" and showing a picture doesn't really help.
July 5, 2017 at 3:31 pm
no, not html format. As long as the query result is the same as what's in the picture, I am good.
July 5, 2017 at 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
July 5, 2017 at 3:37 pm
autoexcrement - Wednesday, July 5, 2017 3:31 PMThis?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.
July 5, 2017 at 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
July 5, 2017 at 4:01 pm
autoexcrement - Wednesday, July 5, 2017 3:47 PMIf 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......
July 5, 2017 at 4:10 pm
I'm really not sure how you want it sorted, would you like to explain it to us?
July 5, 2017 at 4:16 pm
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
July 5, 2017 at 4:17 pm
autoexcrement - Wednesday, July 5, 2017 4:10 PMI'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.
July 5, 2017 at 4:20 pm
Well refer to my last post and see if you can sort out the ORDER BY on your own. I'm sure you can. 🙂
July 6, 2017 at 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;
July 6, 2017 at 7:58 am
Humbled.
July 6, 2017 at 10:44 am
autoexcrement - Thursday, July 6, 2017 7:58 AMHumbled.
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
July 6, 2017 at 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;
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