Solved

Break Up date time range into shifts and days

Posted on 2015-02-02
18
164 Views
Last Modified: 2015-02-09
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

Open in new window

0
Comment
Question by:bhieb
  • 9
  • 7
  • 2
18 Comments
 
LVL 31

Expert Comment

by:Rob Henson
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

by:bhieb
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

Open in new window

0
 
LVL 31

Expert Comment

by:Rob Henson
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

by:bhieb
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 45

Expert Comment

by:aikimark
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	

Open in new window

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

Open in new window


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)

Open in new window


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

Open in new window

0
 

Author Comment

by:bhieb
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

by:bhieb
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 45

Expert Comment

by:aikimark
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

by:bhieb
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 45

Expert Comment

by:aikimark
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

Open in new window

0
 

Author Comment

by:bhieb
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

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
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 45

Expert Comment

by:aikimark
ID: 40586843
Is the shifttime calculation needed?
0
 

Author Comment

by:bhieb
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

Open in new window


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

Open in new window

0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 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

Open in new window

0
 

Author Comment

by:bhieb
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 45

Expert Comment

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

Author Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now