Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
function used in cte
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] GO 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] GO 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] GO --------------- 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 GO ------------- 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);
run
|
edit
|
history
|
help
0
20181CSE0068
ss sqlpractice2014
exercise
TEST-A3
exp7
ms sql 3
Tbl
sadasd
wall
TEST 2