How can I use the if exist in a sql subquery using sql server 2008 ?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

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

SQL

<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

Posted 11-Jun-15 5:39am

Member 10286520

Updated 11-Jun-15 5:41am

v3

Add a Solution

Comments

F-ES Sitecore 11-Jun-15 11:54am

Your query is hard to follow, but at a basic guess I'd try changing your "inner join" types to "left join" and go from there. A left join will keep a row for one part of the join even if the other part has to corresponding data. That "missing" part has NULL for each column value.

CHill60 11-Jun-15 16:07pm

Worth posting as a solution

Member 10286520 11-Jun-15 11:56am

thanks a lot F-ES Sitecore it workes successfully :)

ZurdoDev 11-Jun-15 12:01pm

Reply to the comment so that the user is notified.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 10286520 · Accepted Answer · 2015-06-11T22:38:00

SQL

<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