Get the login time for one week I have two tables like this 

Tc_Date
2016-02-01 00:00:00
2016-02-02 00:00:00
2016-02-03 00:00:00
2016-02-04 00:00:00
2016-02-05 00:00:00
2016-02-06 00:00:00
2016-02-07 00:00:00
2016-02-08 00:00:00
2016-02-09 00:00:00
2016-02-10 00:00:00


and

EmpID   Office_Date          Login_Time                 Logout_Time
397     2016-02-01 00:00:00  2016-02-01 08:35:09.780    2016-02-01 17:18:56.230
397     2016-02-02 00:00:00  2016-02-02 08:28:50.713    2016-02-02 17:10:36.997
397     2016-02-03 00:00:00  2016-02-03 08:27:38.550    2016-02-03 17:22:49.037
397     2016-02-04 00:00:00  2016-02-04 08:34:26.653    2016-02-04 17:11:04.540
397     2016-02-05 00:00:00  2016-02-05 08:36:12.833    2016-02-05 17:07:22.797
397     2016-02-09 00:00:00  2016-02-09 08:35:58.840    2016-02-09 17:15:40.203
397     2016-02-10 00:00:00  2016-02-10 08:30:51.940    2016-02-10 17:08:58.480
396     2016-02-01 00:00:00  2016-02-01 08:42:39.000    2016-02-01 17:57:39.000
396     2016-02-02 00:00:00  2016-02-02 07:49:36.090    2016-02-02 17:05:00.470
396     2016-02-03 00:00:00  2016-02-03 07:49:54.583    2016-02-03 17:14:57.663
396     2016-02-04 00:00:00  2016-02-04 07:54:15.087    2016-02-04 17:05:38.373
396     2016-02-05 00:00:00  2016-02-05 07:51:12.133    2016-02-05 17:00:42.870
396     2016-02-08 00:00:00  2016-02-08 07:58:06.833    2016-02-08 17:01:52.750
396     2016-02-09 00:00:00  2016-02-09 07:56:26.730    2016-02-09 17:04:31.650
396     2016-02-10 00:00:00  2016-02-10 07:43:16.463    2016-02-10 17:03:19.480


i want the answer like this

Empid  2016-02-01  2016-02-02  2016-02-03  2016-02-04  2016-02-05  2016-02-06...

397    08:35:09    08:28:50    08:27:38    08:34:26    08:36:12     Absent

396    08:42:39    07:49:36    07:49:54    07:54:15    07:51:12     Absent
You can solve it using a dynamic PIVOT solution.

Have a look at MS-Docs about PIVOT.




DECLARE @col AS nvarchar(MAX),
        @cmd AS nvarchar(MAX);

SET @col = STUFF((SELECT distinct ',' + QUOTENAME(CONVERT(DATE,dt,101)) 
            FROM Tc_Date
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
            ,1,1,'')

SET @cmd =   'SELECT * '
           + ' FROM  (SELECT EmpId,  Office_Date, CONVERT(VARCHAR(20), Login_time, 108) LoginTime FROM Logins) src'
           + ' PIVOT (MAX(LoginTime) FOR Office_Date IN (' + @col + ')) pvt';

EXECUTE (@cmd);
GO



EmpId | 2016-02-01 | 2016-02-02 | 2016-02-03 | 2016-02-04 | 2016-02-05 | 2016-02-06 | 2016-02-07 | 2016-02-08 | 2016-02-09 | 2016-02-10
----: | :--------- | :--------- | :--------- | :--------- | :--------- | :--------- | :--------- | :--------- | :--------- | :---------
  396 | 08:42:39   | 07:49:36   | 07:49:54   | 07:54:15   | 07:51:12   | null       | null       | 07:58:06   | 07:56:26   | 07:43:16  
  397 | 08:35:09   | 08:28:50   | 08:27:38   | 08:34:26   | 08:36:12   | null       | null       | null       | 08:35:58   | 08:30:51  



dbfiddle here