Untitled

USE [Parikshak8]
GO

/****** Object:  StoredProcedure [schema_HRM3686ER].[SP_prob4_Q9]    Script Date: 19-03-2024 16:57:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [schema_HRM3686ER].[SP_prob4_Q9]
 AS
 
 /*. Refer the tables Employee and Dept_loc. 
 
 Display a column Inc_Sal that shows the new revised salary of people based on the following criteria:
 People belonging to Chicago will have their salary increased by 5%, Boston by 3% , Dallas/NewYork by 10% and others will have no increment.
 
 On the basis of hiredate, display the salary of previously hired employee. Check if the employee has greater or less salary than the previously hired employee. Display a column to show the message as follows:
 if employee has salary more than previously hired employee,display the message- Salary is greater than previously hired employee
 if lesser, display - Salary is less than previously hired employee 
 if equal , display - Salary is same as previously hired employee
 others- no previous employee
 
 Also, Display a column to show the full name with the middle name removed from it.
 
 Note that the NULL values in deptname and loc columns should be replaced by NA.
 
 Create a view named VW_EMP_DEPT to display all above details of employees.
 Refer the output sample below for the column names.
 
 Sample Output :- 
 (Please keep same column names as below):
 
 Empid	Full_name		Salary	Inc_Sal	Previous_emp_sal	Hiredate	deptname			loc				message
 1012	Raji Nair		35500	39050	NULL				09-04-2018	HR					Dallas		No previous employee
 1009	Jeena			12000	12600	35500				17-11-2018	Sales				Chicago		Salary is less than previously hired employee
 1005	Nidhi Shetty	10000	10500	12000				16-01-2019	Customer service	Chicago		Salary is less than previously hired employee
 1011	Jiya Shetty		45500	47775	10000				27-03-2019	Sales				Chicago		Salary is greater than previously hired employee
 
 
 Please keep object name as follows:
 	View Name --> VW_EMP_DEPT
 
 (Tag: Views, String functions, Date functions, Union, Joins)
 
 */
GO
CREATE OR ALTER VIEW VW_EMP_DEPT AS
--logic
WITH cte AS (
    SELECT
        EmpID,
        CASE
            WHEN CHARINDEX(' ', name) > 0 THEN
                CASE
                    WHEN LEN(name) - LEN(REPLACE(name, ' ', '')) = 1 THEN
                        name
                    ELSE
                        SUBSTRING(name, 1, CHARINDEX(' ', name) - 1) + ' ' +
                        SUBSTRING(name, CHARINDEX(' ', name, CHARINDEX(' ', name) + 1) + 1, LEN(name))
                END
            ELSE
                name
        END AS Full_name,
        salary,
        CASE
            WHEN loc = 'Chicago' THEN salary * 1.05
            WHEN loc = 'Dallas' THEN salary * 1.1
            WHEN loc = 'New york' THEN salary * 1.1
            WHEN loc = 'Boston' THEN salary * 1.03
            ELSE
                salary
        END AS Inc_Sal,
        LAG(salary, 1, NULL) OVER(ORDER BY Hiredate) AS Previous_emp_sal,
        FORMAT(hiredate, 'dd-MM-yyyy') AS Hiredate,
        ISNULL(deptname, 'NA') AS deptname,
        ISNULL(loc, 'NA') AS loc
    FROM 
        dept_loc dl
    RIGHT JOIN 
        employee emp ON dl.deptid = emp.deptid
)

SELECT 
    *,
    CASE
        WHEN Previous_emp_sal > salary THEN 'Salary is less than previously hired employee'
        WHEN Previous_emp_sal < salary THEN 'Salary is greater than previously hired employee'
        ELSE 'No previous employee'
    END AS message
FROM 
    cte;