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)