CODEWITHSUNDEEP
sqlsql-serversql-server-2014
BrianMichaels
BrianMichaels

Reputation: 522

Split Time Data into Multiple Rows

I would like to split an employees Time Data into multiple Rows base on what time they clock in. For this example if they clock in before 7 am then anything before that will be seperated into a new row of data and everything 7 am and after is the the other row. Here is an example of some data with the desired Result set. This is to help calculate Overtime. If they show up before their set shift then that time is OT pay

 Create Table TimeData(
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [EmployeeID] int NULL,
   [Date] date NULL,
   [TimeIn] time NULL,
   [TimeOut] time Null,
 )

Insert Into TimeData (EmployeeID,Date,Timein,TimeOut)
Values (100,'9/5/2017','06:00','15:00')

Result Sets

(100,'9/5/2017','06:00','7:00')

(100,'9/5/2017','07:00','15:00')

The Union All suggestions below pointed me in the right direction and I came up with this query set to create a New Time Record for the employees that punched in before 7:00 am and then update the original. 

BEGIN
 INSERT INTO Timedata( EmployeeID,Date,TimeIn,TimeOut)
 SELECT
  [EmployeeID],
  [Date],
  [TimeIn] AS [Time], '7:00'
 FROM
  TimeData
 WHERE
  [TimeIn] < '07:00:00'
END

BEGIN
 UPDATE TimeData Set TimeIn = '07:00' WHERE TimeIN < '07:00' AND TimeOut <> '7:00'
END

Upvotes: 2

Views: 347

Answers (5)

Serkan Arslan
Serkan Arslan

Reputation: 13403

We can use table driven method for the decision of employee time split.

SELECT 
    EmployeeID,
    Date,
    TimeIn = CASE WHEN TD.TimeIn > splitBegin THEN TimeIn ELSE splitBegin END,
    TimeOut = CASE WHEN TD.TimeOut < splitEnd THEN TimeOut ELSE splitEnd END 
from TimeData TD
INNER JOIN (VALUES('00:00','07:00'),('07:00','23:59')) T(splitBegin,splitEnd) 
    ON (TD.TimeIN > T.splitBegin AND TD.TimeIN < T.splitEnd) 
    OR (TD.TimeOut > T.splitBegin AND TD.TimeOut < T.splitEnd)

Upvotes: 1

DatabaseDave
DatabaseDave

Reputation: 101

I am not sure I understand your question, is something similar to this what you are looking for?

SELECT
    TimeData.EmployeeID,
    [Date],
    TimeData.[TimeIn] AS [Time]
FROM
    TimeData
WHERE
    [TimeIn] < '07:00:00'
UNION
SELECT
    TimeData.EmployeeID,
    [Date],
    TimeData.TimeOut As [Time]
FROM
    TimeData
ORDER BY 
    EmployeeID,
    [Date],
    [Time]

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82020

One quick method via a Cross Apply

Select A.ID
      ,A.EmployeeID
      ,A.Date
      ,B.*
 From  TimeData A
 Cross Apply  (values (TimeIn,case when TimeIn<'07:00' then cast('07:00' as time) else TimeOut end)
                     ,(case when TimeIn<'07:00' then cast('07:00' as time) else null end
                      ,case when TimeIn<'07:00' then TimeOut else null end) 
               ) B(TimeIn,TimeOut)
 Where B.TimeIn <> B.TimeOut

Returns -- Added a 7-3 record

enter image description here

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31795

USE UNION ALL and write a top query that gets the before 7 part, and a bottom query that gets the after 7 part.

Then simply ORDER BY EmployeeID, TimeIn.

Upvotes: 1

JNevill
JNevill

Reputation: 50308

Use a UNION query:

 SELECT EmployeeID, Date, TimeIn, '07:00' as TimeOut FROM timedata WHERE Timein < '07:00'
 UNION ALL
 SELECT EmployeeID, Date, '07:00', TimeOut FROM timedata WHERE TimeOut > '07:00';

First selecting all rows where the timein is less than 7am, and setting the appropriate TimeOut. Then selecting all records where TimeOut is over 7am and setting the appropriate TimeIn.

Upvotes: 2

Related Questions