@ChrisM@Home Thanks for the explanation and for your query, it really was enlightening:-D

@VIG Better late than never indeed! I'm busy testing ChrisM@Home's code so I can...

Thanks a million!!! I'm busy testing your code and it seems to return the exact result I am seeking and is very efficient!

Hope this wont be a hassle but is...

Thanks everyone for the help thus far.

I have tested your queries and have picked up a few differences in your result sets.

@ChrisM@home: Your Query is very close to...

@WillBen

I've just tested your latest code and it appears to work perfectly!!!!:-D:-D:-D I've tested about 15 cases of 'Y' but I will do more. My office is closing...

@WillBen

As for your earlier query:

Here is the result I received:

CREATE TABLE #Result (ClaCaseID INT,ReceiverID int,IncidentDate Date, DueDate Date, PaymentStatus int,YearID int, MonthID int, ROWID int )

INSERT INTO #Result...

@ChrisM@home

Your solution is almost correct however these conditions are not met:

2) After Isolating the relevant DueDate Records Find the MAX DueDate for each month. There may be multiple...

So sorry 2009-06-27 should have a 9 not a 4.

Here are the rules, hope this helps and thanks for help, I really appreciate it!

Rules:

1) Compare IncidentDate and DueDate for each...

You can use ANY method to solve the issue at hand and doesn't need to be a loop based method

Hi, sorry for the lack of understanding of the data. It's never been analyzed before so I'm pioneering it:-P

Here is some proper data:

--DROP TABLE #ClaCases

DROP TABLE #ClaCases

CREATE TABLE #ClaCases (ClaCaseID...

Sorry another thing is that ClaCaseID is unique but a NameID can have multiple ClaCaseID's:w00t:

Sorry I just picked up something else in the data. There may be several transactions per month.

CREATE TABLE #AccPayments (PaymentStatus INT,ReceiverID int, DueDate Date )

INSERT INTO #AccPayments VALUES (9,314129,'2009-05-20')

INSERT...

@ChrisM@home

Your code runs however the results are incorrect. I'll post some sample data but from what I see the code is returning 'Y' when there are no records...

Hi:

Your code works in some instances but there are examples where it doesn't. Condition was that the needs to be 3 consecutive PaymentStatus=9 and 6 months PRIOR to IncidentDate (So...

Hi:

Your code works in some instances but there are examples where it doesn't. Condition was that the needs to be 3 consecutive PaymentStatus=9 and 6 months PRIOR to IncidentDate (So...

I'm busy testing your code now. Thanks so much for the help. I'll provide you with feedback tomorrow;-)