Selecting an appropriate period or max/minimum period when outside of the period set

Question

I'm trying to see if it's possible to efficiently select a period a given date belongs to.

Let's say I have a table

id<long>|period_start<date>|period_end<date>|period_number<int>

and lets say I want for every id the period that "2013-11-20" belongs to.

i.e. naively

 select id, period_number 
 from period_table 
 where '2013-11-20' >= period_start and '2013-11-20' < period_end

However, if my date is beyond any period_end or before any period_start, it won't find this id. In those cases I want the minimum (if before the first period_start) or the maximum (if after the last period_end).

Any thoughts if this can be done efficiently? I can obviously do multiple queries (i.e. select into the table as above and then do another query to figure out the min and max periods).

So for example

+--+------------+----------+-------------+
|id|period_start|period_end|period_number|
+--+------------+----------+-------------+
|1 |2011-01-01  |2011-12-31|1            |
|1 |2012-01-01  |2012-12-31|2            |
|1 |2013-01-01  |2013-12-31|3            |
+--+------------+----------+-------------+

If I want what period 2012-05-03 belongs to, my naive sql works and returns period #2 (1|2 as the row, id, period_number). However, if I want what period 2014-01-14 (or 2010-01-14) it can't place it as it's outside the table.

Therefore since "2014-01-14" is > 2013-12-31, I want it to return the row "1|3" if I chose 2010-01-14, I'd want it to return 1|1, as 2010-01-14 < 2011-01-01.

The point of this is that we have a index table that keeps track of different types of periods and what is their relative value (think quarter, half year, years) for many different things and they all don't line up to normal years. Sometimes we want to say we want period X (some integer) relative to date Y. If we can place Y within the table and figure out Y's period_number, we can easily do the math to figure out what to add/subtract to that value. If Y is outside the bounds of the table, we define Y to be the max/min of the table respectively.

Answer 1

It seems that you want to print the first day, and last day of a given date's year.

So:

• For 2012-05-03, print out 1|2012-01-01|2012-12-31|2
• For 2014-01-14, print out 1|2014-01-01|2014-12-31|4

Solutions:

1. Create an insertion script that inserts all the rows for years up to 2100 (you can easily add more).

2. Instead of using a table, simply use Date Functions (or any other programming language).

Using SQL Date Functions

Take the date given, and print out all four needed columns using date functions.

Example (MySQL) - should work with INGRES with some modifications

Note: Should work in INGRES as I checked functions and they are the same:

• http://community.actian.com/wiki/DATE_FORMAT_scalar_function

• http://community.actian.com/wiki/Ingres_DBMS_function_list

     SELECT 1 AS id,
     CONCAT(DATE_FORMAT('2014-01-14', '%Y-'), '01-01') AS period_start,
     CONCAT(DATE_FORMAT('2014-01-14', '%Y-'), '12-31') AS period_end,
     DATE_FORMAT('2014-01-14', '%Y')-2010 AS period_number ;
  

SQLFiddle: http://sqlfiddle.com/#!2/d41d8/29204

UPDATE

A UNION should then work fine for you. Modify below as appropriate for INGRES.

SET @input =  '2015-01-14';

(SELECT id,
          period_number
   FROM period_table
   WHERE @input >= period_start
     AND @input < period_end)
UNION
  (SELECT id,
          period_number
   FROM period_table
   WHERE @input > (select MAX(period_end) FROM period_table) 
   order by period_end desc limit 1
  )
UNION
  (SELECT id,
          period_number
   FROM period_table
   WHERE @input < (select MIN(period_start) FROM period_table) 
   order by period_start asc limit 1
  )

SQLFiddle: http://sqlfiddle.com/#!2/643da/3

ALSO:

SET @input =  '2014-01-14';

SELECT id,
          period_number

   FROM period_table
   WHERE 
     (@input >= period_start
     AND @input < period_end)
     OR 
     (
       @input > (select MAX(period_end) FROM period_table) AND
       period_number= (select MAX(period_number) FROM period_table)
     )
      OR 
     (
       @input < (select MIN(period_start) FROM period_table) AND
       period_number= (select MIN(period_number) FROM period_table)
     )

