Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: SQL for a Weekly Summary?
 
From:   de Wet, Johannes M

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