Comparar hora en rango

AStuckrath_CL · ‎2024-04-23

Hola, estoy intentando crear un rango cada 15 minutos, donde recibo una hora y valido si es mayor a desde y menor a hasta

la función definitivamente no está funcionando

a continuación un ejemplo de como debiese funcionar

Hora_RangoHora	Desde	Hasta	if(Hora_RangoHora>=Desde,1,0)	if(Hora_RangoHora<=Hasta,1,0)
00:00:48	00:00:00	00:14:59	1	1
00:00:48	00:15:00	00:29:59	0	1
00:00:48	00:30:00	00:44:59	0	1
00:00:48	00:45:00	00:59:59	0	1
00:00:48	01:00:00	01:14:59	0	1
00:00:48	01:15:00	01:29:59	0	1
00:00:48	01:30:00	01:44:59	0	1
00:00:48	01:45:00	01:59:59	0	1
00:00:48	02:00:00	02:14:59	0	1
00:00:48	02:15:00	02:29:59	0	1
00:00:48	02:30:00	02:44:59	0	1
00:00:48	02:45:00	02:59:59	0	1
00:00:48	03:00:00	03:14:59	0	1
00:00:48	03:15:00	03:29:59	0	1
00:00:48	03:30:00	03:44:59	0	1
00:00:48	03:45:00	03:59:59	0	1
00:00:48	04:00:00	04:14:59	0	1
00:00:48	04:15:00	04:29:59	0	1
00:00:48	04:30:00	04:44:59	0	1
00:00:48	04:45:00	04:59:59	0	1
00:00:48	05:00:00	05:14:59	0	1
00:00:48	05:15:00	05:29:59	0	1
00:00:48	05:30:00	05:44:59	0	1
00:00:48	05:45:00	05:59:59	0	1
00:00:48	06:00:00	06:14:59	0	1
00:00:48	06:15:00	06:29:59	0	1
00:00:48	06:30:00	06:44:59	0	1
00:00:48	06:45:00	06:59:59	0	1
00:00:48	07:00:00	07:14:59	0	1
00:00:48	07:15:00	07:29:59	0	1
00:00:48	07:30:00	07:44:59	0	1
00:00:48	07:45:00	07:59:59	0	1
00:00:48	08:00:00	08:14:59	0	1
00:00:48	08:15:00	08:29:59	0	1
00:00:48	08:30:00	08:44:59	0	1
00:00:48	08:45:00	08:59:59	0	1
00:00:48	09:00:00	09:14:59	0	1
00:00:48	09:15:00	09:29:59	0	1
00:00:48	09:30:00	09:44:59	0	1
00:00:48	09:45:00	09:59:59	0	1
00:00:48	10:00:00	10:14:59	0	1
00:00:48	10:15:00	10:29:59	0	1
00:00:48	10:30:00	10:44:59	0	1
00:00:48	10:45:00	10:59:59	0	1
00:00:48	11:00:00	11:14:59	0	1
00:00:48	11:15:00	11:29:59	0	1
00:00:48	11:30:00	11:44:59	0	1
00:00:48	11:45:00	11:59:59	0	1
00:00:48	12:00:00	12:14:59	0	1
00:00:48	12:15:00	12:29:59	0	1
00:00:48	12:30:00	12:44:59	0	1
00:00:48	12:45:00	12:59:59	0	1
00:00:48	13:00:00	13:14:59	0	1
00:00:48	13:15:00	13:29:59	0	1
00:00:48	13:30:00	13:44:59	0	1
00:00:48	13:45:00	13:59:59	0	1
00:00:48	14:00:00	14:14:59	0	1
00:00:48	14:15:00	14:29:59	0	1
00:00:48	14:30:00	14:44:59	0	1
00:00:48	14:45:00	14:59:59	0	1
00:00:48	15:00:00	15:14:59	0	1
00:00:48	15:15:00	15:29:59	0	1
00:00:48	15:30:00	15:44:59	0	1
00:00:48	15:45:00	15:59:59	0	1
00:00:48	16:00:00	16:14:59	0	1
00:00:48	16:15:00	16:29:59	0	1
00:00:48	16:30:00	16:44:59	0	1
00:00:48	16:45:00	16:59:59	0	1
00:00:48	17:00:00	17:14:59	0	1
00:00:48	17:15:00	17:29:59	0	1
00:00:48	17:30:00	17:44:59	0	1
00:00:48	17:45:00	17:59:59	0	1
00:00:48	18:00:00	18:14:59	0	1
00:00:48	18:15:00	18:29:59	0	1
00:00:48	18:30:00	18:44:59	0	1
00:00:48	18:45:00	18:59:59	0	1
00:00:48	19:00:00	19:14:59	0	1
00:00:48	19:15:00	19:29:59	0	1
00:00:48	19:30:00	19:44:59	0	1
00:00:48	19:45:00	19:59:59	0	1
00:00:48	20:00:00	20:14:59	0	1
00:00:48	20:15:00	20:29:59	0	1
00:00:48	20:30:00	20:44:59	0	1
00:00:48	20:45:00	20:59:59	0	1
00:00:48	21:00:00	21:14:59	0	1
00:00:48	21:15:00	21:29:59	0	1
00:00:48	21:30:00	21:44:59	0	1
00:00:48	21:45:00	21:59:59	0	1
00:00:48	22:00:00	22:14:59	0	1
00:00:48	22:15:00	22:29:59	0	1
00:00:48	22:30:00	22:44:59	0	1
00:00:48	22:45:00	22:59:59	0	1
00:00:48	23:00:00	23:14:59	0	1
00:00:48	23:15:00	23:29:59	0	1
00:00:48	23:30:00	23:44:59	0	1
00:00:48	23:45:00	23:59:59	0	1

