How to select date between time in sql

siva nathan

I want to select date between time in sql

i have a table called temp,records of temp shown below

in above table employee 1 punched 3 times for a day

if employee punched morning 8 am upto 10 am means at that minimum punch is intime

if employee punched evening greater than 5 pm means maximum of punching as outtime

my expected result is

2 Replies

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

rajendra singh9y ago
Accepted answer
select empid,
(select min(attandancedate) from tbl_test where empid=a1.empid and (cast(attandancedate as time) between '08:00:00' and '09:59:59')) as intime,
(select max(attandancedate) from tbl_test where empid=a1.empid and (cast(attandancedate as time) >= '17:00:00')) as outtime
from tbl_test a1 group by a1.empid

But This Query Gives Null For Emp1 As Outtime Because He Does Not Punched After 5 PM

For Ideal Case We Can Remove Time from second query because we can get last punch of any employee using max, that can be use as outtime

Please Tell If This Is Usefull.
0
Technetshadow9y ago
select empid, min(attendencedate) as inTime, max(attendencedate) as outTime
from temp
group by empid;
0