November 9, 2010 at 12:27 pm
I've been struggling and procrastinating with this for days and so any help is greatly appreciated.
The sample data in the three table expressions below represents employees hours worked and break times.
@OnBreak : represents a clock scan time of when a break began.
@OffBreak : represents a clock scan time of when a break ended.
@DaysHoursWorked : represents total hours worked for any given work-date.
I show the desired result below. Basically, I need to pull the one (DaysHoursWorked) table and the many (OnBreak and OffBreak) tables to a single row for each day worked and show Associate, Date, HoursWorked, and TotalBreakTime.
DECLARE @OnBreak TABLE (AssociateGUID uniqueidentifier, ScanCode int, ScanDateTime datetime)
INSERT INTO @OnBreak
SELECT'11E73546-B56A-41E1-9ABC-C7CCDAA06083','3','2010-10-30 07:40:00.000' UNION
SELECT'11E73546-B56A-41E1-9ABC-C7CCDAA06083','3','2010-11-02 08:45:00.000' UNION
SELECT'11E73546-B56A-41E1-9ABC-C7CCDAA06083','3','2010-11-02 10:55:00.000' UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','3','2010-11-01 08:45:00.000' UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','3','2010-11-02 08:45:00.000' UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','3','2010-11-03 08:55:00.000' UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','3','2010-11-04 09:00:00.000' UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','3','2010-11-06 11:40:00.000'
SELECT AssociateGUID, ScanCode, ScanDateTime FROM @OnBreak
-------------------------------------------------------------------------------------------
DECLARE @OffBreak TABLE (AssociateGUID uniqueidentifier, ScanCode int, ScanDateTime datetime)
INSERT INTO @OffBreak
SELECT'11E73546-B56A-41E1-9ABC-C7CCDAA06083','4','2010-10-30 08:00:00.000' UNION
SELECT'11E73546-B56A-41E1-9ABC-C7CCDAA06083','4','2010-11-02 09:15:00.000' UNION
SELECT'11E73546-B56A-41E1-9ABC-C7CCDAA06083','4','2010-11-02 11:25:00.000' UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','4','2010-11-01 09:15:00.000' UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','4','2010-11-02 08:55:00.000' UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','4','2010-11-03 09:25:00.000' UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','4','2010-11-04 09:20:00.000' UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','4','2010-11-06 11:55:00.000'
SELECT AssociateGUID, ScanCode, ScanDateTime FROM @OffBreak
----------------------------------------------------------------------------------------------
DECLARE @DaysHoursWorked TABLE (AssociateGUID uniqueidentifier, DateWorked datetime, HoursWorked float)
INSERT INTO @DaysHoursWorked
SELECT'11E73546-B56A-41E1-9ABC-C7CCDAA06083','2010-11-01 00:00:00.000','9.31667'UNION
SELECT'11E73546-B56A-41E1-9ABC-C7CCDAA06083','2010-10-30 00:00:00.000','2.55'UNION
SELECT'11E73546-B56A-41E1-9ABC-C7CCDAA06083','2010-11-02 00:00:00.000','6.6333'UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','2010-11-02 00:00:00.000','8.55'UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','2010-11-01 00:00:00.000','8.55'UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','2010-11-03 00:00:00.000','6.55'UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','2010-11-04 00:00:00.000','6.65'UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','2010-11-06 00:00:00.000','7.425'UNION
SELECT'70D8ABC7-4964-4D8A-B04A-06148CDD43E3','2010-11-05 00:00:00.000','8.55'
SELECT AssociateGUID, DateWorked, HoursWorked FROM @DaysHoursWorked
--Desired Result (for brevity I display only the last 12 bytes of associate guid)':
AssociateGUID WorkDate HoursWorked TotalBreakTime
C7CCDAA060832010-10-302.5520
C7CCDAA060832010-11-019.32NoBreak
C7CCDAA060832010-11-026.6360
06148CDD43E32010-11-018.5530
06148CDD43E32010-11-028.5510
06148CDD43E32010-11-036.5530
06148CDD43E32010-11-046.6520
06148CDD43E32010-11-058.55NoBreak
06148CDD43E32010-11-067.4315
The results should always show days when the associate had no breaks and had hours-worked. The results should sum multiple break times to a single total as with associate C7CCDAA06083 on 2010-11-02.
Once I have a solution (or a start to one) the exceptions will be returned dynamically based on parameterization of break-time and hours-worked thresholds which are based on individual state laws and/or company policy.
Thanks everyone.
November 9, 2010 at 1:04 pm
SSSolice (11/9/2010)
@OnBreak : represents a clock scan time of when a break began.@OffBreak : represents a clock scan time of when a break ended.
@DaysHoursWorked : represents total hours worked for any given work-date.
Working on your quandry, but out of curiousity, what business rules dictated splitting the on-break and off-break tables?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 9, 2010 at 1:22 pm
;WITH CTE1 AS
(
-- put the on/off breaks together, along with just the date of the break
SELECT AssociateGUID, ScanCode, ScanDateTime,
ScanDate = DateAdd(day, DateDiff(day, 0, ScanDateTime), 0)
FROM @OnBreak
UNION ALL
SELECT AssociateGUID, ScanCode, ScanDateTime,
ScanDate = DateAdd(day, DateDiff(day, 0, ScanDateTime), 0)
FROM @OffBreak
), CTE2 AS
(
-- get row numbers by associate/date (not time)
SELECT *,
RN = ROW_NUMBER() OVER (PARTITION BY AssociateGUID, ScanDate ORDER BY ScanDateTime)
FROM CTE1
), CTE3 AS
(
-- get the off break, with the total time of the break
SELECT t1.*,
BreakTotal = DateDiff(minute, t2.ScanDateTime, t1.ScanDateTime)
FROM CTE2 t1
JOIN CTE2 t2
ON t1.AssociateGUID = t2.AssociateGUID
AND t1.ScanDate = t2.ScanDate
AND t1.RN = t2.RN+1
WHERE t1.ScanCode = 4
)
-- get the @DaysHoursWorked, summing up the breaks
-- for each associate/date.
SELECT t1.AssociateGUID,
WorkDate = t1.DateWorked,
t1.HoursWorked,
TotalBreakTime = sum(t2.BreakTotal)
FROM @DaysHoursWorked t1
LEFT JOIN CTE3 t2
ON t1.AssociateGUID = t2.AssociateGUID
AND t1.DateWorked = t2.ScanDate
GROUP BY t1.AssociateGUID, t1.DateWorked, t1.HoursWorked
ORDER BY t1.AssociateGUID, t1.DateWorked;
@Craig: 😛 :w00t:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 9, 2010 at 1:22 pm
Hey thanks for the reply.
The break scans are actually a single table. The actual table contains millions of rows with many scan-types (I just need the 3 and 4 types). I've split the 3's and 4's out in the solution I've been working on because I thought it would be cleaner. I've got a brain block on this one! Probably over-thinking it. Anyway, I appreciate your direction.
November 9, 2010 at 1:38 pm
Thanks Wayne. Perfect. I'll study and use this one. Thanks a bunch!
November 9, 2010 at 1:53 pm
WayneS (11/9/2010)
@Craig: 😛 :w00t:
LOL
I was getting brainlocked on trying to get away with some MAXRECURSION 0 method and then had a job I've been working on (at work! Wow! Working at work! The evil!) get random data duplication so I got distracted. Nice code, btw. 😀
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 9, 2010 at 4:47 pm
SSSolice (11/9/2010)
Hey thanks for the reply.The break scans are actually a single table. The actual table contains millions of rows with many scan-types (I just need the 3 and 4 types). I've split the 3's and 4's out in the solution I've been working on because I thought it would be cleaner. I've got a brain block on this one! Probably over-thinking it. Anyway, I appreciate your direction.
Since they are already in one table, here is a modified solution.
To make the following work, first run this to simulate the one table with millions of rows:
DECLARE @Breaks TABLE (AssociateGUID uniqueidentifier, ScanCode int, ScanDateTime datetime);
INSERT INTO @Breaks
SELECT AssociateGUID, ScanCode, ScanDateTime
FROM @OnBreak
UNION ALL
SELECT AssociateGUID, ScanCode, ScanDateTime
FROM @OffBreak;
Here is the modified code:
;WITH CTE2 AS
(
-- get row numbers by associate/date (not time)
SELECT *,
ScanDate = DateAdd(day, DateDiff(day, 0, ScanDateTime), 0),
RN = ROW_NUMBER() OVER (PARTITION BY AssociateGUID, DateAdd(day, DateDiff(day, 0, ScanDateTime), 0) ORDER BY ScanDateTime)
FROM @Breaks
WHERE ScanCode IN (3,4)
), CTE3 AS
(
-- get the off break, with the total time of the break
SELECT t1.*,
BreakTotal = DateDiff(minute, t2.ScanDateTime, t1.ScanDateTime)
FROM CTE2 t1
JOIN CTE2 t2
ON t1.AssociateGUID = t2.AssociateGUID
AND t1.ScanDate = t2.ScanDate
AND t1.RN = t2.RN+1
WHERE t1.ScanCode = 4
)
-- get the @DaysHoursWorked, summing up the breaks
-- for each associate/date.
SELECT t1.AssociateGUID,
WorkDate = t1.DateWorked,
t1.HoursWorked,
TotalBreakTime = sum(t2.BreakTotal)
FROM @DaysHoursWorked t1
LEFT JOIN CTE3 t2
ON t1.AssociateGUID = t2.AssociateGUID
AND t1.DateWorked = t2.ScanDate
GROUP BY t1.AssociateGUID, t1.DateWorked, t1.HoursWorked
ORDER BY t1.AssociateGUID, t1.DateWorked;
Note that every time you reference a CTE, it performs that query again. You may find it more efficient to export the data into a temp table, and then have the CTEs reference the temp table with a smaller dataset, instead of the table with millions of rows. Then again, with a proper index, it may not be needed - so test both ways.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply