x
Solved

# Break Up date time range into shifts and days

Posted on 2015-02-02
Medium Priority
323 Views
Ok here is the problem.  I need some code to break apart any 2 date/times and split those records based on shift and days.

For example I have an in punch of 1/23/2015 5:52:18 AM and an out punch of 1/24/2015 4:24:54 AM. Since the end goal is to report on shift and/or date I need to break that single record up into this. Just kind of stuck on the logic to evaluate this. Ideally this would be handled in a sql stored proc, but i could also do it on the VBA front end. The shifts are 8 to 8's. (8a to 7:59:59 p and 8p to 7:59:59a)

``````Date	Start	        End	       Shift
23-Jan	5:52:18 AM	7:59:59 PM	1
23-Jan	8:00:00 PM	11:59:59 AM	2
24-Jan	12:00:00 AM	4:24:54 AM	2
``````
0
Question by:bhieb
• 9
• 7
• 2

LVL 35

Expert Comment

ID: 40584519
Does each record have some means of identifying person?

Earliest Date plus time related to that date = Start
Latest Date plus time releted to that date = Finish

Finish minus Start = Total time.

Or are you trying to replicate the Shift column?

According to your shift pattern, lets say:
8am to 8pm = shift 1
8pm to 8am = shift 2

Line 1 starts in shift 2 but finishes in shift 1, how would that be classified?
Line 3 starts in shift 1 but finishes in shift 2, same question!

Thanks
Rob H
0

Author Comment

ID: 40584531
My mistake I screwed up the output. It should read like this. Shift 1 is 8a to 759 p shift 2 is 8p to 759a. It needs a new record on each break both dayily at midnight and on shift hence the 2 records for crossing midnight for shift 2.

``````Date	Start	         End	        Shift
23-Jan	5:52:18 AM	7:59:59 AM	2
23-Jan	8:00:00 AM	7:59:59 PM	1
23-Jan	8:00:00 PM	11:59:59 AM	2
24-Jan	12:00:00 AM	4:24:54 AM	2
``````
0

LVL 35

Expert Comment

ID: 40584543
So the above is the Output required, how is the input data formatted?

Do you just have one In punch with Date & Time (23-Jan 5:52:18 AM) and one Out Punch (24-Jan 4:24:54 AM)?

Are dates and times in separate columns of input data or combined in one?

Where is the data, in an Excel sheet or in a database? If its the latter, I will bail out now as I know little about databases, sql etc., if its Excel, I can have a bash at it.

An upload of sample input data and required output would be good.

Thanks
Rob H
0

Author Comment

ID: 40584547
They are one field as date and time, in 2 fields IN and OUT. It is stored in an SQL database, but the logic should be the same either way.
0

LVL 47

Expert Comment

ID: 40585241
Since you can only represent a single date per row, you can do the following.
Use your current table for times.  In my test, I used the following field names to avoid reserved keyword confusion/conflict
``````ID	WorkDate	PunchIn  	PunchOut	Shift
1	1/23/2015	5:52:18 AM	11:30:00 PM
``````
I created a Shifts table:
``````ID	StartTime	EndTime 	Shift
1	8:00:00 AM	7:59:59 PM	1
2	8:00:00 PM	11:59:59 PM	2
3	12:00:00 AM	7:59:59 AM	2
``````

My query looks like this:
``````SELECT Q_2860837.WorkDate, Q_2860837.PunchIn, Q_2860837.PunchOut, Q_2860837_Shifts.Shift, CDate([endtime]-[punchin]) AS shifttime
FROM Q_2860837, Q_2860837_Shifts
WHERE (((Q_2860837.PunchIn) Between [StartTime] And [EndTime]))
UNION
SELECT Q_2860837.WorkDate, Q_2860837.PunchIn, Q_2860837.PunchOut, Q_2860837_Shifts.Shift, CDate([punchout]-[starttime]) AS shifttime
FROM Q_2860837, Q_2860837_Shifts
WHERE (((Q_2860837.Punchout) Between [StartTime] And [EndTime]))
UNION
SELECT Q_2860837.WorkDate, Q_2860837.PunchIn, Q_2860837.PunchOut, Q_2860837_Shifts.Shift, CDate([endtime]-[starttime]) AS shifttime
FROM Q_2860837, Q_2860837_Shifts
WHERE (Q_2860837.Punchin < [StartTime] And [EndTime] < Q_2860837.Punchout)
``````

The output of the query looks like this:
``````WorkDate	PunchIn  	PunchOut	Shift 	shifttime
1/23/2015	5:52:18 AM	11:30:00 PM	1      	11:59:59 AM
1/23/2015	5:52:18 AM	11:30:00 PM	2      	2:07:41 AM
1/23/2015	5:52:18 AM	11:30:00 PM	2      	3:30:00 AM
``````
0

Author Comment

ID: 40586238
Looks good the only problem is this isn't a person punch rather an equipment uptime punch so it is likely the time will always cross midnight and multiple days, that is why the date is currently part of the out punch.
0

Author Comment

ID: 40586245
I still might be able to make this work. I'll just have to use some logic to break up the WorkDates into their own records in the temp table.
0

LVL 47

Expert Comment

ID: 40586286
the date is currently part of the out punch
Your posted examples did not indicate this.

In the scenario you just described, you can drop the date column and have the punchin and punchout columns contain true datetime values.

If you are going to pursue this, you should read my Tally Table article: http:A_5410.html

I include an example of using a tally table to generate a series of date values.

=================
You might not need to do that, as you might add a calculation = 24 hrs * number of days between the punchin and punchout dates.
0

Author Comment

ID: 40586352
Not nitpicking as you are being very helpful, but my examples have all spanned multiple days, and I posted specifically about them being combined. Your solution only used 1 day, and thus did not answer my example exactly. But I digress, as again it was a helpful starting point. Let me give your article a read as well.
They are one field as date and time, in 2 fields IN and OUT.
0

LVL 47

Expert Comment

ID: 40586448
You posted multiple rows of data, one for each day.  I would expect the query to return similar shift time results for each date.  If you have differently configured data, that would be helpful.

Different Data Configuration Example:
``````Machine	Event	DateTime
ABC  	Start	1/15/2015 3:05:22
DEF  	Start	1/15/2015 4:17:00
GHI  	Start	1/16/2015 14:33:00
DEF  	Stop 	1/19/2015 23:15:44
DEF  	Start	1/20/2015 0:58:00
ABC  	Stop 	1/22/2015 1:01:00
``````
0

Author Comment

ID: 40586468
Multiple rows are the output the input is 2 punches.

Inputs
InPunch: 1/23/2015 5:52:18 AM (stored in 1 field as datetime)
OutPunch: 1/24/2015 4:24:54 AM (stored in 1 field as datetime)

Paramenters
Shift 1 = 8a to 7:59:59p
Shift 2 = 8p to 7:59:59a

Output
``````Date	Start	         End	        Shift
23-Jan	5:52:18 AM	7:59:59 AM	2
23-Jan	8:00:00 AM	7:59:59 PM	1
23-Jan	8:00:00 PM	11:59:59 AM	2
24-Jan	12:00:00 AM	4:24:54 AM	2
``````
0

LVL 47

Expert Comment

ID: 40586835
Are the inpunch and outpunch data two different fields on the same row or the same field in two different rows or parameters to some function or something else?

Is there only one machine or multiple machines producing punchin/punchout data?
0

LVL 47

Expert Comment

ID: 40586843
Is the shifttime calculation needed?
0

Author Comment

ID: 40586933
They are 2 fields on one row, not sure what you mean about shifttime calculation. I have a SQL calculated field that calculates duration between the 2 fields. Ideally the proc will do the following.