Answer 2

Note: I missed the database engine you were using, so I answered from the perspective of SQL Server. However, the query is pretty simple and you should be able to adapt it to your own needs.

The best I can come up with is, if your table is clustered (or at least indexed) on FromDate, a query that works in 2 seeks:

DECLARE @SearchDate datetime = '4062-05-04';

SELECT TOP 1 *
FROM
   (
      SELECT TOP 2
         Priority = 0,
         *
      FROM dbo.Period
      WHERE @SearchDate >= FromDate
      ORDER BY FromDate DESC
      UNION ALL
      SELECT TOP 1
         2,
         *
      FROM dbo.Period
      WHERE @SearchDate < FromDate
      ORDER BY FromDate
   ) X
ORDER BY Priority, FromDate DESC
;

See a Live Demo at SQL Fiddle

If you will post more information about your table structure and indexes, it's possible I can advise you better.

I also would like to suggest that if at all possible, you stop using inclusive end dates, where your ToDate column has the last day of the period in them such as '2013-12-31', and start using exclusive end dates, where the ToDate column has the beginning of the next period. The reason for this is usually only apparent after long database experience, but imagine what would happen if you suddenly had to add periods that were shorter than 1 day (such as shifts or even hours)--everything would break! But if you had used exclusive end dates all along, everything would work as is. Also, queries that have to merge periods together become much more complicated because you are adding 1 all over the place instead of doing simple equijoins such as WHERE P1.ToDate = P2.FromDate. I promise you that you will run an enormously greater chance of regretting using inclusive end dates than you will exclusive ones.

Answer 3

I'm going to assume you're using SQL server as well for the purposes of the script below but the SQL is generic and should work with any (?) database as long as the column name delimiters are correct

declare @Period_Table table 
(
    [id] int not null,
    period_start datetime not null,
    period_end datetime not null,
    period_number int not null,
    primary key ([id], period_number)
)

insert into @Period_Table values (1, '2011-01-01', '2011-12-31', 1)
insert into @Period_Table values (1, '2012-01-01', '2012-12-31', 2)
insert into @Period_Table values (1, '2013-01-01', '2013-12-31', 3)

declare @TestDate datetime
declare @TestId int 
set @TestId = 1 -- yearly
set @TestDate = '2012-05-03'
set @TestDate = '2014-01-14'
set @TestDate = '2010-01-14'

select *
  from @Period_Table pt
  where pt.[id] = @TestId and
    (
      pt.period_start <= @TestDate or 
      @TestDate < pt.period_start and
      not exists
      (
        select 1
          from @Period_Table pt2
          where pt.[id] = pt2.[id] and pt2.period_start < pt.period_start
      )
    ) and
    (
      pt.period_end >= @TestDate or 
      @TestDate > pt.period_end and
      not exists
      (
        select 1
          from @Period_Table pt2
          where pt.[id] = pt2.[id] and pt2.period_end > pt.period_end
      )
    )

Answer 4

Why aren't you creating "boundary periods"? Choose arbitrary beginning_of_time and end_of_time dates e.g. 01/01/0001 and 31/12/9999 and insert a fake period. Your example period_table will become:

+--+------------+----------+-------------+
|id|period_start|period_end|period_number|
+--+------------+----------+-------------+
|1 |0001-01-01  |2010-12-31|1            |
|1 |2011-01-01  |2011-12-31|1            |
|1 |2012-01-01  |2012-12-31|2            |
|1 |2013-01-01  |2013-12-31|3            |
|1 |2014-01-01  |9999-12-31|3            |
+--+------------+----------+-------------+

In this case, any query will retrieve one and only one row, e.g:

select id, period_number from period_table 
where '2013-11-20' between period_start and period_end

+--+-------------+
|id|period_number|
+--+-------------+
|1 |2            |
+--+-------------+

select id, period_number from period_table 
where '2010-11-20' between period_start and period_end

+--+-------------+
|id|period_number|
+--+-------------+
|1 |1            |
+--+-------------+

select id, period_number from period_table 
where '2014-11-20' between period_start and period_end

+--+-------------+
|id|period_number|
+--+-------------+
|1 |3            |
+--+-------------+