July 14, 2014 at 4:14 pm
Hello Folks,
I'm in need of your kind help here, I have two tables listed below, the first table has employees pay data, the second table has a day and time reset data, both tables are joined using the ID column, so based on second table's reset day and time I need to grab the hours and also put a flag of 'F' in the last column to indicate that the hours occurred on the reset day at or after the reset time hours are 'special'.
Some days employee work with taking a break like on Monday the 23rd, and some days they work straight like on Tuesday the 24th which happens to be the reset day for this employee.
My challenge is to get the breakdown of hours on the reset days, in the example below this employee worked 10 hours straight from 7am to 5pm, I would like to break it down to 7am to 11am and then 11am to 5pm.
The other (non RESET) days I can just get the hours from time difference between Start time and End time.
I hope this is doable 🙂
Pay Data Table
WITH SampleData (PERSON,[DATE],[STARTIME],[ENDTIME],,[ID],[DOW]) AS
(
-- Tuesday's Reset
SELECT 901,'06/23/2014','07:00:00.0000000','11:00:00.0000000','Regular',51,'Monday'
UNION ALL SELECT 901,'06/23/2014','11:30:00.0000000','17:00:00.0000000','Regular',51,'Monday'
UNION ALL SELECT 901,'06/24/2014','07:00:00.0000000','17:00:00.0000000','Regular',51,'Tuesday'
UNION ALL SELECT 901,'06/24/2014','11:00:00.0000000','12:00:00.0000000','Jury',51,'Tuesday'
UNION ALL SELECT 901,'06/25/2014','07:00:00.0000000','11:00:00.0000000','Regular',51,'Wednesday'
UNION ALL SELECT 901,'06/25/2014','11:30:00.0000000','17:00:00.0000000','Regular',51,'Wednesday'
UNION ALL SELECT 901,'06/26/2014','07:00:00.0000000','17:00:00.0000000','Regular',51,'Thursday'
UNION ALL SELECT 901,'06/27/2014','07:00:00.0000000','11:00:00.0000000','Regular',51,'Friday'
UNION ALL SELECT 901,'06/27/2014','11:30:00.0000000','16:30:00.0000000','Regular',51,'Friday'
)
SELECT *
FROM SampleData;
Reset Data Table
WITH SampleData1 ([ID],[RESETTIME],[DOW]) AS
(
-- First employee -
SELECT 51,'11:00:00.0000000','Tuesday'
UNION ALL SELECT 53,'13:00:00.0000000','Wednesday'
)
SELECT *
FROM SampleData1;
Current Pay Data Table Results
PERSONDATE STARTIME ENDTIME CODEIDDOW
90106/23/201407:00:00.000000011:00:00.0000000Regular51Monday
90106/23/201411:30:00.000000017:00:00.0000000Regular51Monday
90106/24/201407:00:00.000000017:00:00.0000000Regular51Tuesday
90106/24/201411:00:00.000000012:00:00.0000000Jury51Tuesday
90106/25/201407:00:00.000000011:00:00.0000000Regular51Wednesday
90106/25/201411:30:00.000000017:00:00.0000000Regular51Wednesday
90106/26/201407:00:00.000000017:00:00.0000000Regular51Thursday
90106/27/201407:00:00.000000011:00:00.0000000Regular51Friday
90106/27/201411:30:00.000000016:30:00.0000000Regular51Friday
Desired Results After joining both tables - Employee 901's reset day and time is Tuesday's at 11am so his hours on the 24th split up, 7am to 11am (4.00 Hours) and 11am to 5pm (6.00 hours) also after 11am the FLAG will have an 'B'.
PERSONDATE HOURSCODE ID DOW FLAG
90106/23/20144.00Regular 51 Monday
90106/23/20145.5Regular 51 Monday
90106/24/20144.0Regular 51 Tuesday
90106/24/20146.0Regular 51 TuesdayB
90106/24/20141.0Jury 51 Tuesday
90106/25/20144.0Regular 51 Wednesday
90106/25/20145.5Regular 51 Wednesday
90106/26/201410.0Regular 51 Thursday
90106/27/20144.00Regular 51 Friday
90106/27/20145.5Regular 51 Friday
July 15, 2014 at 2:49 pm
Hey there,
I suspect that the reason no-one has helped you yet is that we don't have any data-types to marry up your sample data with and you haven't explained how the "B" comes in to play. Using just your sample data, I spent 5 mins and knocked this out: -
WITH PayData (PERSON,[DATE],[STARTIME],[ENDTIME],,[ID],[DOW]) AS
(
-- Tuesday's Reset
SELECT 901,'06/23/2014','07:00:00.0000000','11:00:00.0000000','Regular',51,'Monday'
UNION ALL SELECT 901,'06/23/2014','11:30:00.0000000','17:00:00.0000000','Regular',51,'Monday'
UNION ALL SELECT 901,'06/24/2014','07:00:00.0000000','17:00:00.0000000','Regular',51,'Tuesday'
UNION ALL SELECT 901,'06/24/2014','11:00:00.0000000','12:00:00.0000000','Jury',51,'Tuesday'
UNION ALL SELECT 901,'06/25/2014','07:00:00.0000000','11:00:00.0000000','Regular',51,'Wednesday'
UNION ALL SELECT 901,'06/25/2014','11:30:00.0000000','17:00:00.0000000','Regular',51,'Wednesday'
UNION ALL SELECT 901,'06/26/2014','07:00:00.0000000','17:00:00.0000000','Regular',51,'Thursday'
UNION ALL SELECT 901,'06/27/2014','07:00:00.0000000','11:00:00.0000000','Regular',51,'Friday'
UNION ALL SELECT 901,'06/27/2014','11:30:00.0000000','16:30:00.0000000','Regular',51,'Friday'
),
ResetData ([ID],[RESETTIME],[DOW]) AS
(
-- First employee -
SELECT 51,'11:00:00.0000000','Tuesday'
UNION ALL SELECT 53,'13:00:00.0000000','Wednesday'
)
SELECT pd.[PERSON], pd.[DATE],
-- More messing around with data
CONVERT(VARCHAR(10),[diff].Seconds/3600)+':'+RIGHT('00'+CONVERT(VARCHAR(2),([diff].Seconds%3600)/60),2)+':'+
RIGHT('00'+CONVERT(VARCHAR(2),[diff].Seconds%60),2) AS [HOURS],
pd.,
pd.[ID],
pd.[DOW],
CASE WHEN pd.[STARTIME] = [times].[EARLY] AND pd.[ENDTIME] = [times].[LATE]
THEN 'B'
ELSE NULL END AS [FLAG]
FROM PayData pd
OUTER APPLY (SELECT CASE WHEN rd.[RESETTIME] > pd.[STARTIME]
THEN pd.[STARTIME]
ELSE rd.[RESETTIME] END AS [EARLY],
CASE WHEN rd.[RESETTIME] > pd.[STARTIME]
THEN rd.[RESETTIME]
ELSE pd.[ENDTIME] END AS [LATE]
FROM ResetData rd
WHERE pd.ID = rd.ID
AND pd.DOW = rd.DOW -- THIS WOULD BE BETTER IF IT WAS THE DATE INSTEAD
)[times]
-- This messing around occurs because you aren't storing the data as "DATETIME". If you're using "DATE" and "TIME" rather than
-- strings as it appears, let me know because this becomes easier.
OUTER APPLY (SELECT DATEDIFF(SECOND, CAST('2000-01-01 '+SUBSTRING(ISNULL([times].[EARLY],pd.[STARTIME]),1,8) AS DATETIME),
CAST('2000-01-01 '+SUBSTRING(ISNULL([times].[LATE],pd.[ENDTIME]),1,8) AS DATETIME)) AS [Seconds]
)[diff];
Note the crazy messing around I had to do because I had to assume that your data is char/varchar/nvarchar/nchar ? It returns this on my system: -
PERSON DATE HOURS CODE ID DOW FLAG
----------- ---------- ---------------- ------- ----------- --------- ----
901 06/23/2014 4:00:00 Regular 51 Monday NULL
901 06/23/2014 5:30:00 Regular 51 Monday NULL
901 06/24/2014 4:00:00 Regular 51 Tuesday NULL
901 06/24/2014 1:00:00 Jury 51 Tuesday B
901 06/25/2014 4:00:00 Regular 51 Wednesday NULL
901 06/25/2014 5:30:00 Regular 51 Wednesday NULL
901 06/26/2014 10:00:00 Regular 51 Thursday NULL
901 06/27/2014 4:00:00 Regular 51 Friday NULL
901 06/27/2014 5:00:00 Regular 51 Friday NULL
Which appears to match what you want, but it will not scale well do to the string manipulations.
Thanks.
July 15, 2014 at 3:51 pm
Hi Cadavre,
I had a feeling that I screwed up somewhere with my post.
The pay Data and Reset Data values comes from different tables customer's database, I could use datetime data types for the starttime and endtime columns however the resettime column is only in seconds for example: for example for 11am it has a value of '39600' so I had use CONVERT(varchar, DATEADD(ms, RESETTIME * 1000, 0), 108) to get a value '11:00:00' so then I can match it up against the starttime column.
Here are the data types from Pay Data Table
(PERSON [nvarchar](15)
DATE [datetime]
[HOURS] [int]
STARTTIME [datetime] ** I grab the time portion only
ENDTIME [datetime] ** I grab the time portion only
CODE [nvarchar](35)
ID [int]
DOW [nvarchar](15)
Here are the data types from Reset Data table
ID [int]
DOW [nvarchar](15)
RESETTIME [nvarchar](15) ** The actual table is using INT data type and stores the values in for this field is seconds, so for example for 11am it has a value of '39600' so I had to use CONVERT(varchar, DATEADD(ms, RESETTIME * 1000, 0), 108) to get a value '11:00:00'
The B flag is an indicator for the hours on and after the Reset time and DOW, so for our example this employee's reset time is 11am and reset day is Tuesday, so on Tuesday 4 hours from 7:00 to 11:00 shows up on one row with no B flag and 6 hours from 11:00 to 17:00 shows up with a B flag.
I hope this makes sense..
PERSONDATE HOURSCODE ID DOW FLAG
90106/24/20144.0Regular 51 Tuesday
90106/24/20146.0Regular 51 TuesdayB
90106/24/20141.0Jury 51 Tuesday
I ran your solution, it is missing the 6 hours with a B flag on Tuesday but it's very close 🙂
Thank you for taking the time to read through this issue.
July 16, 2014 at 3:04 am
DiabloZA (7/15/2014)
Hi Cadavre,I had a feeling that I screwed up somewhere with my post.
Excellent. One little issue, I can't see how "HOURS" could be an INT, judging by the data you've been showing it must either be a string or a decimal of some kind. I'm going to assume VARCHAR(10).
Here's the sample data in a readily consumable format: -
IF OBJECT_ID('tempdb..#PayData') IS NOT NULL
BEGIN;
DROP TABLE #PayData;
END;
CREATE TABLE #PayData
(
[PERSON] NVARCHAR(15),
[DATE] DATETIME,
[HOURS] VARCHAR(10),
[STARTTIME] DATETIME,
[ENDTIME] DATETIME,
NVARCHAR(35),
[ID] INT,
[DOW] NVARCHAR(15),
[FLAG] CHAR(1)
);
IF OBJECT_ID('tempdb..#ResetData') IS NOT NULL
BEGIN;
DROP TABLE #ResetData;
END;
CREATE TABLE #ResetData
(
[ID] INT,
[RESETSECONDS] INT,
[RESETTIME] AS CONVERT(VARCHAR, DATEADD(ms, [RESETSECONDS] * 1000, 0), 108),
[DOW] NVARCHAR(15)
);
INSERT INTO [#PayData]
(
[PERSON],
[DATE],
[HOURS],
[STARTTIME],
[ENDTIME],
,
[ID],
[DOW]
)
SELECT 901,'2014-06-23',NULL,'2014-06-23 07:00:00','2014-06-23 11:00:00','Regular',51,'Monday'
UNION ALL SELECT 901,'2014-06-23',NULL,'2014-06-23 11:30:00','2014-06-23 17:00:00','Regular',51,'Monday'
UNION ALL SELECT 901,'2014-06-24',NULL,'2014-06-24 07:00:00','2014-06-24 17:00:00','Regular',51,'Tuesday'
UNION ALL SELECT 901,'2014-06-24',NULL,'2014-06-24 11:00:00','2014-06-24 12:00:00','Jury',51,'Tuesday'
UNION ALL SELECT 901,'2014-06-25',NULL,'2014-06-25 07:00:00','2014-06-25 11:00:00','Regular',51,'Wednesday'
UNION ALL SELECT 901,'2014-06-25',NULL,'2014-06-25 11:30:00','2014-06-25 17:00:00','Regular',51,'Wednesday'
UNION ALL SELECT 901,'2014-06-26',NULL,'2014-06-26 07:00:00','2014-06-26 17:00:00','Regular',51,'Thursday'
UNION ALL SELECT 901,'2014-06-27',NULL,'2014-06-27 07:00:00','2014-06-27 11:00:00','Regular',51,'Friday'
UNION ALL SELECT 901,'2014-06-27',NULL,'2014-06-27 11:30:00','2014-06-27 16:30:00','Regular',51,'Friday';
INSERT INTO [#ResetData]
(
[ID],
[RESETSECONDS],
[DOW]
)
SELECT 51,39600,'Tuesday'
UNION ALL SELECT 53,46800,'Wednesday';
I feel like this should be easier than this, but well, it's pre-morning coffee right now 😀
-- STEP 1
IF OBJECT_ID('tempdb..#HOLDER') IS NOT NULL
BEGIN;
DROP TABLE #HOLDER;
END;
SELECT [pd].[PERSON],
[pd].[DATE],
[pd].[HOURS],
[pd].[STARTTIME],
[pd].[ENDTIME],
[pd].,
[pd].[ID],
[pd].[DOW],
DATEADD(SECOND, rd.[RESETSECONDS], pd.[DATE]) AS [NEWTIME],
'B' AS [FLAG]
INTO #HOLDER
FROM [#PayData] AS pd
INNER JOIN [#ResetData] AS rd ON pd.[ID] = rd.[ID] AND pd.[DOW] = rd.[DOW]
WHERE DATEADD(SECOND, rd.[RESETSECONDS], pd.[DATE]) > pd.[STARTTIME]
AND DATEADD(SECOND, rd.[RESETSECONDS], pd.[DATE]) < pd.[ENDTIME];
-- STEP 2
MERGE INTO [#PayData] [Target]
USING
(
SELECT [PERSON],
[DATE],
[HOURS],
[STARTTIME],
[ENDTIME],
,
[ID],
[DOW],
[NEWTIME]
FROM [#HOLDER]
) [Source]
ON [Target].[PERSON] = [Source].[PERSON]
AND [Target].[DATE] = [Source].[DATE]
AND [Target].[STARTTIME] = [Source].[STARTTIME]
AND [Target].[ENDTIME] = [Source].[ENDTIME]
AND [Target]. = [Source].
AND [Target].[DOW] = [Source].[DOW]
WHEN MATCHED THEN
UPDATE SET [ENDTIME] = [Source].[NEWTIME];
-- STEP 3
INSERT INTO [#PayData]
(
[PERSON],
[DATE],
[HOURS],
[STARTTIME],
[ENDTIME],
,
[ID],
[DOW],
[FLAG]
)
SELECT [PERSON],
[DATE],
[HOURS],
[NEWTIME],
[ENDTIME],
,
[ID],
[DOW],
[FLAG]
FROM [#HOLDER];
--Step 4
UPDATE [#PayData]
SET [HOURS] = CONVERT(VARCHAR(10), DATEDIFF(SECOND, [STARTTIME], [ENDTIME]) / 3600) + ':' +
RIGHT('00' + CONVERT(VARCHAR(2), ( DATEDIFF(SECOND, [STARTTIME], [ENDTIME]) % 3600 ) / 60), 2) + ':' +
RIGHT('00' + CONVERT(VARCHAR(2), DATEDIFF(SECOND, [STARTTIME], [ENDTIME]) % 60), 2);
--Step 5
IF OBJECT_ID('tempdb..#HOLDER') IS NOT NULL
BEGIN;
DROP TABLE #HOLDER;
END;
-- Display results
SELECT *
FROM [#PayData]
ORDER BY [PERSON],
[DATE],
CASE WHEN = 'Regular' THEN 0
ELSE 1
END;
Which results in: -
PERSON DATE HOURS STARTTIME ENDTIME CODE ID DOW FLAG
--------------- ----------------------- ---------- ----------------------- ----------------------- ----------------------------------- ----------- --------------- ----
901 2014-06-23 00:00:00.000 4:00:00 2014-06-23 07:00:00.000 2014-06-23 11:00:00.000 Regular 51 Monday NULL
901 2014-06-23 00:00:00.000 5:30:00 2014-06-23 11:30:00.000 2014-06-23 17:00:00.000 Regular 51 Monday NULL
901 2014-06-24 00:00:00.000 4:00:00 2014-06-24 07:00:00.000 2014-06-24 11:00:00.000 Regular 51 Tuesday NULL
901 2014-06-24 00:00:00.000 6:00:00 2014-06-24 11:00:00.000 2014-06-24 17:00:00.000 Regular 51 Tuesday B
901 2014-06-24 00:00:00.000 1:00:00 2014-06-24 11:00:00.000 2014-06-24 12:00:00.000 Jury 51 Tuesday NULL
901 2014-06-25 00:00:00.000 4:00:00 2014-06-25 07:00:00.000 2014-06-25 11:00:00.000 Regular 51 Wednesday NULL
901 2014-06-25 00:00:00.000 5:30:00 2014-06-25 11:30:00.000 2014-06-25 17:00:00.000 Regular 51 Wednesday NULL
901 2014-06-26 00:00:00.000 10:00:00 2014-06-26 07:00:00.000 2014-06-26 17:00:00.000 Regular 51 Thursday NULL
901 2014-06-27 00:00:00.000 4:00:00 2014-06-27 07:00:00.000 2014-06-27 11:00:00.000 Regular 51 Friday NULL
901 2014-06-27 00:00:00.000 5:00:00 2014-06-27 11:30:00.000 2014-06-27 16:30:00.000 Regular 51 Friday NULL
Hope that helps!
July 16, 2014 at 11:22 am
Hi Cadavre,
Thank you so much for ALL your help and time in helping me with this issue, God bless you..
I left out a BIG piece of information that the client is using a SQL 2005 environment and I believe the MERGE INTO statement was introduced in SQL 2008, so when I ran your entire SQL query I received the following errors:
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near 'MERGE'.
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near 'Source'.
Best Regards,
July 16, 2014 at 11:37 am
DiabloZA (7/16/2014)
Hi Cadavre,Thank you so much for ALL your help and time in helping me with this issue, God bless you..
I left out a BIG piece of information that the client is using a SQL 2005 environment and I believe the MERGE INTO statement was introduced in SQL 2008, so when I ran your entire SQL query I received the following errors:
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near 'MERGE'.
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near 'Source'.
Best Regards,
You should be able to just convert step 2 into a plain update statement. I'm at the supermarket at the moment, so I'm on my phone so no guarantees that this is correct: -
-- STEP 2
UPDATE [Target]
SET [Target].[ENDTIME] = [NEWTIME]
FROM [#PayData] [Target]
INNER JOIN [#HOLDER] [Source] ON [Target].[PERSON] = [Source].[PERSON]
AND [Target].[DATE] = [Source].[DATE]
AND [Target].[STARTTIME] = [Source].[STARTTIME]
AND [Target].[ENDTIME] = [Source].[ENDTIME]
AND [Target]. = [Source].
AND [Target].[DOW] = [Source].[DOW];
Make sure you test! 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply