![](//teradataforum.com/imgs/ptdfw1.gif) |
![](//teradataforum.com/imgs/ptdfw2.gif) |
Archives of the TeradataForum
Message Posted: Wed, 16 Feb 2005 @ 23:25:19 GMT
Subj: | | Re: Changing oracle code to teradata |
|
From: | | Anomy Anom |
<-- Anonymously Posted: Wednesday, February 16, 2005 18:23 -->
Not that it is any simpler or faster, but if you wanted to do this in plain SQL....
/****************************************************************
* 1. Determine latest weekday in a derived table (x).
* 2. & 3. Determine the count for all days with the same
* weekday in a derived table (y).
* 4. Calculate mean, latest count, and variance using case.
****************************************************************/
select last_date
, avg(case when Bus_date < last_date then cnt end) as mean
, sum(case when Bus_date = last_date then cnt end) as rec
, (rec - mean)*100/mean as result
from (
select Bus_date, last_date, count(*) cnt
from payments
, (select max(Bus_date) last_date from payments)x
where Bus_date > (last_date - 240)
and Bus_date <= last_date
and (last_date (format 'e3')) (char(3))
= (Bus_date (format 'e3')) (char(3))
group by Bus_date, last_date
)y
group by last_date;
| |