Thanks Chris for your solution, today I learned what DENSE_RANK() is! Quite a nifty function. 🙂

Glad to have been of help. I'm still not sure how quick the code will run on a large table, but if you run into issues with that we can...

Ok, I've found a solution that works for 3 consecutive months:

WITH cte_Payments AS (

SELECT ClaCaseID

, NameID

, IncidentDate

, YearID

, MonthID

, PaymentStatus

, ROW_NUMBER()OVER(PARTITION BY ClaCaseID, NameID, IncidentDate, PaymentStatus ORDER BY YearID, MonthID)...

Oh dear, I've just realised my solution doesn't cater for 3 consecutive months 🙁

I'll relook at it!

Ok, here's my solution, hope it helps (and that it produces the correct results 😉 )

WITH cte_Payments AS (

SELECT ClaCaseID

, NameID

, IncidentDate

, PaymentStatus = CASE WHEN PaymentStatus = 4 THEN...

I used the query below to view the data. You can tell based on the joins I've done if i've understood the rules correctly ( OR NOT! :-D)

SELECT ClaCaseID

, NameID

,...

Hi mic.con87

If i understand the rules you require then the result set you posted is incorrect for ClaCaseID 28584 and NameID 580627. If you consider the last DueDate for June...

Hi,

Can you give us more data to work with? I'm trying to build the query but I don't understand the full set of rules you require, it seems like you...

I think the correct term you're looking for is partitioning. Search for "sql server 2008 database partitioning" on Google and you will find the articles you're looking for. BTW, I've...

This is a great question, mostly because it got me searching and learning on the net... 🙂

This article is quite helpful: http://technet.microsoft.com/en-us/library/bb964719.aspx

I guess that if you have a SQL instance...

Hi Everyone,

Sorry for this, we found the problem, it is a ddl trigger.

Regards,

William :w00t: