I have a table containing data like following
EnrollNumber InputMode Date
1059 0 21-07-2018 06:00:00
1059 1 21-07-2018 13:00:00
1059 0 21-07-2018 22:00:00
1059 0 22-07-2018 06:00:00
i have tried the following query
SELECT DISTINCT (SELECT distinct Log.EnrollNumber FROM Log lg4 WHERE Log.EnrollNumber=lg4.EnrollNumber AND lg4.InputMode=Log.InputMode AND CONVERT(date,lg4.Date)=CONVERT(date,Log.Date)) as [Employee Code],(SELECT min(CONVERT(VARCHAR(10), lg2.Date, 105) + ' ' + convert(VARCHAR(8), lg2.Date, 108)) FROM Log lg2 WHERE lg2.EnrollNumber = Log.EnrollNumber AND InputMode=0 AND CONVERT(date,lg2.Date)=CONVERT(date,Log.Date)) as InTime, (SELECT max(CONVERT(VARCHAR(10), lg3.Date, 105) + ' ' + convert(VARCHAR(8), lg3.Date, 108)) FROM Log lg3 WHERE lg3.EnrollNumber = Log.EnrollNumber AND InputMode !=0 AND CONVERT(date,lg3.Date)=CONVERT(date,Log.Date)) as OutTime FROM Log left outer join Employee emp on emp.[Employee Code]=Log.EnrollNumber where [Date] >= '2018-06-30' and [Date] <= '2018-07-31' order by [Date] desc
when shift is morning then its working fine
Employee Code InTime OutTime
1059 21-07-2018 6:00:00 21-07-2018 13:00:00
when shift is night 10pm to 6am it's not showing in different row
Employee Code InTime OutTime
1059 22-07-2018 06:00:00
1059 21-07-2018 22:00:00
I need to display in following manner
Employee Code InTime OutTime
1059 21-07-2018 22:00:00 22-07-2018 06:00:00
What i have to modified in query to get above result,i.e when shift duty is night it should come in one row.