sql - Modify and Implement Query -

-

we trying analysis of how long our staff have been working on hourly basis trending , forecasting purposes.

we have both clock in (shifta_start) , clock out (shifta_end) of employees.

below current code using , have included output shown in sql after execution.

image 1 image 2

however when employee working overnight eg: shiftastart != shiftaend, fields cannot populated. current sql works when employee start , end shift on same day.

does know how modify query when employee works overnight, can populate in sql columns? 

select  --b.*, b.employeename, b.department,  convert(datetime, left(b.shifta_start,17),103) shifta_start, convert(datetime, left(b.shifta_end,17),103) shifta_end, b.starttime_hour, b.starttime_min, b.endtime_hour, b.endtime_min,   case when b.[0h_start] < b.[0h_end]  b.[0h_start] else b.[0h_end] end [0], case when b.[1h_start] < b.[1h_end]  b.[1h_start] else b.[1h_end] end [1], case when b.[2h_start] < b.[2h_end]  b.[2h_start] else b.[2h_end] end [2], case when b.[3h_start] < b.[3h_end]  b.[3h_start] else b.[3h_end] end [3], case when b.[4h_start] < b.[4h_end]  b.[4h_start] else b.[4h_end] end [4], case when b.[5h_start] < b.[5h_end]  b.[5h_start] else b.[5h_end] end [5], case when b.[6h_start] < b.[6h_end]  b.[6h_start] else b.[6h_end] end [6], case when b.[7h_start] < b.[7h_end]  b.[7h_start] else b.[7h_end] end [7], case when b.[8h_start] < b.[8h_end]  b.[8h_start] else b.[8h_end] end [8], case when b.[9h_start] < b.[9h_end]  b.[9h_start] else b.[9h_end] end [9], case when b.[10h_start] < b.[10h_end]  b.[10h_start] else b.[10h_end] end [10], case when b.[11h_start] < b.[11h_end]  b.[11h_start] else b.[11h_end] end [11], case when b.[12h_start] < b.[12h_end]  b.[12h_start] else b.[12h_end] end [12], case when b.[13h_start] < b.[13h_end]  b.[13h_start] else b.[13h_end] end [13], case when b.[14h_start] < b.[14h_end]  b.[14h_start] else b.[14h_end] end [14], case when b.[15h_start] < b.[15h_end]  b.[15h_start] else b.[15h_end] end [15], case when b.[16h_start] < b.[16h_end]  b.[16h_start] else b.[16h_end] end [16], case when b.[17h_start] < b.[17h_end]  b.[17h_start] else b.[17h_end] end [17], case when b.[18h_start] < b.[18h_end]  b.[18h_start] else b.[18h_end] end [18], case when b.[19h_start] < b.[19h_end]  b.[19h_start] else b.[19h_end] end [19], case when b.[20h_start] < b.[20h_end]  b.[20h_start] else b.[20h_end] end [20], case when b.[21h_start] < b.[21h_end]  b.[21h_start] else b.[21h_end] end [21], case when b.[22h_start] < b.[22h_end]  b.[22h_start] else b.[22h_end] end [22], case when b.[23h_start] < b.[23h_end]  b.[23h_start] else b.[23h_end] end [23]   ( /*step 2  - calculating minutes starttime , endtime */  select a.*,  /**calculating number of minutes worked start_time min **/     case when a.starttime_hour = 0 , a.[0] = 1  , a.starttime_min !=0   cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[0] end [0h_start], case when a.starttime_hour = 1 , a.[1] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[1] end [1h_start], case when a.starttime_hour = 2 , a.[2] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[2] end [2h_start], case when a.starttime_hour = 3 , a.[3] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[3] end [3h_start], case when a.starttime_hour = 4 , a.[4] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[4] end [4h_start], case when a.starttime_hour = 5 , a.[5] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[5] end [5h_start], case when a.starttime_hour = 6 , a.[6] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[6] end [6h_start], case when a.starttime_hour = 7 , a.[7] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[7] end [7h_start], case when a.starttime_hour = 8 , a.[8] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[8] end [8h_start], case when a.starttime_hour = 9 , a.[9] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[9] end [9h_start], case when a.starttime_hour = 10 , a.[10] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[10] end [10h_start], case when a.starttime_hour = 11 , a.[11] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[11] end [11h_start], case when a.starttime_hour = 12 , a.[12] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[12] end [12h_start], case when a.starttime_hour = 13 , a.[13] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[13] end [13h_start], case when a.starttime_hour = 14 , a.[14] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[14] end [14h_start], case when a.starttime_hour = 15 , a.[15] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[15] end [15h_start], case when a.starttime_hour = 16 , a.[16] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[16] end [16h_start], case when a.starttime_hour = 17 , a.[17] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[17] end [17h_start], case when a.starttime_hour = 18 , a.[18] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[18] end [18h_start], case when a.starttime_hour = 19 , a.[19] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[19] end [19h_start], case when a.starttime_hour = 20 , a.[20] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[20] end [20h_start], case when a.starttime_hour = 21 , a.[21] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[21] end [21h_start], case when a.starttime_hour = 22 , a.[22] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[22] end [22h_start], case when a.starttime_hour = 23 , a.[23] = 1  , a.starttime_min !=0 cast(cast((60-a.starttime_min) decimal(10,2))/60 decimal(10,2)) else a.[23] end [23h_start],  /**calculating number of minutes worked end_time min **/ case when a.endtime_hour = 0 , a.[0] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[0] end [0h_end], case when a.endtime_hour = 1 , a.[1] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[1] end [1h_end], case when a.endtime_hour = 2 , a.[2] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[2] end [2h_end], case when a.endtime_hour = 3 , a.[3] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[3] end [3h_end], case when a.endtime_hour = 4 , a.[4] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[4] end [4h_end], case when a.endtime_hour = 5 , a.[5] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[5] end [5h_end], case when a.endtime_hour = 6 , a.[6] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[6] end [6h_end], case when a.endtime_hour = 7 , a.[7] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[7] end [7h_end], case when a.endtime_hour = 8 , a.[8] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[8] end [8h_end], case when a.endtime_hour = 9 , a.[9] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[9] end [9h_end], case when a.endtime_hour = 10 , a.[10] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[10] end [10h_end], case when a.endtime_hour = 11 , a.[11] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[11] end [11h_end], case when a.endtime_hour = 12 , a.[12] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[12] end [12h_end], case when a.endtime_hour = 13 , a.[13] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[13] end [13h_end], case when a.endtime_hour = 14 , a.[14] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[14] end [14h_end], case when a.endtime_hour = 15 , a.[15] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[15] end [15h_end], case when a.endtime_hour = 16 , a.[16] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[16] end [16h_end], case when a.endtime_hour = 17 , a.[17] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[17] end [17h_end], case when a.endtime_hour = 18 , a.[18] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[18] end [18h_end], case when a.endtime_hour = 19 , a.[19] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[19] end [19h_end], case when a.endtime_hour = 20 , a.[20] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[20] end [20h_end], case when a.endtime_hour = 21 , a.[21] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[21] end [21h_end], case when a.endtime_hour = 22 , a.[22] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[22] end [22h_end], case when a.endtime_hour = 23 , a.[23] = 1  , a.endtime_min !=0 cast(cast((a.endtime_min) decimal(10,2))/60 decimal(10,2)) else a.[23] end [23h_end]    (--step 1: /*to determine 1 or 0 using start , end hour      if time falls in respective hour = 1 if time doesnt fall in respective hours = 0*/ select   --distinct   [employeename],  [department], [shifta_start], case when  [shifta_start] !='' or shifta_start != null convert(datetime, left([shifta_start],17),103) else null end shifta_start_con, case when  [shifta_start] !='' or shifta_start != null datepart(hh,convert(datetime, left([shifta_start],17),103))  else null end starttime_hour, case when  [shifta_start] !='' or shifta_start != null datepart(mi,convert(datetime, left([shifta_start],17),103))  else null end starttime_min,  [shifta_end], case when [shifta_end] !='' or shifta_end != null convert(datetime, left([shifta_end],17),103) else null end shifta_end_con, case when [shifta_end] !='' or shifta_end != null datepart(hh,convert(datetime, left([shifta_end],17),103))  else null end endtime_hour, case when [shifta_end] !='' or shifta_end != null datepart(mi,convert(datetime, left([shifta_end],17),103))  else null end endtime_min,   case when [shifta_start] !='' , 0 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [0], case when [shifta_start] !='' , 1 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [1], case when [shifta_start] !='' , 2 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [2], case when [shifta_start] !='' , 3 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [3], case when [shifta_start] !='' , 4 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [4], case when [shifta_start] !='' , 5 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [5], case when [shifta_start] !='' , 6 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [6], case when [shifta_start] !='' , 7 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [7], case when [shifta_start] !='' , 8 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [8], case when [shifta_start] !='' , 9 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [9], case when [shifta_start] !='' , 10 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [10], case when [shifta_start] !='' , 11 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [11], case when [shifta_start] !='' , 12 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [12], case when [shifta_start] !='' , 13 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [13], case when [shifta_start] !='' , 14 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [14], case when [shifta_start] !='' , 15 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [15], case when [shifta_start] !='' , 16 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [16], case when [shifta_start] !='' , 17 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [17], case when [shifta_start] !='' , 18 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [18], case when [shifta_start] !='' , 19 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [19], case when [shifta_start] !='' , 20 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [20], case when [shifta_start] !='' , 21 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [21], case when [shifta_start] !='' , 22 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [22], case when [shifta_start] !='' , 23 between datepart(hh,convert(datetime, left([shifta_start],17),103)) , datepart (hh,convert(datetime, left([shifta_end],17),103)) 1  else 0 end [23] [tablename].[dbo].[tms_people]  shifta_start != '' , shifta_end !=''  , shifta_start != '1900-01-01 00:00:00.000' , shifta_end != '1900-01-01 00:00:00.000'  )a  )b


