Display two columns value in one column in ASP.Net RDLC Report

akhter
 
on Jul 05, 2021 03:11 AM
4035 Views

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

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 06, 2021 04:53 AM

Chdek with below expressions.

=FormatDateTime(Fields!TimeIN.Value, DateFormat.ShortTime) & " " & FormatDateTime(Fields!TimeOut.Value, DateFormat.ShortTime)

Or

=Format(CDate(Fields!TimeIN.Value), "hh:mm tt") & " " & Format(CDate(Fields!TimeOut.Value), "hh:mm tt")