Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 24 Oct 2011 @ 19:18:44 GMT


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


Subj:   Aggregate previous four weeks of data
 
From:   DUELL, BOB

Hi,

Suppose I have a table with two columns: ACT_DATE and ACT_COUNT. What is the easiest way to get the sum of the counts for the previous four weeks?

I'm looking for something like the following:

     Select sum(case when ACT_DATE between TARGET    and TARGET+6  then ACT_COUNT
     else 0 end) as WEEK1
          , sum(case when ACT_DATE between TARGET-7  and TARGET-1  then ACT_COUNT else 0 end) as WEEK2
          , sum(case when ACT_DATE between TARGET-14 and TARGET-6  then ACT_COUNT else 0 end) as WEEK3
          , sum(case when ACT_DATE between TARGET-21 and TARGET-13 then ACT_COUNT else 0 end) as WEEK4
     From   my_table a
     Full join (
        select calendar_date as TARGET
        from   sys_calendar.calendar
        where  day_of_week = 1
           and calendar_date between current_date-6 and current_date
        ) b
     On a.ACT_DATE=b.TARGET

Of course this doesn't work (I don't know how to reference TARGET, "full join" was just a guess). There's likely a simple answer but I'm stumped!

Help?


Thanks,

Bob



     
  <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