SQL SERVER 2008 Max Constant Value

Question

Hi all i need help Again :D

it goes like this i need to find max constant value or max counted value for example i have

ClientID   YEAR      МОNTH
1          2013        1    
1          2013        2  
1          2013        3 
1          2013        4  
1          2013        5  
1          2013        6  
1          2013        7  
1          2013        8  
1          2013        9  
1          2013       10  
1          2013       11  
1          2013       12  
2          2013        1    
2          2013        2  
2          2013        3 
2          2013        4    
2          2013        6  
2          2013        7  
2          2013        8   
2          2013       10  
2          2013       11  
2          2013       12  

if i do

select clientID,year, max(month) from table where client id =1

select clientID,year, max(month) from table where client id =2

then i get

ClientID   YEAR      МОNTH
    1      2013        12
    2      2013        12

is there i way to select until the first missing month? Like

ClientID   YEAR      МОNTH
    1      2013        12
    2      2013        4

TNX in Advance

Answer 1

This should do it:

SELECT ClientID, YEAR, MIN(MONTH)
FROM table
WHERE NOT EXISTS (SELECT * 
    FROM table c 
    WHERE c.MONTH = table.MONTH + 1 
        AND c.ClientID = table.ClientID
        AND c.YEAR = table.YEAR)
GROUP BY ClientID, YEAR

That looks for all entries that are just before a gap, then takes the minimum one for any combination of ClientID and YEAR that has more than one result.

SQLFiddle

Answer 2

select clientid,year,max(month) from [dbo].[24] where id <(


SELECT TOP 1 t1.month+1 
FROM [dbo].[24] t1
WHERE NOT EXISTS(SELECT * FROM [dbo].[24] t2 WHERE t2.month = t1.month + 1)
ORDER BY t1.month
)

group by clientid,year