genius idea needed to join tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

genius idea needed to join tables

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2014-09-03 : 06:08:28

I need a genius idea to link 2 tables in order to solve the following problem:

We record timemarks that register our workers start and end of their shift, as well as any changes of sections within the work area. They do not mark when they have coffee or lunch break.

TimeStart  TimeEnd   Section
-----------------------------
06:23      08:27       1
08:27      11:15       3
11:15      NULL(now)   2

The timetable has the following format

Start     End
-------------
06:30    9:00
09:30   12:30
13:15   16:00

I need to get the exact times the workers produce in their sections (minutes outside the official timetable are subtracted) , and therefore have to join these tables in a smart way.

The result of the timemarks table would then be:

TimeStart   TimeEnd   Section   Time
--------------------------------------
06:23       08:27       1       1:57 (6:30 - 8:27)
08:27       11:15       3       2:18 (8:27 - 9:00) + (9:30 - 11:15)
11:15       NULL (now)  2        …   (11:15 – getdate() minus 0:45, if now is after the lunch break) or (11:15 - 16:00 if now is after 16:00)

Someone has any ideas?
Note:
I had a rather complicated solution for 2 possible timetable lines, but my boss wants to add even more timetable lines for planned extra working hours in the future.
Another solution consisted in creating a timetable with a record for each and every minute having a "0" for off-time and "1" for on-time and then summing up the total within the given 2 time marks. That was cool and easy, but is not practical as I need more dynamic timetables that vary a lot and are specific for different workers. Maybe one should think of creating this minute by minute (0/1)timetable in a temporary table, but I have no experience with this.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-03 : 19:12:29

[Soapbox]A join condition is simply a boolean expression that must evaluate to True in order for the two records to be included in the results. It can be anything that you want.[/Soapbox]

[PetPeeve]Stop using reserved words as column names[/PetPeeve][CODE]--- You should supply this...

declare @Timemarks table (
TimeStart time not null,
TimeEnd time null,
Section int not null
)

insert into @Timemarks (
TimeStart,
TimeEnd,
Section
)
values
('06:23', '08:27', 1),
('08:27', '11:15', 3),
('11:15', NULL, 2)

--select '@Timemarks', * from @Timemarks

declare @Timetable table (
Start time not null,
[End] time not null
)

insert into @Timetable (start, [end])
values
('06:30', '9:00'),
('09:30', '12:30'),
('13:15', '16:00')

--select '@Timetable', * from @Timetable

--- Here is what you're looking for...

select
a.TimeStart,
a.TimeEnd,
a.Section,
--sum(a.deltaMinutes) deltaMinutes,
cast(sum(a.deltaMinutes) / 60 as varchar(10)) + ':' + cast(sum(a.deltaMinutes) % 60 as varchar(10)) [Time]
from (
select
tm.TimeStart,
tm.TimeEnd,
tm.Section,
datediff(
minute,
case when tm.TimeStart <= tt.Start then tt.Start else tm.TimeStart end,
case when tm.TimeEnd <= tt.[End] then tm.TimeEnd else tt.[End] end
) deltaMinutes
from
@Timemarks tm
inner join
@Timetable tt
on tm.TimeStart between tt.Start and tt.[End]
or coalesce(tm.TimeEnd, cast(GetDate() as Time)) between tt.Start and tt.[End]
or (tm.TimeStart <= tt.Start and tt.[End] <= coalesce(tm.TimeEnd, cast(GetDate() as Time)))
) a
group by
a.TimeStart,
a.TimeEnd,
a.Section
order by
a.TimeStart,
a.Section[/CODE]

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

barnabeck
Posting Yak Master

236 Posts

Posted - 2014-09-04 : 03:47:43

Great Job! Thank you very much.

I came up with:

select SUM(
           CASE WHEN TTEnd < datepart(hh,@TimeStart)*60+datepart(mi,@TimeStart) OR 
                     TTStart > datepart(hh,COALESCE(@TimeEnd,getdate()))*60 + datepart(mi,COALESCE(@TimeEnd,getdate())) 
                THEN 0 ELSE 
                            CASE WHEN TTEnd < datepart(hh,COALESCE(@TimeEnd,getdate()))*60+datepart(mi,COALESCE(@TimeEnd,getdate())) 
                                 THEN TTEnd 
                                 ELSE datepart(hh,COALESCE(@TimeEnd,getdate()))*60 + datepart(mi,COALESCE(@TimeEnd,getdate())) 
                                 END -
                            CASE WHEN TTStart > datepart(hh,@TimeStart)*60+datepart(mi,@TimeStart) 
                                 THEN TTStart 
                                 ELSE datepart(hh,@TimeStart)*60 + datepart(mi,@TimeStart) 
                                 END
                END
           ) as delta from Timetable

which follows the same idea but isn't half as elegant as your solution. Again, thanks a lot!

Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources