I'm a newbie in [SQL] Server; I have trouble to write a sql-query to create a table for [time-and-attendance]
i am getting perfect output on in_time and out_time are on same day , but not getting in time and out time are on different time
i Have a table named with all employee punch data with below details
| Employee_Id | Attend_date | ETime | Punch_mode |
|---|---|---|---|
| 452 | 2023-03-03 00:00:00.000 | 4.52 | 2 |
| 452 | 2023-03-03 00:00:00.000 | 16.52 | 1 |
| 452 | 2023-03-03 00:00:00.000 | 16.55 | 1 |
| 452 | 2023-03-04 00:00:00.000 | 16.49 | 1 |
| 452 | 2023-03-04 00:00:00.000 | 4.51 | 2 |
| 452 | 2023-03-05 00:00:00.000 | 4.25 | 2 |
mode 2 is check out and mode 1 is check in
by using below #sql query
'SELECT
A.E_ID,
MAX(A.Attend_date) as attendance_date,
MIN(A.E_TIME) as IN_TIME,
MAX(A.E_TIME) as OUT_TIME
FROM ATT AS A
WHERE A.E_ID ='452' and A.Attend_date ='2023-03-03 00:00:00.000'
group by A.attend_date'
i am getting this which is not correct
| E_ID | attendate | IN_TIME | OUT_TIME |
|---|---|---|---|
| 452 | 2023-03-03 00:00:00.000 | 4.52 | 16.55 |
i would like to get the data in this for above employee in below format
| empl_mach_id | attend_date | In_time | out_time |
|---|---|---|---|
| 452 | 2023-03-03 00:00:00.000 | 16.52 | 4.51 |
| 452 | 2023-03-04 00:00:00.000 | 16.49 | 4.25 |
Please help me to sort out this
Thanks in advance
