March 10, 2014 at 7:45 pm
Hi Guys,
I need some help please, 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 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'.
Pay Data Table
WITH SampleData (PERSON,[DATE],[HOURS],[STARTIME],[ENDTIME],,[ID],[DOW]) AS
(
-- First employee -
SELECT 901,'02/15/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',51,'Saturday'
UNION ALL SELECT 901,'02/15/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',51,'Saturday'
UNION ALL SELECT 901,'02/16/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',51,'Sunday'
UNION ALL SELECT 901,'02/16/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',51,'Sunday'
UNION ALL SELECT 901,'02/18/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',51,'Tuesday'
UNION ALL SELECT 901,'02/18/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',51,'Tuesday'
UNION ALL SELECT 901,'02/18/2014',1,'12:00:00.0000000','12:00:00.0000000','Jury',51,'Tuesday'
UNION ALL SELECT 901,'02/20/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',51,'Thursday'
UNION ALL SELECT 901,'02/20/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',51,'Thursday'
UNION ALL SELECT 901,'02/21/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',51,'Friday'
UNION ALL SELECT 901,'02/21/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',51,'Friday'
-- Second employee -
UNION ALL SELECT 902,'02/15/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',53,'Saturday'
UNION ALL SELECT 902,'02/15/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',53,'Saturday'
UNION ALL SELECT 902,'02/16/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',53,'Sunday'
UNION ALL SELECT 902,'02/16/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',53,'Sunday'
UNION ALL SELECT 902,'02/17/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',53,'Monday'
UNION ALL SELECT 902,'02/17/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',53,'Monday'
UNION ALL SELECT 901,'02/18/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',53,'Tuesday'
UNION ALL SELECT 901,'02/18/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',53,'Tuesday'
UNION ALL SELECT 902,'02/19/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',53,'Wednesday'
UNION ALL SELECT 902,'02/19/2014',1.5,'11:30:00.0000000','13:00:00.0000000','Overtime',53,'Wednesday'
UNION ALL SELECT 902,'02/19/2014',3.5,'13:00:00.0000000','16:30:00.0000000','Regular',53,'Wednesday'
)
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 HOURSSTARTIME ENDTIME CODEIDDOW
90102/15/20144.007:00:00.000000011:00:00.0000000Regular51Saturday
90102/15/20145.011:30:00.000000016:30:00.0000000Regular51Saturday
90102/16/20144.007:00:00.000000011:00:00.0000000Regular51Sunday
90102/16/20145.011:30:00.000000016:30:00.0000000Regular51Sunday
90102/18/20144.007:00:00.000000011:00:00.0000000Regular51Tuesday
90102/18/20145.011:30:00.000000016:30:00.0000000Regular51Tuesday
90102/18/20141.012:00:00.000000012:00:00.0000000Jury51Tuesday
90102/20/20144.007:00:00.000000011:00:00.0000000Regular51Thursday
90102/20/20145.011:30:00.000000016:30:00.0000000Regular51Thursday
90102/21/20144.007:00:00.000000011:00:00.0000000Regular51Friday
90102/21/20145.011:30:00.000000016:30:00.0000000Regular51Friday
90202/15/20144.007:00:00.000000011:00:00.0000000Regular53Saturday
90202/15/20145.011:30:00.000000016:30:00.0000000Regular53Saturday
90202/16/20144.007:00:00.000000011:00:00.0000000Regular53Sunday
90202/16/20145.011:30:00.000000016:30:00.0000000Regular53Sunday
90202/17/20144.007:00:00.000000011:00:00.0000000Regular53Monday
90202/17/20145.011:30:00.000000016:30:00.0000000Regular53Monday
90102/18/20144.007:00:00.000000011:00:00.0000000Regular53Tuesday
90102/18/20145.011:30:00.000000016:30:00.0000000Regular53Tuesday
90202/19/20144.007:00:00.000000011:00:00.0000000Regular53Wednesday
90202/19/20141.511:30:00.000000013:00:00.0000000Overtime53Wednesday
90202/19/20143.513:00:00.000000016:30:00.0000000Regular53Wednesday
Desired Results After joining both tables - Employee 901's reset day and time is Tuesday's at 11am so his hours on the 18th after 11am are flagged with an F, employee 902's reset day and time is Wednesday's at 1pm so his hours on the 19th after 1pm are flagged with an F.
PERSONDATE HOURSSTARTIME ENDTIME CODEIDDOW FLAG
90102/15/20144.007:00:00.000000011:00:00.0000000Regular51Saturday
90102/15/20145.011:30:00.000000016:30:00.0000000Regular51Saturday
90102/16/20144.007:00:00.000000011:00:00.0000000Regular51Sunday
90102/16/20145.011:30:00.000000016:30:00.0000000Regular51Sunday
90102/18/20144.007:00:00.000000011:00:00.0000000Regular51Tuesday
90102/18/20145.011:30:00.000000016:30:00.0000000Regular51Tuesday F
90102/18/20141.012:00:00.000000012:00:00.0000000Jury51Tuesday F
90102/20/20144.007:00:00.000000011:00:00.0000000Regular51Thursday
90102/20/20145.011:30:00.000000016:30:00.0000000Regular51Thursday
90102/21/20144.007:00:00.000000011:00:00.0000000Regular51Friday
90102/21/20145.011:30:00.000000016:30:00.0000000Regular51Friday
90202/15/20144.007:00:00.000000011:00:00.0000000Regular53Saturday
90202/15/20145.011:30:00.000000016:30:00.0000000Regular53Saturday
90202/16/20144.007:00:00.000000011:00:00.0000000Regular53Sunday
90202/16/20145.011:30:00.000000016:30:00.0000000Regular53Sunday
90202/17/20144.007:00:00.000000011:00:00.0000000Regular53Monday
90202/17/20145.011:30:00.000000016:30:00.0000000Regular53Monday
90102/18/20144.007:00:00.000000011:00:00.0000000Regular53Tuesday
90102/18/20145.011:30:00.000000016:30:00.0000000Regular53Tuesday
90202/19/20144.007:00:00.000000011:00:00.0000000Regular53Wednesday
90202/19/20141.511:30:00.000000013:00:00.0000000Overtime53Wednesday
90202/19/20143.513:00:00.000000016:30:00.0000000Regular53Wednesday F
March 10, 2014 at 9:05 pm
It looks like you just need a Left Outer Join on the two tables between ID, DOW and checking the StartTime vs. the Resettime:WITH SampleData (PERSON,[DATE],[HOURS],[STARTIME],[ENDTIME],,[ID],[DOW]) AS
(
-- First employee -
SELECT 901,'02/15/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',51,'Saturday'
UNION ALL SELECT 901,'02/15/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',51,'Saturday'
UNION ALL SELECT 901,'02/16/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',51,'Sunday'
UNION ALL SELECT 901,'02/16/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',51,'Sunday'
UNION ALL SELECT 901,'02/18/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',51,'Tuesday'
UNION ALL SELECT 901,'02/18/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',51,'Tuesday'
UNION ALL SELECT 901,'02/18/2014',1,'12:00:00.0000000','12:00:00.0000000','Jury',51,'Tuesday'
UNION ALL SELECT 901,'02/20/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',51,'Thursday'
UNION ALL SELECT 901,'02/20/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',51,'Thursday'
UNION ALL SELECT 901,'02/21/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',51,'Friday'
UNION ALL SELECT 901,'02/21/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',51,'Friday'
-- Second employee -
UNION ALL SELECT 902,'02/15/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',53,'Saturday'
UNION ALL SELECT 902,'02/15/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',53,'Saturday'
UNION ALL SELECT 902,'02/16/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',53,'Sunday'
UNION ALL SELECT 902,'02/16/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',53,'Sunday'
UNION ALL SELECT 902,'02/17/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',53,'Monday'
UNION ALL SELECT 902,'02/17/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',53,'Monday'
UNION ALL SELECT 901,'02/18/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',53,'Tuesday'
UNION ALL SELECT 901,'02/18/2014',5,'11:30:00.0000000','16:30:00.0000000','Regular',53,'Tuesday'
UNION ALL SELECT 902,'02/19/2014',4,'07:00:00.0000000','11:00:00.0000000','Regular',53,'Wednesday'
UNION ALL SELECT 902,'02/19/2014',1.5,'11:30:00.0000000','13:00:00.0000000','Overtime',53,'Wednesday'
UNION ALL SELECT 902,'02/19/2014',3.5,'13:00:00.0000000','16:30:00.0000000','Regular',53,'Wednesday'
)
, SampleData1 ([ID],[RESETTIME],[DOW]) AS
(
-- First employee -
SELECT 51,'11:00:00.0000000','Tuesday'
UNION ALL SELECT 53,'13:00:00.0000000','Wednesday'
)
SELECT a.*, case when b.ID is not null then 'F' else '' end as Flagged
FROM SampleData a
left join SampleData1 b on a.DOW = b.DOW
and a.ID = b.ID
and a.STARTIME >= b.RESETTIME
Let me know if I missed something in the earlier post.
March 10, 2014 at 11:26 pm
Hi Keith,
You have done it, simple and awesome 🙂
Thank you very much,
Best Regards,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply