SQL Querying for two Consecutive Rows

Question

I have the following query:

SELECT 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

Anyone know how to do this? Thanks

---

Got the answer with a little help from here. Thanks

SELECT year_week FROM web_details w 
WHERE location = ''JF'' AND property_id = ''FARM'' AND EXISTS (
SELECT * FROM web_details 
WHERE location = ''JF'' AND property_id = ''FARM'' 
AND cast(year_week as numeric) = cast(w.year_week as numeric) + 1
)

Answer 1

SELECT year_week
FROM web_details
where location = ''JF''
  AND property_id = ''FARM''
  AND year_week + 1 IN ( SELECT year_week
                         FROM web_details
                         where location = ''JF''
                           AND property_id = ''FARM''
                       )

Answer 2

SELECT year_week
  FROM web_details w
 WHERE location = ''JF''
   AND property_id = ''FARM''
   AND EXISTS (
        SELECT * FROM web_details
         WHERE location = ''JF''
           AND property_id = ''FARM''
           AND year_week = w.year_week + 1
       )

Answer 3

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
    )

Answer 4

A first draft, some adjustments may be needed depending on how dates are treated (when does the week start, which is the first week in a year, etc). The end of year verification is also present in the last used rows.

DECLARE @web_details TABLE (year_week VARCHAR(8))

INSERT INTO @web_details (year_week) VALUES ('201035')
INSERT INTO @web_details (year_week) VALUES ('201036')
INSERT INTO @web_details (year_week) VALUES ('201037')
INSERT INTO @web_details (year_week) VALUES ('201039')
INSERT INTO @web_details (year_week) VALUES ('201041')
INSERT INTO @web_details (year_week) VALUES ('201044')
INSERT INTO @web_details (year_week) VALUES ('201045')
INSERT INTO @web_details (year_week) VALUES ('201048')
INSERT INTO @web_details (year_week) VALUES ('201052')
INSERT INTO @web_details (year_week) VALUES ('201101')

DECLARE @results TABLE (year_week VARCHAR(8), year_week_datetime DATETIME, n INT)

INSERT INTO @results (year_week, year_week_datetime, n)
SELECT
    year_week,
    DATEADD(week,
            CAST(SUBSTRING(year_week, 5, 2) AS INT) - 1,
            CAST(SUBSTRING(year_week, 1, 4) + '/1/1' AS DATETIME)
            ),
    ROW_NUMBER() OVER(ORDER BY year_week) AS n FROM @web_details

SELECT
    t1.year_week
FROM
    @results AS t1 JOIN @results AS t2 ON t1.n + 1 = t2.n
WHERE
    DATEDIFF(week, t1.year_week_datetime, t2.year_week_datetime) = 1