CODEWITHSUNDEEP
sqlviewboolean-logic
Guhan Murugesan
Guhan Murugesan

Reputation: 89

SQL View with Boolean value field

I have a task ahead of me that really boggles me.

I have a table Employee in the below format. The employee_id is the auto-incremented field that acts as the primary key. The employee_number is the ID given to the employee by the company and is unique across the table.

enter image description here

There is another table called Employee_Assignment that contains the assignment information of employees to projects. This table will have information of only those employees who have assignments. One employee can have one or more assignments. This table uses the employee_id field from Employee table as the foreign key. Also, the employee's status in each project assignment is determined by the assignment_status_id field.

enter image description here

The Assignment_Status table is as follows. There are 2 types of 'Active' statuses - Active1 and Active2. Similarly, there are 2 types of 'Inactive' statuses - Inactive1 and Inactive2: enter image description here

I want to create a view Assignment_Status_View that is in the following format:

enter image description here

The view should list all employees from Employee table and a boolean value that indicates if they are active in any project or not. As new employee come in, new records are added to the Employee table. Also, for each new project assignment, a new record will be added to Employee_Assignment table. The view should be able to capture both of these.

The overall_status field should show the employees' status across all the project assignments, ie., if the employee has 10 assignments and if he/she is in any of the 2 inactive statuses in all the assignments, then overall_status = 0. Similarly, if the employee is in any of the 2 active statuses in at least one project assignment, then overall_status = 1.

Any help you could provide me will be much appreciated.

Upvotes: 0

Views: 485

Answers (1)

radar
radar

Reputation: 13425

CASE based aggregation can be used to see if there exists at least one active status for employee.

Using left join to make sure employees with no record in assignment status also counted.

        SELECT E.employee_Number, E.name, T.OverAllStatus  FROM 
        employee E
        JOIN (
            SELECT E.employee_Number, 
                   case when COALESCE(SUM( case when EA.assignment_status_id in ( 1,3)  
                                       then 1 
                                       else 0 
                                       end ),0) >0 
                   then 1 else 0 end as OverAllStatus
            FROM Employee E
            LEFT JOIN Employee_Assignment EA
            ON E.employee_id = EA.employee_id
            GROUP BY E.employee_Number
        ) T
        ON E.employee_Number = T.employee_Number

Upvotes: 2

Related Questions