function used in cte, Sql Server

Language: Layout:

CREATE TABLE [dbo].[CALENDAR](
	[clndr_id] [nvarchar](255) NULL,
	[default_flag] [nvarchar](255) NULL,
	[clndr_name] [nvarchar](255) NULL,
	[proj_id] [nvarchar](255) NULL,
	[base_clndr_id] [nvarchar](255) NULL,
	[last_chng_date] [nvarchar](255) NULL,
	[clndr_type] [nvarchar](255) NULL,
	[day_hr_cnt] [nvarchar](255) NULL,
	[week_hr_cnt] [nvarchar](255) NULL,
	[month_hr_cnt] [nvarchar](255) NULL,
	[year_hr_cnt] [nvarchar](255) NULL,
	[rsrc_private] [nvarchar](255) NULL,
	[clndr_data] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

INSERT INTO [dbo].[CALENDAR]
           ([clndr_id]
           ,[default_flag]
           ,[clndr_name]
           ,[proj_id]
           ,[base_clndr_id]
           ,[last_chng_date]
           ,[clndr_type]
           ,[day_hr_cnt]
           ,[week_hr_cnt]
           ,[month_hr_cnt]
           ,[year_hr_cnt]
           ,[rsrc_private]
           ,[clndr_data])
     VALUES

('9316',	'N',	'2 Shifts 6 days 18Hrs',null,null,			'2018-05-31 00:00',	'CA_Base',	'22',	'132',	'572',	'6880',	'N',	'(0||CalendarData()(  (0||DaysOfWeek()(    (0||1()())    (0||2()(      (0||0(s|03:00|f|21:00)())))    (0||3()(      (0||0(s|03:00|f|21:00)())))    (0||4()(      (0||0(s|03:00|f|21:00)())))    (0||5()(      (0||0(s|03:00|f|21:00)())))    (0||6()(      (0||0(s|03:00|f|21:00)())))    (0||7()(      (0||0(s|03:00|f|21:00)())))))  (0||VIEW(ShowTotal|Y)())  (0||Exceptions()(    (0||0(d|36982)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||1(d|36983)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||2(d|36984)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||3(d|36985)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||4(d|36986)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||5(d|36987)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||6(d|36988)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||7(d|36989)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||8(d|36990)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||9(d|36991)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||10(d|36992)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||11(d|36993)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||12(d|36994)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||13(d|36995)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||14(d|36996)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||15(d|36997)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||16(d|36998)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||17(d|36999)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||18(d|37000)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||19(d|37001)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||20(d|37002)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||21(d|37003)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||22(d|37004)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||23(d|37005)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||24(d|37006)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||25(d|37007)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||26(d|37008)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||27(d|37009)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||28(d|37010)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||29(d|37011)(      (0||0(s|08:00|f|12:00)())      (0||1(s|13:00|f|17:00)())))    (0||30(d|40179)())    (0||31(d|40329)())    (0||32(d|40364)())    (0||33(d|40427)())    (0||34(d|40507)())    (0||35(d|40536)())    (0||36(d|40543)())    (0||37(d|40693)())    (0||38(d|40728)())    (0||39(d|40791)())    (0||40(d|40871)())    (0||41(d|40903)())    (0||42(d|40910)())    (0||43(d|41057)())    (0||44(d|41094)())    (0||45(d|41155)())    (0||46(d|41235)())    (0||47(d|41268)())    (0||48(d|41275)())    (0||49(d|41421)())    (0||50(d|41459)())    (0||51(d|41519)())    (0||52(d|41606)())    (0||53(d|41633)())    (0||54(d|43251)(      (0||0(s|03:00|f|12:00)())      (0||1(s|13:00|f|22:00)())))    (0||55(d|43285)())    (0||56(d|43346)())    (0||57(d|43426)())    (0||58(d|43427)())    (0||59(d|43428)())    (0||60(d|43458)())    (0||61(d|43459)())    (0||62(d|43466)())))))')
go

---------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[TASK](
	[task_id] [nvarchar](255) NULL,
	[proj_id] [nvarchar](255) NULL,
	[wbs_id] [nvarchar](255) NULL,
	[clndr_id] [nvarchar](255) NULL,
	[phys_complete_pct] [nvarchar](255) NULL,
	[rev_fdbk_flag] [nvarchar](255) NULL,
	[est_wt] [nvarchar](255) NULL,
	[lock_plan_flag] [nvarchar](255) NULL,
	[auto_compute_act_flag] [nvarchar](255) NULL,
	[complete_pct_type] [nvarchar](255) NULL,
	[task_type] [nvarchar](255) NULL,
	[duration_type] [nvarchar](255) NULL,
	[status_code] [nvarchar](255) NULL,
	[task_code] [nvarchar](255) NULL,
	[task_name] [nvarchar](255) NULL,
	[rsrc_id] [nvarchar](255) NULL,
	[total_float_hr_cnt] [nvarchar](255) NULL,
	[free_float_hr_cnt] [nvarchar](255) NULL,
	[remain_drtn_hr_cnt] [nvarchar](255) NULL,
	[act_work_qty] [nvarchar](255) NULL,
	[remain_work_qty] [nvarchar](255) NULL,
	[target_work_qty] [nvarchar](255) NULL,
	[target_drtn_hr_cnt] [nvarchar](255) NULL,
	[target_equip_qty] [nvarchar](255) NULL,
	[act_equip_qty] [nvarchar](255) NULL,
	[remain_equip_qty] [nvarchar](255) NULL,
	[cstr_date] [nvarchar](255) NULL,
	[act_start_date] [nvarchar](255) NULL,
	[act_end_date] [nvarchar](255) NULL,
	[late_start_date] [nvarchar](255) NULL,
	[late_end_date] [nvarchar](255) NULL,
	[expect_end_date] [nvarchar](255) NULL,
	[early_start_date] [nvarchar](255) NULL,
	[early_end_date] [nvarchar](255) NULL,
	[restart_date] [nvarchar](255) NULL,
	[reend_date] [nvarchar](255) NULL,
	[target_start_date] [nvarchar](255) NULL,
	[target_end_date] [nvarchar](255) NULL,
	[rem_late_start_date] [nvarchar](255) NULL,
	[rem_late_end_date] [nvarchar](255) NULL,
	[cstr_type] [nvarchar](255) NULL,
	[priority_type] [nvarchar](255) NULL,
	[suspend_date] [nvarchar](255) NULL,
	[resume_date] [nvarchar](255) NULL,
	[float_path] [nvarchar](255) NULL,
	[float_path_order] [nvarchar](255) NULL,
	[guid] [nvarchar](255) NULL,
	[tmpl_guid] [nvarchar](255) NULL,
	[cstr_date2] [nvarchar](255) NULL,
	[cstr_type2] [nvarchar](255) NULL,
	[driving_path_flag] [nvarchar](255) NULL,
	[act_this_per_work_qty] [nvarchar](255) NULL,
	[act_this_per_equip_qty] [nvarchar](255) NULL,
	[external_early_start_date] [nvarchar](255) NULL,
	[external_late_end_date] [nvarchar](255) NULL,
	[create_date] [nvarchar](255) NULL,
	[update_date] [nvarchar](255) NULL,
	[create_user] [nvarchar](255) NULL,
	[update_user] [nvarchar](255) NULL,
	[location_id] [nvarchar](255) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[TASK]
           ([task_id]
           ,[proj_id]
           ,[wbs_id]
           ,[clndr_id]
          
           ,[target_work_qty]
      
           ,[early_start_date]
           ,[early_end_date]
         )  --58
     VALUES
           ('286519',	
            '5705',
            '59026',
            '9316',	
          
            '192',	
      
            '2018-08-11 14:00',	
            '2018-08-24 16:00'
          )
GO
--------------------

--code for spitting the activity by daterange

WITH  SplitActivitybyDaterange_CTE  AS 
(
    SELECT 
        task_id,
      CAST (early_start_date AS DATETime) AS early_start_date,
      DATEADD(day, -1, DATEADD(DD,1,early_start_date)) newDateTo,
       early_end_date,
	   target_work_qty
    FROM 
       task
    UNION ALL
        SELECT 
         m.task_id,
         DATEADD(DD,1,c.early_start_date),
         C.newDateTo,
         c.early_end_date,
		 c.target_work_qty
        FROM 
            TASK m
            JOIN SplitActivitybyDaterange_CTE c ON  c.task_id = m.task_id
            AND DATEADD(DD,1,c.early_start_date) < =  m.early_end_date 
)
SELECT  
    task_id,

day(EARLY_START_DATE),

early_end_date,

target_work_qty
FROM 
    SplitActivitybyDaterange_CTE

WHERE task_id = 286519 --298266--286615--286534 --

OPTION (MAXRECURSION 0);

-----------------------

CREATE TABLE [dbo].[Calenderdetail](
	[clndrid] [float] NULL,
	[day] [float] NULL,
	[date] [datetime] NULL,
	[starttime_shift 1] [datetime] NULL,
	[EndTime _shift 1 ] [datetime] NULL,
	[StartTime_Shift2 ] [datetime] NULL,
	[EndTime_Shift2] [datetime] NULL,
	[Holiday_flag] [float] NULL,
	[hours ] [float] NULL,
	[IsException] [float] NULL
) ON [PRIMARY]

---------------
INSERT INTO [dbo].[Calenderdetail]
           ([clndrid],[day],[date],[starttime_shift 1],[EndTime _shift 1 ],[StartTime_Shift2 ],[EndTime_Shift2],[Holiday_flag],[hours ],[IsException])
           
values
('9316','1',NULL,'1900-01-01 03:00:00.000','1899-12-30 21:00:00.000',NULL,NULL,'1','0','0');
INSERT INTO [dbo].[Calenderdetail]
           ([clndrid],[day],[date],[starttime_shift 1],[EndTime _shift 1 ],[StartTime_Shift2 ],[EndTime_Shift2],[Holiday_flag],[hours ],[IsException])
values
('9316','2',NULL,'1900-01-01 03:00:00.000','1899-12-30 21:00:00.000',NULL,NULL,'0','18','0');
INSERT INTO [dbo].[Calenderdetail]
           ([clndrid],[day],[date],[starttime_shift 1],[EndTime _shift 1 ],[StartTime_Shift2 ],[EndTime_Shift2],[Holiday_flag],[hours ],[IsException])
  values         
('9316','3',NULL,'1900-01-01 03:00:00.000','1899-12-30 21:00:00.000',NULL,NULL,'0','18','0');
INSERT INTO [dbo].[Calenderdetail]
           ([clndrid],[day],[date],[starttime_shift 1],[EndTime _shift 1 ],[StartTime_Shift2 ],[EndTime_Shift2],[Holiday_flag],[hours ],[IsException])
		   values
('9316','4',NULL,'1900-01-01 03:00:00.000','1899-12-30 21:00:00.000',NULL,NULL,'0','18','0');
INSERT INTO [dbo].[Calenderdetail]
           ([clndrid],[day],[date],[starttime_shift 1],[EndTime _shift 1 ],[StartTime_Shift2 ],[EndTime_Shift2],[Holiday_flag],[hours ],[IsException])
		   values
('9316','5',NULL,'1900-01-01 03:00:00.000','1899-12-30 21:00:00.000',NULL,NULL,'0','18','0');
INSERT INTO [dbo].[Calenderdetail]
           ([clndrid],[day],[date],[starttime_shift 1],[EndTime _shift 1 ],[StartTime_Shift2 ],[EndTime_Shift2],[Holiday_flag],[hours ],[IsException])
		   values
('9316','6',NULL,'1900-01-01 03:00:00.000','1899-12-30 21:00:00.000',NULL,NULL,'0','18','0');
INSERT INTO [dbo].[Calenderdetail]
           ([clndrid],[day],[date],[starttime_shift 1],[EndTime _shift 1 ],[StartTime_Shift2 ],[EndTime_Shift2],[Holiday_flag],[hours ],[IsException])
		   values 
('9316','7',NULL,'1900-01-01 03:00:00.000','1899-12-30 21:00:00.000',NULL,NULL,'0','18','0');

INSERT INTO [dbo].[Calenderdetail]
           ([clndrid],[day],[date],[starttime_shift 1],[EndTime _shift 1 ],[StartTime_Shift2 ],[EndTime_Shift2],[Holiday_flag],[hours ],[IsException])
           
values
('9316','5','2018-08-23 00:00:00.000',NULL,NULL,NULL,NULL,'1','0','1')
INSERT INTO [dbo].[Calenderdetail]
           ([clndrid],[day],[date],[starttime_shift 1],[EndTime _shift 1 ],[StartTime_Shift2 ],[EndTime_Shift2],[Holiday_flag],[hours ],[IsException])
           
values
('9316','5','2018-05-31 00:00:00.000',NULL,NULL,NULL,NULL,'1','0','1')

--------------function

CREATE function [dbo].[getworkinghours]
( @datefrom date ,
--@Dateto datetime ,
@calenderid float
)

Returns int
as
Begin

Declare @calculatehours int

select @calculatehours =  Case when  DATEpart(dw,@datefrom) = 1 then 0 
 when  DATEpart(dw,@datefrom) <> 1 and @datefrom in (Select date from calenderdetail where datepart (year,date) = 2018) then 0 
 ---when  DATEpart(dw,@datefrom) <> 1 and (holiday_flag = 0) and( Isexception = 0)  and  Calenderdetail.[day] = DATEpart(dw,@datefrom) then [hours ] end 
 when  DATEpart(dw,@datefrom) <> 1  and  (holiday_flag = 0) and( Isexception = 0)and @datefrom not in (Select date from calenderdetail where datepart (year,date) = 2018)  then [hours ] 
 end
 from calenderdetail 
 where clndrid = @calenderid

Return  @calculatehours

end

-------------
WITH  SplitActivitybyDaterange_CTE  AS 
(
    SELECT 
        task_id,
      CAST (early_start_date AS DATETime) AS early_start_date,
      DATEADD(day, -1, DATEADD(DD,1,early_start_date)) newDateTo,
       early_end_date,
	   target_work_qty,
	   DATEpart(dw,EARLY_START_DATE) as weeknumber,
	   clndr_id
    FROM 
       task
    UNION ALL
        SELECT 
         m.task_id,
         DATEADD(DD,1,c.early_start_date),
         C.newDateTo,
         c.early_end_date,
		 c.target_work_qty,
		 DATEpart(dw,c.early_start_date) as weeknumber,
		 c.clndr_id
        FROM 
            TASK m
            JOIN SplitActivitybyDaterange_CTE c ON  c.task_id = m.task_id
            AND DATEADD(DD,1,c.early_start_date) < =  m.early_end_date 
)
SELECT

task_id,

--day(EARLY_START_DATE),
	DATENAME(dw,EARLY_START_DATE) ,
	DATEpart(dw,EARLY_START_DATE) as weeknumber,
	EARLY_START_DATE,
	early_end_date,
    target_work_qty,
	clndr_id,
[dbo].[getworkinghours](EARLY_START_DATE , 9316) as hoursfromfunction

FROM 
    SplitActivitybyDaterange_CTE SAC

where task_id = 286519

OPTION (MAXRECURSION 0);

View schema
Absolute service time: 0,23 sec, absolute service time: 0,23 sec