如何使用sql server 2008在sql子查询中使用if？ [英] How can I use the if exist in a sql subquery using sql server 2008 ?

问题描述

我正在尝试显示特定月份中每位员工批准的总工时和总权限小时数，我尝试此sql查询但是，如果其中一名员工在特定月份没有权限，则系统不会显示这个员工，我希望即使员工没有权限，系统也会显示这个员工的权限小时= 0

I'm trying to display the total worked hours and total permission hours approved for each employee in a specific month, I try this sql query but, if one of the employee doesn't have permisions in a specific month the system doesn't display this employee, i want that even if the employee doesn't have permission, the system display this employee with permissionhours = 0

<pre lang="sql">SELECT  MST_EMPLOYEE.[ID] as ID
      ,[EMPCODE]

      ,[EMPFNAME]+' '+[EMPMNAME]+' '+[EMPLNAME] AS EMPNAME
     ,(SELECT DESIGNATION FROM MST_DESIGNATION WHERE ID=
     (SELECT TOP(1) DESIGNATIONID FROM EMP_PROFESSIONALDETAILS WHERE EMPID=MST_EMPLOYEE.ID ORDER BY HIREDATE DESC)) AS DESIGNATION
    , min(Times_IN) as TimeIn , max(Times_Out) as TimeOut,
    ( select count (distinct _Date)  from Emp_Attendance  where  month (_date)= 6  and EmpID = MST_EMPLOYEE.[ID] ) as WorkedDays
    ,(select CONVERT(varchar(3),SUM(DATEDIFF(MINUTE, Times_In, Times_Out)) / 60)
     +':' +  RIGHT('0' + CONVERT(varchar(2),SUM(DATEDIFF(MINUTE, Times_In, Times_Out)) % 60),2)
      from Emp_Attendance where month (_date)= 6 and EmpID = MST_EMPLOYEE.[ID] )
     as TotalWorkedHours
     ,
     (select  CONVERT(varchar(3),SUM(DATEDIFF(MINUTE, StartTime, EndTime)) / 60)
     +':' +  RIGHT('0' + CONVERT(varchar(2),SUM(DATEDIFF(MINUTE, StartTime, EndTime)) % 60),2)
from Emp_PermissionsRequest where MONTH(_Date) = 6 and EmployeeID = MST_EMPLOYEE.[ID] )

as TotalPermissionHours

  FROM [MST_EMPLOYEE] inner join Emp_Attendance on Emp_Attendance.EmpID=MST_Employee.ID --group by MST_Employee.EmpFName
  inner join Emp_PermissionsRequest on Emp_PermissionsRequest.EmployeeID=MST_Employee.ID

 where month(Times_IN) = 6
 group by MST_Employee.ID, MST_Employee.EmpCode, EmpFName, EmpMName, EmpLName

推荐答案

<pre lang="sql">SELECT  MST_EMPLOYEE.[ID] as ID
      ,[EMPCODE]

      ,[EMPFNAME]+' '+[EMPMNAME]+' '+[EMPLNAME] AS EMPNAME
     ,(SELECT DESIGNATION FROM MST_DESIGNATION WHERE ID=
     (SELECT TOP(1) DESIGNATIONID FROM EMP_PROFESSIONALDETAILS WHERE EMPID=MST_EMPLOYEE.ID ORDER BY HIREDATE DESC)) AS DESIGNATION
    , min(Times_IN) as TimeIn , max(Times_Out) as TimeOut,
    ( select count (distinct _Date)  from Emp_Attendance  where  month (_date)= 6  and EmpID = MST_EMPLOYEE.[ID] ) as WorkedDays
    ,(select CONVERT(varchar(3),SUM(DATEDIFF(MINUTE, Times_In, Times_Out)) / 60)
     +':' +  RIGHT('0' + CONVERT(varchar(2),SUM(DATEDIFF(MINUTE, Times_In, Times_Out)) % 60),2)
      from Emp_Attendance where month (_date)= 6 and EmpID = MST_EMPLOYEE.[ID] )
     as TotalWorkedHours
     ,
     (select  CONVERT(varchar(3),SUM(DATEDIFF(MINUTE, StartTime, EndTime)) / 60)
     +':' +  RIGHT('0' + CONVERT(varchar(2),SUM(DATEDIFF(MINUTE, StartTime, EndTime)) % 60),2)
from Emp_PermissionsRequest where MONTH(_Date) = 6 and EmployeeID = MST_EMPLOYEE.[ID] )

as TotalPermissionHours

  FROM [MST_EMPLOYEE] left join Emp_Attendance on Emp_Attendance.EmpID=MST_Employee.ID --group by MST_Employee.EmpFName
  left join Emp_PermissionsRequest on Emp_PermissionsRequest.EmployeeID=MST_Employee.ID

 where month(Times_IN) = 6
 group by MST_Employee.ID, MST_Employee.EmpCode, EmpFName, EmpMName, EmpLName

这篇关于如何使用sql server 2008在sql子查询中使用if？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！