Hello,
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 (???) ?

Thanks,
GB

[Message not available]

This is the DDL:
create table MyTable (d datetime, x real)
insert into MyTable values('20060101', 15.75)
insert into MyTable values('20060201', 16.5)
insert into MyTable values('20060301', 16.5)
insert into MyTable values('20060401', 16.5)
insert into MyTable values('20060501', 16.5)
insert into MyTable values('20060601', 16.5)
insert into MyTable values('20060701', 16.0)
insert into MyTable values('20060901', 13.5)
insert into MyTable values('20061001', 13.)
insert into MyTable values('20061101', 12.)
insert into MyTable values('20061201', 11.5)
insert into MyTable values('20070101', 11.)

I need the output like this:
Datadate   Flag
------------------
20060301   1
20060401   1
20060501   1
20060601   1
20060701   1
20060901   0
20061001   0
20061101   0
20061201   0
20070101   0


Thanks,
GB
Show quoteHide quote

select * from(
select t1.d,
case
  when (select top 1 x from MyTable t2 where t2.d < t1.d and t2.x <>
t1.x) < t1.x
    then 1
  when (select top 1 x from MyTable t2 where t2.d < t1.d and t2.x <>
t1.x) > t1.x
    then 0
  else null
end md
from Mytable t1) t
where md is not null

correction:

select * from(
select t1.d,
case
  when (select top 1 x from MyTable t2 where t2.d < t1.d and t2.x <>
t1.x order by d desc) < t1.x
    then 1
  when (select top 1 x from MyTable t2 where t2.d < t1.d and t2.x <>
t1.x order by d desc) > t1.x
    then 0
  else null
end md
from Mytable t1) t
where md is not null

I added "order by d desc" for both subqueries

Thanks!

Show quoteHide quote

GB
Assuming that 'd' is a PRIMARY KEY.

create table MyTable (d datetime, x real)

insert into MyTable values('20060101', 15.75)
insert into MyTable values('20060201', 16.5)
insert into MyTable values('20060301', 16.5)
insert into MyTable values('20060401', 16.5)
insert into MyTable values('20060501', 16.5)
insert into MyTable values('20060601', 16.5)
insert into MyTable values('20060701', 16.0)
insert into MyTable values('20060901', 13.5)
insert into MyTable values('20061001', 13.)
insert into MyTable values('20061101', 12.)
insert into MyTable values('20061201', 11.5)
insert into MyTable values('20070101', 11.)


select case when x<=x1 then 1 else 0 end
from
(
select * ,( select top 1 m.x from MyTable m where m.d>MyTable.d) as x1
from  MyTable
) as der



drop table MyTable
news:hIZmg.77517$S61.8993@edtnps90...
Show quoteHide quote

Uri,

I agree that your query looks nicer than mine, thanks! Yet because
TOP(n) without an ORDER BY is unpredictable, I think your query should
be corrected as follows:

select * ,( select top 1 m.x from MyTable m where m.d>MyTable.d ORDER
BY D DESC) as x1

What do you think?

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.

Datetime comparison problem
paramater values inside IN clause
creating Powerpoint presentations (etc) from SS 2000 T-SQL
Multiple Inserts in the Same Stored Procedure
SQL 2005 updating data in SQL 2000 DB via linked server returns error