Compare time ranges (impossible mission)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Compare time ranges (impossible mission)

Author

Topic

antoniomm91
Starting Member

7 Posts

Posted - 2011-01-11 : 09:55:20

Hello everybody!

I am gettin' kind of green and a headache with this. Let's try to explain:

I have a table that specifies shifts:
-Shift----StartTime-------------------EndTime
--1------1900-12-30 08:00:00.000----1900-12-30 15:30:00.000
--2------1900-01-01 15:30:00.000----1900-01-01 23:00:00.000
--3------1900-01-01 23:00:00.000----1900-01-01 06:30:00.000

Then, I specify a time range in order to identify the shift it belongs; for example:

If I say that StartTime is 09:30:00.000 and EndTime is 14:05:00.000 it belongs to 1st Shift. Right?

Up to now everything is ok. My problem is with 3rd Shift.

Thrird shift involves 2 different days 'cause it starts at 11:00pm and ends at 6:30am (next day), so when I try to compare times for a shift like this:

StartTime: 02:29am (02:29:00.000)
EndTime: 04:29am (04:29:00.000)

It should belong to third shift but if I compare 2:29 am with starttime from my table which is 23:00 it is not a greater value, in fact it is a lower value because the system is not identifying that my value belongs to the next day.

I can't figure out a way to identify times for this 3rd shift 'cause I shouldn't provide dates, the values in the shifttable are not supposed to be variables, so I can't be modifying dates everytime i compare and my system should be able to identify shifts ignoring dates, just time.

Can anyone help me?

Thank you in advance!
I am posting the SP that I have created for your reference:

