Joe Celko s SQL for Smarties - Advanced SQL Programming P71. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended. | 672 CHAPTER 29 TEMPORAL QUERIES The real trick here is that the start_date is a date data type so it will be at 00 00 when it is converted to a timestamp. The end_time is a timestamp so we can place it almost but not quite to the next day. This will let us use between predicates as we will see in the next section. You could also do this in a view make both columns date data types and add the extra hours to end_time. In practice this is going to be highly proprietary code and you might consider using triggers to keep the start_time end_time pairs correct. Using Duration Pairs If the table did not have start_time end_time pairs but used a code to identify the start and finish points of durations then we need to use a self-join to construct the pairs. For example how would you write a SELECT query for returning all Projects whose current project status is 10 given the following schema CREATE TABLE Projects proj_id INTEGER NOT NULL PRIMARY KEY proj_name CHAR 15 NOT NULL CREATE TABLE ProjectStatusHistory project_id INTEGER NOT NULL REFERENCES Projects proj_id proj_date DATE DEFAULT CURRENT_DATE NOT NULL proj_status INTEGER NOT NULL PRIMARY KEY project_id proj_date A solution from David Portas which assumes that the project is still active SELECT FROM Projects AS P WHERE EXISTS SELECT FROM ProjectStatusHistory AS H WHERE HAVING MAX CASE WHEN 10 THEN proj_date END MAX proj_date Calendar Auxiliary Table 673 But now try to answer the question Which projects had a status of 10 on a prior date SELECT FROM SELECT AS start_date MIN AS end_date FROM Projects AS P1 LEFT OUTER JOIN Projects AS P2 ON AND WHERE proj_status 10 GROUP BY AS X proj_id start_date end_date WHERE my_date BETWEEN AND COALESCE CURRENT_DATE The X subquery expression is what Projects would have .
