sql server - help in query

Question

I have a table called employee_salary_master in which we keep salary effective dates and entry dates of each employee in the company. For processing salary of an employee for a month, we need to fetch most recently entered record. If the effective date is less than that month then we pick the most recent entry. but if effective date is greater than the first date of the month, then there will be more than one effective dates for that month

example: the data for an employee is as below.

SAL_MATSER_ID EMPLOYEE_ID EFFECTIVE_DATE ENTRY_DATE
------------- ----------- -------------- ------------
1             5814        Jan  6 2006    Jan 12 2006 
2             5814        Jan 10 2006    Jul 17 2006 
3             5814        Jan 20 2006    Dec 22 2006 
4             5814        May 10 2007    Jul 18 2007 
5             5814        Nov  1 2007    Dec 18 2007 
6             5814        Aug  1 2008    Aug 20 2008 
7             5814        May  1 2008    Sep  2 2008 
8             5814        Sep  1 2009    Sep 18 2008 
9             5814        Nov  1 2008    Apr 20 2009 
10            5814        Nov 10 2009    Nov 25 2009 
11            5814        Nov  5 2009    Nov 26 2009 

If i need to get the record for Nov 2009, I write the query below

select EMPLOYEE_SALARY_MASTER_ID, EMPLOYEE_ID, EFFECTIVE_DATE, ENTRY_DATE, ARREAR_PROCESS_FLAG from employee_salary_master esm where employee_id = 5814 
and (esm.entry_date = (select max(entry_date) from employee_salary_master where employee_id = 5814 and effective_date <= @monthfirstdate)
        or (esm.effective_date between @monthfirstdate and @monthlastdate))

which gives the result below..

SAL_MATSER_ID EMPLOYEE_ID EFFECTIVE_DATE ENTRY_DATE
------------- ----------- -------------- ------------
9             5814        Nov  1 2008    Apr 20 2009 
10            5814        Nov 10 2009    Nov 25 2009 
11            5814        Nov  5 2009    Nov 26 2009 

What I need is as follows...

For Nov 1 - Nov 4, salary should be processed as per employee_salary_masterId - 9 and Nov 5 - Nov 30, salary should be processed as per employee_salary_masterId - 11.

SAL_MATSER_ID EMPLOYEE_ID EFFECTIVE_DATE ENTRY_DATE
------------- ----------- -------------- ------------
9             5814        Nov  1 2008    Apr 20 2009 
11            5814        Nov  5 2009    Nov 26 2009 

Please help me build this query.

Answer 1

The rule for excluding nov 10 record is that we need to filter out those records for the month of November in which entry_date is greater but effective_date is smaller.

Let me explain you wrt data provided: As a rule, the latest/last entry will take precedence over the previous entries in a particular month which implies that the entrty date of Nov 26 2009 would take precedence over the entry date of Nov 25 2009. Now since effective date of SAL_MATSER_ID - 10 is greater than that of SAL_MATSER_ID - 11, hence Nov 10 record would be nullified.

Had the data been like the below, all the 3 records would have been used for salary processing.

SAL_MATSER_ID - 9 for salary of Nov 1 - Nov 9 SAL_MATSER_ID - 10 for salary of Nov 10 - Nov 14 SAL_MATSER_ID - 11 for salary of Nov 15 - Nov 30

SAL_MATSER_ID EMPLOYEE_ID EFFECTIVE_DATE ENTRY_DATE
------------- ----------- -------------- ------------
9             5814        Nov  1 2008    Apr 20 2009 
10            5814        Nov 10 2009    Nov 25 2009 
11            5814        Nov 15 2009    Nov 26 2009 

But since SAL_MATSER_ID - 11 is applicable from 5th Nov. onwards, the previous record is nullified. I hope this explains the situation.

Thanks for your support, Sweta

Answer 2

Not quite sure if I understand you completely correct, but have a look at this example

DECLARE @employee_salary_master TABLE(
        EMPLOYEE_SALARY_MASTER_ID INT,
        EMPLOYEE_ID INT,
        EFFECTIVE_DATE DATETIME,
        ENTRY_DATE DATETIME
)

INSERT INTO @employee_salary_master SELECT  1,5814,'Jan  6 2006','Jan 12 2006'
INSERT INTO @employee_salary_master SELECT  2,5814,'Jan 10 2006','Jul 17 2006' 
INSERT INTO @employee_salary_master SELECT  3,5814,'Jan 20 2006','Dec 22 2006' 
INSERT INTO @employee_salary_master SELECT  4,5814,'May 10 2007','Jul 18 2007' 
INSERT INTO @employee_salary_master SELECT  5,5814,'Nov  1 2007','Dec 18 2007' 
INSERT INTO @employee_salary_master SELECT  6,5814,'Aug  1 2008','Aug 20 2008' 
INSERT INTO @employee_salary_master SELECT  7,5814,'May  1 2008','Sep  2 2008' 
INSERT INTO @employee_salary_master SELECT  8,5814,'Sep  1 2009','Sep 18 2008' 
INSERT INTO @employee_salary_master SELECT  9,5814,'Nov  1 2008','Apr 20 2009' 
INSERT INTO @employee_salary_master SELECT  10,5814,'Nov 10 2009','Nov 25 2009' 
INSERT INTO @employee_salary_master SELECT  11,5814,'Nov  5 2009','Nov 26 2009' 


DECLARE @monthfirstdate DATETIME,
        @monthlastdate DATETIME

SELECT @monthfirstdate = '01 Nov 2009',
        @monthlastdate = '30 Nov 2009'

SELECt  *
FROM    (
            SELECT  TOP 1
                    *
            FROM    @employee_salary_master esm
            WHERE   esm.EFFECTIVE_DATE BETWEEN @monthfirstdate and @monthlastdate
            AND     esm.EFFECTIVE_DATE < esm.ENTRY_DATE
            ORDER BY esm.ENTRY_DATE DESC
        ) sub
UNION ALL
SELECT *
FROM    (
            SELECT  TOP 1
                    *
            FROM    @employee_salary_master esm
            WHERE   esm.EFFECTIVE_DATE <= @monthfirstdate
            AND     esm.EFFECTIVE_DATE < esm.ENTRY_DATE
            ORDER BY esm.EFFECTIVE_DATE DESC
        ) sub
ORDER BY EFFECTIVE_DATE