SQL - displaying row where the next numeric value is also available

Question

I have the following query:

SELECT CAST(year_week AS NUMERIC) as year_week FROM web_details  where location = ''JF'' AND property_id = ''FARM''

which produces the following results.

YEAR_WEEK
201035
201036
201037
201039
201041
201044
201045
201048

What I actually want is to produce a set of results which only displays values if the consecutive value is available - so producing the following results...

YEAR_WEEK
201035
201036
201044

To add another spanner into the works, the column year_week is not a numeric value so has needed to be converted.

Thanks

Answer 1

SELECT 
    CAST(year_week AS NUMERIC) as year_week 
FROM 
    web_details wd 
WHERE 
    EXISTS(
        SELECT 
            year_week
        FROM 
            web_details wd2 
        WHERE 
            wd2.year_week = CASE(RIGHT(wd.year_week, 2))
                        WHEN '48' THEN CAST((CAST(LEFT(wd.year_week,4) AS INT) + 1) AS VARCHAR(4)) + '01'
                        ELSE LEFT(wd.year_week,4) + CAST((CAST(RIGHT(wd.year_week,2) AS INT) + 1) AS VARCHAR(2))
                     END
    )