Improve code to return he correct code Id

Transact-SQL
1

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)

2

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

1 Like
3 
Hi

Hope this helps 

UPDATE t
SET [OutsideOperatingIntervalTypeId] = 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 
            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 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
) r ON t.MovementDateTimeLT = r.MovementDateTimeLT;