dimanche 23 août 2015

SQL Server conditional query

I have a table for attendance entries looking like this :

enter image description here

I need a query to export the following format :

enter image description here

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