Find Min Time in Sql

ShiftMst
shiftID	Name	startTime	EmdTime	isNightshift
1	Def	14:30	01:30	TRUE
2	test	09:30	18:00	FALSE
3	test1	23:58	6	TRUE

Find The Min Time From Table Permissible consider shift table

tblPermissibleTime
	shiftID	Starttime	EndTime	Mode
	1	14:00	15:00	In
	1	18:00	18:15	out
	1	18:16	18:30	in
	1	00:30	02:30	out
	2	09:00	10:00	In
	2	00:00	00:00	out
	2	17:30	18:30	out
	2	00:00	00:00	in
	3	23:28	00:28	In
	3	05:30	06:30	out
	3	23:00	01:00	out
	3	02:00	03:00	in

OutPut
shiftID	StartTime
1	14:00
2	09:00
3	23:00

declare @ShiftMst table (shiftID int ,Name varchar(64),startTime time,EmdTime time,isNightshift bit)

insert into @ShiftMst values(1,'Def','14:30','01:30',1)

insert into @ShiftMst values(2,'test','09:30','18:00',0)

insert into @ShiftMst values(3,'test1','23:58','06:00',1)

declare @tblPermissibleTime table (shiftID int ,startTime time,EmdTime time,Mode varchar(4))

insert into @tblPermissibleTime values(1,'14:00','15:00','In')

insert into @tblPermissibleTime values(1,'18:00','18:15','out')

insert into @tblPermissibleTime values(1,'18:16','18:30','in' )

insert into @tblPermissibleTime values(1,'00:30','02:30','out')

insert into @tblPermissibleTime values(2,'09:00','10:00','In' )

insert into @tblPermissibleTime values(2,'00:00','00:00','out')

insert into @tblPermissibleTime values(2,'17:30','18:30','out')

insert into @tblPermissibleTime values(2,'00:00','00:00','in' )

insert into @tblPermissibleTime values(3,'23:28','00:28','In' )

insert into @tblPermissibleTime values(3,'05:30','06:30','out')

insert into @tblPermissibleTime values(3,'23:00','01:00','out')

insert into @tblPermissibleTime values(3,'02:00','03:00','in')

Select p.shiftID,s.Name, MIN(p.Starttime) as StartTime from @tblPermissibleTime p

INNER JOIN @ShiftMst s ON p.shiftID=s.shiftID WHERE Mode='in' and p.EmdTime != p.startTime

group by p.shiftID,s.Name

Hello,

See if the following fits your requirement:

declare @ShiftMst table
(
[shiftID] integer not null primary key clustered
,[Name] varchar(30) not null
,[startTime] time not null
,[EmdTime] time not null
,[isNightshift] bit not null
)
insert into @ShiftMst values
(1, 'Def', '14:30', '01:30', 1)
,(2, 'test', '09:30', '18:00', 0)
,(3, 'test1', '23:58', '06:00', 1)
declare @tblPermissibleTime table
(
[shiftID] integer not null
,[startTime] time not null
,[EmdTime] time not null
,[Mode] varchar(5) not null
)
insert into @tblPermissibleTime values
(1, '14:00', '15:00', 'In')
,(1, '18:00', '18:15', 'out')
,(1, '18:16', '18:30', 'In')
,(1, '00:30', '02:30', 'out')
,(2, '09:00', '10:00', 'In')
,(2, '00:00', '00:00', 'out')
,(2, '17:30', '18:30', 'out')
,(2, '00:00', '00:00', 'In')
,(3, '23:28', '00:28', 'In')
,(3, '05:30', '06:30', 'out')
,(3, '23:00', '01:00', 'out')
,(3, '02:00', '03:00', 'In')
select [shiftID], convert(time, min([startTime]))
from (
select pt.[shiftID], (convert(datetime2, case when pt.[startTime] > '03:00:00' then '2000-01-01' else '2000-01-02' end + ' ' + convert(varchar, pt.[startTime]))) [startTime]
from @tblPermissibleTime pt
left outer join @ShiftMst sm on sm.[shiftID] = pt.[shiftID]
where 1 = 1
and pt.[Mode] = 'In'
and pt.[startTime] != '00:00'
and pt.[EmdTime] != '00:00'
) dt
group by [shiftID]

select temp.ShiftId,min( temp.Starttime) startTime from (
select [dbo].[tblPermissibleTime].ShiftId,[dbo].[tblPermissibleTime].starttime,
datediff( HOUR, (select startTime from [dbo].[ShiftMst] where [dbo].[ShiftMst].shiftID = [dbo].[tblPermissibleTime].[ShiftId])
, starttime) as minTime
from [dbo].[tblPermissibleTime] where mode='in' ) temp

where minTime >=0 group by temp.ShiftId

Dear Vishnu

I think you have some relationship with IsNightShift. Which is not explained in query. Otherwise 2:00 is always less than 23:00. Kindly explain that relationship. I will attach that in query.

Thanks All,

Predeep sir you sent me

Select p.shiftID,s.Name, MIN(p.Starttime) as StartTime from tblPermissibleTime p INNER JOIN ShiftMst s ON p.shiftID=s.shiftID WHERE Mode='in' group by p.shiftID,s.Name

but it show first 2 result but for shift 3 starttime is 23.58 and end 06:00

i want 23 in result but is show 02:00

Select p.shiftID,s.Name, MIN(p.Starttime) as StartTime from tblPermissibleTime p INNER JOIN ShiftMst s ON p.shiftID=s.shiftID WHERE Mode='in' group by p.shiftID,s.Name

select [ShiftId] ,min(Starttime) as stratTime from tableName where mode='in' group by ShiftId

Select shiftId, Min(startTime) as StartTime from tablename group by shiftid

8 Replies

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

Jayesh Vaghasiya8y ago
declare @ShiftMst table (shiftID int ,Name varchar(64),startTime time,EmdTime time,isNightshift bit)
insert into @ShiftMst values(1,'Def','14:30','01:30',1)
insert into @ShiftMst values(2,'test','09:30','18:00',0)
insert into @ShiftMst values(3,'test1','23:58','06:00',1)
declare @tblPermissibleTime table (shiftID int ,startTime time,EmdTime time,Mode varchar(4))
insert into @tblPermissibleTime values(1,'14:00','15:00','In')
insert into @tblPermissibleTime values(1,'18:00','18:15','out')
insert into @tblPermissibleTime values(1,'18:16','18:30','in' )
insert into @tblPermissibleTime values(1,'00:30','02:30','out')
insert into @tblPermissibleTime values(2,'09:00','10:00','In' )
insert into @tblPermissibleTime values(2,'00:00','00:00','out')
insert into @tblPermissibleTime values(2,'17:30','18:30','out')
insert into @tblPermissibleTime values(2,'00:00','00:00','in' )
insert into @tblPermissibleTime values(3,'23:28','00:28','In' )
insert into @tblPermissibleTime values(3,'05:30','06:30','out')
insert into @tblPermissibleTime values(3,'23:00','01:00','out')
insert into @tblPermissibleTime values(3,'02:00','03:00','in')
Select p.shiftID,s.Name, MIN(p.Starttime) as StartTime from @tblPermissibleTime p
INNER JOIN @ShiftMst s ON p.shiftID=s.shiftID WHERE Mode='in' and p.EmdTime != p.startTime
group by p.shiftID,s.Name
+2