1. Break apart any durations that cross a shift and/or day (the point of this thread).
2. Update the shift field on any non crossing entries.
3. Mark the transaction as validated (so that the proc only looks at new stuff).

So for this example this is the record as it stands.
``````id_num	StatusID InTime	                OutTime    	        Duration Shift  Validated
42	4	 01/23/2015 5:52:18 AM	01/24/2015 4:24:54 AM	1353		0
``````

These are the records as they would be added. then the original record id_num 42 would be removed.
``````id_num	StatusID InTime	                OutTime    	        Duration Shift  Validated
43	4	 01/23/2015 5:52:18 AM	01/23/2015 7:59:59 AM	128	 2	1
44	4	 01/23/2015 8:00:00 AM	01/23/2015 7:59:59 PM	720	 1	1
45	4	 01/23/2015 8:00:00 PM	01/23/2015 11:59:59 PM	240	 2	1
46	4	 01/24/2015 12:00:00 AM	01/24/2015 4:24:54 AM	265	 2	1
``````
0

LVL 47

Accepted Solution

aikimark earned 2000 total points
ID: 40590193
In this example, TallyTable2 contains the numbers 1-10.  Therefore, we can track up to 10 days worth of continual up time.

The Query has more columns than is necessary for the purposes of communicating its function and calculations.
``````SELECT Q_2860837_Shifts.ID,
Q_2860837_Shifts.StartTime,
Q_2860837_Shifts.EndTime,
Q_2860837_Shifts.Shift,
Q_2860837.InTime, Q_2860837.OutTime,
Q_2860837.InTime AS ThisStartTime,
(DateValue([InTime]))+[endtime] AS ThisEndTime,
DateDiff("n",timevalue(Q_2860837.InTime),[endtime])+1 AS MinDiff

FROM Q_2860837, Q_2860837_Shifts
WHERE ((timevalue(Q_2860837.InTime) Between [StartTime] And [EndTime]))

UNION

SELECT Q_2860837_Shifts.ID,
Q_2860837_Shifts.StartTime,
Q_2860837_Shifts.EndTime,
Q_2860837_Shifts.Shift,
Q_2860837.InTime, Q_2860837.OutTime,
(DateValue([outTime]))+[starttime]  AS ThisStartTime,
Q_2860837.OutTime AS ThisEndTime,
DateDiff("n",[starttime],timevalue(Q_2860837.outTime)) AS MinDiff

FROM Q_2860837, Q_2860837_Shifts
WHERE ((timevalue(Q_2860837.OutTime) Between [StartTime] And [EndTime]))

UNION

SELECT Q_2860837_Shifts.ID,
Q_2860837_Shifts.StartTime,
Q_2860837_Shifts.EndTime,
Q_2860837_Shifts.Shift,
Q_2860837.InTime, Q_2860837.OutTime,
(DateValue([InTime])+[tallytable2].[id]-1)+[starttime] AS ThisStartTime,
(DateValue([InTime])+[tallytable2].[id]-1)+[endtime] AS ThisEndTime,
DateDiff("n",[starttime],[endtime])+1 AS MinDiff

FROM Q_2860837, TallyTable2, Q_2860837_Shifts
WHERE
((DateValue([InTime])+[tallytable2].[id]-1)+[starttime] > [intime] )
And
((DateValue([InTime])+[tallytable2].[id]-1)+[endtime] < [outtime] )

Order By ThisStartTime
``````
0

Author Comment

ID: 40598281
Looks interesting, I won't have time to test it this week. You are using timevalue and I'm on SQL 2005 (we are migrating to 2012 this month). Since I'm not getting any other methods, I'll just assume yours works and toss you the points.
0

LVL 47

Expert Comment

ID: 40598868
I used MSAccess to develop the query.  You will probably need to cast the value as a time value.
0

Author Comment

ID: 40598911
Even better I can test there too.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.