Following is my table data
EnrollNumber InputMode Date
7024 0 2018-01-06 05:41:52.000
7024 0 2018-01-06 05:41:57.000
7024 1 2018-01-06 18:20:01.000
7024 0 2018-02-06 17:37:50.000
7024 0 2018-03-06 06:08:51.000
7024 1 2018-03-06 06:09:08.000
7024 1 2018-03-06 13:59:15.000
7024 0 2018-04-06 17:45:20.000
7024 1 2018-05-06 06:08:09.000
7024 0 2018-05-06 17:45:50.000
7024 1 2018-06-06 06:09:06.000
Following is the result which i am getting from query
SELECT DISTINCT EnrollNumber,
(SELECT min(CONVERT(VARCHAR(10), Date, 121)) FROM Log lg1 WHERE Log.EnrollNumber=lg1.EnrollNumber AND InputMode=0 AND CONVERT(date,lg1.Date)=CONVERT(date,Log.Date)) as Date,
(SELECT STUFF((SELECT ','+ CONVERT(VARCHAR(8), lg2.Date,114) FROM Log lg2 WHERE lg2.EnrollNumber = Log.EnrollNumber AND InputMode=0 AND CONVERT(date,lg2.Date)=CONVERT(date,Log.Date) FOR XML PATH('')),1,1,'')) as InTime,
(SELECT STUFF((SELECT ','+ CONVERT(VARCHAR(8), lg3.Date,114) FROM Log lg3 WHERE lg3.EnrollNumber = Log.EnrollNumber AND InputMode=1 AND CONVERT(date,lg3.Date)=CONVERT(date,Log.Date) FOR XML PATH('')),1,1,'')) as OutTime
FROM Log where EnrollNumber='7024' AND InputMode=0 order by [Date]
EnrollNumber Date InTime OutTime
7024 2018-01-06 05:41:52,05:41:57 18:20:01
7024 2018-02-06 17:37:50 NULL
7024 2018-03-06 06:08:51 06:09:08,13:59:15
7024 2018-04-06 17:45:20 NULL
7024 2018-05-06 17:45:50 06:08:09
As we see,I am not getting 2018-06-06 data, which input mode is only 1, so what i have to modify in query.
