Thanks for you support.

it is mandatory in my query ti use(NOLOCK or WITH(NOLOCK), so i can prefer to use WITH (NOLOCK).

it is your conclusion.

Hi ALl,

Thank you very much this query is giving good performance.

Thanks

Bhanu

Thank you very much boss

it is working fine

I am very very thankful to your support.

Thanks

Bhanu

Hi All,

Sorry for the inconvenience in providing the inputs correctly.

DECLARE @COMP_RATINGS TABLE

(

EID INT,

RATING VARCHAR(20),

AbsenceStartDate DATETIME,

AbsenceEndDate DATETIME

)

INSERT INTO @COMP_RATINGS VALUES

(769,'bb','2011-06-30','2011-09-30'),

(769,'bb','2011-12-31 ','2012-03-31 '),

(769,'bb','2012-03-31 ','2012-06-30 '),

(769,'ccc','2009-12-31','2010-03-31'),

(769,'ccc','2010-03-31','2010-06-30 '),

(769,'ccc','2010-06-30','2010-09-30'),

(769,'ccc','2010-09-30','2010-12-31'),

(769,'ccc','2010-12-31','2011-03-31'),

(769,'ccc','2011-03-31','2011-06-30'),

(769,'ccc','2012-06-30','2012-12-31'),

(769,'ccc','2012-12-31','2013-03-31')

select * from @COMP_RATINGS

The original...

hi,

in the original data

b+ has only one records it should display as it is.

bb has 3 records it should display as 2 records based on the date sequence.

bb+ has...

Hi All,

I set up the new sample data with insert statement.

DECLARE @COMP_RATINGS TABLE

(

EID INT,

RATING VARCHAR(20),

AbsenceStartDate DATETIME,

AbsenceEndDate DATETIME

)

INSERT INTO @COMP_RATINGS VALUES

(769,'b+','2011-06-30','2011-09-30'),

(769,'bb','2011-06-30','2011-09-30'),

(769,'bb','2011-12-31 ','2012-03-31 '),

(769,'bb','2012-03-31 ','2012-06-30 '),

(769,'bb+','2009-12-31','2010-03-31'),

(769,'bb+','2010-03-31','2010-06-30 '),

(769,'bb+','2010-06-30','2010-09-30'),

(769,'bb+','2010-09-30','2010-12-31'),

(769,'bb+','2010-12-31','2011-03-31'),

(769,'bb+','2011-03-31','2011-06-30'),

(769,'bb+','2012-06-30','2012-12-31'),

(769,'bb+','2012-12-31','2013-03-31')

select * from...

HI MITESH,

THANK YOU FOR YOUR HELP.

THE QUERY IS SATISFYING THE SAMPLE GIVEN DATA.

BUT IT IS NOT SOLVING MY ORIGINAL PROBLEM.

THE ORIGINAL DATA IN THE TABLE:

5364914b+2011-09-30 00:00:00.00000002011-12-31 00:00:00.0000000

5364914bb2011-06-30 00:00:00.00000002011-09-30 00:00:00.0000000

5364914bb2011-12-31 00:00:00.00000002012-03-31...

IF YOU NEED ANY MORE DETAILS OR CLARIFICATION ON THE REQUIREMENT

PLEASE ASK I WILL PROVIDE CLARIFICATION.

THANKS

BHANU

WE CAN DO THIS PLEASE TRY MORE TIME.