Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>  
Next> Last>>  

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


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

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!




  <Prev Next>  
Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023