time-series calculation logic question

SQL Apprentice Question
I have a simple table of 2 columns: datadate ( datetime) like '2006-01-31'
and x (real).
I am trying to implement the following logic:

If x(t-1) > x(t-2) then Flag(t) = 1
If x(t-1) < x(t-2) then Flag(t) = 0
If x(t-1) = x(t-2) then Flag(t) = Flag(t-1)


So, t is datadate column value here.


I am trying to do that as:


SELECT DATEADD(MM,2,T2.datadate) as datadate,
Flag = (CASE
WHEN T1.x > T2.x THEN 1
WHEN T1.x = T2.x THEN (???)
ELSE 0
END)
FROM MyTable T1
LEFT OUTER JOIN MyTable T2
ON DATEADD(MM,1,T1.datadate) = DATEADD(MM,2,T2.datadate)


My question is : what I need to put instead of (???) ?



Celko Answers
This is a good example of why time is modeled as a duration or as
changes, but not as static points. Your model is always going to
require complex outer self-joins to get a simple fact about the delta
in your measurements because that fact is split over at least two rows
in the same table. Why not store the whole fact in one row to start
with? Then your query is trivial.

CREATE TABLE SampleDeltas
(sample_time DATETIME NOT NULL,
prev_measure REAL NOT NULL,
curr_measure REAL NOT NULL);


INSERT INTO SampleDeltas VALUES ('2006-01-01', 0.00, 15.75);
INSERT INTO SampleDeltas VALUES ('2006-02-01', 15.75, 16.5);
INSERT INTO SampleDeltas VALUES ('2006-03-01', 16.5, 16.5);
INSERT INTO SampleDeltas VALUES ('2006-04-01', 16.5, 16.5);
INSERT INTO SampleDeltas VALUES ('2006-05-01', 16.5, 16.5);
INSERT INTO SampleDeltas VALUES ('2006-06-01', 16.5, 16.5);
INSERT INTO SampleDeltas VALUES ('2006-07-01', 16.5, 16.0);
INSERT INTO SampleDeltas VALUES ('2006-09-01', 16.0, 13.5);
INSERT INTO SampleDeltas VALUES ('2006-10-01', 13.5, 13.0);
INSERT INTO SampleDeltas VALUES ('2006-11-01', 13.0, 12.0);
INSERT INTO SampleDeltas VALUES ('2006-12-01', 12.0, 11.5);
INSERT INTO SampleDeltas VALUES ('2007-01-01', 11.5, 11.0);


This is the table that you construct everytime on the fly at greater
and greater expense.