September 2, 2008 at 12:07 pm
Was wondering if there was anyone out there that can help me on this one.
I have to take a table of time punches and I need segment those punches into two fields. (Company is not worried about logic at this time)
Data Example
TimeLogID, DepartmentID, AssociateID, PunchTime
1237689107822008-08-18 07:20:45.000
1263049107822008-08-18 11:00:12.000
1263069107822008-08-18 11:29:24.000
1263709107822008-08-18 15:40:36.000
1237699107832008-08-18 07:03:49.000
1263059107832008-08-18 11:00:17.000
1263079107832008-08-18 11:29:30.000
1263719107832008-08-18 15:35:41.000
I need to take the following data and make it look like this
DepartmentID, AssociateID, PunchStart, PunchEnd
9107828/18/08 7:20:45.0008/18/08 11:00:12.000
9107828/18/08 11:29:24.0008/18/08 15:40:36.000
9107838/18/08 7:03:49.0008/18/08 11:00:17.000
9107848/18/08 11:29:30.0008/18/08 15:35:41.000
I am not worried about the date format, I know it looks different (I did the second table by hand in excel)
I can do this in a cursor, but that is in my opinion the worst way to do this, and the first time I attempted it the processing time was close to 1min.
Any takers????
Thank you very much for anyone's help on this.
September 2, 2008 at 12:31 pm
Do you have anything that distinguishes an in-punch from an out-punch?
This is close, but without knowing an in from an out a little more logic will be required to distill it down to what you want:
[font="Courier New"]DECLARE @punches TABLE(id INT, dept_id INT, assoc_id INT, punch_time DATETIME)
INSERT INTO @punches
SELECT
123768, 9, 10782 ,'2008-08-18 07:20:45.000'
UNION ALL
SELECT
126304, 9, 10782 ,'2008-08-18 11:00:12.000'
UNION ALL
SELECT
126306, 9, 10782 ,'2008-08-18 11:29:24.000'
UNION ALL
SELECT
126370, 9, 10782 ,'2008-08-18 15:40:36.000'
UNION ALL
SELECT
123769, 9, 10783 ,'2008-08-18 07:03:49.000'
UNION ALL
SELECT
126305, 9, 10783 ,'2008-08-18 11:00:17.000'
UNION ALL
SELECT
126307, 9, 10783 ,'2008-08-18 11:29:30.000'
UNION ALL
SELECT
126371, 9, 10783 ,'2008-08-18 15:35:41.000'
;WITH ctePunches AS
(
SELECT
ROW_NUMBER() OVER (Partition BY dept_id, assoc_id ORDER BY dept_id, assoc_id, punch_time) AS row_id,
dept_id,
assoc_id,
punch_time
FROM
@punches
)
SELECT
A.dept_id,
A.assoc_id,
A.punch_time AS punch_start,
B.punch_time AS punch_end
FROM
ctePunches A LEFT JOIN
ctePunches B ON
A.dept_id = B.dept_id AND
A.assoc_Id = B.assoc_id AND
A.row_id = B.row_id - 1
ORDER BY
A.dept_id,
A.assoc_id,
A.punch_time
[/font]
This code is pretty much taken from this article: http://www.sqlservercentral.com/articles/T-SQL/62159/
Also note how I provided the test data so the next person can just copy and paste the data generation and work out a solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 2, 2008 at 12:46 pm
Looks good for the most part, however there is one line extra, but I can do a null statement on that I guess.
Thanks for the help
9107822008-08-18 07:20:45.0002008-08-18 11:00:12.000
9107822008-08-18 11:00:12.0002008-08-18 11:29:24.000
9107822008-08-18 11:29:24.0002008-08-18 15:40:36.000
9107822008-08-18 15:40:36.000NULL
9107832008-08-18 07:03:49.0002008-08-18 11:00:17.000
9107832008-08-18 11:00:17.0002008-08-18 11:29:30.000
9107832008-08-18 11:29:30.0002008-08-18 15:35:41.000
9107832008-08-18 15:35:41.000NULL
September 2, 2008 at 1:08 pm
I don't know if this will be a problem in your case, but the above code will track the gaps as if they were present.
For example, look at the second time of the first row and the first time of the second row. They're the same.
If the table doesn't have anything about what type of punch it was (which you could then include in the join), you might be able to assume that odd values of the Row_Number are In, and even are Out. That depends on the reliability of the data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2008 at 1:12 pm
Just tried it and not quite working right...
190167672008-08-17 01:30:00.0002008-08-17 09:30:00.000
190167672008-08-17 09:30:00.0002008-08-18 02:30:31.000
190167672008-08-18 02:30:31.0002008-08-18 09:00:00.000
190167672008-08-18 09:00:00.0002008-08-19 02:30:00.000
190167672008-08-19 02:30:00.0002008-08-19 10:30:00.000
190167672008-08-19 10:30:00.0002008-08-20 01:30:00.000
190167672008-08-20 01:30:00.0002008-08-20 10:00:21.000
190167672008-08-20 10:00:21.0002008-08-21 02:30:00.000
Unfortunetly I think this goes back to how I described my issue......
We are on track as to how the data should be displayed
However it is not getting the correct data... If you look you can see that the punchend is being restarted on the following line. I would like for it to move to the next value and not re-use that time.
This is how I would like the data to appear.
Should look like this
190167672008-08-17 01:30:00.0002008-08-17 09:30:00.000
190167672008-08-18 02:30:31.0002008-08-18 09:00:00.000
190167672008-08-19 02:30:00.0002008-08-19 10:30:00.000
190167672008-08-20 01:30:00.0002008-08-20 10:00:21.000
190167672008-08-21 02:30:00.0002008-08-21 10:31:19.000
Data Provided
190167672008-08-17 01:30:00.000300
190167672008-08-17 09:30:00.000303
190167672008-08-18 02:30:31.000300
190167672008-08-18 09:00:00.000303
190167672008-08-19 02:30:00.000300
190167672008-08-19 10:30:00.000303
190167672008-08-20 01:30:00.000300
190167672008-08-20 10:00:21.000303
190167672008-08-21 02:30:00.000300
190167672008-08-21 10:31:19.000303
Thank you very much for your help. Much appreciated.
September 2, 2008 at 1:13 pm
GSquared (9/2/2008)
I don't know if this will be a problem in your case, but the above code will track the gaps as if they were present.For example, look at the second time of the first row and the first time of the second row. They're the same.
If the table doesn't have anything about what type of punch it was (which you could then include in the join), you might be able to assume that odd values of the Row_Number are In, and even are Out. That depends on the reliability of the data.
Exactly why I asked about how you know in's and out's at the start of my first post. Without some definitive way of identifying that you will have a bit of a crapshoot on your hands.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 2, 2008 at 1:40 pm
parackson (9/2/2008)
Unfortunetly I think this goes back to how I described my issue......We are on track as to how the data should be displayed
However it is not getting the correct data... If you look you can see that the punchend is being restarted on the following line. I would like for it to move to the next value and not re-use that time.
This is how I would like the data to appear.
Which I basically said in my original post and why I asked how to identify a punch in or punch out. Without knowing whether a row is a punch in or punch out you would have to make some assumptions as GSquared said as well.
In your latest data provided you have an extra column with either 300 or 303 in it, does this identify whether the record is a punch in or punch out?
If you add this to the final select in my original post it eliminates the problem, but it also assumes that odd numbers are ALWAYS the punch in and evens the punch out:
Where
A.row_id % 2 = 1 -- % is the modulo operator
Now if the 300/303 column identifies the type of punch the work is much easier. Here is what I'd do if this assumption is correct:
[font="Courier New"]DECLARE @punches TABLE(id INT, dept_id INT, assoc_id INT, punch_time DATETIME, punch_type INT)
INSERT INTO @punches
SELECT
123768, 9, 10782 ,'2008-08-18 07:20:45.000', 300
UNION ALL
SELECT
126304, 9, 10782 ,'2008-08-18 11:00:12.000', 303
UNION ALL
SELECT
126306, 9, 10782 ,'2008-08-18 11:29:24.000', 300
UNION ALL
SELECT
126370, 9, 10782 ,'2008-08-18 15:40:36.000', 303
UNION ALL
SELECT
123769, 9, 10783 ,'2008-08-18 07:03:49.000', 300
UNION ALL
SELECT
126305, 9, 10783 ,'2008-08-18 11:00:17.000', 303
UNION ALL
SELECT
126307, 9, 10783 ,'2008-08-18 11:29:30.000', 300
UNION ALL
SELECT
126371, 9, 10783 ,'2008-08-18 15:35:41.000', 303
;WITH ctePunches AS
(
SELECT
ROW_NUMBER() OVER (Partition BY dept_id, assoc_id ORDER BY dept_id, assoc_id, punch_time) AS row_id,
dept_id,
assoc_id,
punch_time,
punch_type
FROM
@punches
)
SELECT
A.row_id,
A.dept_id,
A.assoc_id,
A.punch_time AS punch_start,
B.punch_time AS punch_end
FROM
ctePunches A LEFT JOIN
ctePunches B ON
A.dept_id = B.dept_id AND
A.assoc_Id = B.assoc_id AND
A.row_id = B.row_id - 1
WHERE
A.punch_type = 300 AND
B.punch_type = 303
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 2, 2008 at 1:46 pm
yes the last number is a punch type but not necessary a start or end.... Sometimes you will only have a Shift Start(300) and a Shift End(303).. Sometimes you might have all for in a day Shift Start(300), Lunch Start(301), Lunch End(302), Shift End(303).....
However I have figured out my issue thanks to the code that was earlier provided just need to make some tweaks to make it work... Unfortunetly there is a lot of assumptions going on in the programming but my bosses do not care to make it right just make it work at this time....
So with what what was provided and a couple of tweaks I got it to work for now.....
DECLARE @FromDate AS DATETIME
DECLARE @ToDate AS DATETIME
SET @FromDate = '8/17/2008 12am'
SET @ToDate = '8/23/2008 11:59pm'
SET NOCOUNT ON
--DECLARE ALL TEMP VARIABLES
DECLARE @TimeLogID AS INT
DECLARE @DepartmentID AS INT
DECLARE @AssociateID AS INT
DECLARE @CursorCount AS INT
DECLARE @TimeLogInd AS INT
--DROP ALL POSSIBLE TEMP TABLES
IF OBJECT_ID('TEMPDB..#TimeLogAssociates') IS NOT NULL DROP TABLE #TimeLogAssociates
IF OBJECT_ID('TEMPDB..#TimeLogPunches') IS NOT NULL DROP TABLE #TimeLogPunches
--PULL GROUPING OF DepartmentS AND ASSOCIATES
SELECT
TL.DepartmentID
,TL.AssociateID
INTO
#TimeLogAssociates
FROM
TimeLog AS TL WITH (NOLOCK)
WHERE
PunchTime BETWEEN @FromDate AND @ToDate
AND Void = 0
GROUP BY
DepartmentID
,AssociateID
ORDER BY
DepartmentID
,AssociateID
--GET ALL VALID PUNCHES FOR DATERANGE
SELECT
ROW_NUMBER() OVER (Partition BY TL.DepartmentID, TL.AssociateID ORDER BY TL.DepartmentID, TL.AssociateID, TL.PunchTime) AS row_id
,TL.DepartmentID
,TL.AssociateID
,TL.PunchTime
,TL.PunchTypeID
INTO
#TimeLogPunches
FROM
timelog AS TL WITH (NOLOCK)
INNER JOIN
#TimeLogAssociates AS TLA WITH (NOLOCK)
ON
TL.AssociateID = TLA.AssociateID
ANDTL.DepartmentID = TLA.DepartmentID
WHERE
TL.PunchTime BETWEEN @FromDate AND @ToDate
AND TL.Void = 0
ORDER BY
TL.DepartmentID
,TL.AssociateID
,TL.PunchTime
,TL.PunchTypeID
SELECT
ROW_NUMBER() OVER (Partition BY A.DepartmentID, A.AssociateID ORDER BY A.DepartmentID, A.AssociateID, A.PunchTime)%2 AS Valid
,A.DepartmentID
,A.AssociateID
,A.PunchTime AS PunchStart
,B.PunchTime AS PunchEnd
INTO
#TimeLogExport
FROM
#TimeLogPunches AS A
LEFT JOIN
#TimeLogPunches AS B
ON
A.DepartmentID = B.DepartmentID
AND A.AssociateID = B.AssociateID
AND A.row_id = B.row_id - 1
WHERE
B.PunchTime IS NOT NULL
ORDER BY
A.DepartmentID
,A.AssociateID
,A.PunchTime
SELECT
DepartmentID
,AssociateID
,PunchStart
,PunchEnd
FROM
#TimeLogExport WITH (NOLOCK)
WHERE
Valid = 1
--DROP ALL POSSIBLE TEMP TABLES
IF OBJECT_ID('TEMPDB..#TimeLogAssociates') IS NOT NULL DROP TABLE #TimeLogAssociates
IF OBJECT_ID('TEMPDB..#TimeLogPunches') IS NOT NULL DROP TABLE #TimeLogPunches
IF OBJECT_ID('TEMPDB..#TimeLogExport') IS NOT NULL DROP TABLE #TimeLogExport
You will see that I added the following to one of the final queries..
ROW_NUMBER() OVER (Partition BY A.DepartmentID, A.AssociateID ORDER BY A.DepartmentID, A.AssociateID, A.PunchTime)%2 AS Valid
This gives me a 0 or 1 value.... I noticed that basically every other line was not correct. I am all good now and I thank both of you for assisting me on this.
September 2, 2008 at 1:59 pm
parackson (9/2/2008)
yes the last number is a punch type but not necessary a start or end.... Sometimes you will only have a Shift Start(300) and a Shift End(303).. Sometimes you might have all for in a day Shift Start(300), Lunch Start(301), Lunch End(302), Shift End(303).....
You really should use these codes to make sure you have it right. It would be as simple as making your IN punches 300, 302 and out punches 301,303.
Unfortunetly there is a lot of assumptions going on in the programming but my bosses do not care to make it right just make it work at this time....
If I were you I would take issue with this comment, because it is either right (works) or wrong (doesn't work). I would think that there are some kinds of decisions to be made from the data and if it isn't right then the decisions are flawed. To me not providing accurate data means my code doesn't work. Just my opinion. I hope you have the mandate to "make it work, but we don't care if it is right" in writing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 2, 2008 at 2:06 pm
Trust me I fully understand what you are saying... I am in the wrong place and am just biding my time till I get somewhere else.... Boss is an Ms Access Hack that has no clue as to DB design or data integrity.
Wish I could get that in writing as well...
Thank you very much for your help on this... I hope my frustration did not come out too much in the last post.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply