Adding hours to a datetime field with the result within working hours with lunch time / holidays and-CodePudding

I use this function below from (Adriaan Stander), but now I need to consider also lunch time / holidays / Extra time. I have trouble finding a solution. Can anyone help me, what am O doing wrong?

Assuming the data, calendar, tbl_workhrs, etc were stored in the database, the following would be all we need, essentially:

WITH xoverlap AS (                     
         SELECT CASE WHEN dh.xstart >= wk.start_datetime THEN dh.xstart ELSE wk.start_datetime END AS xstart
              , CASE WHEN dh.xend   <= wk.end_datetime   THEN dh.xend   ELSE wk.end_datetime   END AS xend
              , wk.*
           FROM tbl_workhrs AS wk
           JOIN datehours   AS dh
             ON dh.xstart < wk.end_datetime AND dh.xend > wk.start_datetime
          WHERE wk.extra = 0
          UNION
         SELECT wk.start_datetime, wk.end_datetime, wk.start_datetime, wk.end_datetime, wk.extra
           FROM tbl_workhrs AS wk
           JOIN drange      AS dr
             ON wk.extra = 1 AND wk.start_datetime < dr.date_end AND wk.end_datetime > dr.date_start
     )
   , totalhrs AS (
         SELECT t.*
              , SUM(DATEDIFF(MINUTE, xstart, xend)) OVER (ORDER BY xstart) / 60.0 AS hours
           FROM xoverlap AS t
     )
SELECT * FROM totalhrs
 ORDER BY xstart
;

CodePudding user response：

I have a list of orders each with the amount of time needed to complete it.

The orders table is very simple, it only has the :

Order num - Time _needed - Date_Start - Date_end

Order1 - 23h:15m - ....... - .......

Order2 - 2h:01m - ....... - .......

Order3 - 16h:45m - ....... - .......

Order4 - 18h:35m - ....... - .......

Order5 - 46h:05m - ....... - .......

With the function (based on the company's working time - holidays -holidays extras) I can fill the date_start and data_end.

When I have the date_start and date_end fill I create a timeline.

And when the company add new extra time to the calendar, i can exec the function and have all the orders update.

I created a function based on ( T-Sql 2005 Adding hours to a datetime field with the result within working hours) which works well, but which had its limitations (it did not include holidays, vacations, lunchtime or overtime)

I simply, if I wanted to know when order #1 ended in order to start order #2, I would execute the function:

set @date_start='2021-10-06 08:00:00.000' ;
set @time_add = '1900-01-01 01:01:00.000' ;
set @result = (Select dbo.ufn_getworkhr (@date_start , @time_add) )

and thus had an answer of the end date of Order No. 1 and therefore the beginning of Order No. 2

performed the function and knew when Order No. 2 ended and Order No. 3 began ...... and so on

The problem is that the dates that were generating me did not include holidays, vacations, lunchtime or overtime, which resulted in poor planning.

With your example I realized that I could really leave the function I have and create a new one, but the example you showed is good but I'm not able to fit my need.

Can you make an example?

Thanks