SQL to calculate working hours for female votes

After excluding non-working hours and nonworking days, calculate the working time, the code is as follows:

--delete function IF EXISTS calculateworkingtime;set @workStartTime = ' 09:30:00 '; Set @workEndTime = ' 18:30:00 '; --Create Functions--/create function calculateworkingtime (startdate datetime,enddate datetime) RETURNS decimal (32,4) BEGIN DECLARE  Intnum Int (255) DEFAULT 0;  DECLARE decimalnum Decimal (32,4) DEFAULT 0.000;  DECLARE workstarttimehour Decimal (32,4) DEFAULT 0.000;  DECLARE workendtimehour Decimal (32,4) DEFAULT 0.000;  DECLARE Currentday Int (ten) DEFAULT 0;  DECLARE temptimehour Decimal (32,4) DEFAULT 0.000;    --DECLARE temp varchar (2048) DEFAULT '; --deal StartTime and Endtime is nonworkdays SET startdate = (case when weekday (startdate) =5 then concat (Date (timestampad D (day,2,startdate)), ", @workStartTime) when weekday (StartDate) =6 then concat (Date (Timestampadd (day,1,startdate)), '  ', @workStartTime) ELSE startdate END); SET endDate = (case if weekday (endDate) =5 then concat (Date (Timestampadd (day,-1,enddate)), ", @workEndTime) when Weekday (endDate) =6 then concat (Date (Timestampadd (day,-2, endDate)), ", @workEndTime) ELSE endDate END);--SET temp = concat (temp, ', StartDate, '; ', endDate); If StartDate < endDate then--deal StartTime and Endtime are nonworktime if time (startdate) <[email protec    Ted] then SET startdate = concat (Date (StartDate), ", @workStartTime); ElseIf Date (StartDate) < date (endDate) and Time (startdate) > @workEndTime then SET startdate = concat (date    _add (StartDate, Interval 1 day)), ", @workStartTime);        End If;    If time (endDate) >[email protected] then SET endDate = concat (Date (endDate), ', @workEndTime); ElseIf Date (StartDate) < date (endDate) and Time (endDate) < @workStartTime then SET endDate = concat (Date (date_a    DD (endDate, Interval-1 Day)), ", @workEndTime);        End If; --Calculate time diff SET decimalnum = (Minute (endDate) *60+second (endDate)-minute (startdate) *60-second (startdate))/      3600;    End If; --Calculate work time second SET Workstarttimehour = Hour (@workStartTime) +minute (@workStartTime)/60+second (@workStartTime)/3600;    SET Workendtimehour = Hour (@workEndTime) +minute (@workEndTime)/60+second (@workEndTime)/3600; --while (Floor (Unix_timestamp (endDate)-Unix_timestamp (StartDate))/3600) > 0) does while (Floor (Unix_timestamp ( EndDate)/3600)-Floor (Unix_timestamp (startdate)/3600)) > 0) do SET Temptimehour = Hour (startdate) +minute (startdate          )/60+second (startdate)/3600; If Workstarttimehour <= temptimehour and Temptimehour < workendtimehour then-SET temp = concat (temp, ', TE       Mptimehour, '; ');     SET Intnum = (case if weekday (StartDate) =5 or Weekday (startdate) =6 then intnum ELSE intnum+1 END);     End If;   SET StartDate = Timestampadd (hour,1,startdate);     END while;  SET decimalnum = Intnum + decimalnum;  --Concat (Decimalnum, '; ', Workstarttimehour, ', Workendtimehour, ', intnum, ';;; ', temp); RETURN Decimalnum; END/--Select Calculateworkingtime (' 2017-02-17 07:30:00 ', ' 2017-02-21 17:39:00 '); selECT TRANSPORT_ID,CREATE_AT1,CREATE_AT2,CREATE_AT3, Calculateworkingtime (CREATE_AT1,CREATE_AT2), Calculateworkingtime (CREATE_AT2,CREATE_AT3), Calculateworkingtime (CREATE_AT1,CREATE_AT3) from NewTable;select Transport_id,calculateworkingtime (CREATE_AT1,CREATE_AT2) from newtable;

Note: The first time to achieve this demand, when it was written and more rushed, after writing, found that although the function has been achieved, but there are many places can be tuned

Female ticket is to engage in data operation, often need to statistical staff working hours; listen to the female votes, this SQL code statistics seems to have some problems, but not yet the reason for the urgent analysis; there are new needs for women's tickets, not only to remove non-working hours, nonworking days, but also to remove the above code refactoring, Implement the desired function (the refactoring code will not release it)

If you think reading this blog gives you something to gain, you might want to click " recommend " in the lower right corner.
If you want to find my new blog more easily, click on " Follow me " in the lower left corner.
If you are interested in what my blog is talking about, please keep following my follow-up blog, I am " Liu Chao ★ljc".

This article is copyright to the author and the blog Park, Welcome to reprint, but without the consent of the author must retain this paragraph, and in the article page obvious location to the original link, otherwise reserves the right to pursue legal responsibility.

SQL to calculate working hours for female votes