CREATE PROCEDURE spCopSDelayCertificate ( @dcCorrelativoNaveViaje INT )
AS
BEGIN
--
DECLARE @dcTurno SMALLINT
, @dgDescripcionEspanol VARCHAR(60)
, @dgDescripcionIngles VARCHAR(60)
, @dfHoraInicio DATETIME
, @dfHoraTermino DATETIME
, @HoraInicioDelay DATETIME
, @FechaInicioPara DATETIME
, @dfHoraInicioAux DATETIME
, @dfHoraTerminoAux DATETIME
--
SELECT 'Nave' = Nave.dgNave + ' v.' + NaveViaje.dgViaje
, 'Viaje' = NaveViaje.dgViaje
, 'Fecha' = DATENAME(MONTH, ParaDemora.dfInicioParaDemora ) + ' ' +
CASE WHEN DAY(ParaDemora.dfInicioParaDemora) = 1 THEN CONVERT(VARCHAR(2), DAY(ParaDemora.dfInicioParaDemora)) + 'st'
WHEN DAY(ParaDemora.dfInicioParaDemora) = 2 THEN CONVERT(VARCHAR(2), DAY(ParaDemora.dfInicioParaDemora)) + 'nd'
WHEN DAY(ParaDemora.dfInicioParaDemora) = 3 THEN CONVERT(VARCHAR(2), DAY(ParaDemora.dfInicioParaDemora)) + 'rd'
ELSE CONVERT(VARCHAR(2), DAY(ParaDemora.dfInicioParaDemora)) + 'th'
END + ', ' + convert(varchar(4),year(ParaDemora.dfInicioParaDemora))
, 'Grua' = Grua.dgDescripcionIngles
, 'Turno' = '3rd Shift'
, 'Dia' = upper(datename( weekday, ParaDemora.dfInicioParaDemora ))
, 'HoraInicio' = convert(varchar(10),ParaDemora.dfInicioParaDemora, 108)
, 'HoraTermino' = convert(varchar(10),ParaDemora.dfTerminoParaDemora, 108)
, 'NroGangs' = isnull(ParaDemora.dnCuadrilla,0)
, 'Observaciones' = ParaDemora.dgObsParaDemora
, CASE WHEN (CONVERT(VARCHAR(10),ParaDemora.dfInicioParaDemora, 108) = '15:30:00'
or CONVERT(VARCHAR(10),ParaDemora.dfInicioParaDemora, 108) = '23:00:00')
THEN DATEADD(mi, 1, ParaDemora.dfInicioParaDemora)
ELSE ParaDemora.dfInicioParaDemora
END 'FechaInicioPara'
, 'dfHoraInicioTurno' = GETDATE()
, 'dfHoraTerminoTurno' = GETDATE()
INTO #delay
FROM tbCopParaDemora ParaDemora
, bdBasico..tbCopGruaFaena Grua
, tbGralNaveViaje NaveViaje
, bdBasico..tbGralNave Nave
WHERE ParaDemora.dcCorrelativoNaveViaje = @dcCorrelativoNaveViaje
AND ParaDemora.dcGrua *= Grua.dcGrua
AND NaveViaje.dcCorrelativoNaveViaje = @dcCorrelativoNaveViaje
AND Nave.dcNave = NaveViaje.dcNave
GROUP BY ParaDemora.dfInicioParaDemora
, Grua.dgDescripcionIngles
, ParaDemora.dfInicioParaDemora
, ParaDemora.dfTerminoParaDemora
, ParaDemora.dgObsParaDemora
, Nave.dgNave
, NaveViaje.dgViaje
, ParaDemora.dnCuadrilla
--
DECLARE Delay_cursor CURSOR FOR
SELECT FechaInicioPara FROM #delay
OPEN Delay_cursor
FETCH next FROM Delay_cursor INTO @FechaInicioPara
WHILE @@fetch_status = 0
BEGIN
--
DECLARE TURNO_cursor CURSOR FOR
SELECT dcTurno
, dgDescripcionEspanol
, dgDescripcionIngles
, CASE WHEN dcTurno=1 THEN dfHoraInicio
ELSE DATEADD(mi, 1, dfHoraInicio)
END dfHoraInicio
, dfHoraTermino
FROM bdBasico..tbCopTurno
OPEN TURNO_cursor
FETCH next FROM TURNO_cursor INTO @dcTurno
, @dgDescripcionEspanol
, @dgDescripcionIngles
, @dfHoraInicio
, @dfHoraTermino
WHILE @@fetch_status = 0
BEGIN
SELECT @dfHoraInicioAux = @dfHoraInicio
SELECT @dfHoraTerminoAux = @dfHoraTermino
--
SELECT @dfHoraInicio = DATEADD (YEAR , YEAR(@FechaInicioPara) - YEAR( @dfHoraInicio), @dfHoraInicio )
SELECT @dfHoraInicio = DATEADD (MONTH, MONTH(@FechaInicioPara) - MONTH( @dfHoraInicio), @dfHoraInicio)
SELECT @dfHoraInicio = DATEADD (DAY, DAY(@FechaInicioPara) - DAY( @dfHoraInicio), @dfHoraInicio)
SELECT @dfHoraTermino = DATEADD (YEAR , YEAR(@FechaInicioPara) - YEAR( @dfHoraTermino), @dfHoraTermino )
SELECT @dfHoraTermino = DATEADD (MONTH, MONTH(@FechaInicioPara) - MONTH( @dfHoraTermino), @dfHoraTermino)
SELECT @dfHoraTermino = DATEADD (DAY, DAY(@FechaInicioPara) - DAY( @dfHoraTermino), @dfHoraTermino)
--
IF @dcTurno = 3
BEGIN
IF DATEPART(hh, @FechaInicioPara) = 0
SELECT @FechaInicioPara = DATEADD(DAY, 1 , @FechaInicioPara)
SELECT @dfHoraTermino = DATEADD(DAY,1 , @dfHoraTermino)
END
IF @FechaInicioPara between @dfHoraInicio and @dfHoraTermino
UPDATE #delay
SET Turno = @dgDescripcionIngles
, dfHoraInicioTurno = @dfHoraInicioAux
, dfHoraTerminoTurno = @dfHoraTerminoAux
WHERE @FechaInicioPara between @dfHoraInicio and @dfHoraTermino
AND FechaInicioPara = @FechaInicioPara
--
FETCH next FROM TURNO_cursor INTO @dcTurno
, @dgDescripcionEspanol
, @dgDescripcionIngles
, @dfHoraInicio
, @dfHoraTermino
END
CLOSE TURNO_cursor
DEALLOCATE TURNO_cursor
FETCH next from Delay_cursor into @FechaInicioPara
END
CLOSE Delay_cursor
DEALLOCATE Delay_cursor
SELECT * FROM #delay
END

