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

Atul Patil

6y1.1k

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 <= @NumberofRowint
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 < 1)
-- 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 <= 12 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 <= @boundry_to_time_hr_int)
BEGIN
SET @result = 1;
END
ELSE
BEGIN
SET @result = 0;
END
SET @inirow = @inirow + 1;
END
SELECT @result status
--RETURN @result;

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Atul Patil6y ago
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 Ahmed6y ago
Example

CurrentDate GETDATE() IN INSERT QUERY