Archives of the TeradataForum
Message Posted: Mon, 24 Oct 2011 @ 19:18:44 GMT
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!
|Copyright 2016 - All Rights Reserved|
|Last Modified: 15 Jun 2023|