Tony

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-11 : 10:01:17

Can you change the table?
1900-01-02 06:30:00.000

Jim

Everyday I learn something that somebody else already knew

antoniomm91
Starting Member

7 Posts

Posted - 2011-01-11 : 10:08:00

I did it... same thing.

Tony

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-11 : 10:40:10

Why not break shift 3 into two parts?

3------1900-01-01 23:00:00.000----1900-01-01 06:30:00.000

3a------1900-01-01 23:00:00.000----1900-01-01 23:59:59.997
3b------1900-01-01 00:00:00.000----1900-01-01 06:30:00.000

N 56°04'39.26"
E 12°55'05.63"

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-11 : 10:47:36

Something like this would work

Declare @minutes int
SELECT	@minutes = select datepart(hour, @startTime) * 60 + datepart(minute, @startTime)

SELECT	CASE 
	WHEN @minutes BETWEEN 480 AND 929 THEN '1st Shift'
	WHEN @minutes BETWEEN 930 AND 1379 THEN '2nd Shift'
	ELSE '3rd Shift'
END as shift

By the way, you have a logic error which might produce unexpected results...you have start times equal to end times. This means that the value 15:30 falls in both 1st and 2nd shift. You should adjust these to not overlap.

edit -- nice Peso. :)

antoniomm91
Starting Member

7 Posts

Posted - 2011-01-11 : 11:13:03

quote:
Originally posted by Peso

Why not break shift 3 into two parts?

3------1900-01-01 23:00:00.000----1900-01-01 06:30:00.000

3a------1900-01-01 23:00:00.000----1900-01-01 23:59:59.997
3b------1900-01-01 00:00:00.000----1900-01-01 06:30:00.000

N 56°04'39.26"
E 12°55'05.63"

That should solve the problem, but I'm not the database owner, so I can't modify it's structure. Thanks anyway!

Tony

antoniomm91
Starting Member

7 Posts

Posted - 2011-01-11 : 11:20:30

quote:
Originally posted by russell

Something like this would work

Declare @minutes int
SELECT @minutes = select datepart(hour, @startTime) * 60 + datepart(minute, @startTime)

SELECT CASE
WHEN @minutes BETWEEN 480 AND 929 THEN '1st Shift'
WHEN @minutes BETWEEN 930 AND 1379 THEN '2nd Shift'
ELSE '3rd Shift'
END as shift

By the way, you have a logic error which might produce unexpected results...you have start times equal to end times. This means that the value 15:30 falls in both 1st and 2nd shift. You should adjust these to not overlap.

edit -- nice Peso. :)

Thank you rusell, thank you for you reply and advices. I've noticed about overlap, however, the system always compares start date with respective startdate field, it will never compare it against endtime field, so this should cause no problem.

About your solution, I can try with given values, but I'm not sure where do you get it from, I mean, I know they're times expressed in minutes, but I'm not using constants but variables, I can't say "between 480 and 929", I should say "between starttime and endtime" where start and end time are fields from my database's table, so I assume that your conversion formula should work for these values too? (datepart(hour, @startTime) * 60 + datepar(minute, @startTime)). Please explain me a little more about it, however I think it's a great solution leaving 3rd shift to else condition ;-)

Thank you!

Tony

antoniomm91
Starting Member

7 Posts

Posted - 2011-01-11 : 14:40:10

quote:
Originally posted by russell

Something like this would work

Declare @minutes int
SELECT @minutes = select datepart(hour, @startTime) * 60 + datepart(minute, @startTime)

SELECT CASE
WHEN @minutes BETWEEN 480 AND 929 THEN '1st Shift'
WHEN @minutes BETWEEN 930 AND 1379 THEN '2nd Shift'
ELSE '3rd Shift'
END as shift

By the way, you have a logic error which might produce unexpected results...you have start times equal to end times. This means that the value 15:30 falls in both 1st and 2nd shift. You should adjust these to not overlap.

edit -- nice Peso. :)