Comments

Post a Comment

Popular posts from this blog

javascript - Laravel datatable invalid JSON response -

-
everything work fine on code below fetch user logs: js: <script> $(document).ready(function () { var table =$('#systemlogs').datatable({ responsive: true, processing: true, serverside: true, "language": { "url": "/datatables/media/plug-in/persian.json" }, ajax: '{!! url('/admin/systemlogs/data/systemlogsdatatable') !!}', columns: [ { data: 'name', name: 'name' }, { data: 'message_text', name: 'message_text' }, { data: 'remote_addr', name: 'remote_addr' }, { data: 'log_created_at', name: 'management_logs.created_at' }, { data: 'log_updated_at', name: 'management_logs.updated_at' }, ] }); }); </script> routes: route::get('/systemlogs/data/systemlogsdatatable','systemlogscontrolle...
Read more

java - Exception in thread "main" org.springframework.context.ApplicationContextException: Unable to start embedded container; -

-
i getting error when run spring application: exception in thread "main" org.springframework.context.applicationcontextexception: unable start embedded container; nested exception org.springframework.context.applicationcontextexception: unable start embeddedwebapplicationcontext due missing embeddedservletcontainerfactory bean. @ org.springframework.boot.context.embedded.embeddedwebapplicationcontext.onrefresh(embeddedwebapplicationcontext.java:124) @ org.springframework.context.support.abstractapplicationcontext.refresh(abstractapplicationcontext.java:476) @ org.springframework.boot.context.embedded.embeddedwebapplicationcontext.refresh(embeddedwebapplicationcontext.java:109) @ org.springframework.boot.springapplication.refresh(springapplication.java:691) @ org.springframework.boot.springapplication.run(springapplication.java:320) @ org.springframework.boot.springapplication.run(springapplication.java:952) @ org.springframework.boot.springa...
Read more

sql server 2008 - My Sql Code Get An Error Of Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '8:45 AM' to data type int -

-
msg 245, level 16, state 1, line 1 conversion failed when converting varchar value '8:45 am' data type int. script declare @specimenid bigint = 0, @script varchar(max); declare @starttime int = (select convert(int,substring(start_time,0,charindex(':', start_time)) - 12) appt appt._id=112601) --select @starttime set @script = 'select distinct ptlast_name + '', '' + pt.first_name patient_name, dbo.formatdate(pt.dob,''mm/dd/yyyy'') dob, (convert(varchar, (datediff(yy, dob, appt_date) - case when (month(dob) > month(appt_date)) or (month(dob) = month(appt_date) , day(dob) > day(appt_date)) 1 else 0 end)) + ''/'' + convert(varchar, (datediff(m, dateadd(yy, (datediff(yy, dob, appt_date) - case when (month(dob) > month(appt_date)) or (month(dob) = month(appt_date) , day(dob) > day(appt_date)) 1 else 0 end), d...
Read more