Archives of the TeradataForum

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

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

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