April 24, 2019 at 3:02 pm
Once again, I come to rely on this helpful community.
For a while now, I have been struggling with a problem that (even if it is more or less solved with application procedural code) would greatly benefit if it could be solved at the database level.
Explaining the business case is a bit difficult but let me try:
We have a table that contains “SHIFT CODES”, where each shift code is defined by, you know, a code… and by “time boundaries”. For example, shift code 1ST is “delimited” by the time boundaries “9:00 AM” – “5:00 PM”
We have another table that contains “EMPLOYEE TIMECARDS” for a certain week ending. The table has room for 4 sets of IN/OUT times – and employees enter their own data. IN and OUT time entered here by the employee may not really match the time boundaries of a certain shift. (Incidentally: no control over data types)
It is up to a “process” to parse EACH DAY and EACH IN/OUT block to (possibly) split the total hours over different shifts.
For example:
(we use decimal representation for hours… common in payroll and time tracking)
If you were to run the attached script you could quickly recreate a simple test case and observe the expected results.Could this be actually solved with a procedure / function…?
I have been browsing and searching on this topic but haven't been able to find anything related...
Thanks in advance for any help or suggestion
Giorgio
IF UPPER(DB_NAME()) = 'MASTER'
SET NOEXEC ON;
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_PADDING ON;
GO
/*
Run this script ONCE to create test framework...
The table “SHIFTS” holds the description of work shifts.
Each shift is identified by a code and by a “time in” and “time out”.
The table “TIMECARDS” holds the timecards of employees, with in/out times.
In and out times may or may not exactly match the in and out time of a predefined shift code.
We must “parse” each day in the time card and generate one or more rows, and
calculate the total hours for each shift.
For example:
Given the shift codes boundaries in SHIFTS, and given the employee time in/out in TIMECARDS
we must write these lines into RESULTS:
SELECT * FROM SHIFTS WHERE customer_id = 1 ORDER BY shift_code
SELECT * FROM [dbo].[TIMECARDS] WHERE timecard_week_of = '2019-04-21'
AND customer_id = 1 AND employee_id = 100 AND timecard_day = '2019-04-15'
SELECT * FROM RESULTS where timecard_week_of = '2019-04-21'
AND customer_id = 1 AND employee_id = 100 AND timecard_day = '2019-04-15'
ORDER BY shift_code
*/
IF NOT EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'SHIFTS'
AND TABLE_SCHEMA = 'dbo'
)
BEGIN
CREATE TABLE [dbo].[SHIFTS]
([id] [INT] IDENTITY(1, 1) NOT NULL,
[customer_id] [INT] NOT NULL,
[shift_code] [VARCHAR](10) NOT NULL,
[time_in] [VARCHAR](10) NULL,
[time_out] [VARCHAR](10) NULL,
CONSTRAINT [PK_SHIFTS] PRIMARY KEY CLUSTERED([customer_id] ASC, [shift_code] ASC)
WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY];
END;
GO
IF NOT EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'TIMECARDS'
AND TABLE_SCHEMA = 'dbo'
)
CREATE TABLE [dbo].[TIMECARDS]
([id] [INT] IDENTITY(1, 1) NOT NULL,
[timecard_week_of] [DATE] NOT NULL,
[customer_id] [INT] NOT NULL,
[employee_id] [INT] NOT NULL,
[timecard_day] [DATE] NOT NULL,
[time_in_1] [VARCHAR](10) NULL,
[time_out_1] [VARCHAR](10) NULL,
[time_in_2] [VARCHAR](10) NULL,
[time_out_2] [VARCHAR](10) NULL,
[time_in_3] [VARCHAR](10) NULL,
[time_out_3] [VARCHAR](10) NULL,
[time_in_4] [VARCHAR](10) NULL,
[time_out_4] [VARCHAR](10) NULL,
CONSTRAINT [PK_TIMECARDS] PRIMARY KEY CLUSTERED([timecard_week_of] ASC, [customer_id] ASC, [employee_id] ASC, [timecard_day] ASC)
WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY];
IF NOT EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'RESULTS'
AND TABLE_SCHEMA = 'dbo'
)
CREATE TABLE [dbo].[RESULTS]
([id] [INT] IDENTITY(1, 1) NOT NULL,
[timecard_week_of] [DATE] NULL,
[customer_id] [INT] NULL,
[employee_id] [INT] NULL,
[timecard_day] [DATE] NULL,
[shift_code] [VARCHAR](10) NULL,
[hours] [NUMERIC](3, 2) NULL
)
ON [PRIMARY];
SET ANSI_PADDING OFF;
GO
INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-15' AS Date), N'2ND', CAST(0.50 AS Numeric(3, 2)))
GO
INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-15' AS Date), N'3RD', CAST(5.50 AS Numeric(3, 2)))
GO
INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-15' AS Date), N'3RD', CAST(1.50 AS Numeric(3, 2)))
GO
INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-16' AS Date), N'2ND', CAST(0.25 AS Numeric(3, 2)))
GO
INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-16' AS Date), N'3RD', CAST(5.50 AS Numeric(3, 2)))
GO
INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-16' AS Date), N'3RD', CAST(1.75 AS Numeric(3, 2)))
GO
INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-17' AS Date), N'2ND', CAST(0.25 AS Numeric(3, 2)))
GO
INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-17' AS Date), N'3RD', CAST(6.00 AS Numeric(3, 2)))
GO
INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-17' AS Date), N'3RD', CAST(2.00 AS Numeric(3, 2)))
GO
INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-18' AS Date), N'2ND', CAST(0.25 AS Numeric(3, 2)))
GO
INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-18' AS Date), N'3RD', CAST(6.00 AS Numeric(3, 2)))
GO
INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-18' AS Date), N'3RD', CAST(2.00 AS Numeric(3, 2)))
GO
INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-18' AS Date), N'1ST', CAST(1.50 AS Numeric(3, 2)))
GO
INSERT [dbo].[SHIFTS] ([customer_id], [shift_code], [time_in], [time_out]) VALUES (1, N'1ST', N'09:00 AM', N'05:30 PM')
GO
INSERT [dbo].[SHIFTS] ([customer_id], [shift_code], [time_in], [time_out]) VALUES (1, N'2ND', N'05:30 PM', N'12:30 AM')
GO
INSERT [dbo].[SHIFTS] ([customer_id], [shift_code], [time_in], [time_out]) VALUES (1, N'3RD', N'12:30 AM', N'09:00 AM')
GO
INSERT [dbo].[SHIFTS] ([customer_id], [shift_code], [time_in], [time_out]) VALUES (2, N'1ST', N'08:00 AM', N'04:00 PM')
GO
INSERT [dbo].[SHIFTS] ([customer_id], [shift_code], [time_in], [time_out]) VALUES (2, N'2ND', N'04:00 PM', N'12:00 AM')
GO
INSERT [dbo].[SHIFTS] ([customer_id], [shift_code], [time_in], [time_out]) VALUES (2, N'3RD', N'12:00 AM', N'08:00 AM')
INSERT [dbo].[TIMECARDS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [time_in_1], [time_out_1], [time_in_2], [time_out_2], [time_in_3], [time_out_3], [time_in_4], [time_out_4]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-15' AS Date), N'12:00 AM', N'06:00 AM', N'06:30 AM', N'08:00 AM', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[TIMECARDS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [time_in_1], [time_out_1], [time_in_2], [time_out_2], [time_in_3], [time_out_3], [time_in_4], [time_out_4]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-16' AS Date), N'12:15 AM', N'06:00 AM', N'06:30 AM', N'08:15 AM', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[TIMECARDS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [time_in_1], [time_out_1], [time_in_2], [time_out_2], [time_in_3], [time_out_3], [time_in_4], [time_out_4]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-17' AS Date), N'12:15 AM', N'06:30 AM', N'07:00 AM', N'09:00 AM', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[TIMECARDS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [time_in_1], [time_out_1], [time_in_2], [time_out_2], [time_in_3], [time_out_3], [time_in_4], [time_out_4]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-18' AS Date), N'12:15 AM', N'06:30 AM', N'07:00 AM', N'10:30 AM', NULL, NULL, NULL, NULL)
GO
/*
This is what the results of the "processing"
should look like
*/
SELECT * from dbo.RESULTS ORDER BY timecard_week_of, customer_id, employee_id, timecard_day, shift_code
SET NOEXEC OFF;
April 24, 2019 at 4:08 pm
This isn't right, and you'll want to convert to a function that takes in your punches and test it thoroughly, but it's the time I have at the moment. You can use case statements and dateadd/datediff to do math to get what you need
DECLARE @InPunch datetime
DECLARE @outPunch datetime
DECLARE @dateStart datetime
SET @InPunch = dateadd(hh,-4,getdate());
SET @outPunch = getdate()
SET @dateStart = DATEADD(dd,DATEDIFF(dd,'1/1/1900',@InPunch),'1/1/1900')
SELECT @InPunch AS inPunch,@outPunch AS outPunch,
@dateStart AS dateStart,
--datediff(minute,@dateStart,@InPunch
--)/60.0 AS timeBeforeStarting,
CASE
WHEN dateadd(hh,3,@dateStart) >= @InPunch
AND @dateStart <= @outPunch
THEN (
datediff(minute,dateadd(hh,3,@dateStart),@OutPunch)
-
datediff(minute,dateadd(hh,3,@dateStart),@InPunch)
)
/60.0
ELSE 0
END
+
CASE
WHEN dateadd(hh,24,@dateStart) >= @InPunch
AND dateadd(hh,17,@dateStart) <= @outPunch
THEN (
datediff(minute,dateadd(hh,17,@dateStart),@OutPunch)
)
/60.0
ELSE 0
END
AS ThirdShiftHours,
CASE
WHEN dateadd(hh,9,@dateStart) >= @InPunch
AND dateadd(hh,3,@dateStart) <= @outPunch
THEN (
datediff(minute,dateadd(hh,9,@dateStart),@OutPunch)
-
datediff(minute,dateadd(hh,9,@dateStart),@InPunch)
)
/60.0
ELSE 0
END AS SecondShiftHours,
CASE
WHEN dateadd(hh,17,@dateStart) >= @InPunch
AND dateadd(hh,9,@dateStart) <= @outPunch
THEN (
datediff(minute,dateadd(hh,17,@dateStart),@OutPunch)
-
datediff(minute,dateadd(hh,17,@dateStart),@InPunch)
)
/60.0
ELSE 0
END AS FirstShiftHours
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
April 24, 2019 at 9:40 pm
Jonathan,
Thank you. I have tested the code you have provided and is somewhat similar to what I came up with... but it falls into the same problems... I'll keep working on it...
Giorgio
April 25, 2019 at 5:34 pm
This was removed by the editor as SPAM
April 25, 2019 at 5:35 pm
This was removed by the editor as SPAM
April 25, 2019 at 5:38 pm
This was removed by the editor as SPAM
April 25, 2019 at 5:39 pm
This was removed by the editor as SPAM
April 25, 2019 at 5:41 pm
This was removed by the editor as SPAM
April 25, 2019 at 5:43 pm
This was removed by the editor as SPAM
April 25, 2019 at 6:01 pm
I keep trying to post on this thread with my solution, but the post is not being shown. If I post without code, the reply shows. I then try to add code, and the reply disappears again. I sent a personal message with my email address, so you can contact me directly so I can send my solution to you.
April 26, 2019 at 9:49 pm
>> We have a table that contains “SHIFT CODES”, where each shift code is defined by, you know, a code… and by “time boundaries”. For example, shift code 1ST is “delimited” by the time boundaries “09:00:00’ to “17:00:00” <<
SQL currently has both a DATE and a TIME data type. This is in addition to the DATETIME2(n) data type.
CREATE TABLE Shifts
(shift_code CHAR(3) NOT NULL PRIMARY KEY,
start_time TIME NOT NULL,
end_time TIME NOT NULL,
CHECK (start_time < end_time));
I’ve left off any constraints to make sure the shifts don’t overlap.
>> We have another table that contains “EMPLOYEE TIMECARDS” for a certain week ending. The table has room for 4 sets of IN/OUT times – and employees enter their own data. IN and OUT time entered here by the employee may not really match the time boundaries of a certain shift. <<
are you familiar with the ISO standard weekly date format? Instead of month and day it looks like this: “yyyyWww-d” where yyyy is the year, the W is punctuation, ww is between 01 and 52 or 53, the dash is more punctuation and d is the day (1= Monday, 7= Sunday). You can download calendars in this format and map them to the usual yyyy-mm-dd formatted dates
>> (Incidentally: no control over data types)<<
Unfortunately this means you’re screwed. You’re actually keeping temporal data in character strings! You might want to run over him in the parking lot, find out what Cody’s actually written, and then spent a couple of months replacing all of his bad stuff.
>> It is up to a “process” to parse EACH DAY and EACH IN/OUT block to (possibly) split the total hours over different shifts.
shift code 1ST is “delimited” by the time boundaries ‘09:00:00’ – ‘17:00:00’ (and there are other codes covering the entire 24 hours day)
employee enters a time in/out block 08:30:00– 17:30:00
that block would have to be parsed as something like
SHIFT CODE 3RD = 0.5 hours
SHIFT CODE 1ST = 8 hours
SHIFT CODE 2ND = 0.5 hours
(we use decimal representation for hours… common in payroll and time tracking) <<
Many decades ago, I had a similar problem. We rounded the durations to 15 minute blocks (xx:00:00 thru xx:14:59, xx:15:00 thru xx:29:59, xx:30:00 thru xx:44:59, xx:45:00 thru xx:59:59) as per union rules. 24 hrs per day* 4 blocks per hour = 96 blocks per day, 5 days per work week = 480, 50 work weeks per year = 24,000; a decade of lookups = 240,000 rows). Put a running total of blocks in each row, then subtract the starting block count from the ending block count.
We found that a simple lookup table was easier than trying to do temporal math. After all, SQL is a data language, not a computational language. Use a spreadsheet and load the table, so you are done for a decade.
Please post DDL and follow ANSI/ISO standards when asking for help.
April 28, 2019 at 9:10 pm
WITHAllShifts
AS(/* If the start date is before the end date, then the shift crosses a date boundary; adjust either the start or end date as needed, but not both. */
SELECTs.id,
s.customer_id,
s.shift_code,
/* Shift the end date one day later. */
--CAST(CONVERT(VARCHAR(10), t.timecard_day, 121) + ' ' + s.time_in AS DATETIME) AS shift_start_time,
--DATEADD(DAY, CASE WHEN CAST(time_out AS TIME) < CAST(time_in AS TIME) THEN 1 ELSE 0 END,
--CAST(CONVERT(VARCHAR(10), t.timecard_day, 121) + ' ' + s.time_out AS DATETIME)) AS shift_end_time
/* Shift the start date one day earlier. */
DATEADD(DAY, CASE WHEN CAST(time_out AS TIME) < CAST(time_in AS TIME) THEN -1 ELSE 0 END,
CAST(CONVERT(VARCHAR(10), t.timecard_day, 121) + ' ' + s.time_in AS DATETIME)) AS shift_start_time,
CAST(CONVERT(VARCHAR(10), t.timecard_day, 121) + ' ' + s.time_out AS DATETIME) AS shift_end_time
FROMShifts s
CROSS JOIN (
SELECTDISTINCT timecard_day
FROMTimeCards
) t
),
AllTimeCards
AS(
SELECTid, timecard_week_of, customer_id, employee_id, timecard_day,
CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_in_1 AS DATETIME) AS time_in,
CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_out_1 AS DATETIME) AS time_out
FROMTimeCards
WHEREtime_in_1 IS NOT NULL
UNION
SELECTid, timecard_week_of, customer_id, employee_id, timecard_day,
CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_in_2 AS DATETIME) AS time_in,
CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_out_2 AS DATETIME) AS time_out
FROMTimeCards
WHEREtime_in_2 IS NOT NULL
UNION
SELECTid, timecard_week_of, customer_id, employee_id, timecard_day,
CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_in_3 AS DATETIME) AS time_in,
CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_out_3 AS DATETIME) AS time_out
FROMTimeCards
WHEREtime_in_3 IS NOT NULL
UNION
SELECTid, timecard_week_of, customer_id, employee_id, timecard_day,
CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_in_4 AS DATETIME) AS time_in,
CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_out_4 AS DATETIME) AS time_out
FROMTimeCards
WHEREtime_in_4 IS NOT NULL
)
SELECTCAST(ROW_NUMBER() OVER (ORDER BY c.timecard_day, c.employee_id, c.time_in) AS INTEGER) AS id,
c.timecard_week_of,
c.customer_id,
c.employee_id,
c.timecard_day,
s.shift_code,
DATEDIFF(MINUTE, CASE WHEN c.time_in > s.shift_start_time THEN c.time_in ELSE s.shift_start_time END, CASE WHEN c.time_out < s.shift_end_time THEN c.time_out ELSE s.shift_end_time END) / 60.0 AS Hours,
s.shift_start_time,
s.shift_end_time,
c.time_in,
c.time_out
FROMAllTimeCards c
JOINAllShifts s
ONs.customer_id = c.customer_id
ANDc.time_in <= s.shift_end_time
ANDc.time_out >= s.shift_start_time
WHEREDATEDIFF(MINUTE, CASE WHEN c.time_in > s.shift_start_time THEN c.time_in ELSE s.shift_start_time END, CASE WHEN c.time_out < s.shift_end_time THEN c.time_out ELSE s.shift_end_time END) > 0
ORDER BY c.timecard_week_of,
c.customer_id,
c.employee_id,
c.timecard_day,
s.shift_code,
c.time_in,
s.shift_start_time
April 29, 2019 at 1:08 pm
Thanks for your suggestions and sorry about delay of reply - have been out of office for a couple of days/
Unfortunately, as good as they are, the suggestions do not really apply to the specific "business case". To simplify the question, I left out important details - for example the fact that the "shifts" table is filled by our clients with data related to their customers and, as such, can contain thousands of different variations... because shifts can vary by customer, and by customer departments...
Not to mention the fact that the application I am referring to has been in use for more than 10 years and has generated (is generating) tens of millions in revenues... so... it is a bit difficult to propose dramatic changes. Especially considering that this particular issue just emerged - because of a new "category" of clients... all dealing with specific businesses...
In any case, thanks again for the suggestions !
Giorgio Vidali
mailto:giorgio.vidali@abd.net
April 29, 2019 at 1:28 pm
Thanks for your suggestions and sorry about delay of reply - have been out of office for a couple of days/ Unfortunately, as good as they are, the suggestions do not really apply to the specific "business case". To simplify the question, I left out important details - for example the fact that the "shifts" table is filled by our clients with data related to their customers and, as such, can contain thousands of different variations... because shifts can vary by customer, and by customer departments... Not to mention the fact that the application I am referring to has been in use for more than 10 years and has generated (is generating) tens of millions in revenues... so... it is a bit difficult to propose dramatic changes. Especially considering that this particular issue just emerged - because of a new "category" of clients... all dealing with specific businesses... In any case, thanks again for the suggestions ! Giorgio Vidali mailto:giorgio.vidali@abd.net
So what is the "new category" of clients and what are the specific business cases that you're trying to solve? Or is that actually covered in your original post?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply