Archives of the TeradataForum

Message Posted: Wed, 10 Mar 2004 @ 13:09:35 GMT

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

Manish,

You could use the System Calendar table to do summaries by week.

In the example below (there may be other ways to do this is well), the first join sums the Check amounts by week and the second join determines Start and End dates for each week.

SELECT
        scm.week_of_calendar
        ,min(scm.calendar_date) AS Week_Begin_Date
        ,max(scm.calendar_date) AS Week_End_Date
        ,sum(a.check_gross_amt)
FROM
        clmdb.claim_payment a
JOIN
        sys_calendar.calendar sc
        ON
        a.payment_date = sc.calendar_date
JOIN
        sys_calendar.calendar scm
        ON
        sc.week_of_calendar = scm.week_of_calendar
GROUP   BY 1
ORDER   BY 2 DESC;

Johannes de Wet
UnumProvident

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