CODEWITHSUNDEEP
sqloracle-database
CigarDoug
CigarDoug

Reputation: 1576

Sum data by week in Oracle SQL

I have an Oracle table (Oracle 11g) that contains hours of overtime worked (WRK_HR) and the date worked (CHRG_DT). I want to build a stored procedure that returns the sum of WRK_HR for Saturday through Friday, each Friday, from the start of the project to the date provided. Say my table contains the following:

CHRG_DT   WRK_HR
--------  ------
01/01/14       4
01/02/14       8
01/15/14       7

My output should look like this (each date is a Friday):

CHRG_DT   TOTAL
--------  -----
01/03/14     12
01/10/14     12
01/17/14     19

Here is the SQL I have:

PROCEDURE GET_OVERTIME(  
  p_chrg_dt IN VARCHAR2,  
  cur OUT sys_refcursor  
)  
IS  
BEGIN  
OPEN cur FOR  
  SELECT  
  TO_CHAR(CHRG_DT, 'mm/dd/yyyy') AS CHRG_DT,  
  SUM(WRK_HR) AS TOTAL  
  FROM OVERTIME
  WHERE TRUNC(CHRG_DT) <= TO_DATE(p_chrg_dt, 'mm/dd/yyyy')
  GROUP BY CHRG_DT
  ORDER BY CHRG_DT;
END;

Here is the result from my SQL. As you can see, it gives me a result for each unique day. I need it to total on every given Friday, even if there was no entry that Friday:

CHRG_DT   TOTAL
--------  -----
01/01/14      4
01/02/14     12
01/15/14     19

Upvotes: 1

Views: 1992

Answers (2)

Ren&#233; Nyffenegger
Ren&#233; Nyffenegger

Reputation: 40499

With this set of test data....

 create table tq84_sum_data_by_week (
   chrg_dt   date,
   wrk_hr    number
 );

 insert into tq84_sum_data_by_week values (date '2014-01-01', 4);
 insert into tq84_sum_data_by_week values (date '2014-01-02', 8);
 insert into tq84_sum_data_by_week values (date '2014-01-15', 7);

... the following should do: 

 with fridays as (
 --
 -- 1 First, we need to "create" all fridays that we want
 --   the report to sum on
 --
   select
 --
 -- 2 The first friday is 01/03/14
 --
     date '2014-01-03' + 
 --
 -- 3  The fridays are 7 days apart:
 --
    (level-1) * 7 date_
 --
   from dual
 --
 -- 4  Three fridays are sufficient
 --    for this small test.
 --
     connect by level <= 3
 )
 --
 select 
   fridays.date_,
 --
 -- 7  This expression does the 'cumulative sum':
 --
   sum(sum(weeks.wrk_hr)) over (order by fridays.date_)  total_hrs
 from 
 --
 -- 5  Since we want a record for *each* friday,
 --    we use a 'left join':
 --
   fridays               left   outer join
 --
   tq84_sum_data_by_week weeks
 --
 -- 6  next_day(..., 'friday') returns the next
 --    friday for a date which we need for the
 --    join:
 --
   on fridays.date_ = next_day(weeks.chrg_dt, 'friday')
 group by
   fridays.date_;

Cleaning up:

 drop table tq84_sum_data_by_week purge;

Same thing on SQL Fiddle

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52346

You just need to convert your day values to be the end-of-week, and select and group by that value.

Subtract one day from them, and use NEXT_DAY() to get the next friday ...

NEXT_DAY(CHRG_DT-1,'FRIDAY')

Upvotes: 3

Related Questions