Archives of the TeradataForum
Message Posted: Wed, 22 Jan 2003 @ 11:33:32 GMT
All rows up to the first <= 0 should be included? And if there's no row <= 0 all rows of that set?
234 * 333 * 2 * -77 * no rows after this? 2 -11 sel * from sales s1 where salesdate >= (select max(case when s2.salesamt <= 0 then salesdate else date '1900-01-01' end) from sales s2 where s1.storeid = s2.storeid and s1.prodid = s2.prodid ) order by 1,2,3 desc
This is probably slow, when sales is a complex query and no table. But you could materialize it in a volatile table.
Another way is to use OLAP-functions:
sel storeid ,prodid ,salesdate ,salesamt from ( sel storeid ,prodid ,salesamt ,salesdate ,sum(salesamt) over (partition by storeid, prodid order by salesdate desc rows 1 preceding) - salesamt as prevsalesamt from sales )dt qualify sum(case when prevsalesamt > 0 then 0 else 1 end) over (partition by storeid, prodid order by salesdate desc rows unbounded preceding) = 1 ;
Would be easier, if the first <= 0 row was excluded.
Which one is faster depends on many factors...
Sales is a slight modification of a table i use for SQL training classes:
create multiset table sales as ( select storeid ,prodid ,salesdate ,(case when random(1,50) = 1 then -1 else 1 end) * sum(salesamt) as salesamt from ( select cast(random(1,10) as smallint) as storeid ,random(1,50) + random(0,50) as prodid ,date '2002-01-15' + random(0,99) as salesdate ,cast(random(1, 100) * random(1,100) as dec(10,2)) as salesamt from sys_calendar.calendar )tmp group by 1,2,3 ) with data primary index(storeid, prodid);
Forget about it for that problem, it doesn't deal with OLAP-functions when it comes to runs and sequences.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|