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 :)

Do all consecutive dates exist in the table (no gaps) and one row per date? — ypercubeᵀᴹ, Jan 04 '13 at 14:55

Gordon Linoff · Accepted Answer · 2013-01-04T14:48:15.257

4

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.

edited Jan 04 '13 at 14:48

answered Jan 04 '13 at 14:29

Gordon Linoff

1,242,037
58
646
786

Thankyou for your quick reply Gordon. This query is close to what I want but its unfortunately returning the wrong dates. Each year there is 13 periods and in my table I have records from 2005 onwards. Because its getting the min and max(date) it is return records from way back then rather than for the current period. Does that make sense? – user1636130 Jan 04 '13 at 14:41
@user1636130 . . . Stupid me. The defined `nextp` and then grouped by `period`. I fixed the problem. It should now work as I described it. – Gordon Linoff Jan 04 '13 at 14:48
Many thanks for your help! I got this to work in the end. I am very pleased with it – user1636130 Jan 06 '13 at 20:00

score 3 · Answer 2 · edited Jan 18 '21 at 12:30

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

Lloyd Banks · Answer 3 · 2013-01-04T14:50:18.373

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!

score 0 · Answer 4 · answered Jan 04 '13 at 14:50

You can try (in my test data, 56 was the largest number of days in a given period)

SELECT A.period
     , MIN( B.date )
     , MAX( B.date )
  FROM dates A
 INNER JOIN dates B
    ON A.date >= DATEADD( dd, -56, B.date )
   AND A.date <= DATEADD( dd, 56, B.date )
   AND A.period = B.period 
 GROUP BY A.period

score 0 · Answer 5 · answered Jan 04 '13 at 15:37

If - and only if - no date gaps exist in the table and there is exactly one row per date, you can use this:

; WITH cte AS
  ( SELECT
        a.Date AS StartDate
      , b.Date AS EndDate
      , a.Period
      , ROW_NUMBER() OVER (ORDER BY a.Date) AS rn
    FROM tableX AS a
      LEFT JOIN tableX AS b
        ON  DATEADD(day, -1, a.Date) = b.Date
    WHERE a.Period <> b.Period 
       OR b.Date IS NULL 
  )
SELECT
    a.StartDate
  , COALESCE(b.EndDate, (SELECT MAX(Date) FROM tableX)) AS EndDate
  , a.Period
FROM
    cte AS a
  LEFT JOIN 
    cte AS b
      ON a.rn + 1 = b.rn ;

Tested in SQL-Fiddle

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

5 Answers5