I want to display IN and OUT in One column in Rdlc report
I just want to display time not date.
;with cte as (
select a.EnrollNumber,a.EmpName,a.FatherName,b.Date,b.Day,c.A_Date ,a.Duty_Hours,a.INTIME,lD.L_Date Leaves,ld.leave_Name as lName
from EmpMaster a
cross join MonthDate b
left join AttendenceLOG c
on a.EnrollNumber=c.EnrollNumber
and cast(c.A_Date as date)=b.Date
left join Tbl_Leaves_Details LD on LD.empid=a.EnrollNumber
--full join tbl_Leave_type t on t.Leave_Type_Id=ld.Leave_Type_Id
and Ld.L_date =b.Date
)
,cte1 AS (
SELECT EnrollNumbeR,EmpName,FatherName,Date,Day,Duty_Hours,INTIME,leaves,lName
,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]
FROM CTE
GROUP BY EnrollNumbeR,EmpName,FatherName,Date,Day,Duty_Hours,INTIME,Leaves,lName)
,CTE2 AS (
SELECT EnrollNumbeR,EmpName,FatherName,Date,LEFT(Day,3)Day,Duty_Hours,INTIME,Leaves,lName
,CASE WHEN [IN(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([IN(A_Date)] AS DATE) AS DATETIME)) THEN NULL ELSE [IN(A_Date)] END TimeIN
,CASE WHEN [OUT(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([OUT(A_Date)] AS DATE) AS DATETIME)) THEN [OUT(A_Date)] ELSE NULL END TimeOut
FROM cte1)
,CET3 as (
SELECT *
,CASE
WHEN [DAY] ='Sun' and TimeIN IS NULL AND TimeOut IS NULL THEN 'S'
WHEN DATEDIFF(HOUR,TimeIN,TimeOut)>=Duty_Hours THEN 'P'
when Leaves is not null then lName
ELSE 'A' END STATUS,
Case
when Day ='Sun' then 1
WHEN DATEDIFF(HOUR,TimeIN,TimeOut)>=Duty_Hours THEN 1 else 0
END
Duty,
CASE WHEN TimeIN IS NOT NULL AND TimeOut IS NOT NULL AND DATEDIFF(HOUR,TimeIN,TimeOut)>=8
THEN CAST(CAST(DATEDIFF(MINUTE,TimeIN,TimeOut) AS FLOAT)/60 AS decimal(10,2)) ELSE 0 END [HOUR],
Case when DATEDIFF(MINUTE,INTIMe,Convert(TIme(0),TimeIN))>15 Then 1 else 0 END Late
,DATEDIFF(MINUTE,INTIMe,Convert(TIme(0),TimeIN)) LateM,
Case when (case when Day ='Sun' then 1 WHEN DATEDIFF(HOUR,TimeIN,TimeOut)>=Duty_Hours THEN 1 else 0 ENd ) =1
then sum(case when Day ='Sun' then 1 WHEN DATEDIFF(HOUR,TimeIN,TimeOut)>=Duty_Hours THEN 1 else 0 ENd) else 0 end Total
,Case when Day= 'Sun' then 0 WHEN DATEDIFF(HOUR,TimeIN,TimeOut)>=Duty_Hours then 0 else 1
END Absent
FROM CTE2
GROUP BY EnrollNumbeR,EmpName,FatherName,Date,Day,Duty_Hours,InTime,TimeIN,TimeOut,Leaves,lName)
SELECT EnrollNumbeR,EmpName,FatherName,Date,Day,Duty_Hours,InTime,Leaves,lName,Absent,Total,Late,LateM
,CASE WHEN TimeIN IS NULL AND TimeOut IS NULL AND [STATUS]='A'
THEN [STATUS]
ELSE CONVERT(varchar(30),TimeIN,108)
END TimeIN
,TimeOut,[STATUS],[HOUR]
FROM CET3
where date between @DateFrom and @DateTo
GROUP BY EnrollNumbeR,EmpName,FatherName,Date,Day,Duty_Hours,InTime,TimeIN,TimeOut,Leaves,lName,STATUS,HOUR,Absent,Total,Late,LateM
ORDER BY EnrollNumbeR,Date