How to find Check current time is in break or not?

Question

Table Name - Shift CREATE TABLE [dbo].[shift]( [shift_id] [ int ] IDENTITY(1,1) NOT NULL , [shift_name] [ varchar ](50) NULL , [from_time] [datetime] NULL , [to_time] [datetime] NULL , [added_by] [ int ] NULL , [modified_by] [ int ] NULL , [added_on] [datetime] NULL , [modified_on] [datetime] NULL , [is_active] [ bit ] NULL , [venue_id] [ int ] NULL , CONSTRAINT [PK__shift__7B267220887FF64E] PRIMARY KEY CLUSTERED ( [shift_id] ASC ) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) ON [ PRIMARY ] GO Table Name - Rel Shift break Schedule CREATE TABLE [dbo].[rel_shift_break_schedule]( [break_id] [ int ] IDENTITY(1,1) NOT NULL , [break_name] [ varchar ](250) NULL , [break_start_time] [datetime] NULL , [break_end_time] [datetime] NULL , [venue_id] [ int ] NULL , [shift_id] [ int ] NULL , [zone_id] [ int ] NULL , [added_by] [ int ] NULL , [added_on] [datetime] NULL , [modified_by] [ int ] NULL , [modified_on] [datetime] NULL , [is_active] [ bit ] NULL , PRIMARY KEY CLUSTERED ( [break_id] ASC ) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) ON [ PRIMARY ] GO Question - Each shift have multiple breaks. So, how to check current time is in break or not. Result is return True or False. Following is my running query, its working perfect, but i dont want to use WHILE LOOP because of permormance impact. So please suggest me a better option over WHILE LOOP. DECLARE @shift_id INT = 65 DROP TABLE #shiftListCnt CREATE TABLE #shiftListCnt( ID INT IDENTITY(1, 1), shift_id INT , break_start_time DATETIME, break_end_time DATETIME ) INSERT INTO #shiftListCnt SELECT shift_id, break_start_time,break_end_time FROM rel_shift_break_schedule WHERE shift_id = @shift_id AND is_active = 1 DECLARE @check_time_hr_int AS FLOAT , @boundry_from_time_hr_int AS FLOAT , @boundry_to_time_hr_int AS FLOAT , @result AS INT , @check_time VARCHAR (10); DECLARE @inirow INT = 1; DECLARE @NumberofRowint INT ; DECLARE @venue_id INT ; DECLARE @boundry_from_time VARCHAR (10), @boundry_to_time VARCHAR (10); SELECT @venue_id = venue_id FROM rel_shift_break_schedule WHERE shift_id = 65 SET @NumberofRowint = ( select COUNT (*) from #shiftListCnt) SET @check_time = CAST (dbo.fnGetAirportDate(@venue_id) AS TIME ) -- VENUE TIME SELECT @check_time venue_time, @NumberofRowint While @inirow BEGIN SET @boundry_from_time = CAST (( SELECT break_start_time FROM #shiftListCnt WHERE ID=@inirow) AS TIME ) SET @boundry_to_time = CAST (( SELECT break_end_time FROM #shiftListCnt WHERE ID=@inirow) AS TIME ) SET @check_time_hr_int = CONVERT ( DECIMAL (10, 2), CONVERT ( FLOAT , DATEDIFF( MINUTE , 0, @check_time))) / 60; -- IF (@check_time_hr_int -- BEGIN -- -- SET @check_time_hr_int += 24; -- END SET @boundry_from_time_hr_int = CONVERT ( DECIMAL (10, 2), CONVERT ( FLOAT , DATEDIFF( MINUTE , 0, @boundry_from_time))) / 60; SET @boundry_to_time_hr_int = CONVERT ( DECIMAL (10, 2), CONVERT ( FLOAT , DATEDIFF( MINUTE , 0, @boundry_to_time))) / 60; IF (@boundry_from_time_hr_int > @boundry_to_time_hr_int) BEGIN SET @boundry_to_time_hr_int += 24; IF (@check_time_hr_int AND @boundry_from_time_hr_int > 12) BEGIN SET @check_time_hr_int += 24; END END IF (@check_time_hr_int >= @boundry_from_time_hr_int AND @check_time_hr_int BEGIN SET @result = 1; END ELSE BEGIN SET @result = 0; END SET @inirow = @inirow + 1; END SELECT @result status --RETURN @result;

Atul Patil · Answer

Following is the insert queries for tables INSERT [dbo].[shift] ([shift_id], [shift_name], [from_time], [to_time], [added_by], [modified_by], [added_on], [modified_on], [is_active], [venue_id]) VALUES (65, N'Regular Shift', CAST(N'1900-01-01T02:00:00.000' AS DateTime), CAST(N'1900-01-01T23:59:00.000' AS DateTime), 42, 91, CAST(N'2018-06-08T16:29:06.190' AS DateTime), CAST(N'2019-08-22T14:17:14.753' AS DateTime), 1, 331) GO INSERT [dbo].[rel_shift_break_schedule] ([break_id], [break_name], [break_start_time], [break_end_time], [venue_id], [shift_id], [zone_id], [added_by], [added_on], [modified_by], [modified_on], [is_active]) VALUES (7, N'FirstBreak', CAST(N'2019-07-09T00:30:00.000' AS DateTime), CAST(N'2019-07-09T08:23:00.000' AS DateTime), 331, 65, 88, 91, CAST(N'2019-11-13T10:17:39.120' AS DateTime), 91, CAST(N'2019-11-13T10:17:39.120' AS DateTime), 1) INSERT [dbo].[rel_shift_break_schedule] ([break_id], [break_name], [break_start_time], [break_end_time], [venue_id], [shift_id], [zone_id], [added_by], [added_on], [modified_by], [modified_on], [is_active]) VALUES (8, N'SecondBreak', CAST(N'2019-07-09T08:25:00.000' AS DateTime), CAST(N'2019-07-09T09:00:00.000' AS DateTime), 331, 65, 88, 91, CAST(N'2019-11-13T10:17:39.160' AS DateTime), 91, CAST(N'2019-11-13T10:17:39.160' AS DateTime), 1) INSERT [dbo].[rel_shift_break_schedule] ([break_id], [break_name], [break_start_time], [break_end_time], [venue_id], [shift_id], [zone_id], [added_by], [added_on], [modified_by], [modified_on], [is_active]) VALUES (9, N'ThirdBreak', CAST(N'2019-07-09T15:30:00.000' AS DateTime), CAST(N'2019-07-09T16:00:00.000' AS DateTime), 331, 65, 88, 91, CAST(N'2019-11-13T10:17:39.180' AS DateTime), 91, CAST(N'2019-11-13T10:17:39.180' AS DateTime), 0) INSERT [dbo].[rel_shift_break_schedule] ([break_id], [break_name], [break_start_time], [break_end_time], [venue_id], [shift_id], [zone_id], [added_by], [added_on], [modified_by], [modified_on], [is_active]) VALUES (10, N'4th Break', CAST(N'2019-07-09T18:15:00.000' AS DateTime), CAST(N'2019-07-09T18:30:00.000' AS DateTime), 331, 65, 88, 91, CAST(N'2019-11-13T10:17:39.187' AS DateTime), 91, CAST(N'2019-11-13T10:17:39.187' AS DateTime), 0)

Farhan Ahmed · Answer

Example CurrentDate GETDATE() IN INSERT QUERY