I have a table for attendance entries looking like this :
I need a query to export the following format :
which present the Check-in and Check-out timings using British/French format (103)
I tried the following query :
SELECT UserID,
(Select Min(checktime) from [FingerPrint].[dbo].[CHECKINOUT] I where CONVERT(VARCHAR(10),i.checktime,111) = CONVERT(VARCHAR(10),p.checktime,111) and i.userid = p.userid ),
(Select Max(checktime) from [FingerPrint].[dbo].[CHECKINOUT] I where CONVERT(VARCHAR(10),i.checktime,111) = CONVERT(VARCHAR(10),p.checktime,111) and i.userid = p.userid)
FROM [FingerPrint].[dbo].[CHECKINOUT] p
GROUP BY p.checktime,p.UserID
Basically I need a query to select the minimum time (check-in) and maximum time (check-out) for each day using the export format above, yet when there is no value for check-in and check-out, then query should return (null) for time.
Aucun commentaire:
Enregistrer un commentaire