SQL: Getting the period start and end datetimes from data like this? Tricky little puzzle I'm struggling with

Question

I have a table which lists every date within the year. Each row also contains a period number from 1 to 13 which loop around each year. It looks something like this:

|        Date         | Period |
| 2012-12-27 00:00:00 |   12   |
| 2012-12-28 00:00:00 |   12   |
| 2012-12-29 00:00:00 |   13   |
| 2012-12-30 00:00:00 |   13   |
| 2012-12-31 00:00:00 |   13   |
| 2013-01-01 00:00:00 |   13   |
| 2013-01-02 00:00:00 |   13   |
|        . . .        |   13   | 
| 2013-02-10 00:00:00 |   01   |
|        . . .        |   01   | 
| 2013-03-14 00:00:00 |   02   |
|        . . .        |   02   | 
| 2013-05-05 00:00:00 |   03   |

As you can see above, period 13 starts on the 29th of dec 2012 and finishes on the 09th of Febuary 2013. I work this out by getting the first and last dates for period 13.

I need to write a query that will get the period start and end dates for the current period. But I'm having problems because the periods sometimes occur twice a year and sometimes overlap between the years like this example does.

A few more examples:

If getDate() = '2013-02-25 13:45:00' then the period should be 01 and the start and end dates would be '2013-02-10 00:00:00' and '2013-03-14 00:00:00'

If getDate() = '2013-03-15 00:00:00' then the period should be 02 and the start and end dates would be '2013-03-14 00:00:00' and '2013-05-05 00:00:00'

I really hope this makes sense. Let me know if I need to clarify it! Thanks for your help :)

Answer 1

If you were using SQL Server 2012, you would use lag or lead for this. Instead, I'm going to use a correlated subquery:

select min(period) as period, MIN(date), MAX(date)
from (select t.*,
             (select min(date) from t t2 where t2.period <> t.period and t2.date > t.date) as nextp
      from t
     ) t
group by nextp

The inner subquery gets the date of the next period. This is the same for consecutive records with the same period record. I can then use this for grouping.

Answer 2

Two recursive cte's that finds the start date and end date in a period.

with CStart as
(
  select Date,
         Period
  from DateTable
  where Date = cast(getdate() as date)
  union all
  select D.Date,
         D.Period
  from DateTable as D
    inner join CStart
      on dateadd(day, -1, CStart.Date) = D.Date and
         CStart.Period = D.Period
),
CEnd as
(
  select Date,
         Period
  from DateTable
  where Date = cast(getdate() as date)
  union all
  select D.Date,
         D.Period
  from DateTable as D
    inner join CEnd
      on dateadd(day, 1, CEnd.Date) = D.Date and
         CEnd.Period = D.Period
),
CPeriod as
(
  select Period
  from DateTable
  where Date = cast(getdate() as date)
)
select Period,
       (select min(Date) from CStart) as StartDate,
       (select max(Date) from CEnd) as EndDate 
from CPeriod

Test on SE-Data

Answer 3

Give this code a try

DECLARE @TODAYSPERIOD AS VARCHAR(2)

SET @TODAYSPERIOD = (SELECT PERIOD FROM MYTABLE
                    WHERE DATE = (DATEADD(D, DATEDIFF(D, 0, GETDATE()), 0)))

SELECT MAX(DATE), MIN(DATE), @TODAYSPERIOD FROM MYTABLE
WHERE PERIOD = @TODAYSPERIOD AND DATE BETWEEN ((DATEADD(D, DATEDIFF(D, 0, GETDATE()), 0)) - 45) AND ((DATEADD(D, DATEDIFF(D, 0, GETDATE()), 0)) + 45)

Essentially, we're using your table (in the above code, it is referenced to as 'MYTABLE') to find the period and then taking only dates within a 45 day radius (both before and after the current date) that is listed as the same period as today.

Hope this helps!