sql server programming: Remember Zeno and Einstein from your freshman college days? Correct
your temporal model to show durations instead of points in time.
Remember the SQL-92 standards? Correct your date formats to ISO-8601.
Remember basic netiquette? Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential Integrity,
datatypes, etc. in your schema are. You have posted her before and know
this.

CREATE TABLE Foobar
(start_date DATETIME NOT NULL PRIMARY KEY,
end_date DATETIME NOT NULL,
foo_value INTEGER NOT NULL,
CHECK (start_date < end_date),
... );

[quoted text, click to view]
given date. <<

We call this "yesterday" and compute it with the function DATEDIFF(D,
-1, my_date) is the answer to your question as you wrote it.

The most recent starting date, which is probably what you meant, is

SELECT F1.*
FROM Foobar AS F1
WHERE F1.end_date
= (SELECT MAX(F2.end_date)
FROM Foobar AS F2
WHERE F2.end_date < F1.end_date);

Look up a book on temporal queries by Rick Snodgrass; it is in PDF at
his website at the University of Arizona.

--CELKO--


*** Sent via Developersdex http://www.developersdex.com ***

Here's how you would get the value for the last date preceeding April 1
2004:

SELECT col_value
FROM YourTable
WHERE col_date = (SELECT MAX(col_date)
FROM YourTable T1
WHERE T1.col_date < '20040401')


[quoted text, click to view]

Suppose I have a changing value across time as following:

col_date col_value
1 Jan 2004 17
1 Feb 2004 25
1 March 2004 64
1 April 2004 15
1 May 2004 43
1 June 2004 50


etc.

I want a query to give me the value at the last date just before any given
date.

i.e. given any date (1 April 2004 say), what is the last value of the
variable before (1 April 2004)?
The answer is the value at (1 March 2004) which is 64 in this example?

Thanks in advance.