How to select date between time in sql

Question

I want to select date between time in sql i have a table called temprecords of temp shown below empid attendencedate 1 04-01-17 08:45:12 1 04-01-17 12:45:12 1 04-01-17 16:45:12 2 04-01-17 08:45:12 2 04-01-17 18:45:12 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 empid intime outtime 1 04-01-17 08:45:12 04-01-17 16:45:12 2 04-01-17 08:45:12 04-01-17 18:45:12

rajendra singh · 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.

Technetshadow · Answer

select empid min(attendencedate) as inTime max(attendencedate) as outTime from temp group by empid;

empid	attendencedate
1	04-01-17 08:45:12
1	04-01-17 12:45:12
1	04-01-17 16:45:12
2	04-01-17 08:45:12
2	04-01-17 18:45:12

empid	intime	outtime
1	04-01-17 08:45:12	04-01-17 16:45:12
2	04-01-17 08:45:12	04-01-17 18:45:12