Improve code to return he correct code Id

Hello SQL team,

I am struggling with this SQL code and I want to optimize it/correct it to show correct interval.

First of all, I have this table called fboworkscheduleIntervalDim

FboSiteId TimeIntervalTypeId StartDate EndDate DayOfWeek StartHour EndHour TimeZone
19 4 2024/01/01 2024/12/31 4 8:01 19:00 LT
19 1 2024/01/01 2024/12/31 4 19:00 23:00 LT
19 2 2024/01/01 2024/12/31 4 23:01 06:59 LT
19 3 2024/01/01 2024/12/31 4 7:00 8:00 LT

My main table is [GbiStgDb].[dbo].[FboGroupMovementT]
Example: if I have for instance a movement with the datetime 25.4.2024 00:21, I want to calculate the range dates ( between 24.04;2024 23:01 and 25.04;2024 6:59) and return 1 as IntervalId

here is the main code to calculate and display this operatingintervaltypeId
UPDATE t

			SET  [OustideOperatingIntervalTypeId]= CASE WHEN r.TimeIntervalTypeName IN (
					                                                'After Hours',
																	'Night Time',
																	'Call-Out') 
															THEN r.TimeIntervalTypeId
					                                        ELSE -2
															END

							FROM [GbiStgDb].[dbo].[FboGroupMovementT] t
							LEFT JOIN

							(
							     SELECT DISTINCT
																		 
																		   g.MovementDateTimeLT,
																		   c.[WeekDay],
																		   tm.TimeIntervalTypeId,
																		   tm.TimeIntervalTypeName,
																			g.MovementSourceId,
																			fbo.AirportIcao,
																			work.StartHour
																			,work.EndHour
																





																		,range_start_date= CASE WHEN cast(g.MovementTimeLT as time)>= cast('00:00' as time) AND cast(g.MovementTimeLT as time)<= cast('06:00' as time)
																		                        THEN (cast(dateadd(day,-1,g.MovementDateLT) as datetime)+cASt(work.StartHour AS datetime))
																								
																								ELSE cast(g.MovementDateLT as datetime)+cASt(work.StartHour AS datetime)
																			                     			                       
																		                                           END,
																		range_end_date=cast(g.MovementDateLT as datetime)+ cast(work.EndHour as datetime)
																			
																		
																			
		 
																								FROM  [GbiStgDb].[dbo].[FboGroupMovementT] AS g
																								INNER JOIN [GbiDwhDb].[global].[CalendarDim] as c  
																								 ON g.MovementDateLT= c.DateId

																								INNER JOIN [GbiDwhDb].[global].[GroupFboSiteDim] AS fbo
																								ON g.HandlingFboIcao= fbo.AirportIcao 
																								and g.HandlingFboIcao= 'TNCM'
																								
														
																								left JOIN 
																									[GbiDwhDb].[fbo].[WorkScheduleDim] AS work
																									ON g.MovementDateLT between work.StartDate and work.EndDate
																									AND  g.MovementDateTimeLT between 
													                                                  
																							--range_Startdatetime_interval
																									CASE WHEN cast(g.MovementTimeLT as time)>= cast('00:00' as time) 
																									AND cast(g.MovementTimeLT as time)<= cast('06:00' as time) THEN 
																									(cast(dateadd(day,-1,g.MovementDateLT) as datetime)+cASt(work.StartHour AS datetime))
																									ELSE cast(g.MovementDateLT as datetime)+cASt(work.StartHour AS datetime)        
																									END
																											


																									       AND 
																									--range_Enddatetime_interval
																									  cast(g.MovementDateLT as datetime)+ cast(work.EndHour as datetime)
																									   


																										
																									      AND c.[WeekDay]= work.[DayOfWeek]
																									      AND fbo.FboSiteId=work.FboSiteId
																			
																										LEFT JOIN [GbiDwhDb].[global].[TimeIntervalTypeDim] tm
																										ON work.TimeIntervaltypeId= tm.TimeIntervalTypeId

Thanks in advance for any help to improve my code ( as I am working to improve my SQL skills)

How to post a T-SQL question on a public forum | spaghettidba

1 Like