CREATE TABLE #Employees (
empid  int ,
salary money , 
start_datetime  datetime ,
end_datetime datetime
);

INSERT #Employees VALUES ( 
6969 , 35000.00 , '01/06/1999 11:11:20', '01/06/2000 13:30:00'
);
INSERT #Employees VALUES ( 
6969 , 40000.00 , '01/06/2000 13:30:24', '03/30/2000 18:02:38'
);
INSERT #Employees VALUES (
6969 , 45000.00 , '03/30/2000 18:02:55', NULL
);
INSERT #Employees VALUES ( 
7124 , 55000.00 , '02/06/2000 13:30:24', NULL
);
SELECT * FROM #Employees;
/*
empid       salary      start_datetime            end_datetime                                           
----------- ----------- ------------------------- ------------------------
       6969 35000.0000  1999-01-06 11:11:20.000   2000-01-06 13:30:00.000
       6969 40000.0000  2000-01-06 13:30:24.000   2000-03-30 18:02:38.000
       6969 45000.0000  2000-03-30 18:02:55.000   NULL
       7124 55000.0000  2000-02-06 13:30:24.000   NULL
*/
-- Problem: To calculate the old / previous salary & current salary for each employee
-- The query should select only all employees whose salaries have changed.
DECLARE @SearchDate datetime;
SELECT @SearchDate = '02/05/00';
SELECT e3.empid , e3.Old_Salary , e3.Current_Salary
FROM (
SELECT e2.empid ,
       MAX( CASE e2.IsCurrent WHEN 0 THEN e2.Salary ELSE 0 END ) AS Old_Salary ,
       MAX( CASE e2.IsCurrent WHEN 1 THEN e2.Salary ELSE 0 END ) AS Current_Salary
FROM ( 
SELECT e1.empid , e1.Salary ,
       -- Based on today's date , determine the current salary row for each employee
       ( CASE WHEN CURRENT_TIMESTAMP BETWEEN e1.Start_Datetime AND
                                     COALESCE( e1.End_Datetime , CURRENT_TIMESTAMP )
                 THEN 1
                 ELSE 0
       END ) AS IsCurrent
FROM #Employees AS e1
WHERE @SearchDate BETWEEN e1.Start_Datetime AND e1.End_Datetime /* Old Salary check */
      Or
      e1.Start_Datetime > @SearchDate                           /* Current Salary check */
) AS e2
GROUP BY e2.empid
) AS e3
/*
        Show only employees whose current salary is different from
        their old salary. The NULLIF function is one way to do the <> check.
*/
WHERE NULLIF( e3.Old_Salary  , e3.Current_Salary ) IS NOT NULL
ORDER BY e3.empid;
/*
empid       Old_Salary            Current_Salary        
----------- --------------------- --------------------- 
       6969            40000.0000            45000.0000 
       7124 NULL                             55000.0000 
*/
GO
/*
        More sample data to test against. In this case , the employee's
        salary doesn't change.

INSERT INTO #Employees VALUES ( 6969, 35000.00 , '06/06/1999 13:30:24' , '01/06/2000 13:30:24' )
INSERT INTO #Employees VALUES ( 6969, 45000.00 , '01/06/2000 13:30:24' , '03/30/2000 18:02:38' )
INSERT INTO #Employees VALUES ( 6969, 45000.00 , '03/30/2000 18:02:55' , NULL)
*/
GO        
DROP TABLE #Employees;

This page was last updated on May 01, 2006 04:28 PM.