tsql - SQL Server 2008 R2: How to get Free spaces Between times -
i have 2 columns in table represent start , end employee work time (it stores hh:mm:ss). have table working stack store busy time. let's stored result employee start 8:00:00 , end 17:00:00
startcolumn endcolumn 08:15:00.0000000 08:45:00.0000000 11:00:00.0000000 12:00:00.0000000 12:00:00.0000000 13:00:00.0000000 i want following result considering above data in table:
8:00 a.m 8:45 a.m 10:00 a.m 1:00 p.m 2:00 p.m 3:00 p.m 4:00 p.m thanks in advance!
edit: here solution quarters of hour, coming quarters without working activities...
declare @empl table(id int, emplname varchar(100)); insert @empl values (1,'mr. x'); declare @emplstandard table(emplid int, starthour time, endhour time); insert @emplstandard values (1,{t'08:00:00'},{t'17:00:00'}); declare @work table(emplid int,workday date,starthour time,endhour time); insert @work values (1,{d'2016-02-05'},{t'08:15:00'},{t'08:45:00'}) ,(1,{d'2016-02-05'},{t'11:00:00'},{t'12:00:00'}) ,(1,{d'2016-02-05'},{t'12:00:00'},{t'13:00:00'}); tallytimes(thehour) ( select {t'08:00:00'} union select {t'08:15:00'} union select {t'08:30:00'} union select {t'08:45:00'} union select {t'09:00:00'} union select {t'09:15:00'} union select {t'09:30:00'} union select {t'09:45:00'} union select {t'10:00:00'} union select {t'10:15:00'} union select {t'10:30:00'} union select {t'10:45:00'} union select {t'11:00:00'} union select {t'11:15:00'} union select {t'11:30:00'} union select {t'11:45:00'} union select {t'12:00:00'} union select {t'12:15:00'} union select {t'12:30:00'} union select {t'12:45:00'} union select {t'13:00:00'} union select {t'13:15:00'} union select {t'13:30:00'} union select {t'13:45:00'} union select {t'14:00:00'} union select {t'14:15:00'} union select {t'15:30:00'} union select {t'16:45:00'} union select {t'15:00:00'} union select {t'15:15:00'} union select {t'15:30:00'} union select {t'15:45:00'} union select {t'16:00:00'} union select {t'16:15:00'} union select {t'16:30:00'} union select {t'16:45:00'} union select {t'17:00:00'} union select {t'17:15:00'} union select {t'17:30:00'} union select {t'17:45:00'} ) select e.emplname ,cast(cast(tt.thehour time) varchar(8)) thehour tallytimes tt cross join @empl e inner join @emplstandard es on es.emplid=e.id not exists(select 1 @work w w.emplid=e.id , cast(tt.thehour time)>=w.starthour , cast(tt.thehour time)<=w.endhour ) following first attempt (before question changed)
if understand correctly dealing full hours only:
this pick out hours employee working:
a short explanation: after filling test data in declared table variables start cte creating list of hours 06:00 20:00. final select checks whether exists entry within table "work" current hour within intervall or not. it's on decide, if endhour included or not. own sample data inconsistent in point of view.
you might shift case when-logic where if want non-working hours only...
and aware, need further logic distinguish between different employees, workdays , maybe differing time standards each workday (friday other monday...)
declare @empl table(id int, emplname varchar(100)); insert @empl values (1,'mr. x'); declare @emplstandard table(emplid int, starthour time, endhour time); insert @emplstandard values (1,{t'08:00:00'},{t'17:00:00'}); declare @work table(emplid int,workday date,starthour time,endhour time); insert @work values (1,{d'2016-02-05'},{t'08:00:00'},{t'09:00:00'}) ,(1,{d'2016-02-05'},{t'11:00:00'},{t'12:00:00'}) ,(1,{d'2016-02-05'},{t'12:00:00'},{t'13:00:00'}); tallytimes(thehour) ( select {t'06:00:00'} union select {t'07:00:00'} union select {t'08:00:00'} union select {t'09:00:00'} union select {t'10:00:00'} union select {t'11:00:00'} union select {t'12:00:00'} union select {t'13:00:00'} union select {t'14:00:00'} union select {t'15:00:00'} union select {t'16:00:00'} union select {t'17:00:00'} union select {t'18:00:00'} union select {t'19:00:00'} union select {t'20:00:00'} ) select e.emplname ,tt.thehour ,case when exists(select 1 @work w w.emplid=e.id , cast(tt.thehour time)>=w.starthour , cast(tt.thehour time)<=w.endhour ) 'x' else '' end tallytimes tt cross join @empl e inner join @emplstandard es on es.emplid=e.id the result:
empl thehour wasworking mr. x 06:00:00 mr. x 07:00:00 mr. x 08:00:00 x mr. x 09:00:00 x mr. x 10:00:00 mr. x 11:00:00 x mr. x 12:00:00 x mr. x 13:00:00 x mr. x 14:00:00 mr. x 15:00:00 mr. x 16:00:00 mr. x 17:00:00 mr. x 18:00:00 mr. x 19:00:00 mr. x 20:00:00 
Comments
Post a Comment