Archives of the TeradataForum

Message Posted: Wed, 22 Jan 2003 @ 11:33:32 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Frank,

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.

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>