need to retrive data between period of data for The given date is dynamic based on belwo scenarios

Database

need to retrive data between period of data for The given date is dynamic based on belwo scenarios

Im going to run a report For ratings prior to assessment year 2014, review date would be used to identify the “Performance Year Rating”. For any specific year, review date will be between 1st November of that year and 31st May of the next year. Ex: If the review date is between 1st November, 2013 to 31st May, 2014 “Performance Year Rating” would be 2013.

If I am running for the date which prior to   year 2014

Senario1:  for a year 2014 im running now I need to pick the review date  records between  is between 1st November, 2013 to 31st May, 2014 “

Scenario 2: if im running for the year 2013 I need to pick the review date  records between  is between 1st November, 2013 to 31st May, 2014 “ for each employees to check the performance history of them .

The given date is dynamic I need to retrieve the records based on above scenarios

Employee id     Perfrmance rating    Effective date
00000472922 A1 16-Oct-01
00000472922 A++ 1-Jul-13
102242 OUTSTanding 1-May-13
102242 Not Rated 1-Oct-13
102242 Not completed 1-Aug-07
102242 PRO 1-Jan-08
102242 DEM 1-Dec-08
102242 aaa 1-Mar-02
102242 PRO 1-Apr-02
102242 OUTSTanding 1-Aug-02
102242 Not Rated 1-Sep-02
102242 Not completed 1-Jan-03
13245 bbb 1-Feb-03
13245 achiver 1-Mar-03
13245 OUTSTanding 1-May-03
13245 Not Rated 1-Jul-03
13245 Not completed 1-Oct-03
13245 PRO 16-Jan-04
13245 DEM 1-Feb-04
13245 aaa 1-Mar-14
13245 PRO 1-Jul-14
13245 OUTSTanding 1-Aug-14
13245 Not Rated 1-Jan-14
13245 Not completed 1-Mar-13
13245 bbb 1-Apr-13
13245 achiver 1-May-13
CV27WH24 OUTSTanding 1-Apr-13
CV27WH24 Not Rated 23-May-12
CV27WH24 Not completed 4-Aug-12
CV27WH24 PRO 1-Oct-12
CV27WH24 DEM 1-Apr-13
CV27WH24 aaa 29-Sep-13
CV27WH24 A-- 1-Oct-13
Tags (1)