Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Question on the use of a stored procedure
 
From:   Dieter Nöth

Hi Frank,

  The main SQL that runs once for the procedure runs 1/2 hour > by itself. What happens is that the results of the main SQL > returns a set of ordered sets, descending by date, each of > which is processed, looking for the point at which a figure > falls to or below zero. The first row where the figure is zero > should be included in the set, but not subsequent rows.  


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);

  Wish I had my (was that two or three copies I've given away?) copy of SQL for Smarties by Celko.  


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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016