código de carga

MaestroFecha:

LOAD Distinct

ID_Fecha,

Date(Floor(ID_Fecha),'DD/MM/YYYY') as Fecha,

Year(ID_Fecha) as Año,

Month(ID_Fecha) as Mes,

Dual(Year(ID_Fecha)&'-'&Month(ID_Fecha), monthstart(ID_Fecha)) AS AñoMes,

Dual('Q'&Num(Ceil(Num(Month(ID_Fecha))/3)),Num(Ceil(NUM(Month(ID_Fecha))/3),00)) AS Quarter,

time(ID_Fecha) as Hora

Resident Consolidado;

Maestro_RangoHora_TEMP:

LOAD HoraDesde as HDesde,

HoraHasta as HHasta

FROM [lib://Aldesa:DataFiles/Maestros Aldesa Excel.xlsx]

(ooxml, embedded labels, table is [Maestro Hora]);

Maestro_RangoHora_TEMP2:

LOAD

Time(MakeDate(1,1,2024)&' '&HDesde) as Desde,

Time(MakeDate(1,1,2024)&' '&HHasta) as Hasta

Resident Maestro_RangoHora_TEMP;

Drop table Maestro_RangoHora_TEMP;

Join (Maestro_RangoHora_TEMP2) Load Distinct time(ID_Fecha)as Hora_RangoHora Resident MaestroFecha;

Maestro_RangoHora_Temp3:

LOAD Distinct Hora_RangoHora,

Desde as HoraDesde,

Hasta as HoraHasta,

IF(Hora>=HoraDesde and Hora<=HoraHasta,HoraDesde&'-'&HoraHasta,'Vacio') as RangoHorario

Resident Maestro_RangoHora_TEMP2;

Drop table Maestro_RangoHora_TEMP2;

Maestro_RangoHora:

NoConcatenate

LOAD Hora_RangoHora as Hora, HoraDesde, HoraHasta,

RangoHorario Resident Maestro_RangoHora_Temp3 Where RangoHorario<>'Vacio';

Drop table Maestro_RangoHora_Temp3;

F_B · ‎2024-08-13

HI @AStuckrath_CL ,

here’s a refined version of your script with some adjustments and added comments for clarity:

// Load distinct dates and times from Consolidado
MaestroFecha:
LOAD Distinct
ID_Fecha,
Date(Floor(ID_Fecha),'DD/MM/YYYY') as Fecha,
Year(ID_Fecha) as Año,
Month(ID_Fecha) as Mes,
Dual(Year(ID_Fecha)&'-'&Month(ID_Fecha), monthstart(ID_Fecha)) AS AñoMes,
Dual('Q'&Num(Ceil(Num(Month(ID_Fecha))/3)),Num(Ceil(NUM(Month(ID_Fecha))/3),00)) AS Quarter,
Time(ID_Fecha) as Hora
Resident Consolidado;

// Load time ranges from Excel file
Maestro_RangoHora_TEMP:
LOAD
HoraDesde as HDesde,
HoraHasta as HHasta
FROM [lib://Aldesa:DataFiles/Maestros Aldesa Excel.xlsx]
(ooxml, embedded labels, table is [Maestro Hora]);

// Convert the time ranges to a comparable format
Maestro_RangoHora_TEMP2:
LOAD
Time(MakeDate(1,1,2024)&' '&HDesde) as Desde,
Time(MakeDate(1,1,2024)&' '&HHasta) as Hasta
Resident Maestro_RangoHora_TEMP;

Drop table Maestro_RangoHora_TEMP;

// Join the time ranges with the times from MaestroFecha
Join (Maestro_RangoHora_TEMP2)
LOAD Distinct
Time(ID_Fecha) as Hora_RangoHora
Resident MaestroFecha;

// Validate if the time falls within the specified ranges
Maestro_RangoHora_Temp3:
LOAD Distinct
Hora_RangoHora,
Desde as HoraDesde,
Hasta as HoraHasta,
IF(Hora_RangoHora >= HoraDesde and Hora_RangoHora <= HoraHasta,
HoraDesde & '-' & HoraHasta,
'Vacio') as RangoHorario
Resident Maestro_RangoHora_TEMP2;

Drop table Maestro_RangoHora_TEMP2;

// Filter out the valid time ranges
Maestro_RangoHora:
NoConcatenate
LOAD
Hora_RangoHora as Hora,
HoraDesde,
HoraHasta,
RangoHorario
Resident Maestro_RangoHora_Temp3
Where RangoHorario <> 'Vacio';

Drop table Maestro_RangoHora_Temp3;

Data Load Editor