Closed. This question needs details or clarity . It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post .

Closed 8 days ago.
Improve this question

I have a table ab_relationship that describes a relationship between A and B with an end date of that relationship, or NULL in the end column if the relationship is still valid. When doing manual queries on that database it is common to want to find which relation is valid at a given time.

So I am searching for the best way to add an other column "start" that contains the start date which is either NULL if it is the first entry for a given A or the end date for the last entry of the given A.

To be clear I do not want to permanently alter the table ab_relationship I want to create a temporary view or table to use in subsequent queries.

Old table

a_id    b_id    end
  ----    ----    ---
  1       10      2000-01-01
  2       10      2000-06-01
  1       30      2010-01-01
  3       20      NULL
  2       20      NULL
  1       10      NULL

Desired Result:

a_id    b_id    start      end
  ----    ----    -----      ---
  1       10      NULL       2000-01-01
  2       10      NULL       2000-06-01
  1       30      2000-01-01 2010-01-01
  3       20      NULL       NULL
  2       20      2000-06-01 NULL
  1       10      2010-01-01 NULL

It would be ok if NULL values in the end column get replaced with a value like 9999-12-31

Ideally the solution should work both on SQLite and SQL Server.

Why do I want to add the start column?

When I use the table as is and join it with other tables, which contain the dates of interest, I obviously get multiple lines for every given A. Filtering lines with an end date smaller than the desired point in time is easy with a simple WHERE statement. But to find the remaining smallest end date I currently need to do subqueries on the joined table which has millions of rows.

Having a start date, would reduce the filtering to two simple where statements.