How to calculate Day on the base of Hours Condition.? SELECT 
    Empcode,
    cast(Date as date) As Date,
    MIN(CASE WHEN INOUT = 1 THEN date END) AS INOUT_INTIME,
    MAX(CASE WHEN INOUT = 2 THEN date END) AS INOUT_OutTime,
    DATEDIFF(Hour ,MIN(Date), Max(Date)) AS Hours 
FROM 
    HR2 AS t 
GROUP BY 
    Empcode,
    CONVERT(date, date);


I want when Hours=>8 then Day=1 and Hours&lt;=8 then Day =0

please guide..
I want when Hours=>8 then Day=1 and Hours&lt;=8 then Day =0


So when it is 8 you want both 0 and 1? 

I have chosen hours smaller than, or equal to 8 = 1 and hours &lt; 8 = 0 as day. 

Let me know if that is not what you want.

   SELECT Empcode,cast(Date as date) As [Date],
   MIN(CASE WHEN INOUT = 1 THEN date END) AS INOUT_INTIME,
   MAX(CASE WHEN INOUT = 2 THEN date END) AS INOUT_OutTime,
   DATEDIFF(Hour ,MIN(Date),Max(Date)) AS [Hours],
   case when DATEDIFF(Hour ,MIN(Date),Max(Date)) &gt;= 8 Then 1 
        when DATEDIFF(Hour ,MIN(Date),Max(Date))  &lt; 8 then 0 
        end as [Day]
   FROM @HR2 AS t
   GROUP BY Empcode,CONVERT(date, date);


Testing

DECLARE @HR2 table(empcode int, date datetime2,inout tinyint)
insert into @HR2(empcode,date,inout)
values(1,'2019-01-03 07:47:46.487', 1)

insert into @HR2(empcode,date,inout)
values(1,'2019-01-03 17:47:46.487', 2)

insert into @HR2(empcode,date,inout)
values(2,'2019-01-03 09:47:46.487', 1)

insert into @HR2(empcode,date,inout)
values(2,'2019-01-03 15:47:46.487', 2)


SELECT Empcode,cast(Date as date) As [Date],
   MIN(CASE WHEN INOUT = 1 THEN date END) AS INOUT_INTIME,
   MAX(CASE WHEN INOUT = 2 THEN date END) AS INOUT_OutTime,
   DATEDIFF(Hour ,MIN(Date),Max(Date)) AS [Hours],
   case when DATEDIFF(Hour ,MIN(Date),Max(Date)) &gt;= 8 Then 1 
        when DATEDIFF(Hour ,MIN(Date),Max(Date))  &lt; 8 then 0 
        end as [Day]
   FROM @HR2 AS t
   GROUP BY Empcode,CONVERT(date, date);


Result

Empcode Date    INOUT_INTIME    INOUT_OutTime   Hours   Day
1   2019-01-03  2019-01-03 07:47:46.4870000 2019-01-03 17:47:46.4870000 10  1
2   2019-01-03  2019-01-03 09:47:46.4870000 2019-01-03 15:47:46.4870000 6   0