SQL Server - Querying for Closest Date Range

Question

If I have a table structure like this:

ProductCode  Date
Foo          4/1/2012
Foo          4/2/2012
Foo          4/3/2012
Foo          4/6/2012
Foo          4/7/2012
Foo          4/8/2012
Foo          4/9/2012
Foo          4/10/2012
Foo          4/15/2012
Foo          4/16/2012
Foo          4/17/2012

Is there a way to query for the date range for a given ProductCode and Date (assuming that ranges MUST be sequential)? In other words, for this table, Foo exists on 3 date ranges: 4/1-4/3; 4/6-4/10; and 4/15-4/17 and I'm looking for the date range given a date.

Please note that Foo doesn't have date's 4/4, 4/5, 4/11, 4/12, 4/13 and 4/14.

Examples:
ProductCode=Foo, Date=4/2 would return 4/1-4/3 because the entries are sequential.
ProductCode=Foo, Date=4/4 would return nothing
ProductCode=Foo, Date=4/7 would return 4/6-4/10 because the entries are sequential.
ProductCode=Foo, Date=4/12 would return nothing
etc.

Answer 1

A new range starts when there is no row for the previous day. If you are running SQL Server 2012, you can use the lag window function to check if a row introduces a new range. Once you know which rows introduce a new range, you can count the number of head rows to assign a unique number to each range.

Having a range number allows you to find the start and end date with min and max. After that, it's just a question of selecting the row:

; with  IsHead as
        (
        select  ProductCode
        ,       Date
        ,       case when lag(Date) over (partition by ProductCode 
                  order by Date) = dateadd(day, -1, Date) then 0 
                  else 1 end as IsHead
        from  YourTable
        )
,       RangeNumber as
        (
        select  ProductCode
        ,       Date
        ,       sum(IsHead) over (partition by ProductCode order by Date) 
                  as RangeNr
        from    IsHead
        )
,       Ranges as
        (
        select  *
        ,       min(Date) over (partition by RangeNr) as RangeStart
        ,       max(Date) over (partition by RangeNr) as RangeEnd
        from    RangeNumber
        )
select  *
from    Ranges
where   ProductCode = 'Bar'
        and Date = '4/2/2012'

Example at SQL Fiddle.